Search Button

[Icecream7]

I have a form I've been working on and ready to scream....

I'm new to VBA which doesn't help any. The form I created is linked to a table. On it I have the fields that are associated with the table, a text box and a search button. What I'm trying to do is get the value in the text box (user entered) and search the table for a match. If there isn't a match display an error message. If there is a match have that information display in the fields.

I've found different ways of writing it but nothing seems to work. Do I put it in the private button on click or have it as a public? It's the only button on the form.

I think my biggest problem is that I'm making it harder than it needs to be.

Thanks

 

[Remou]

Unless you intend to use the search function for several forms, the click event for the search button is a good place for the code.
If you are having problems with the code, you will need to post the troublesome section, and perhaps, say how you intend the search to work: filter, find first, etc.


You will find a good number of posts on searching forms in these fora.

 

[UnicornRainbow]

Two things: If you create a query based on your table (selecting all fields into the query) and base your form on the query instead of the table itself, it will give you a lot more flexibility.

In design view of your form, highlight the magic wand on the toolbox and then add a command button. Follow the wizard through the steps for the Find Record action.

Take a look at the code to understand how it functions and determine what changes you would want to make to it.

 

[Icecream7]

Thanks UnicornRainbow I'll work more with the query for this. I wondered if that would give me more control. If I could just realize that this isn't as hard as I'm making it I could get a lot further. I've been bouncing between the search button on one form and a listbox or combo box on another.

I'll let you know the out come.

 

[MajP]

Use a combo box or listbox if the amount of records is relatively small, and can be sorted logically. Why return an error message when you can just show the possible choices? Here is one way. Build an unbound combobox, and call it cmboSearch. The bound column is the field that you are looking for.

Private Sub CmboSearch_AfterUpdate()
  Dim fldName As String
  Dim rs As DAO.Recordset
  fldName = "autoScoutID" 'put your field name here
  Set rs = Me.RecordsetClone
  rs.FindFirst fldName & " = " & ActiveControl.Value
  ' Use this if the value to find is a string
  'rs.FindFirst fldName & " = '" & ActiveControl.Value & "'"
  Me.Bookmark = rs.Bookmark
End Sub

 

[Icecream7]

I ended up creating a parameter query to get the
value needed for the form. It still needs to be run
past the person I'm creating it for to see if he's ok with it. Now I'm working on code for a list box to get the value of the selected and set it in a table.

It doesn't help that I graduated a year ago and this is the first I've had to use what I learned.