IF statement to test Null values in 2 different forms

[Anwar1968]

Hello everyone. I have a main form with many subforms. Each subform has couple of ComboBoxes or/and TextBoxes. I want to make sure that end users dont miss completing any of required values. For example, in the subform (Clients), an end user is supposed to put either "Male" or "Female" in the (Gender) ComboBox. In another subform (Job Info), the end user will put info of the job like Wage, Employer, Date of Job Start... ete.

What I want is to get VB IF statement which tests if the textboxes "Wage", "Employer", and "Date of Job Start" in the subform (Job Info) are not null, then the CobmoBox "Gender" in the subform (Clients) should not be null too, with a warning message to complete the missing value.

I highly appreciate your answers and feedbacks.
Anwar

 

[Ranman256]

When the user clicks Save, it must pass validation via code:

usage:
if IsValidForm() then
'run data process macro
else
'user must correct entries
end if

public Function IsValidForm() as boolean
dim vMsg

  select case true
       case txtName = "" 
          vMsg = "Client Name is missing"

       case isnull(cboState)
          vMsg = "State is missing"
	  cboState.setfocus

  end select
  if vMsg <>"" then msgbox vmsg,vbCritical,"Required"
  IsValidForm =vMsg =""
end sub

 

[Anwar1968]

Thank you Ranman for your reply. Sorry, I could not understand the code. Also I tried to read more to understand IsValidForm() in MS ACCESS but no success. I wonder is this Access code?
Thank you again, with my appology for my poor skill in Access VBA.
Anwar

 

[Ranman256]

yes, access vb code. click Ctl-G
on the left project panel, dbl-click your form
go to the bottom of the code
paste the IsValid function code

now change any controls i have , like cboState, txtName,
and swap them with YOUR control names you want to validate..
text boxes, combos, etc

then to validate, use this code
if IsValidForm() then
'run data process macro
else
'user must correct entries
end if

 

[Anwar1968]

Thanks Ranman. I tried to follow your instructions and changed the code but with no success. Remember, my original problem is to handle Null values in 2 different forms. Here is my code:

Public Function IsValidForm() As Boolean
Dim vMsg
Select Case True
Case Me.BenefitStatusComboBox = ""
vMsg = "Benefit Status is missing"
Case IsNull(Me.BenefitStartDateTextBox)
vMsg = "Benefit Status is missing"
Me.BenefitStartDateTextBox.SetFocus

End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required"
IsValidForm = vMsg = ""
End Sub

 

[Ranman256]

null isnt the same as ""

case isnull(Me.BenefitStatusComboBox)

 

[vbaInet]

I hope Ranman256 won't mind if I make a little contribution here

If Me.[COLOR="blue"]BenefitStatusComboBox[/COLOR].ListIndex = 0 Then
    With Me.Parent.[COLOR="blue"]JobInfoSubform[/COLOR]
        If IsNumeric(.[COLOR="Blue"]Wage[/COLOR]) And Len(.[COLOR="blue"]Employer[/COLOR] & vbNullString) <> 0 And IsDate(.[COLOR="blue"]DateOfJobStart[/COLOR]) Then
            MsgBox "... your message here ..."
        End If
    End With
End If

* This should go in the Before Update event of the Job Info subform
* Amend the bits in blue to reflect the right name
* If the combo box has Column Headers the ListIndex check should be change from =0 to =1
* Note that because you're checking against another subform, it's going to check against the current record.

 

[Anwar1968]

Thank you vbaInet for your reply and contribution. I did not reply to your post right away because I made many attempts to solve the problem, this took me a lot of time, but sitll with no luck. I followed your instruction step by step and did all required changes, but does not work. There should be something simple which I am not fully aware of. Here is my real scenario:
- I have two forms (JobInfo) and (Benefits).
- In the form (Benefits), I have couple of controls like "BenefitStatus", "BenefitStartDate"...etc.
- In the form (JobInfo), I have controls like "Wage", "Employer", and "JobStartDate".

I want to create a data validation rule in the form (JobInfo) that if the controls "Wage", "Employer", and "JobStartDate" are not NULL; then the control "BenefitStatus" in the form (Benefits) should not be NULL

