Search screen on form

[Guest]

hello,

I have a database created with many records. On the main form I have an
unbound object created to allow the user to enter a number and then have
created a button so that the user can click to search the database and if the
record matches all the record details are displayed.

My problem is that if there are no matching details the user is just
presented with a blank screen and one needs to X from it to return to the
search screen. I would like to build a small bit of error coding to let the
user know that no record was found and then retun the user to the main search
screen.

I realise that this may be a primitive way to perform a search however I
dont have much time or experience to play around with it.

The code from the search button is belows & it would be great is somebody
could let me know If I can add in some bit of code to carry out the above.

Thanks

Resume Exit_Search_for_Record_Click

End Sub
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Archived Current"

stLinkCriteria = "[Reg No]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub
Private Sub Close_Search_Screen_Click()
On Error GoTo Err_Close_Search_Screen_Click


DoCmd.Close

Exit_Close_Search_Screen_Click:
Exit Sub

Err_Close_Search_Screen_Click:
MsgBox Err.Description
Resume Exit_Close_Search_Screen_Click

End Sub




Phil

 

[Guest]

I'm no expert, but this works for me, perhaps you can make use of it.

Put it in your (the one that opens if there is a match) form's Open event

Cheers

------------------------------------
Private Sub Form_Open(Cancel As Integer)

'Show number of records found matching criteria selected

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

If Me.RecordsetClone.EOF Then
MsgBox ("No records found. Please try again.")
DoCmd.Close
Exit Sub
End If

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

Call MsgBox(" " & lngCount & " record(s) found", vbOKOnly, "Search
completed...")

End Sub

 

[Guest]

Paul's suggestion will work; however, it takes time to load the form,
establish the recordset, check the count, etc. There is another way that may
be a little faster using the DCount function. I don't know your table name or
field name you want to compare on, but before you open the form:

(TableNameHere) would be the same table or query you use for the form's
recordset.

stLinkCriteria = "[Reg No]=" & "'" & Me![Text0] & "'"
If DCount("*","TableNameHere",strLinkCriteria) = 0 Then
MsgBox "No Matches Found"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

 

[Guest]

Hi,

Thanks for your suggestions

This works well for me.

Thanks
--
Phil

Paul's suggestion will work; however, it takes time to load the form,
establish the recordset, check the count, etc. There is another way that may
be a little faster using the DCount function. I don't know your table name or
field name you want to compare on, but before you open the form:

(TableNameHere) would be the same table or query you use for the form's
recordset.

stLinkCriteria = "[Reg No]=" & "'" & Me![Text0] & "'"
If DCount("*","TableNameHere",strLinkCriteria) = 0 Then
MsgBox "No Matches Found"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

hello,

I have a database created with many records. On the main form I have an
unbound object created to allow the user to enter a number and then have
created a button so that the user can click to search the database and if the
record matches all the record details are displayed.

My problem is that if there are no matching details the user is just
presented with a blank screen and one needs to X from it to return to the
search screen. I would like to build a small bit of error coding to let the
user know that no record was found and then retun the user to the main search
screen.

I realise that this may be a primitive way to perform a search however I
dont have much time or experience to play around with it.

The code from the search button is belows & it would be great is somebody
could let me know If I can add in some bit of code to carry out the above.

Thanks

Resume Exit_Search_for_Record_Click

End Sub
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Archived Current"

stLinkCriteria = "[Reg No]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub
Private Sub Close_Search_Screen_Click()
On Error GoTo Err_Close_Search_Screen_Click


DoCmd.Close

Exit_Close_Search_Screen_Click:
Exit Sub

Err_Close_Search_Screen_Click:
MsgBox Err.Description
Resume Exit_Close_Search_Screen_Click

End Sub




Phil