Form Validation Rule with multiple conditions

[Anwar1968]

Hello everyone,
I have a main form which has couple of subforms bound to one main table. One of the subfomrs is about employment info. It has 3 TextBoxes: (Job Start Date), (Employer Name), and (Wage). It has also 3 ComboBoxes: (Job Type [Full Time or Part Time]), (Hire Status [Permanent or Temporary]), (Quarter [1st, 2nd, or 3rd]). I want to force users to fill all these 6 fields if they put any value in any one of them.
I tried to set a Validation Rule in the TextBox/ComboBox’s property including IIF statement condition, but I did not succeed, and these rules did not work.
I put the following code in the subform’s (After Update), (Before Update), and (On Current) events which works very well when I keep the (Wage) value 0 and move to a new record or to another subform:
Private Sub Form_AfterUpdate()
If Me.[Start Date] > 0 And Me.Wage.Value = 0 Then
MsgBox "Oops!! Sorry, you did not put how much is the wage."
Cancel = True
End If
End Sub
-----------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.[Start Date] > 0 And Me.Wage.Value = 0 Then
MsgBox "Oops!! Sorry, you did not put how much is the wage."
Cancel = True
End If
End Sub
-----------------------
Private Sub Form_Current()
If Me.[Start Date] > 0 And Me.Wage.Value = 0 Then
MsgBox "Oops!! Sorry, you did not put how much is the wage."
Cancel = True
End If
End Sub

I don’t know how to make a similar validation rule for the other TextBoxes and ComboBoxes. I highly appreciate any tip and help.
Thank you so much.. Anwar

 

[bob fitz]

Validation should be done in the forms Before Update event. Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.[Start Date] > 0 And Me.Wage.Value = 0 Then
  MsgBox "Oops!! Sorry, you did not put how much is the wage."
  Cancel = True
End If

