create search form with one text box that searches all fields 1

[rockjockb]

This is what I'd like to happen:
1. User enters "smit" into a text box on a form
2. User then hits <Enter> or clicks a command button
3. A query searches all fields in the table and puts the results in a combo box
4. The user clicks the dropdown on the combo box
5. The combo box displays results like:
First Last SSN
Smitty Jones 111-11-1111
Terry Smith 222-22-2222
George Smith 333-33-3333
6. The user clicks &quot;George Smith 333-33-3333&quot;
7. Using the SSN, the form updates with George Smiths info

Please note that I am not a programmer. I can massage VBasic a bit if I can make out what is logically happening, but I am an Intermediate Access Programmer at best. Thanks in advance for your help

 

[Eupher]

Piece 'o cake...

In the After Update event of your input textbox, create an event procedure and insert code like this:

Dim SQLStmt As String
SQLStmt = "SELECT * FROM tblMyTable " _
& "WHERE (LName LIKE '*" & Me!txtMySearchString & "*') OR " _
& "(FName LIKE '*" & Me!txtMySearchString & "*') " _
& "ORDER BY LName, FName;"
Me!cmbFindRecord.RowSource = SQLStmt
Me!cmbFindRecord.Requery

... where &quot;tblMyTable&quot; is the name of your data source, &quot;txtMySearchString&quot; is the name of your input text box, and &quot;cmbFindRecord&quot; is the name of your combo box.

Place your combo box on your form and let the combo wizard walk you through it. At the first prompt, select &quot;Find a record on my form based on the value I selected in my combo box&quot;, then select the table that is your data source, then select the fields you want to appear in the combo drop-down (for the above code to work, you should select all the fields). Assuming that the SSN field is the primary key in your table, on the next screen clear the &quot;Hide primary key&quot; checkbox, then click &quot;Next&quot;; then click &quot;Finish&quot; and you're done. Make sure you either change your code to match the default name the wizard gives to the combo box, or change the name of the combo to the match your code.

Give that a try...

Ken S.

 

[rockjockb]

Ken, this works fantastic for First Name and Last Name, but...(hate to &quot;but&quot; here because I very much appreciate your help)...I also need to be able to search by SSN. I attempted to copy the LName line, paste it on the next line, and then change LName to SSN, but that didn't work. Tried the same with the FName line, but that didn't work, either. I know I'm close.

Thanks again for your help. Saved me hours of banging my head against the wall.

Brandon

 

[rockjockb]

This is what I tried:

Private Sub txtSearch_AfterUpdate()
Dim SQLStmt As String
SQLStmt = &quot;SELECT * FROM qrySearch &quot; _
& &quot;WHERE (SSN LIKE '*&quot; & Me!txtSearch & &quot;*') OR &quot; _
& &quot;WHERE (LName LIKE '*&quot; & Me!txtSearch & &quot;*') OR &quot; _
& &quot;(FName LIKE '*&quot; & Me!txtSearch & &quot;*') &quot; _
& &quot;ORDER BY LName, FName, SSN;&quot;
Me!cmbResults.RowSource = SQLStmt
Me!cmbResults.Requery
End Sub

and

Private Sub txtSearch_AfterUpdate()
Dim SQLStmt As String
SQLStmt = &quot;SELECT * FROM qrySearch &quot; _
& &quot;WHERE (LName LIKE '*&quot; & Me!txtSearch & &quot;*') OR &quot; _
& &quot;(FName LIKE '*&quot; & Me!txtSearch & &quot;*') &quot; _
& &quot;(SSN LIKE '*&quot; & Me!txtSearch & &quot;*') &quot; _
& &quot;ORDER BY LName, FName, SSN;&quot;
Me!cmbResults.RowSource = SQLStmt
Me!cmbResults.Requery
End Sub

 

[rockjockb]

GOT IT!!!

This worked:

Private Sub txtSearch_AfterUpdate()
Dim SQLStmt As String
SQLStmt = &quot;SELECT * FROM qrySearch &quot; _
& &quot;WHERE (SSN LIKE '*&quot; & Me!txtSearch & &quot;*') OR &quot; _
& &quot;(LName LIKE '*&quot; & Me!txtSearch & &quot;*') OR &quot; _
& &quot;(FName LIKE '*&quot; & Me!txtSearch & &quot;*') &quot; _
& &quot;ORDER BY LName, FName, SSN;&quot;
Me!cmbResults.RowSource = SQLStmt
Me!cmbResults.Requery
End Sub

 

[Eupher]

Glad you got it to work!

Ken S.

 

[rockjockb]

New, but related issue.

Let me give details before explaining the problem. This is difficult to describe without showing, so please bear with me.

I have three forms: switchboard, demographics, certifications. The txtSearch and cmbResults are now on each form and working properly. I have command buttons on each form to open the other forms. I use the SSN as the stLinkCriteria between each form. I have command buttons open the other forms and bring up the data for that form, eg. clicking the Certifications button on the Demographics form opens the Certifications form and brings up the certification info for the person I searched for on the Demographics form.

