Need to create a search box on my form

[mhshane]

I am using a db to track the cellular equipment of each user. I have a form that shows me name, wireless number, dept, and so on in the Detail section of the form. I want to create a search field in the Form Header so all I have to do is type in the wireless number of a user and all the fields in the Detail section will autopopulate. Any suggestions?

Using Access 2003.

Thanks.

 

[PHV]

You may follow the combobox wizard.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhshane]

Thank you for helping PHV. I'm afraid I am doing something wrong. I created an unbound combo box, followed the wizard like this:

look up the value in tbl or qry
selected the tbl with the cell number
selected cell number as my field
selected cell number as my sort order
next box asked if I wanted to set the width, left at default
Selected Remmeber the value for later
Named the box
Clicked Finish

I now have a box with a drop down menu of all cell phones but it does not correlate to any other fields on my Form. How do I make the other fields look to this combo box and go get the corresponding info (name, dept, etc) associated with this cell number?

Thanks again for helping me.

 

[PHV]

What is the actual VBA code of the AfterUpdate event procedure of the combo ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhshane]

I don't have any VBA code. I think I see where you are going with this now, though. I'm new to Access. I can build tables and queries, design forms...the basics, but I have not started working with VBA yet.

So I need to use VBA to create a search of a table and have it triggered to happen after I type in a number.

Any suggestions on learning VBA?

Thanks for your help.

 

[dhookom]

Let the combo box wizard write the code for you (this is one of the options available when creating a new combo box) and then review the code.

Duane
Hook'D on Access
MS Access MVP

 

[mhshane]

I am using Acess 2003. I went back through the wizard but I don't see an option that will do the work for me. Can you give me a step by step process?

 

[mhshane]

I did some research and found another website that had pictures and step by step of the process. My problem is I do not have the third option in the Combo Box Wizard:
"Find a record on my form based on the value I selected in my combo box."

Is that a feature of Acess 2007 or do I need to install something to make it appear in 2003?

Thanks.

 

[dhookom]

Is your form bound to a recordset as suggested in your first post or is it a subform?

I have found the third option for finding a record is available when a form is bound to a table or saved query. When I change the record source of the form to a SQL statement, the 3rd option isn't available. The code generated by the wizard is as follows:

Private Sub Combo6_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Factory] = '" & Me![Combo6] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[mhshane]

I see what you mean. I created another Form with only fields from a qry and got the third option. I will play around with it. Thank you for the help. Very much appreciated. Have a great day.

 

[mhshane]

I just thought I had a solution.

Here is the design of my Form:

Fields in Form Header:
Search box - type in cell number
Search box - type in datacard number

Fields in Detail section:
Full Name - get from qry of tblEmployee
Extension - get from tblComm
Dept number - get from tblemployee
Territory - get from tblComm
Supervisor - get from tblemployee
Cell Number - get from tblCom
Cell Device model - get from tblCell
Cell ESN - get from tblCell
Carrier - get from tblCell
Datacard Number - get from tblCom
Datacard device - get from tblDatacards
Datacard ESN - get from tblDatacards

Subform Cell Phone:
Dislay cell events (lost phone, stolen, water damage) and resolution - obtained from tblEvents

Subform Datacard:
Dislay datacard events (lost, stolen, broken) and resolution - obtained from tblEvents


I want to be able to search by Cell or Datacard number. Different search fields is fine. I figure I need to create a search box in th header. If I type in a cell number in the search field I want the fields in the Detail section to populate with the corresponding info.

When I make a form from only one tbl or qry then I get the third option in the combo box wozard to create a search box. If I use even one item from a second tbl or qry then I lose the third option that creates a search box for me.

Based on the Form design I have mentioned what do you suggest I do to create a search box (or boxes).

Thanks for the help.

 

[dhookom]

So your original question of looking up a single value/field "wireless number of a user" has changed to searching based on two fields? Are either or both of these fields optional or do you want to always have a value in the search boxes? Do you expect an exact match or can the user enter part of the cell or datacard number?

Duane
Hook'D on Access
MS Access MVP

 

[mhshane]

It has not changed but I abbreviated the explaination in my first post. In hindsight I see that was a mistake. Sorry, I'm having a learning moment. I figured if I learned how to create a search field based on cell phone I could replicate it for datacard on my own later. Once I learned the fields on the form determine what options are available to me in the wizard I realized I needed to fully explain every item on my form and what I was planning to do. I thought I would start small with name, ext, cell phone number and create my search field. Now I see that doing so could be for nothing when I add more information to the form and the search might not work any longer.

 

[dhookom]

When I have multiple search criteria, I use code run from an "Apply Filter" command button like:

Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT * FROM qMySavedQuery "
strWhere = " WHERE 1=1 "
strOrderBy = "ORDER BY fld1, fld2, fld3,..." 'modify this
If Not IsNull(Me.txtSearchCell) Then
   'assumes exact match on a text data type
   strWhere = strWhere & " AND [Cell Number] = '" & _
       Me.txtSearchCell & "' "
End If
If Not IsNull(Me.txtSearchData) Then
   'assumes exact match on a text data type
   strWhere = strWhere & " AND [Datacard Number] = '" & _
       Me.txtSearchData & "' "
End If
Me.RecordSource = strSQL & strWhere & strOrderBy

This would allow the user to enter values and then click the command button to review the results. If you want exact matches, the search text boxes should be combo boxes.

Duane
Hook'D on Access
MS Access MVP

 

[mhshane]

Excellent!!! Thank you. That is exactly what I was looking for. Have a great weekend.