validation entry

Hey guys.. i'm newbie in access, i just want to ask how to make validation data entry from the form entry using a VBA script.
so what i want to do is when i click the save button, if one of the textbox is empty.. the program will notify the user that the one of the field entry is empty and need to be filled
Here's what i got :

table "info" with fields: no,name,price
and what i got so far on my VBA code is like this:

Private Sub Command15_Click()

Set infodb = CurrentDb().OpenRecordset("info")
If Me!nametxt = "" Then MsgBox "write your name!"
infodb!no = me!infodb_txt

End Sub

and i keep getting message "ELSE without If" could u guys tell me the correct script?

Post your answer or comment

comments powered by Disqus
Hi all,

I have a problem I hope I can get some help on. I want to be able to validate data when entered using a list of valid entries stored in a table, but can't seem to work out how.

The main tables in my (simplified) example database are:

Tissue Samples
Sample ID/River (and lots of other fields in the read db)
eg data

River List
eg data

When I enter records into my Tissue Samples table I want to run a check on the river name using a list of valid entries from the River List table. It seems such a simple thing to want to do, but I just can't work out how.

Data is entered into the table using a form which is linked to a third table:

Tissue Samples new data
Sample ID/River

Users open this new data form/table and paste the data in (often hundreds of records). Then run a macro that will:
1) Append new records to the Tissue Samples table if the ID on these records is not already in this table (using an append query)
2) Update records which have their ID already in the Tissue Samples table with new data in their various fields (overwriting what is already in there if necessary, or just filling in missing data in empty fields) (using an update query)
3) Clear the new data table for further use later (using a delete query)

What I want to do is that when I run the append/update/clear macro the river names are checked and if the river name is not in the Rivers List a message appears telling the user that there are records that do not match this validation. I would then like the rest of the correct data to be added to the Tissue Samples table, and the problem records stored in a 'Paste Errors' table so the user can go through and make corrections.

Although I am a bit of a noob, I have been able to get simple validation rules working, but can't seem to work out how to validate against fields in a separate table. I thought it would be a common requirement, but lots of searching has led me no-where (although it did lead me to this very useful forum from which I have already learnt a lot!).

I have attached the example db, and I hope I have given enough info, and that some kind soul can help me.
Thanks in advance

I'm new to access so I thought this would be easier than it is. I have a list of cities in a simple table . I want use this list (or a query of it) to create a validation rule for a field in a second table "City". How, either with VBA or some other method do I use the Cities database to validate entry. These tables are both in the same project.

Any help is appreciated.

Is it possible to validate entry in a text box against a table

So for example I enter a number 45678 and it looks this up in tbl Serial Number?

I can do so with a combo box but would like to avoid that if possible



Is there anyway to force/validate entry into a subform from the Parent?

I want to force the data input to be done in a specific order(the forms tab order)

I suppose the simplest way would to prevent the mouse from interacting with form completely. and force the data in-putter to rely solely on the Keyboard. (a few googles revealed no clues as to how this might be achieved)

I could do an after update event for each control which sets all subsequent controls in the tab order to Null

but i dont want the data in-putter to miss control one and enter a dozen more fields Only to be told that he cant save the record because control 1 is required and he missed control One and then when he enters control one on the form all the subsequent fields require re-entry. It would annoy the hell out of the data in-putter.

So logically i'm thinking as well as writing the afore mentioned Afterupdate event event for each control I would have to write some sort of before update event for each control which checks that all previous controls in the tab order contain a valid entry.

