Populate form from a pop-up search box.

[jason123]

I know this should be pretty trivial, but I haven't done this stuff in years so:

I have a form which I use for data entry into an incident tracking database. Basically I want to be able to create a pop up window triggered by a button that will allow the user to type in a few characters of the clients last name and bring up a list of people to choose from and once the client has been chosen fill in all the relevant client fields in the form. Does anyone have a link to a sample DB that has this type of functionality? That or a description of what I need to do would be great.

 

[MarkK]

You need to have a textbox and a listbox on a form.
In the change event of the textbox you need to modify the rowsource of the list box.

Private Sub YourText_Change()
  'routine fires for each keystroke in Me.YourText
  'modifying the rowsource of Me.YourList
  'and limiting the records returned there to
  'only those that conform to the pattern entered in the textbox
  Me.YourList.RowSource = _
    "SELECT ID, First, Last FROM tClient " & _
    "WHERE Last LIKE '*" & Me.YourText & "*' " & _
    "ORDER BY Last;"
  'maybe you need to requery to list
  Me.YourList.Requery
End Sub

Then you can leverage the click event of the listbox to determine the client that the user has selected.

Private Sub YourList_Click()
  'when clicked, the list populates the ID field on a different form
  'and closes this selection form
  Forms("YourDataEntryForm").ID = Me.YourList
  DoCmd.Close acForm, Me.Name
End Sub

Hope that gives you a reasonable starting point...

 

[jason123]

Ah needed to use the .text property

Thanks, This is probably even simpler, but now my little problem is when I do the query in your first sub the Me.Mytextentryfield in the query it is always null. This leads me to get all the records in the database alphabetized in the text box. If I throw in a string in quotes in the place of the me.mytextentryfield (Not what I really called it) does the search perfectly based on the string I have hardcoded in. I tried popping up a messagebox with the me.mytext.... as an argument it and it give a runtime error about trying to send it a null string.

As I said I haven't done this stuff in a long time, so I'm sure I'm missing something really simple.

 

[MarkK]

Ya, right. The WHERE clause in the SQL in the first routine should be

WHERE Last LIKE '*" & Me.YourText.Text & "*'

Was that your edit?

 

[jason123]

Ya, right. The WHERE clause in the SQL in the first routine should be

WHERE Last LIKE '*" & Me.YourText.Text & "*'

Was that your edit?

Yes, I figured it out with a bit of messing around.
Edit: Fixed part of this (which i removed from this post with the columns property)

Thanks you so much.

 

[Skelly]

I know this post has been out for a while but this is exactly what I'm looking for. I named my textbox (Search) and my listbox (SearchResults) my form name is (Pournelle Client Listing) and the feilds I want to search are (Payors Last Name) and (Payor First Name).

The text box works fine and does the search perfectly. but when I click on the Name I want I get a syntax error

Option Compare Database
Private Sub Search_Change()
'routine fires for each keystroke in Me.Search
'modifying the rowsource of Me.SearchResults
'and limiting the records returned there to
'only those that conform to the pattern entered in the textbox
Me.SearchResults.RowSource = _
"SELECT ID, First, Last FROM tClient " & _
"WHERE Last LIKE '*" & Me.Search.Text & "*'" & _
"ORDER BY Last;"
'Maybe.SearchResults.Requery
End Sub
Private Sub SearchResults_Click()
'when clicked, the list populates the ID field on a different form
'and closes this selection form
Forms(Pournelle Client Listing).ID = Me.SearchResults
DoCmd.Close acForm, Me.Name
End Sub



please help I'm in the last few steps of finishing this database. I have been working on strictly trial and error learning this from the begining. Thanks to this forum.

 

[Skelly]

I almost forgot I named the unbound form with my textbox and listbox as SearchList

 

[MarkK]

Use the name of your form in quotes...

Forms("YourFormName").SomeMember = SomeValue

...to reference a member of the Forms collection.

 

[CJ.B]

This post describes exactly what I want to do. Which is to dynamically populate a list box based on each keystroke in an adjacent text box.

Problem I'm having seems to be with the change event (??) I can get the list box to populate but only after typing data into the text box, changing focus, going back and deleting the data -- then the list box populates. Works the same whether requery or not.

My code is below -- any suggestions?

 [COLOR=black][FONT=Verdana]Private Sub txtBoxSearch_Change()
    
   Me.listBoxNames.RowSource = _
    "SELECT FirstName,LastName FROM tblClients " & _
    "WHERE FirstName LIKE '*" & Me.txtBoxSearch & "*'" & _
    "ORDER BY FirstName;"[/FONT][/COLOR]
 
 [COLOR=black][FONT=Verdana]  Me.listBoxNames.Requery[/FONT][/COLOR]
 
 [COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

 

[CJ.B]

OK, got it working, the answer was in the post already. Needed the .Text property on the text box as below:

"WHERE FirstName LIKE '*" & Me.txtBoxSearch.Text & "*'" & _