Checking if the value in a combo box is equal to that in another field

[frantik]

Hi

Can anyone tell me how to check if the value in a combo box is equal to a value in another field.

What I want to do is to compare two values - one from the combo, one from a a text bow. If they are queal I want to display a subform if not, I don't, but My code just keeps ignoring the fact that they are equal!!!

thanks

 

[evalesthy]

It is not hard to do - can you post your code and give us example values that you are comparing. Also, even though the values look the same are you comparing identical datatypes? (e.g. are you comparing a string to a string or a date to a date, etc.) Also, does the combo box contain one row of data or multiple rows. If more than one row, which row is bound? Post detailed answer/description and we will help you here.

 

[frantik]

Hi evalesthy

Thanks for your help - the code I have is as below. It is not working at all. I always get the box - Not equal - even if they so are.
Combo11 is based on a select query. and is on a subform on a page of the main form.

UID is looked up from a table containing one record.

I know I have done something silly I am an ameteur.


Private Sub Combo11_AfterUpdate()
Dim uid As String
Dim rmgr As String

rmgr = Me.Combo11.Value

uid = DLookup("UID", "N_tblLocalUser&quot

If rmgr = uid Then
MsgBox "eeequal"
Forms!Fm_Risk_ass.Fm_TbApproved.Visible = True
Else
MsgBox "not equal"
Forms!Fm_Risk_ass.Fm_TbApproved.Visible = False
End If
End Sub

 

[WildHare]

Try this:

Private Sub Combo11_AfterUpdate()

IF Not IsNull
(DLookup("UID",
"N_tblLocalUser",
"n_tblLocalUser.UID = '" & me!Combo11 & "'&quot)

MsgBox "eeequal"
Forms!Fm_Risk_ass.Fm_TbApproved.Visible = True
Else
MsgBox "not equal"
Forms!Fm_Risk_ass.Fm_TbApproved.Visible = False
End If
End Sub


There's no reason to do all the assigning back and forth.

If the Dlookup returns a non-null value, then the expression is true , and so on.

Remember the Dlookup takes THREE arguments:
1) what to find
2) where to find it
3) what to use to find it with

So you need to add the combo box bound column as the third argument, surrounded by quotes if it is a text value.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[danvlas]

If the combo, the textbox and the subform are on the same form, this should work:


Private Sub Combo11_AfterUpdate()
Fm_TbApproved.Visible = Combo11 = TextBoxName
End Sub

If the combo is bound to first column, but displays another column (typical case of EmployeeID - EmployeeName) and you want to compare the displayed value, then:

Fm_TbApproved.Visible = Combo11.Column = TextBoxName
where n is the number of the displayed column minus 1

If they are located on different open forms, you will have to fully qualify them (prefix with Forms![FormName]!)

I don't understand why you're using DLookup, but if you do need it, you should use some criteria:
DLookup("FieldName", "TableOrQueryName","IDField = " & Value)

Otherwise, DLookup will return the first value in that field of the table/query.

Good luck

Good luck


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[evalesthy]

I have to run out for a meeting - but I see you have gotten several helpful posts already. I will check back later.

 

[frantik]

Thanks guys

After some playing around I still cannot get it to work - although I can see why it should! - wish I could see why its not!!

I am using now:

Private Sub Combo11_AfterUpdate()

If Not IsNull(DLookup("UID", "N_tblLocalUser", _
"n_tblLocalUser.UID = '" & Me!Combo11 & "'&quot) Then

MsgBox "eeequal"
Forms!Fm_Risk_ass.Fm_TbApproved.Visible = True
Else
MsgBox "not equal"
Forms!Fm_Risk_ass.Fm_TbApproved.Visible = False
End If
End Sub

When I substitue " & Me!Combo11 & " for Mark - Everyoes name comes up as equal! - not just Marks!!

I am using DLookup because I want to look up the person who is currently logged in.

I know I must be misisng the obvious!!

Thanks again

 

[danvlas]

You don't need to fully qualify the field in DLookup. Only the field name (without table name) is enough.

If UID is a numeric field, drop the single quotes:

Private Sub Combo11_AfterUpdate()
If Not IsNull(DLookup("UID", "N_tblLocalUser", _
"UID = " & Me!Combo11)) Then
MsgBox "eeequal"
Forms!Fm_Risk_ass.Fm_TbApproved.Visible = True
Else
MsgBox "not equal"
Forms!Fm_Risk_ass.Fm_TbApproved.Visible = False
End If
End Sub

This is equivalent with:

Private Sub Combo11_AfterUpdate()
Fm_TbApproved.Visible =Not IsNull(DLookup("UID", "N_tblLocalUser", _
"UID = " & Me!Combo11))
End Sub

or, if UID is text:

Private Sub Combo11_AfterUpdate()
Fm_TbApproved.Visible =Not IsNull(DLookup("UID", "N_tblLocalUser", _
"UID = '" & Me!Combo11 & "'&quot)
End Sub

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[frantik]

Thanks Daniel

Fm_TbApproved.Visible =Not IsNull(DLookup("UID", "N_tblLocalUser", _
"UID = '" & Me!Combo11 & "'&quot)

works a treat and its much neater!!

Fran.