By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,435 Members | 2,033 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,435 IT Pros & Developers. It's quick & easy.

Validate values of controls in continuous form

P: 24
Hi,

I am struggling to find the right solution for my situation. I am still very new to Access, continuous forms and VBA, so excuse my ignorance

I have a continuous form in Access 2007. One of the controls on the form is a text box with an expected arrival date. This control can be edited by the user. I need to validate that this control is not blank before running my insert and/or update SQL queries.

Currently I am validating this in a button click event, but this only checks the first line on the form and when a value is entered on the first line, it continues with the insert/update commands.

I need to validate that all the expected arrival date values are entered before any SQL queries may execute.

Expand|Select|Wrap|Line Numbers
  1. If Me.ArrivalDate = "" Or IsNull(Me.ArrivalDate) Then
  2.         MsgBox "You have not selected an arrival date!", vbCritical + vbOKOnly, "Empty Arrival Date!"
  3.         Me.ArrivalDate.SetFocus
  4.         Exit Sub
  5. End If
Aug 20 '15 #1

✓ answered by Seth Schrock

Expand|Select|Wrap|Line Numbers
  1. Dim blnSuccess as Boolean
  2.  
  3. blnSuccess = True
  4.  
  5. Me.Recordset.MoveFirst
  6. Do While Not Me.Recordset.EOF
  7.     If Me.ArrivalDate & "" = "" Then
  8.         blnSuccess = False
  9.         Exit Do
  10.     End If
  11.     Me.Recordset.MoveNext
  12. Loop
  13.  
  14. If blnSuccess = True Then
  15.     'Perform Update query
  16. Else
  17.     MsgBox "You have not selected an arrival date!", vbCritical + vbOKOnly, "Empty Arrival Date!"
  18. End If
  19.  

Share this Question
Share on Google+
8 Replies


Seth Schrock
Expert 2.5K+
P: 2,939
Put this code in your form's Before Update event and replace the Exit Sub with Cancel = True.

This will run before each record gets saved before it moves to the next one, so it will be testing the current record and not let you move on without validation passing.
Aug 20 '15 #2

P: 24
Hi Seth,

The form that loads gives me a set of data based on what was entered in a previous form. I need the validation to happen in the click event of a button on this form, because it needs to run a few insert, update and delete queries.

I need it to check that the arrival date for each line on the continuous form is filled in before it can continue doing everything else.

Doing the validation in the form's before update event does nothing for me.
Aug 20 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,939
Then you are going to have to loop through your recordset. Do you want it to stop at every line that has a problem or do you want it to just tell you that there are problems once it has gone through all the records?
Aug 20 '15 #4

P: 24
I need it to check if the arrival date has a value and for each line that does not have a value, it must throw message box stating that no date was entered.
Aug 20 '15 #5

Seth Schrock
Expert 2.5K+
P: 2,939
But do you want it to stop on that record so that you know which one failed, or just a general message that one failed?
Aug 20 '15 #6

P: 24
Having it stop on that record will be helpful.
Aug 20 '15 #7

Seth Schrock
Expert 2.5K+
P: 2,939
Expand|Select|Wrap|Line Numbers
  1. Dim blnSuccess as Boolean
  2.  
  3. blnSuccess = True
  4.  
  5. Me.Recordset.MoveFirst
  6. Do While Not Me.Recordset.EOF
  7.     If Me.ArrivalDate & "" = "" Then
  8.         blnSuccess = False
  9.         Exit Do
  10.     End If
  11.     Me.Recordset.MoveNext
  12. Loop
  13.  
  14. If blnSuccess = True Then
  15.     'Perform Update query
  16. Else
  17.     MsgBox "You have not selected an arrival date!", vbCritical + vbOKOnly, "Empty Arrival Date!"
  18. End If
  19.  
Aug 20 '15 #8

P: 24
Great! This works exactly the way I need it to. Thank you so much Seth!
Aug 20 '15 #9

Post your reply

Sign in to post your reply or Sign up for a free account.