Modules & VBA :: Data Validation With Comboboxes
Jan 23, 2015
I have 2 combo boxes on a form. The 2nd box is not visible unless the 1st box is set to a specific value (Illness). Once the specific value is set, the 2nd box appears with appropriate data for selection.
Goal #1:
I would like to have the form checked before it is closed to ensure that if the condition in box 1 is "Illness" then the 2nd box must have a value and can not be null.
Goal #2:
I am also having problems with the 2nd combo being visible when form opens or becomes current. The field is set to visible=false normally but needs to be visible when form opens if the conditions mentioned above are meet (true).
Private Sub Form_Unload()
If IsNull(Me.Incident_Classification) Then
MsgBox "Please select a type of Illness"
Me.Incident_Classification.SetFocus
]Code] .....
View Replies
ADVERTISEMENT
Jun 12, 2013
I would like to write some code to validate that when a user enters data into a text field, the user must enter 4 digits.
View 3 Replies
View Related
Feb 15, 2015
I am using following routine to lift data from Excel files into Access tables. Whole thing works, well, most of the time. The only issue I have is the spreadsheets are received from warehouses and even though they have been given strict instruction to stick to the template, I have had to adjust the spreadsheets. Amongst errors I get are:
Field 'F16' does not exist in table 'SA1'. (In this case I simply delete the last most empty column to fix this).
Or there are column name spellings and in such cases, I get no error and the simply code hangs.
Is there any routine that I could incorporate in the code that clearly states what issues are being experienced. This way I can pass the db to the user to run it themselves.
'Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb
On Error GoTo Macro1_Err
DoCmd.SetWarnings False
' RunSQL executes a SQL string
[Code] .....
View 7 Replies
View Related
Aug 13, 2015
There are 4 unbound dropdown list box that will have a default value of empty string. All 4 must have values entered (data is required).Once all 4 are required - the bottom 5 text box will become enabled (optional data).Forcing the user to click on a Validation Button
Some users type then use the mouse to select the next.Other users type to select then tab.The trick is: After filling in the 4th listbox and validating all 4 have values, the event must trigger code to enable the 5 optional text boxes below.This provides a seamless data entry environment.
I have been doing this in other places, but the transisition after the last required field is complete gets ugly.If Trim(cmbBoxname1 & "") = vbNullString ' check all 4..If it is the 4th one, the event seems not to happen quick enough for the bottom 5 text boxes to be enabled so the user can continue with data entry.
View 9 Replies
View Related
Nov 18, 2014
I have a form with many comboboxes. The comboboxes need to have their original rowsource set to a table in my database. However, when the user selects a value in a combobox, it is programatically fed into a SQL string which serves to populate a subform. This part all works great.
However, I'm trying to reset the comboboxes as well so that options will "narrow down" until only a single option is possible. So far, I have:
Code:
Private Sub cmb_Author_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode,
[Code] ....
GetWhere() is the portion that feeds info from the comboboxes to the Finder string.
The red highlighted bit is the part I'm working on. Obviously, once I figure out the proper syntax, all of the other
comboboxes will be set up the same way.
What's funny is that in testing (all of the data currently input is simple and allows me to select options where I know what the response will be in other boxes), when I change Author and go to use cmb_RecordName, it replies that "the record source 'My Data' specified on this form or report does not exist" (where 'My Data' represents real data).
So, clearly, it is finding the data, just not using it as a record source, but rather believing that the name of the recordsource is the value.
View 9 Replies
View Related
Feb 20, 2015
how I can get GotFocus and LostFocus of all comboboxes. See below the three codes (code 1, 2 and 3) that I've change from an example from the internet. But the problem is that the function "InitialiseEvents" is not running in these comboboxes. If I try the code 4 as an example, the function "InitialiseEvents" is running perfectly. I don't understand why I can't get GotFocus and LostFocus? You can to see two attachments:
Afbeelding1: The codes 1, 2 and 3 are running.
Afbeelding2: The codes 1, 2 and 4 are running.
Code 1:
Code:
Private Sub Form_Open(Cancel As Integer)
InitialiseEvents Me
End Sub
Code 2:
Code:
Public Function InitialiseEvents(frm As Access.Form)
Dim ctl As Control
For Each ctl In frm.Controls
With ctl
If .ControlType = acComboBox Then
.OnGotFocus = "=HandleFocus('" & frm.Name & "', '" & .Name & "', 'Got')"
[code]...
View 7 Replies
View Related
Jun 11, 2006
I'm trying to make my database easy as possible for everyone to use while protecting against potential typos and the likes. I then had an idea and wanted to make sure if that was in line with accepted practices everywhere else. After all, just because you can do it, doesn't mean you should.
Would it be reasonable to have fields that would likely to have repeating data across various records to be a comboboxes. The fields I'm thinking of are City and ZIP code, which I would start with some more common ones, and allow users to add new ones while having the benefit of auto-expand as spell checker and reduce possiblity for bad data.
I'm not sure what ramifications that may have on forms beyond the fact that more lookup tables=slower database, especially over the network.
So would you want to make city or zip code or other similar fields combobox instead of a bound textbox?
View 4 Replies
View Related
Jul 8, 2015
I am running an export function from a module1 that contains a loop nested within a second loop. Each loop is running through items in a separate combobox on a single form1. The outside loop goes through combo1 items and the inner loop goes through combo2 items.
My issue is that the value of combo1 determines what items are available in combo2 (values are tied to tables). I can get the combo2 values to update when a user changes the values in combo1 (using requery in the afterupdate property of the combo1). However, I do not want a user to change the values, and the code module1 is ignoring the requery. How to force the combobox to requery through code in a module?
View 8 Replies
View Related
Sep 9, 2013
I'm making an accounts package.
I've made a graph of revenue by customer but due to the number of customers it's a bit hard to see the customer names.
So I would like to be able to select my own custom group of 5 customers to plot on the graph.
To do this I think I need a query that selects the 5 customers which is populated from 5 comboboxes on a new form that the user can select customer names from but I'm not sure of the VBA that links all this together.
View 2 Replies
View Related
Jun 25, 2006
Hello, I'm trying to use information based on items selected in a combobox in the main form. Once the user selects the desired info in the comboboxes on the main form, I want the subform to display the data that matches the search criteria in the main form. For instance, if I select a job number, I want everything that matches that job number to be displayed the subform (along with the corresponding job name, version, etc.). I also want to get more specific in a search where I could select a job number, job name, and job version from the comboboxes in the main form to where all the data that matches the search criteria in the main form will be displayed in the subform.
Attached is what I have so far. The form I'm trying to perform this in is the SearchByResults form and the subform is the qryk95 form. I'm trying to populate this using the K95-Template table. I performed a query (qryk95) to select only the fields I want to display in my subform. Any help would be much appreciated.
View 6 Replies
View Related
Mar 27, 2013
I tried and failed to get this to work using a multiselect listbox..I have a list of departments in tblFunctionalArea...My main table is tblStatic..I want to be able to for each record select multiple departments affected by a record and store them in the tblStatic.After looking around i couldn't find many people successfully maanging to store listbox values in a table...
I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields.I want to be able to ensure the list for any combo box requeries and takes out any selection in the other boxes.
I have this working in a strict cascade fashion i.e. in cbo1 all dept's visible, in cbo2 it takes off whatever was selected in cbo1 etc. But if someone then jumps back and deletes the content of cbo3 then the whole thing breaks or if they amend in the wrong order it breaks
View 2 Replies
View Related
Jul 24, 2014
I have the following that I found in a post on Keypress of a text field to limit the field to only numbers. This works great only I need to disallow the entry of zero.
If KeyAscii <> 8 Then ' Not Backspace (important for error correction)
If KeyAscii < 48 Or KeyAscii > 57 Then 'Not a number
Beep 'Let the user know they hit an illegal key
KeyAscii = 0 'Don't let the keystroke through
End If
End If
View 14 Replies
View Related
Dec 14, 2004
Hi!
I have a Question about validation rules. I have a table with a field where the values can either be 3 numbers (like 321,245) or 4numbers separated by . (like 321.4). Other employees update these fields so it is importatant that they can't put in text etc.
In the field for validationrule I put the text (Select Distinct [fieldname] from table) but that did not work. Do anybody know an easy way to set the validation rule?
Thanks :eek:
View 1 Replies
View Related
Feb 22, 2005
I am trying to validate a text field (named Customer_Surname) and i want to make it so that only letters can be entered into the field and no other numbers or symbols. Is there any way this vlaidation can be done and if so, how?
Cheers
View 1 Replies
View Related
Apr 3, 2014
I have one case about validation in the form
example: I have entry in table this mention below:
No.Register | Code_machine
________________________
001 | X01
002 | X01
001 | X02
003 | X02
003 | X01
002 | X03
How to make validation for this case when I will submit the data 001 and X01, the system will be cancel and show warning message : "Data Duplicate!".and of course with this happen, the user will be know what they will submit, it have been submit into table before by other user. if I will do make with one validation like noregister as checking data and use the DLookup, this is no problem.
View 6 Replies
View Related
Jul 10, 2015
I've tried to steal an SQL Postcode validator (This is for UK postcodes) and add it to an AfterUpdate event on a field in MS Access. It doesn't work, obviously,The VBA code is:
Private Sub Postal_Code_AfterUpdate()
Dim CHECK_Code As Integer
Dim LResponse As Integer
If InStr("[A-Z][0-9] [0-9][A-Z][A-Z]", Postal_Code) = 0 Then
CHECK_Code = 0
[code]....
Also, how would I put the Check_Code value into the Message box to see what it's bringing back?
View 8 Replies
View Related
Dec 1, 2004
Hi there!
I wonder if someone can help me please? I have an Access 2000 database used to record the daily treatments received by patients in our Intensive Care Unit. The database consists of 2 tables;
Table 1 has these fields; [First Name], [Surname], [Hospital Number], [ICU Admission Number] (Primary Key), [Date of admission], [Date of discharge].
Table 2 has these fields; [Date], [ICU Admission number], and various treatment fields requiring a true or false response.
The tables are linked through the [ICU Admission Number] field.
I enter data in the first instance through Table 1 and then click on the '+' in the far left hand column to access Table 2 for each record (Patient), whereupon I enter the daily treatments - each record in this table accounting for 1 day on the unit (i.e. there must be no duplicate values).
When I open Table 2 I need three things to happen to try and avoid mistakes being made.
(1) The first record in the [Date] field must only be equal to the [Date of admission] in Table 1. It must not be possible to enter anything else
(2) In subsequent records in Table 2 the date must increase by one day until it equals the [Date of discharge] in Table 1.
(3) Once this value has been reached it must not be possible to enter subsequent dates or duplicate dates.
If anybody can help me with this query I would be delighted to hear from you. I have tried fiddling around with Validation rules but have had no luck.
If you can help then that would be fantastic.
Best regards
Usr33t
View 3 Replies
View Related
May 9, 2007
I am trying to validate data in a table.
I have two fields, status and completion date.
What I want is the following:
If [status] field = "Complete" then [completion date] cannot be null.
I don't know if doing this in a table of the form is best using the forms Before Update Event, but I am having trouble figuring how to write it using the expression builder.
Can anyone help or offer suggestions?
View 8 Replies
View Related
Oct 30, 2007
I have a table embedded in a form that data is entered on. The data to be entered is a project number, date, and a numerical value. I want the date to be entered only be on a Sunday. Is there a data validation rule to apply or do I need to use VBA? I do not know VBA!
View 3 Replies
View Related
Apr 12, 2008
Hello,
I am want to validate the data in a table column as per the data in a corresponding column in teh same table.
Ex. I have a table with column "Frequency" of datatype Text.
And other 12column are in the name of the months. i.e. Jan, Feb etc of datatype "Yes/No" in checkbox format. Default value as false.
I have only 4 different values i.e. Yearly, Half Yearly, Quarterly And Monthly.
If the value is Yealy then I should able to change the value on Only "Jan" from False to True.
Similarly If the value is Yearly then I should able to change the value on Only "Jan" & "July" from False to True.
Same holds true for Quarterly changing the values only of March, June, Sep & Dec from false to true.
Can anyone guide me to do this would be great.
Thanks
Sudhir
:(:(:confused:
View 4 Replies
View Related
May 16, 2005
Hi,
I am using the following validation code for the part number field on one of my forms:
DLookUp("[Door Part Number]","2-MAIN DOOR PART DATABASE","[Door Part Number] = '" & [Door_Part_Number] & "'") Is Null And Not ""
The purpose of this is to prevent users from changing/creating parts with a number that already exists in the database (and the last bit prevents no part number being entered).
It is fine for most situations except 1. If the user changes a part number, then changes it back to the original number again, the validation rule thinks this already exists and forces the user to give another part number.
Can anyone suggest how to get around this problem? It is hopefully very simple, but I can't get my head round how to do it!
Many thanks in advance!!
John
View 1 Replies
View Related
Feb 26, 2006
I'm making a student report card. Teachers enter data and then I print them all.
Sometimes data is missed in a field or it's deliberately left empty becasue the data doesn't apply to that student.
I'd like to generate a rpt that shows empty fields. Basically, I'd like a list of student names and the field names that are empty/null so that I can approach teachers and ask them if it was deliberate on not before I print. With 100 or so fields, I can't seem to nut out a query to make it all work.
Any ideas??
View 1 Replies
View Related
Apr 26, 2006
Hi - I have an inventory management database allowing stock to be moved between various subinventories. The user choose a transaction type and then details which goods to move. However at the moment they could enter a Quantity that is greater than that available in that subinventory.
So I have a query that gives me:
Part#
Subinventory
OnHandQty
The data entry form has a control source directly to the Inventory Table. I want to compare a text field [Qty] on a new inventory transaction (the person already having selected Part# / SubInventory from combo boxes) to the On Hand Qty.
I have tried Expression Builder in the Field Validation Property and I have tried some sql code on the AfterUpdate of the text box. I'm just not getting it.
Any help appreciated.
Cheers,
Darren
View 3 Replies
View Related
Feb 15, 2008
I am an absolute beginner and urgently need help with a validation rule. I need to know how to set a rule to set a minimum and maximum length for a text field record. I want minimum to be 4 and maximum to be 15 (I know I can set a max field size and set to not null but not sure how to make a minimum validation rule.
I am a teacher (not ICT!) having to cover for a sick colleague and students have asked me how to do this and although I am working my way through many of the great learning resources you link to here I have not yet worked out how to do this.
Would really appreciate any help you can give me so I can do my best by the kids.
J
View 4 Replies
View Related
Jul 26, 2013
I have a lookup which selects a 'Recipient'.
I need to add a check to see if this 'Recipient' (user selected) already exists within another table.
If so, I need a pop-up to appear.
If not, I want the user to continue unimpeded.
View 8 Replies
View Related
Dec 18, 2004
I have a field that looks up the JobType using a LookUp Combobox from another table where these records for JobType will never change.
I want the user to only be able to select from the Combobox for the job type they want and be able to type the beggining of the job type so that the desired job will pop up. Now, i need the field to not be able to accept any other data than what is in the LookUp, so that they cannot enter false values. Duplicates of jobtypes are possible so a primary key cant be used.
Any help guys?
-thanks
View 1 Replies
View Related
