msgbox, yes or no based on value on form

[inademam]

I have a form with one field on it. I want to be able to look up a value on this form and based on whether it is in the table or not give a message box saying yes it is there, or no it is not and then reset the form to look up another value.

Can anyone help with that?

Thanks,

 

[boblarson]

Something like this:

Dim strResp As String
 
strResp = IIf(DCount("*", "TableNameHere", "[FieldNameInTableHere]= " & Chr(34) & Me.TextBoxOnFormHere & Chr(34)) > 0, "Yes, it is there.", "No it is not there.")
 
Msgbox strResp

 

[inademam]

This is what I have,
Dim strResp As String
strResp = IIf(DCount("*", "Inactive_tbl_ImportMaster", "[Pernr]= " & Forms!frmswitchboard!subfrm!txtPernr.Value) > 0, "Message 1 here.", "Message 2 here.")
MsgBox strResp

and its still giving me a syntax error. I don't get it. Everything is right, I can't find anything wrong with this. UGH!!

 

[boblarson]

It could be your reference to the subform control and is Pernr a numeric or text field?

Is your subform control (the control that the subform is housed/displayed in) actually named subfrm? And, is that the name of your subform too? If not, then you need to refer to the subform control not the subform name itself and you need to include the .Form part as shown without changing the word FORM.

"[Pernr]= " & Forms!frmswitchboard.subfrm.Form.txtPernr.Value

If the field is text you need quotes.

"[Pernr]= " & Chr(34) & Forms!frmswitchboard.subfrm.Form.txtPernr.Value & Chr(34)