multiple table validations

[Cindy]

Now without the quotes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
const string vbNullString
Me.chkIndomethacin.Value = False
Me.chkNifedipine.Value = False
Me.chkNitroglycerin.Value = False
If Len(txtOther.Text & vbNullString) = 0 Then
txtOther.Text = vbNullString
me.cmbTocolytic.text= vbNullString
me.cmbMultipleTocolytic = vbNullString

If me.cmbTocolytic.text=Yes Then
me.chkIndomethacin =True
Else
chkNifedipine = True
Else
chkNitroglycerin = True
Else
Len([txtOther])>0),([chkIndomethacin]=False Or [chkNifedipine]=False Or [chkNitroglycerin]=False Or Len([txtOther])=0))
End If
End Sub

 

[vbaInet]

By the way, don't use the word string as a variable name. It's a reserved keyword. I'm sure either myself or someone else has given you the list of reserved keywords.

For your first criteria to go in the Before Update event of the form:

    Dim addValues As Integer
    
    With Me
        If Nz(![Tocolytic?], 0) + Nz(![Multiple Tocolytic?], 0) < 0 Then
            
            addValues = Nz(![Indomethacin], 0) + Nz(![Nifedipine], 0) + Nz(![Nitroglycerin], 0)
            
            If addValues = 0 And Len(![Other] & vbNullString) = 0 Then
                Cancel = True
                MsgBox "Please check one of the boxes", vbOKOnly + vbExclamation, "Missing data"
            End If
        End If
    End With

Yes is -1 and No is 0, so if you add (-1 + 0) or add (0 + -1) you get -1, hence the first check of <0 meaning something has been checked. The same principle applies in the other checks.

 

[Cindy]

This makes sense. A million thanks vbaInet for your help and patience Might not look like it but I've learnt some from you and this website. I really appreciate it. The thought of taking VB classes is no longer a daunting prospect. You know, I just might just be teaching y'all someday. I say this with the utmost humility of course

 

[vbaInet]

You know, I just might just be teaching y'all someday. I say this with the utmost humility of course

You just never know ay!

 

[Cindy]

um... vbaInet, I saved the code in the Form's BeforeUpdate event but it is not obeying the constraint. It is not prompting me to check a box if either of the combo boxes is Yes and it goes ahead to save the record...

 

[vbaInet]

Check the values that your combo boxes are returning. Use a Msgbox for example.

 

[Cindy]

the values are the same..? Yes, No, Not Applicable.
How do you mean, Use a messagebox?

 

[vbaInet]

The values look the same but confirm that's exactly what it is returning.
Msgbox Me.ComboBoxName

 

[vbaInet]

By the way is it linked to a Yes/No field or did you just type Yes and No as value lists?

 

[Cindy]

okay, sorry where do I use this messagebox. In the AfterUpdate event?

 

[vbaInet]

Trial and error is the way forward. Give it a try where ever you feel it should go and get the hang of it.

 

[Cindy]

It is linked to a text field and I have the display control as "Combo Box". Row source type is Value List, and in the Row source I typed out the options: Yes, No, Not applicable, etc

I just used it in the AfterUpdate event of Tocolytic and it confirmed each selection with a msg box.

MsgBox Me.cmbTocolytic

 

[Cindy]

oh by the way, the check boxes are Yes/No data type

 

[vbaInet]

Upload your db. I need to see what you're doing.

 

[Cindy]

please see the attached.

 

[vbaInet]

Ah the famous Obstetrics table with the famous incredibly long field names

It is linked to a text field and I have the display control as "Combo Box". Row source type is Value List, and in the Row source I typed out the options: Yes, No, Not applicable, etc

I just used it in the AfterUpdate event of Tocolytic and it confirmed each selection with a msg box.

MsgBox Me.cmbTocolytic

If these are list values of text then you just need to change just this line:

If ![Tocolytic?] = "Yes" Or ![Multiple Tocolytic?] = "Yes" Then

 

[Cindy]

just to be clear, are you saying replace this line of code:

If Nz(![Tocolytic?], 0) + Nz(![Multiple Tocolytic?], 0) < 0 Then

with this?:
If ![Tocolytic?] = "Yes" Or ![Multiple Tocolytic?] = "Yes" Then

 

[vbaInet]

Like I said earlier, you don't always need to ask me about these sort of things. Just give it a try and see if it works. If I tell you every little detail it won't stick.

 

[Cindy]

okay, you'd told me earlier not to use "Yes" and I didn't want to mess up the program. But I get it

It worked! Thank you!!!

 

[Cindy]

Have a great evening/nite and many thanks for your patience.