IF statement to test Null values in 2 different forms

[Anwar1968]

Hi vbaInet. I added your code to the form (frm_Benefits) according to your instructions. The forms are bound to the table (MainListTable).
The scinario is to open the form (frm_Clinets), then to open the tab (Job Info) and start filling the fields. In case if I filled the fields (Start Date), (Employer), and (Wage); but the field (Benefit Status) in the tab (Benefits) is null/no any value, then I want to get a message to complete the missing info/values. Thanks.

 

[vbaInet]

    If Me.BenefitStatusComboBox.ListIndex = -1 Then
        With Me.Parent.subfrm_JobEntry.Form
            If IsNumeric(.Wage_texbox) And Len(.EmployerName_textbox & vbNullString) <> 0 And IsDate(.StartDate_textbox) Then
                MsgBox "Please complete the missing information."
            End If
        End With
    End If

However, you need to re-think your design and normalise your table.

 

[Anwar1968]

Thank you vbaInet. I put the new code in the cut down db but still did not work. I wonder did you try it yourself?
Also I realized that when i move from tab to another to add info/data in each form's new record, this info/data will be saved in different rows in the main table. I thought it will be in one row for each client/individual.

 

[vbaInet]

Thank you vbaInet. I put the new code in the cut down db but still did not work. I wonder did you try it yourself?

I just tested it on the db you uploaded. Make sure that you:
1. Enter data in all three controls in the Jobs tab
2. Save that record and keep the cursor on that record in the Jobs tab... I mentioned a few posts before that the record it will check against is the record that has focus.
3. Move to the Benefits tab, select a date in the Start Date field and now try to move to another record, it will complain.

Also I realized that when i move from tab to another to add info/data in each form's new record, this info/data will be saved in different rows in the main table. I thought it will be in one row for each client/individual.

Related to this:

However, you need to re-think your design and normalise your table.

 

[Anwar1968]

Thank you vbaInet for your reply. The steps you gave me will not work in my case because I assume that the user completely ignors or forgets to open the (Benefits) tab, and I want to warn him/her of the missing data once he/she fills the three fields and saves the record.

Yes, I agree that I want to work on the design which is a new story and issue for me. Do you have some quick hints.

I highly appreciate your time, patience, posts, and feedback.

 

[vbaInet]

Unfortunately your current setup won't allow this to run any other way. Your subforms are using the same source plus each time you move between tabs the record gets saved anyway, that's how subforms behave.

If you want this sort of validation then you need to move your controls from the subform to the page. But most importantly, normalise your table.

 

[Anwar1968]

Got it. That means I need to redesign the entire db. I will see how to do that. I need to start over.
Again thank you for your time and feedback..

 

[vbaInet]

Whilst you're starting over, post a new thread in the Tables section asking for some help on your table design. Someone will be able to help.

NB: I've been away hence the late reply.

 

[Anwar1968]

Okay thank you I will later.