Createing a search box

[Morgandy]

I want a way to type in the customer ID number into a enabled textbox and pull up the corresponding customer file that does not have any enabled boxes. Is this possible?

 

[missinglinq]

Assuming the CustomerID number is actually defined as Text in the underlying table, as it should be (a field containing digits that is not used for math should not be defined as Numeric) this should do the job, where

CustomerID is the ID field name
SearchBox is the textbox where you enter the ID number to be searched
SearchButton is the command button name

Private Sub Form_Load()
'Lock All Data Controls except SearchBox
Dim ctl As Control

 For Each ctl In Me.Controls
   If ctl.Name <> "SearchBox" Then
    If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Or TypeOf ctl Is ListBox _
    Or TypeOf ctl Is CheckBox Then
     ctl.Enabled = False
     ctl.Locked = True
    End If
   End If
  Next ctl
End Sub

Private Sub SearchButton_Click()
Dim rs As Object
  
If Not IsNull(Me.SearchBox) Then
   Set rs = Me.Recordset.Clone
   rs.FindFirst "[CustomerID] = '" & Me![SearchBox] & "'"
   If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  Else
   MsgBox "You Must Enter An ID Number Before Searching!"
  End If
End Sub

 

[Morgandy]

Awesome! That worked great. Is there a way to make it so once I've typed in the ID number and hit enter it does the search?

I'll also need a way to have it error when you do a search for a record that doesn't exist. Is that possible?

 

[missinglinq]

Sure! In Design View, select your command button and goto Properties - Other and set the Default Property to Yes. Then add the bold line below to pop up an error if no match is found:

Private Sub SearchButton_Click()
 Dim rs As Object
  If Not IsNull(Me.SearchBox) Then
   Set rs = Me.Recordset.Clone
   rs.FindFirst "[NName] = '" & Me![SearchBox] & "'"
   
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
   
    [B]If rs.NoMatch Then MsgBox "No Match Found!"[/B]
  Else
   MsgBox "You Must Enter An ID before Searching!"
  End If
End Sub

 

[Morgandy]

hum it's not liking this line...what is the NName supposed to be?

rs.FindFirst "[NName] = '" & Me![SearchBox] & "'

 

[missinglinq]

Sorry, Morgany! That was from my original code, before I modified it for you! NName was the field I was searching on in my original code, would be CustomerID in yours! Should be:

Private Sub SearchButton_Click()
Dim rs As Object
  
If Not IsNull(Me.SearchBox) Then
   Set rs = Me.Recordset.Clone
   rs.FindFirst "[CustomerID] = '" & Me![SearchBox] & "'"

   If Not rs.EOF Then Me.Bookmark = rs.Bookmark

   [B]If rs.NoMatch Then MsgBox "No Match Found!" [/B]
  Else
   MsgBox "You Must Enter An ID Number Before Searching!"
  End If
End Sub

 

[Morgandy]

Is that code adding or replacing the one you gave me earlier? (first time)

Private Sub SearchButton_Click()
Dim rs As Object

If Not IsNull(Me.SearchBox) Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = '" & Me![SearchBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Else
MsgBox "You Must Enter An ID Number Before Searching!"
End If
End Sub

 

[missinglinq]

Replace it. It has the extra line (in bold type) that pops up a message if no match is found.

 

[Morgandy]

Awesome! Works! Do you have a way to program it so that when I hit Enter after typing in the code it knows to press the SearchButton? I can't remember if that is VB or and Access function.

 

[missinglinq]

First line in Post # 4: In Design View select your command button, goto Properties - Other and set the Default Property to Yes.

 

[Morgandy]

Ok but I did that and it had no effect. I still have to click the search button for it to run.

 

[pbaldy]

If you want to take the button out of the equation, try moving the code to the after update event of the textbox.

 

[Morgandy]

OOh that is a good idea...can I have the code in both spots so the option is there to click ok? or does that mess it up?

 

[missinglinq]

Both places will be fine, Morgany. Don't know why you can't set the Default Property for the button. Works for me and for everyone I've ever given the advice to. Only caveat is that, obviously, only one command button can be set as the Default.

 

[Morgandy]

Thanks for your help! ...again hehe