Search via VBA

[Joker via AccessMonster.com]

Hello,

I am using Graham Thorpes search but I can't get it to work. I have it set
on a button but when I click it, it does nothing like a "dead" button. Any
ideas would be greatly appreciated.


'--------------------------------------------------------------
'Graham Thorpe 25-01-02
'--------------------------------------------------------------
Private Sub Command283_Click()
Dim strStudentRef As String
Dim strSearch As String

'Check Combo161 for Null value or Null Entry first.

If IsNull(Me![Combo161]) Or (Me![Combo161]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![Combo161].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into Combo161
'and evaluates this against values in Invnum

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Invnum")
DoCmd.FindRecord Me!Combo161

Invnum.SetFocus
strStudentRef = Invnum.Text
Combo161.SetFocus
strSearch = Combo161.Text

'If matching record found sets focus in Invnum and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
Invnum.SetFocus
Combo161 = ""

'If value not found sets focus back to Combo161 and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.
", _
, "Invalid Search Criterion!"
Combo161.SetFocus
End If

End Sub

 

[Joker via AccessMonster.com]

Do I need a query with this code?

 

[Guest]

I believe you are over complicating the matter. First, it is usually not
necessary to use a command button when you want to use a combo box to perform
a search. It is normally done using the Combo's After Update event. Here is
your code modified to use that technique:

First, use the combo's Before Update if you want to check for no value:

If Nz(Me.Combo161,"") = vbNullString Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Cancel = True
End If

Now to do the search in the After Update event:

Dim rst As Recordset

Set rst = Me.RecordsetClone

rst.FindFirst "[INV_NBR] = '" & Me.Combo161 & "'"
If Not rst.NoMatch Then
Me. Bookmark = rst.BookMark
End If

Set rst = Nothing

That's all there is to it.
Notes:
The [INV_NBR] would be the field name in the form's record source you want
to search for.
The FindFirst criteria assumes [INV_NBR] is a text field.
The code you posted is VB code, Not VBA. That is why you have all the
changing of focus. The Text property is available only when the control has
the focus. The Text property is mostly useless in VBA.

 

[Steve]

Just as a wild guess, create a new button. Go to properties and the event
tab. Click on the three dots at the On Click event and invoke code builder.
Copy and paste the code between Private Sub and End sub into the subroutine
for the new button. Now click on the new button.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)