Combo Box in form/searchable by 2 values 1

[applevacross]

I've created a combo box with the combo box wizard. It is currently set to lookup the record by the value set which is serial #. However, I've been asked to have this searchable two ways. Management wants to be able to search by either username or serial. How can I set this up to be able to do this? Would I enter through the vb window or add to the existing statent created by the wizard in the row source properties of the actual box? Any Ideas? Any help is greatly appreciated.

Here is what is in the row source at the moment:

SELECT [AppleVac_Inv_DB Query].[Serial #] FROM [AppleVac_Inv_DB Query];

thanks to all in advance.

 

[jebry]

Hi!

I would make two combo boxes one to search by serial and other by name. Then I would create a frame with two radio buttons that the user can click to choose which to search by. Then in the after update event of the frame I would check the value and make the appropriate combobox with label visible and the other invisible. You can even put the two combo boxes right on top of each other to conserve space. You can use the frame value then in your search code to let you know which type of search is being done.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[applevacross]

Sounds good but I tried to create another combo box and it won't look up the records.

 

[PHV]

Even with the 3rd option of the combo wizard ?

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

 

[jebry]

Hi!

Could you post the code you have?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[applevacross]

The one that works

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Serial #]= '" & Me![Combo74] & "'"
Me.Bookmark = rs.Bookmark



End Sub



The one that doesn't [pretty much the same thing]


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

Set rs = Me.Recordset.Clone
rs.FindFirst "[User]= '" & Me![Combo74] & "'"
Me.Bookmark = rs.Bookmark



End Sub

 

[jebry]

Hi!

Are both combo boxes really named Combo74?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[applevacross]

74 and 109

 

[PHV]

And the bound column of Combo109 has the same User value than the RecordsetClone ?

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

 

[jebry]

Hi!

Change the second one like this:

Set rs = Me.Recordset.Clone
rs.FindFirst "[User]= '" & Me![Combo109] & "'"
Me.Bookmark = rs.Bookmark


And look at PH's post because you will need an exact match.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[applevacross]

Sorry had a long day. I noticed I copied the code of the same combo box, So sorry about that.

Jebry, I followed your instructions in creating the frame. What I did is created an option group with two radio buttons [search by serial # and search by user]
I set the frame default value to 2, Next I went to my combo boxes and set their default values accordingly [1 and 2]next, I went to the vb editor for both combo boxes to enter the values in the code. However, I'm not sure how I should enter the values, and am I doing what you advised correctly?

Thanks I appreciate both of your help.

 

[jebry]

Hi!

Could you please post the code you are using and the events procedures they are in?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[applevacross]

Ok, I removed the combos and started from scratch so we're on the same page. Here is the frame and the boxes code:



Private Sub Toggle115_AfterUpdate()

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[User] = '" & Me![Combo117] & "'"
Me.Bookmark = rs.Bookmark
End Sub

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[User] = '" & Me![Combo126] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[jebry]

Hi!

First the combo boxes do not need to have their value match the frame. Second, I am assuming that Toggle115 is the frame? If it is really a toggle then you may need to change the code some.

Try this:

Private Sub Toggle115_AfterUpdate()
'Make the appropriate combo visible
If Me!Toggle115.Value = 1 Then
Me!Combo117.Visible = True
Me!Combo126.Visible = False
Else
Me!Combo117.Visible = False
Me!Combo126.Visible = True
End If

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Serial #] = '" & Me![Combo117] & "'"
Me.Bookmark = rs.Bookmark
End Sub

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[User] = '" & Me![Combo126] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Notes:

I assumed that the check box with the value of 1 was for searching by Serial# and 2 was for searching by User.
I also assumed that combo117 was for searching by Serial# and Combo126 by User.

Let me know if this helps!


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[lespaul]

You are going to want to get in the habit of actually naming your controls and not using the Default names (where you might end up with: Combo25890). Now if you have renamed Combo25890 to cboUserCriteria, you have a really good idea of what the combo box is!!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[applevacross]

Let me tell you something.

You my friend are a genius. Thank you much for your patience and support!