I followed vbaInet's code with some changes but with no luck. The error I get is the line highlighted in blue in the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Forms!Benefits!BenefitStatusComboBox = 0 Then
With Me.Parent.JobInfo
If IsNumeric(.Wage) And Len(.Employer & vbNullString) <> 0 And IsDate(.JobStartDate) Then
MsgBox "... your message here ..."
End If
End With
End If
End Sub

 

[vbaInet]

- I have two forms (JobInfo) and (Benefits).

You stated that these are subforms but now state that they are forms. What is the real case? The code given was based on the premise that they were subforms.

If Me.BenefitStatusComboBox.ListIndex = 0 Then

If Forms!Benefits!BenefitStatusComboBox = 0 Then

These two aren't quite the same.

 

[Anwar1968]

Sorry for this confusion. In my first post I tried to simplify my question and gave a general example for my problem. Actually, I have a main form which includes 9 tabs and each tab has a form; I called them "subforms" to idicate that they are embeded in the main form, and did not mean to mislead you. Sorry for my poor knowledge. All fomrs are linked with a primary key. Data of a new record in all forms are saved in one new row in the main table.

The two quoted codes are not quite the same because I tried to use your first code but did not work, then I tried different codes and approches, and this was the latest attempt which did not work too.

Thank you vbaInet for your help and understanding, and sorry for any inconvenience.

 

[vbaInet]

You were right to state that they are subforms because they are embedded in a subform control. You will still need to use the code I initially wrote and only amend the blue bits.

To get the name of the subform control, click on it once and look in the property sheet for the Name property.

 

[Anwar1968]

I followed your codes and instruction again. I put the follwoing code in the BeforeUpdate event of the subform (frm_JobInfo) . I got the error message (Compile error: Method or data member not found), and (.BenefitStatusComboBox) is highlighted in dark blue color. By the way the control (BenefitStatusComboBox) is in a different subform called (frm_Benefits).

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.BenefitStatusComboBox.ListIndex = 0 Then
With Me.Parent.frm_JobInfo
If IsNumeric(.Wage) And Len(.Employer & vbNullString) <> 0 And IsDate(.JobStartDate) Then
MsgBox "... your message here ..."
End If
End With
End If
End Sub

 

[vbaInet]

Ok. Wherever the BenefitStatusComboBox is located is the form that the code should be put.

 

[Anwar1968]

Ok. I put the code in the other form (frm_Benefits), I did not get any error message, but the problem is not resolved and I dont get any validation rule message that I need to complete the missing value of the (Benefet Status).

I tried the follwoing options as you recommended in your very first reply, but did not work:
If Me.BenefitStatusComboBox.ListIndex = 0 Then
If Me.BenefitStatusComboBox.ListIndex = 1 Then


And also tried the following but no success:
If Me.BenefitStatusComboBox = 0 Then
If Me.BenefitStatusComboBox = 1 Then

 

[vbaInet]

Upload a cut down version of your db so I can see what you're doing.

 

[Anwar1968]

Hello vbaInet. Sorry, I did not get back to you. I was too busy today. I will upload a cut down verstion of the db soon. Thanks.

 

[vbaInet]

No problem!

 

[Anwar1968]

Hello vbaInet. I have uploaded the db. Here are some useful notes:
- I deleted many tables, queries, forms, and reports from the original db.
- In order to open the db, please first press shift and hold and then open the db.
- The main form to open is (frm_Clients) which includes many tabs including (Benefits) and (Job Info).
- I kept only 2 forms (frm_Benefits) and (frm_JobEntry) which we have arleady talked about in our previous posts duirng solving the issue.
Good luck, and thank you in advance for putting your time and effort to help me solving this issue.

 

[Ranman256]

Me.BenefitStatusComboBox = 0
will only work IF those are the values that the box can be.

BenefitStatusComboBox = 0 looks more like an index value but if the values are
CLOSED, OPEN, etc
etc , then zero wont work. It must be the bound field value.

 

[vbaInet]

I see zero code in the db you uploaded. Upload it with your attempts and make sure the forms are bound to a local table. And include some information on which form to open.

It's Me.BenefitStatusCombobox.ListIndex = 0 that's being referred to in the code provided.