Problem: When I click in the txtSearch and enter new criteria (after entering the form as described above) the cmbResults has new results. But when I click on one of the results, the info for the record I entered the page with appears. In other words, the fields are not updated with the new choice, but with the record that was originally displayed.

Attempted resolution: I created a macro for GotFocus of txtSearch that does:
RunCommand -> RefreshPage
GoToRecord -> New
GoToControl -> txtSearch

It didn't work, and I'm kind of stumped. Thanks again.

 

[Eupher]

If you used the wizard to create your command buttons, they are opening your forms in filtered mode, i.e. the form is only displaying one record. So, naturally, when you select another record from the combo, the form can't find it (because the recordset has been filtered) and it just stays put on the record that it opened with. Change the code behind the command button:

Instead of:

DoCmd.OpenForm stDocName, , , stLinkCriteria

you can try:

DoCmd.OpenForm stDocName
With Forms(stDocName).RecordsetClone
     .FindFirst stLinkCriteria
     Forms(stDocName).Bookmark = .Bookmark
End With

This will open the form and find the record that matches your criteria without filtering the recordset down to a single record, so your search field should work again. This was a tip provided to me by Dan Vlas in thread702-399358.

Ken S.

 

[rockjockb]

Muy Perfecto! Ken you are the shiznit. Props to Dan, also.

Thank you very much, again.

Brandon

 

[rockjockb]

Complicating matters, now....

I have created permissions and cut access to certain forms based on group. The Certifications group has full permissions to all forms, and the Advisors group has permission to only certain forms. I have limited permissions to forms by creating frmAdvisorDemographics and frmCertDemographics. The forms are identical at the moment, but I want to limit the search or results on frmAdvisorDemographics to only the current classes. Please note that I am using the code in the above postings.

There is a [Class] field in tblDemographics that is a text field, but has class numbers such as 06-96 (old naming convention) and 96025 (current naming convention).

I have created tblDBAdmin and frmDBAdmin to enter the current classes in session. I tried to limit the search by changing the code (see below), but it didn't work...I get a prompt for Class1 and Class2, then the results show everyone who meets the criteria, regardless of Class.

Dim SQLStmt As String
SQLStmt = &quot;SELECT * FROM qryDemoSearch &quot; _
& &quot;WHERE (SSN LIKE '*&quot; & Me!txtSearch & &quot;*') OR &quot; _
& &quot;(LName LIKE '*&quot; & Me!txtSearch & &quot;*') OR &quot; _
& &quot;(FName LIKE '*&quot; & Me!txtSearch & &quot;*') AND &quot; _
& &quot;([Class] = Forms![frmDBAdmin]![Class1]) OR &quot; _
& &quot;([Class] = Forms![frmDBAdmin]![Class2]) &quot; _
& &quot;ORDER BY [Class], LName, FName, SSN;&quot;
Me!cmbResults.RowSource = SQLStmt
Me!cmbResults.Requery

Thanks, again, in advance for your help.

 

[Eupher]

If you are passing form field values as criteria, you must change your code to accomodate this in the same manner as the other criteria in your SQL statement, otherwise you're just passing the name of the field as part of the string, not the value that's contained in the field. So you should change it thusly:

& "([Class] = '" & Forms![frmDBAdmin]![Class1] & "') OR " _
& "([Class] = '" & Forms![frmDBAdmin]![Class2] & "') " _

Also, be careful with the logic of your WHERE clause, else your SQL won't return the results you expect, i.e.:

clause1 OR clause2 OR clause3 AND clause4 OR clause5

...almost certainly will NOT return the same results as...

(clause1 OR clause2 OR clause3) AND (clause4 OR clause5)

...so insert extra parentheses as needed. As your queries get more complex, it's often a good idea to first build the query in the query design grid, then view the SQL to see how it all fits together. Then you can even cut and paste, although there are some syntax differences and IMO the query builder puts in WAY too many sets of parentheses.

Ken S.

 

[rockjockb]

I pasted this and got a run-time error 2450 &quot;...can't find the form &quot;frmDBAdmin&quot; ... It's there, and I can open it just fine and view the info from the table.

???

Any ideas?

Thanks again for the help.

 

[Eupher]

Is the form open when the SQL code is executing? If not, you'll have to amend your SQL statement to include the field from the appropriate table or edit qryDemoSearch to include the fields.

Ken S.

 

[rockjockb]

The form wasn't open, and that was the trick. Thanks again, Ken.

 

[asherid]

Please excuse my inexperience, but how does one use the above search method (which I have working) and then upon finding the correct record propagate all of the remaining fields on the form with the data for that record?

Basically, I want it to sort of act like the built-in search function that you can use when you create a button to &quot;find&quot; a record. When I use this method it automatically places all of the data for the record in the fields. I just don't like the &quot;Find&quot; form that pops up with this function. I'd rather use some sort of search method described in this post.

Clear as mud?

Aidan