OpenForm

[benb]

I'm using a form (Form A) with one text box & on combo box to search for a
record in my database, then open a separate form (Form B), filtered to that
record. Both controls on Form A are unbound. The idea is that the user will
enter a name/ID/etc. into the text box, select the search category (field)
from the combo box, then a command button will execute code to find the
record. Three issues:

1) I would like to display a message and cancel the code if there is no
matching entry. I thought DLOOKUP, but seems that's not going to work.

2) With the code I've written, I am prompted to enter a parameter value
instead of getting the desire result (Form B opened & filtered to the desired
records).

3) I'd like to make search result more inclusive using syntax "Like
'*String*'" where String is whatever word is being searched for, but I'm
getting an error with all the adaptations of this I've tried.

Here's my code. Help appreciated.

Private Sub cmdSearch_Click()
On Error GoTo err_Search
Dim category As String, subject As String
Forms!frm_Search!txtSubject.SetFocus
category = Forms!frm_Search!txtSubject.Text
Forms!frm_Search!cmbCategory.SetFocus
subject = Forms!frm_Search!cmbCategory.Text
'If IsNull(DLookup(subject, category)) Then _
'do nothing
'Else
DoCmd.OpenForm FormName:="frm_Browse", view:=acNormal,
wherecondition:=category & " = '" & subject & "'"
'End If
Exit Sub
err_Search:
MsgBox Error$
End Sub

 

[Douglas J Steele]

In order for your OpenForm to work, Category must be the name of a field in
the underlying recordset associated with the form. Is it?

If it is, the following should work for your Like:

wherecondition:=category & " Like '*" & subject & "*'"

Note that this will fail if subject contains a single quote (or apostrophe).
In that case, you need:

wherecondition:=category & " Like " & Chr$(34) & "*" & subject & "*" &
Chr$(34)

 

[benb]

Thanks, that was helpful. I needed to add the fields to Form B. I was
thinking only in terms of Form A. Any clue how to test whether there are any
result to return so that a blank form doesn't open up?