Check to see if 2 fields are filled in?

[KevinSlater]

hello I have 2 drop down list boxes in a form that both contain numbers & would like to prevent the user from selecting them both together, ie the user should only be allowed to select one of the drop down llists & not the other, if they do i would like a message to be displayed if both are selected, i thought the: "is not null" statement might work as shown below but it doesnt, any one know what i need to do?

Private Sub Form_BeforeUpdate(Cancel As Integer)

If FIELD1 & FIELD2 Is Not Null Then

MsgBox "Please select just one "
Cancel = True
End If
End Sub

 

[Summerwind]

How about if in the AfterUpdate or OnChange event of each list box you hide and disable the other one?

Your user simply cannot select both then.

 

[KevinSlater]

Thanks for your reply Summerwind, that Sounds like a good idea, but i really dont know how to do that, (the form id like to do this on allows entry of more that one record) anyone help?

ive tried the below code on the: "after update" & "on change" of the field but it doesnt seem to do anything (no error messages, & form is contineues)

Private Sub FIELD1_AfterUpdate()
If Me.FIELD1 = True Then
Me.FIELD2.Enabled = False
End If
End Sub

 

[Summerwind]

Simple really, and you don't need an If clause.

Private Sub List1_AfterUpdate()
With Me.List2
.Visible = False
.Enabled = False
End With
*********** If you want you could update your underlying tables and clear the form
from here
End Sub

Private Sub List2_AfterUpdate()
With Me.List1
.Visible = False
.Enabled = False
End With
End Sub

 

[ghudson]

How will the user bring the invisible or disabled list box back if they need to use it?

Also, what about the value of the invisible or disabled list box? You need to set it to an empty string.

Private Sub ListBox1_AfterUpdate()
 
    Me.ListBox2.Value = ""
 
End Sub

Private Sub ListBox2_AfterUpdate()
 
    Me.ListBox1.Value = ""
 
End Sub

 

[KevinSlater]

all list boxes are disabled/hidden instead of just 1 on current record

Thank you Summerwind & ghudson it works what you both said, but i have 2 very similar problems i would like to fix:

1)When i select field1 for example all of the field2 list boxes are disabled/invisible but i only want that particular records list box that im on disabled not every single records list box. (the form is in continuoes format) Would like to do this same bit of code/check for each individual records list box on the form. is this possible? any help would be great

2) ghudson you made a good point about how will the user bring back the disabled/hidden list boxes...well ive created a button to reset the both the list boxes back to visible/hidden but ive got the same problem as above it makes all of the records listboxes visible rather than just that particular record.

Any help on how to fix this problem would be great, cheers.

 

[KevinSlater]

Ok ive searched these forums & the internet and think what im tyring to do could be impossible on a continuoes form, could anyone confirm this? or does anyone know a way around it?

if this is not possible could i display a message if both drop down fields are filled in on the continuoes form?.

 

[ghudson]

You will not be able to do what you want with a continious form. If you must show all of the records then you should create a subform and display the new subform at the bottom of your main form. That way you can do what you want with the list boxes within the main form and still show all of the records in the subform.

 

[goppo]

is ok if the value from first list is not appear in second list. even both lists have same source?

I upload a example

 

[KevinSlater]

Ok thanks for your replies ghudson & goppo. goppo i cant open your file, im using MS accsess 97 so that could be why if youve created it in a newer version. Would you be able to save it in 97 format & upload it?. ghudson the fields i want to do this code on are already in a subform (the fields need to be changed for each individual record not just on the main form)

 

[goppo]

here is in 97 format

 

[KevinSlater]

Ive looked at the file goppo but i think you mis-understand what im trying to do, thanks for trying anyway

 

[KevinSlater]

Reached a dead end on this one i think?

Think im stuck on this one if anyone has any ideas around this please let me know.