But now were talking about doing quite a bit of code (which I'm not averse to if needs must), but my gut tells me that less code is better code.

Is this not a reasonably common Issue of sorts?
perhaps I cant find the answer cos Im still very green with access and VBA

all sugesstions help or advice much appreciated

I have a continuous form based on a query. It basically gives me seven rows on my form (one for each day of the week). Each row has two combo boxes and one text box.
I need to somehow ensure that all 21 boxes have a valid entry before I close the form.
Is there any way I can reference the 21 boxes rather than just the three components of each row, or how else can I do it?
The two combos are cboWeekA and cboWeekB and the textbox is txtHours. They all start off in the query as null. The combos both store a field which is a single letter and the textbox stores a double number.
The textbox which I suppose generates the seven rows is txtDay which has Mon, Tue, Wed etc.
I am sure this must not be a unique problem but cannot find anything to help so if anyone has any bright ideas I would love to hear
Thanks and best wishes

Hello, I'm new to Access 2007, but not to relational concepts and design. I'm learning the Access interface by starting a db project. In setting up a table, I entered a validation test for a field in design view, then went to enter a row of data. When the validation test failed on that field, I realized I hadn't understood the syntax of the test, and I couldn't figure out how to make a valid entry. (ie. syntax was valid code, but I didn't know what it required. Dumb, I know.) I just wanted to cancel the row and go back to design, but I could click almost nowhere else on the screen without getting that error message. The only thing I finally was able to do was delete the whole column (and then redefine it). Couldn't find this in the help facility, so a dumb noob question: how do you escape a validation error message loop? A corollary question: I noticed there was a button for testing a validation expression on the menu bar, but it was not enabled? How do you test before being caught in the validation error loop?

Many thanks, Ron

Edit - May as well push my luck and ask this too: What's the proper expression to validate a 10 character string so that the first character is a capital letter and the remaining nine are digits? (I have a mask, but want further refinement..)

Today: Tuesday, July 4, 2006 @ 4P Arizona Time

I am an entry level VBA Access programmer and I need assistance with a simple login window.

My login window has the following controls: a cbo_Username or txt_UserName, a txt_Password, and a cmd_LoginCommand.

I am trying to write validation code for the txt_UserName and txt_Password text box controls. I want the code to prevent focus from moving to the txt_Password control when the user enters no data or invalid data. I want the code to do the same thing for the txt_Password control.

The following is my code attempt for the txt_UserName control (LostFocus Event):

Private Sub txt_UserName_LostFocus()

MsgBox "Welcome to the LostFocus Event of the txt_UserName Object"
If IsNull(Me.txt_UserName) Or txt_UserName = "" Then
MsgBox "Valid User Name Is Required - Please Enter UserName", vbOKOnly, _
"This Is Required Log On Information"
'Me.txt_UserName.Enabled = False - Did Work, Commented Out
'Me.txt_UserName.SetFocus - Did Work, Commented Out
Exit Sub
MsgBox " Valid Entry - Go To txt_Password Text Box Object"
End If

MsgBox "After Exit Sub Fires - What Happens Next"

End Sub

Thank you in advance for your people friendly assistance

Wayne V

I tried looking but to be honest i'm not sure what i'm looking for, other than advice that is!!

I'm designing a database to log details of boxes sent for storage. Users need to be able to enter details of new boxes and search existing data to request them back from storage. My main data table is as follows:

BoxID (PK)
DescriptionID (FK)
TeamID (FK)

Boxes are added to the database with the above details. The problem i'm having is with the StartDate and EndDate fields. When I started building this I was told that the boxes contain documents between two dates, the StartDate being the date of the oldest document and EndDate being the date of the most recent. Now they've told me that sometimes it's not dates they use to refer to documents, it's file numbers.

So, for a handful of (quite a large list of) DescriptionIDs they want it to record file numbers instead of dates.

I'm thinking at the moment that just making the fields Text fields is the way to go since then they'll be able to put whatever they want in, but i'm concerned that that will make it difficult to validate entries and thus difficult to perform searches on the records.

Any ideas folks?

Thanks in advance for any replies!!!

I have a sum query issue I am trying to work out and I have trouble trying to coordinate it.I am trying to build sum queries inside one select query that would allow me to sum point values in a separate table. The problem I am foreseeing is that I have three tables all connected with realationships. The structure with the fields is below:

Test Case


Group (Run)

Table1.Test_case-->Table2.test_case ( 1-many)
Table2.Run--> Table3.Group ( 1-many)

I want to be able to sum the total amount of points in a test case. Currently I am able to sum the number of points per run by doing a sum query on SUM(Table3.Points) in a select query that queries Table2 and Table3. But I try to do that with select query for Table1 and Table2 the query returns the same sum for all test cases. That sum is equal to the point total for the 1st entry in Table1.
Can I add a criteria somehow to make the sum work for each Test Case entry in Table1?

Also in regards to this same summing issue I spawned a second issue. I wanted to run a sum query on Table3 that would allow me to sum all the entries in that table that had valid entries in the Date1 field. I tried the following criteria.

Not IsNull([Date1])

The query came back with the same output as the above sum query.

What I am trying to accompolish essentially is the to get two sums out of the query. The first sum would be to sum all of the points totals for each Test Case. The other being able to sum point totals based of if a Date1 Field being filled in for each Task.

I am pretty sure I can do this in one query and just manipulate the data in the form of expressions and sum queries.

I am still searching through this forum for valid suggestions. But any thoughts will be welcome.

Thanks in advance.


My database has some queries and reports which I want retrieved by selecting a button on a form.

I want the user to select a query, and a form will pop up to request which date they would like (this form contains comboboxes and listboxes to ensure valid entries). The user can select their criteria, and this will feed a table (tbl_date). I've done the above, this was fine, but the next step is proving difficult:

1. open form to select date
2. select date
3. close form
4. open report (which is the result of a query using tbl_date)

I've tried writing a macro to do these steps, but they all occur at once. I want to be able to select the date, and then press a button to open a report.

The form for selecting a date is used in more than one query, so I don't want to create loads of identical forms with buttons to do different things.

Please help on how I can do this?

I hope this is not confusing, please let me know if you would like more detail.


This is probably a simple question for folks that live in the UK. I'm developing a db for a UK company and I haven't dealt with phone number formats there before. North America is so easy because it's always

(###) ###-#####

What I understand is that the area codes in the UK can be anywhere from 2 to 5 digits after the 0.

Does anyone have advice as to ensuring valid entry in a form and format for viewing in a form or report? TIA

I am using a combo box that allows the user to select only from a list of current products (e.g. ice cream flavors) from the Products table. The Products table includes the fields “ProdName” and “ProdAvail”. The Row Source uses the following query:

SELECT [Products].ProdAbbr, [Products].ProdName, [Products].ProdAvail
FROM [Products]
WHERE ((([Products].ProdAvail)=-1))
ORDER BY [Prods].ProdName

This works fine. My problem is that when a product is no longer available, it does not show up in the box. That is, if I look at a customer’s past order, I can see that there are ‘x’ number of records (products) but only the current ones are showing.

The control is in a subform that is linked to another subform (date of order).

Any help to show the invisible products would be appreciated.

What is the best way to replace 2 digits within a number on a form ?.

I have a form on which the user will enter a 10 digit number (Digits 1-3 = Country, Digits 4-5 = City, Digits 6-10 = Department).
The only digits which I want to change are 4 and 5 depending on the 10 digit number input by the user.
EG. If the user enters in 6043710036, I want 6044810036 to be returned. It is not practical to make a table of all valid entries which a user may make and the corresponding value to be returned.

I think What I am after is a combination of :

Left()+ Dlookup + Right() but am not sure how to do that.


Hi, I have a field in my table by which I want the records to be ordered by.
The values for this field are alphanumeric, something lke this:


The problem is that the user wants these ordered "numerically", i.e. they want that example above to be ordered in the following way:


However because I obviously have to have this field as a text field I cannot do this because Access (quite rightly) orders them alphabetically:


Is there any way I can parse the different parts of the string and use the Val() function in the ORDER BY statement in the sql of an underlying query or am I just screwed? (By the way the forward slashes are standard but the values can vary i.e. c12/1 and c12/234/56/1/23 ar both valid entries) No way, right!?



What can I say, my users are idjits and so the boss is requiring the following of my db:

There is a form used to enter charges. The first two fields are FILENO and CLIENTNO. What is the best way to

a) ensure that each number exists in their respective tables before accepting the entered values

b) ensure that the fileno and clientno match up as entered. (There is a table of Files, and each fileno has a clientno associated with it here)

Thanks so much for any help.


I have 4 drop down lists calling values from a query.
This part works as expected.

However, I would like the focus to automatically change to the next drop down field after ANY (valid) entry is made.


Drop Down 1: "any value" ----> focus automatically set to Drop Down 2, etc

The code I have only relates to 1 of the drop down values, I want it to rerlate to any of the values.

Private Sub Question1_AfterUpdate()

If Me.Question1 = "Yes" Then
End If
End Sub

My values are simple, just "yes" "no" "I don't know", etc.


If this cannot be done with drop downs, how about with field lists?

I have two php extension code one for form input & another for upload to MySql database management system. The form input code is as below:

denoted by "main.php"

Animal Information


Animal Information

Animal Name

Animal Description

Animal Photo

There is no error in this above code

The upload code is as below:

denoted by "storeinfo.php"

Hi - I have a form that uses an update query with a parameter that will ask for the user's zipcode. The zipcode the user inputs will lookup the zipcode in a table that has the zipcode, city and state. Then the query will place the city, state and zipcode in the table that holds the user information. When I run the query in standalone mode it works just like I want it too, but when I use the combobox wizard to create the unbound combobox and select the query I receive an error message that the query contains no valid entries. Any and all help or suggestions are greatly appreciated.

I am beginning to think what I want is not possible.

I have an unbound textbox that is used to input search criteria to find record in subform.
InputMask: 0000"A-"0000;0;_
DefaultValue: Year(Date())

Code for the textbox: Code: Private Sub tbxLabNum_BeforeUpdate(Cancel As Integer)With Me.ctrSampleList.Form.RecordsetClone .FindFirst "LabNum='" & Me.tbxLABNUM & "'" If .NoMatch = True Then MsgBox Me.tbxLABNUM & " is not valid Lab Number", , "EntryError" Me.tbxLABNUM.SelStart = 6 Cancel = True Else Me.ctrSampleList.Form.Bookmark = .Bookmark End If End With End Sub This all works but decided it would be even better if I could undo the user's bad input. As is, the invalid input is retained by the textbox and user can't leave the textbox without hitting ESC key or typing valid entry. I know I could use a combobox but users like just entering the last 4 characters, rarely (except at beginning of new year) do they need to search for prior year record by typing the prefix . I have tried:

1. The obvious - UNDO method, doesn't seem to do anything with unbound control
2. Sendkeys "{ESC}" but then the entire default value is selected
3. Setting the textbox value can't be done in BeforeUpdate
4. The SelStart method is totally ignored in AfterUpdate and cursor sits at beginning of the prefix and user has to cursor right or mouse click to the suffix or type the entire value

This is not a critical enhancement, just a 'would be nice', although users feel current behavior is tolerable.

So can I have my cake and eat it too?

I inherited this db mid-completion and was asked to finish it. The person who started this left the company. I am fairly new to Access so I am stumbling through this project. I had completed the db and was testing it when it crashed. Now it's not working and I cannot figure out why. I am sure it is simple but I have looked at it so long I am overlooking the problem. Could someone take a look at it and tell me what's missing? In the "View Accomodation Records", I have a query and macro set up to filter the results by policy number and populate the record information in a form. The same thing goes for "Export Referrals". This query and macro populate the record information in a report, then export it to an excel file.

I have attached the db. The records are ones that I made up and put in there for testing purposes and not real customer information.

Thank you in advance for any and all assistance with this issue!! Attached Files Copy of CM Referral Database (177.5 KB, 6 views) Reply With Quote 06-06-2012, 05:02 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,111 Exactly what are the issues?

I tested the View Accommodation button and a popup prompts for input, however, it prompts twice. I am guessing that is not correct. When I view properties, I see a prompt in the Filter property of [Form CR Accommodations - ALL] as well as in the query [Accommodation Report All]. When I remove the one from the query, no popups trigger. Weird. I cannot get this parameter input to work.

I won't use query input parameter popups because can't validate entry.

I see ALike used instead of Like in filter criteria, as in: ALike "*" & [Forms]![frm_Rpt_Criteria]![cboCare Manager] & "*"
I have never seen 'Alike' and it won't change. My research indicates this has something to do with SQL Server compatibility.
When I changed the wildcard from * to %, the ALike criteria worked.

I also don't use macros, only VBA.

Seems like I have read places that error trapping is supposed to be done on the After_Update of the same field. For some reason mine does not work because after the first time through the trap then a user can just 'Enters through it' on to the next field! User is not trapped into circling back through the same field until a valid entry. The only way that I have been able to do what I want above is to do all of my error trapping in the on_gotFocus of the next field, which then sets the focus back to the prior field and the loop works just fine. I'm concerned that I am doing things bass-ackwards.
Does it matter?

Here's the deal. The Assessment Form is where all the data is loaded, but for some reason a couple of queries don't include records w/ blank fields while other queries will include them. This skews the numbers so bad that different reports and pivot tables come up w/ different numbers. I can't find anything in the queries to cause this.

To overcome this I loaded some code to ensure that all the fields are filled in, even if it's 'n/a.' This works fine, but it can be a little cumbersome when deleting a blank entry, i.e. someone accidentally starts an assessment then for whatever reason it needs to be deleted. Message boxes pop up one after the other telling the user to enter various data. It's irritable, but livable.

I would like to use one grouping of code as opposed to a dozen for all the fields. Right now this is the code on the Form AfterUpdate function I use for close to a dozen fields:
If IsNull(Me.Rating) Then
MsgBox "All inspections require a rating. Please select from the list provided."
Cancel = True
DoCmd.GoToControl "Rating"
End If

I've tried using a few other bits of code that are designed to loop through all the fields and then give you one message if a field is blank, but no luck in making it work.

Also, this doesn't actually force an entry, it only gives you a message. After scrolling through the messages you can still move to the subforms or move to another record.

Also, I had been using subforms to show related info, i.e. the user selects the Main Assessee via a combo box and the subform shows name, rank, AFSC. I removed those subforms and started using DLookup. I had the DLookUp loaded under the AfterUpdate event which worked fine unless you were scrolling through the records. Then the DLookUp didn't update for the current record, so I added the DLookUp to the Form_Current event.

This works fine, except that it opens in Add mode and because of the Form_Current event a record is created before you actually type anything. The problem comes up when you try to click the View Open Assessments or Show All buttons. Then the validating code runs and you have to run through a bunch of message boxes before you can view those records.

I can probably work around this by having the form open showing the open assessments (records w/o a Chief Inspector Review) as opposed to opening in Add mode. sum things up. I need to be able to incorporate both the validating functions and the DLookUp functions w/o running into these errors.

Desparately seeking help!


DLookUp, validate form field, auto fill, autofill, required entry

can someone help me with a question?
i need to validate a number entry in a table...
i don't know hhow to write this function

function needs to validate

number needs to have 9 digits;
firt digit has to be 1, 2, 5, 6, 8, or 9
control sum given by 9xn1 +8xn2+ 7xn3+ 6xn4+ 5xn5 +4xn6 +3xn7 +2xn8 +n9 has to be multiple of 11 (sum%11=0).
n1 is the first digit, n9 is the last digit (check digit).
someone has a function that does this?

Not finding an answer? Try a Google search.