Need code fix for searching

[Paul B.]

Greetings,

I have a form that I use to search records. There are two fields,
SearchSurname and SearchCallSign, and I only want to search based on one of
these.

Two things, I have created an AfterUpdate event that deletes(?) the other
field if the current field has text entered into it, and I'm not sure it does
what I want.

Second, what code would I use and where would I place it to display a MsgBox
if no records are found so that the results form is not opened.

Thanks in advance.

Here is my code:

-----------------------------------
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MemberInfo"

If IsNull(Me.SearchCallSign) Then

stLinkCriteria = "[LastName]=" & "'" & Me![SearchSurname] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

stLinkCriteria = "[CallSign]=" & "'" & Me![SearchCallSign] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If


Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub
------------------------------------------------
Private Sub SearchCallSign_AfterUpdate()

Me.SearchSurname = ""
Me.cmdSearch.SetFocus

End Sub
-------------------------------------------------
Private Sub SearchSurname_AfterUpdate()

Me.SearchCallSign = ""
Me.cmdSearch.SetFocus

End Sub

 

[Marshall Barton]

I have a form that I use to search records. There are two fields,
SearchSurname and SearchCallSign, and I only want to search based on one of
these.

Two things, I have created an AfterUpdate event that deletes(?) the other
field if the current field has text entered into it, and I'm not sure it does
what I want.

No, it doesn't. See Below.

Second, what code would I use and where would I place it to display a MsgBox
if no records are found so that the results form is not opened.

I also rearranged your logic a little to check for nothing
in either text box:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MemberInfo"

If Not IsNull(Me.SearchSurname) Then
stLinkCriteria = "LastName='" & Me!SearchSurname & "'"
ElseIf Not IsNull(Me.SearchCallSign) Then
stLinkCriteria = "CallSign='" & Me!SearchCallSign & "'"
Else
MsgBox "Please enter a search name"
End If

If DCount("*", "[the table name]", stLinkCriteria) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "There are no entries with that name"
End If
. . .
------------------------------------------------
Private Sub SearchCallSign_AfterUpdate()

Me.SearchSurname = Null '<<<<<<<<<<
Me.cmdSearch.SetFocus

End Sub
-------------------------------------------------
Private Sub SearchSurname_AfterUpdate()

Me.SearchCallSign = Null '<<<<<<<<<<
Me.cmdSearch.SetFocus

End Sub

 

[Paul B.]

Thank you!

Works great now.

Cheers

I have a form that I use to search records. There are two fields,
SearchSurname and SearchCallSign, and I only want to search based on one of
these.

Two things, I have created an AfterUpdate event that deletes(?) the other
field if the current field has text entered into it, and I'm not sure it does
what I want.

No, it doesn't. See Below.

Second, what code would I use and where would I place it to display a MsgBox
if no records are found so that the results form is not opened.

I also rearranged your logic a little to check for nothing
in either text box:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MemberInfo"

If Not IsNull(Me.SearchSurname) Then
stLinkCriteria = "LastName='" & Me!SearchSurname & "'"
ElseIf Not IsNull(Me.SearchCallSign) Then
stLinkCriteria = "CallSign='" & Me!SearchCallSign & "'"
Else
MsgBox "Please enter a search name"
End If

If DCount("*", "[the table name]", stLinkCriteria) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "There are no entries with that name"
End If
. . .
------------------------------------------------
Private Sub SearchCallSign_AfterUpdate()

Me.SearchSurname = Null '<<<<<<<<<<
Me.cmdSearch.SetFocus

End Sub
-------------------------------------------------
Private Sub SearchSurname_AfterUpdate()

Me.SearchCallSign = Null '<<<<<<<<<<
Me.cmdSearch.SetFocus

End Sub