Problem of having a message box(where)

[reena]

I have a search form, when the user types in the partial text and click OK, it opens another form with company names in a combo box, where the user can select a particular name from the combo box.
My question is how to set up a message box and where to set up if the user typed partially and there was nothing found hence nothing to select in the form containing the combobox.
i want a msgbox saying no item was found and again redirect the user to the search form(cosearch).

'cosearch' is the search form, where the user types the text(name to search)
'comform' is the form with the combobox which gives the search results of the text typed in the cosearch form.
The code I have at present is.

Dim strSQL As String
strSQL = "Select * From company Where name Like '" & Forms![cosearch].[txtname] & "*';"
If IsNull(Me.txtname) Then
MsgBox "Please Enter the Company Name that you would like to view.", vbCritical
Me.txtname.SetFocus
Else
DoCmd.Close acForm, "cosearch"
DoCmd.OpenForm "comform", , , , acFormEdit
Forms!comform!cboname.RowSourceType = "Table/Query"
Forms!comform!cboname.RowSource = strsql
end if

Thanks a lot

 

[jwindon]

As part of your else block..

MsgBox "No records matching your criteria were found", vbInformation, "Sorry :-("

When the user hits OK, the code continues to run, reopening your search form....

HTH



[This message has been edited by jwindon (edited 10-01-2001).]

 

[Jack Cowley]

I have mulled on this a while and it is the only answer that I can come up with...

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim strSQL As String
strSQL = "Select * From company Where name Like '" & Forms![cosearch].[txtname] & "*';"
If IsNull(Me.txtname) Then
MsgBox "Please Enter the Company Name that you would like to view.", vbCritical
Me.txtname.SetFocus
Else
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount <= 0 Then
MsgBox "No records meet your Criteria."
Exit Sub
End If
DoCmd.Close acForm, "cosearch"
DoCmd.OpenForm "comform", , , , acFormEdit
Forms!comform!cboname.RowSourceType = "Table/Query"
Forms!comform!cboname.RowSource = strsql
end if

 

[reena]

Thanks Jack,
Thanks a lot