How do I send results of a search to a combo box, etc.

[jsdavis]

I am trying to create a search form that searches based on a Field and Criteria selection and then sends the records that match the criteria to a combo or list box with only certain fields showing up (i.e. only first, last, middle, SS#). I would then like to be able to click on/select any of the results and have the record data populate a main form.

In my case, I have a main form ("Files") that contains information from the FILES table. Within the main form there is also a tab control with 4 other forms which list information relating to the same person listed in the main record that shows (all linked by FileID field); these forms are Applicants, Members, and Historical (w/ links to their respective tables) and a Family Tree w/ pulls from the Files table. I then have a Search button that pops up a search form.

Again, I would like to be able to pull up the search form, choose the field I want to search from, choose criteria, and then get a list of all the records (from the FILES table for right now..eventually I will need one field from the Members table) that match the search criteria. Once I click on the record I want to view, I would like that record's full information to be put into the main form.

I am not sure how to make this work as I'm pretty inexperienced in this depth of work in Access. I've tried a few different things but am not getting the desired result that I want. Thanks for any help anyone could be in this situation.

 

[TheAceMan1]

How are ya jsdavis . . .

What you need is a base [blue]SQL statement[/blue] in which you [blue]concatnate selected criteria[/blue]. The combobox/listbox [blue]RowSource[/blue] is then simply set to the new SQL . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[jsdavis]

TheAceMan1,

I guess that's what I need to do. I'm trying to find a way to search records in a table (linked to a form) by one of multiple fields (First Name, Last Name, Middle, SS#, Mother of person, Father of person) which I already have set up in a combo box. Once I choose a Search Field, I then want to enter in the criteria and then have ANY records that match that criteria show up in a combo box format.

I guess I'm just repeating myself..sorry Here is some code that I've been working with-I found most of it in a forum somewhere. I'm trying to edit it but I don't know much of how to really make it work right..

<code>
Private Sub cmdSearch_Click()

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter Files Table based on search criteria
Form_Files.RecordSource = "select * from Files where " & GCriteria

'Send records that match criteria to listbox

Form_SCITSearch.cmbSearchResults =

End If

End Sub
</code>

I probably need to start from scratch or really redo what I have. I'm pretty lost right now.

 

[PHV]

Something like this ?
Form_SCITSearch.cmbSearchResults.RowSource = "select * from Files where " & GCriteria

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jsdavis]

PHV,

That works wonderful! Thank you for that! Now, what would I need to do to be able to click on one of my "results" of my search in the combo box and have it then populate a form that is already set up with the appropriate fields for the record? I know it's obviously an onClick command but I really don't know how to set it up.

Thank you so much for your help and in such a short amount of time. I really appreciate it!

 

[TheAceMan1]

jsdavis . . .

This is a good place to start another thread so others can benefit of any resolution achieved.

BTW: [blue]Welcome to Tek-Tips![/blue] To get great answers and know whats expected of you in the forums be sure to have a look at FAQ219-2884

See Ya! . . . . . .