Validation in VB

[helloworld]

Hi there,

The code below takes an option from a combobox and puts it in a criteria for a query in order to filter it to whatever option the user clicks on. Could someone please help me to add on to the code so that if the option the user clicks is not in the query/table a message box pops up and displays a message. Thanks.

This is the code I have so far (this code works)

Private Sub Open_report_Click()
On Error GoTo Err_Open_report_Click

Dim stDocName As String

stDocName = "Reports"
DoCmd.OpenReport "Reports", acPreview, , "[NameoffieldIwanttosetcritieriato]Like forms!Reports![nameofcombobox]"

Exit_Open_report_Click:
Exit Sub

Err_Open_report_Click:
MsgBox Err.Description
Resume Exit_Open_report_Click

End Sub

 

[pdx_man]

Check out the DLOOKUP function:

Dim stDocName As String

If Dlookup("SomeFieldInTHEQUERY","THEQUERY","[NameoffieldIwanttosetcritieriato] Like " & forms!Reports![nameofcombobox]) Is Not Null Then

stDocName = "Reports"
:
:
End If

Remember, if your Criteria field is non-numeric, you may have to use apostrophes for text and # for dates around the criteria:

[NameoffieldIwanttosetcritieriato] Like '" & forms!Reports![nameofcombobox] & "'") Is Not Null Then

[NameoffieldIwanttosetcritieriato] = #" & forms!Reports![nameofcombobox] & "#") Is Not Null Then

 

[Pat Hartman]

NEVER use Like unless you have to because the user is only going to input part of a field value and then you must use at least one wild-card character either before or after the supplied value. In your case, use =.

 

[helloworld]

Hi there,

I tried using what you said pdx, but I get a syntax error saying the field could not be found. The field does contain text and I also used your alternatives. Nothing worked. Kept getting the syntax error.

 

[pdx_man]

Try this:

[NameoffieldIwanttosetcritieriato] Like '*" & forms!Reports![nameofcombobox] & "*'") Is Not Null Then

 

[helloworld]

Try this:

[NameoffieldIwanttosetcritieriato] Like '*" & forms!Reports![nameofcombobox] & "*'") Is Not Null Then

I now get an error saying 'object required'

thanks for trying to help out man!

 

[pdx_man]

Post the a few lines before and after this line that is giving you probs. I have tested this and it works for me. Your syntax is sideways, somewhere.

 

[helloworld]

thanks for your help man.
I managed to get it to work!