If Nz(Me.[Employer Name],0=0 Then
  Msgbox "Please enter Name."
  Cancel = True
End If

If Nz(Me.[Job Type],0=0 Then
  Msgbox "Please select job type."
  Cancel = True
End If

If Nz(Me.[Hire Status ],0=0 Then
  Msgbox "Please select Hire Status."
  Cancel = True
End If

If Nz(Me.[Quarter  ],0=0 Then
  Msgbox "Please select Quarter ."
  Cancel = True
End If

End Sub

 

[Anwar1968]

Thank you Bob for your reply. It helps but still have some errors. I added only your first code related to (Employer Name) starting with the line:

If Nz(Me.[Employer Name],0=0 Then


It works only when I put the value (0) as the Employer Name. If I put any other value like (Walmart), and when I move to another record or another subform, then I get the Micorsoft Visual Basic error message (Run-time error '13': Type mismatch"). Again only the value (0) works well, I put the values (1) or (2) but did not work. What should I do?? Thank you again.

 

[bob fitz]

Sorry. I've made a silly mistake. Left out a closing bracket. Code should be:

If Nz(Me.[Employer Name],0[COLOR="Red"][B])[/B][/COLOR]=0 Then

 

[Anwar1968]

It works well now. Thank you so much Bob for your help and experience. You are the man.

 

[bob fitz]

Thank you for your kind words. Glad to be of help Good luck with your project.

 

[Anwar1968]

Hello Bob.. I have one more question related to my one yesterday but with a different scenario.
The same subform we were talking about yesterday has also 2 CheckBoxes (Job Stop) and (Retention), and 1 TextBox (Date of Job Stop) which might not be required for every record. Here are more details:

Yesterday we were dealing with the first scenario where a user wants to enter employment info (employer name, wage, start date...etc) which are all required once he/she enters any value in any of them. Let's assume this was for the 1st Quarter. This was done perfect.. Thanks.

Now the second scenario, assume we are in the 2nd Quarter [3 months later], and the user wants to report (Job Stop), (Date of Job Stop), or (Retention [after 90 days]) only for the previous job entry, as well as the current (Quarter). Once I start in a new record and check any of the two CheckBoxes or put the Date of Job Stop and the Quarter, then want to move to another record or subform, I get the validation messages that required values are missing (Start Date), (Employer Name), (Wage), (Job Type), (Hire Status) which I don’t want to report anything related to these fields.

To remind you and make it easier for you, here is my current code of the BeforeUpdate Event which works properly, and want to know how to add the code of the new scenario:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(Me.StartDate_textbox, 0) = 0 Then
MsgBox "A required value is missing! Please enter the (Start Date)."
Cancel = True
End If

If Nz(Me.EmployerName_textbox, 0) = 0 Then
MsgBox "A required value is missing! Please enter the (Employer Name)."
Cancel = True
End If

If Nz(Me.Wage_texbox, 0) = 0 Then
MsgBox "A required value is missing! Please enter the (Wage)."
Cancel = True
End If

If Nz(Me.JobType_combox, 0) = 0 Then
MsgBox "A required value is missing! Please enter the (Job Type)."
Cancel = True
End If

If Nz(Me.HireStatus_combox, 0) = 0 Then
MsgBox "A required value is missing! Please enter the (Hire Status)."
Cancel = True
End If

If Nz(Me.Quarter_combox, 0) = 0 Then
MsgBox "A required value is missing! Please enter the (Quarter)."
Cancel = True
End If

End Sub

Thank you so much Bob for your help and support.
Anwar

 

[bob fitz]

At the moment the record being entered/edited can not be saved unless the following fields have values: (Start Date), (Employer Name), (Wage), (Job Type), (Hire Status)
If you are now saying that you want to add a new record but this new record will not need values in those fields, then I am a little confused
Can you post a copy of the db in A2003 mdb format with a few dummy records, to help me understand how it all fits together.

 

[Anwar1968]

Bob, how can I post (attach) a copy of the db??

 

[bob fitz]

Bob, how can I post (attach) a copy of the db??

See: http://www.access-programmers.co.uk/forums/faq.php?faq=vb3_reading_posting#faq_vb3_attachments

 

[Anwar1968]

Oh! Good to know. I will post the db soon. Thank you Bob.

 

[Anwar1968]

Hello Bob,
I attached the db file in this post. I made it very simple. There are 2 tables and 3 forms. The form (frm_Cases) is the main form to open the DB where you can use and try the subform (JobInfo) where you can add as many records as you want of job info to the same person in the same subform. Then you can navigate to another person to add as many records as you want, and so forth. Some records of the same person may have full job info (Start Date, Employer Name, Wage, ...etc) which are required, and if you miss one of them then you will get the validation rule meesage. Some records of the same person may have only the Job Stop Date and related info, then you don't need to enter the required values of other fields. That's why I am asking how to add a code to the previous one to force the user entering more reasonable and accurate data. You may play with this db and discover it to understand exactly what I mean. Thank you, and have a good day.

 

[bob fitz]

Thank you. I will take a look at as soon as I am able to.

 

[bob fitz]

Some records of the same person may have only the Job Stop Date and related info, then you don't need to enter the required values of other fields.

I don't really understand this. What determines if other field values are required or not.
Also, the check box on the sub form labelled "Job Stopped?" should not be bound and saved to a field in the table because this is basically a calculation. Its value can be determined by whether the field "Job Stopped Date" is null or not. So this should be calculated whenever and where ever it is needed. Not stored in a table.

 

[Anwar1968]

Hi Bob.. Sorry for any confusion. Let me walk you through this:
When you the db I sent, and open the form (frm_Cases), there are 2 cases (families), the 1st family has 4, and 2nd one has 3. In the 1st family, John Doe (the hasband) got two jobs, and lost them later. His third job started on 7/2/2013. So you can see there are 3 records for John Doe. You can navigate to John Doe's wife's page and see that she got a job 2/12/2013, or to the 2nd family's pages. Now, imagine you are the user of this db, and you want to reprot that John Doe lost his job today, 4/17/2014, and did not get any new job. That means you need ONLY to check the box (Job Stopped), put the (Job Stop Date), and(Trimester) ONLY. But because of our first code, we will get the validation rule message that (Start Date, employer name, wage, job type, and hire statues) which are not required here because there is no a new job. Is there a way to find a solutioin for this?
Thank you so much Bob for your help, and sorry for this complicated situation, and any confusion.

 

[bob fitz]

Now, imagine you are the user of this db, and you want to reprot that John Doe lost his job today, 4/17/2014, and did not get any new job. That means you need ONLY to check the box (Job Stopped), put the (Job Stop Date), and(Trimester) ONLY.

What I don't understand is why this goes in a new record on the sub form. Surely this detail just completes the third record (Employer: xvbvbxvbxcvb) because that is the job he has just lost.