Basing One Combo Bax on Another

[Guest]

I saw this article:
http://office.microsoft.com/en-ca/assistance/HA011730581033.aspx, but doesn't
look like it works in my case.

Assuming:

I have 1 table, 1 form. On the form, I want two combo boxes.

It would say something like

SEARCH BY [combo A] SEARCH FOR [combo B]

Combo A - data consist of the name of the field or column, like company name
or customer name (column name)

Combo B - the data in the particular field or column selected from Combo B.

So if you selected company name for combo A, you could search by company
name for all records in combo B and so forth.

Is this even possible? I can do this by creating a combo box for each one I
want to look in, but that takes lots of form space...

Your help is truly appreciated!

Curtis

 

[Douglas J Steele]

For ComboA, you'd set the RowSourceType to "Field List" and the RowSource to
the name of the table or query in question.

In ComboA's AfterUpdate event, you'd put code like:

Private Sub ComboA_AfterUpdate()
Me.ComboB.RowSourceType = "Table/Query"
Me.ComboB.RowSource = "SELECT DISTINCT [" & _
Me.ComboA & "] FROM [" & _
Me.ComboA.RowSource & _
"] ORDER BY [" & Me.ComboA & "]"
End Sub

(the square brackets are really only required if the field name or table
name have embedded blanks)

 

[Arvin Meyer [MVP]]

Just answered this, so I'll copy and paste the answer:

I like to create a query as the source for the second combo box and use the
first combo as a criteria. You can use the Build command (right-click in the
criteria box) or simply type in: [Forms]![YourFormName]![ComboA]
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

I used what DOUGLAS said. I tried ARVIN's advise, but not prevail.

When they select a record in ComboB, it's suppose to populate the
information into the form.

This is what I put in ComboB after update so it populates. However, it only
works for the last two, dba & company name fields, merchant & contact doesn't
work. Need to tweak something?

Private Sub ComboB_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Merchants Name] = '" & Me![ComboB] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.FindFirst "[Contact Name] = '" & Me![ComboB] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.FindFirst "[Company Name] = '" & Me![ComboB] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.FindFirst "[DBA Name] = '" & Me![ComboB] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.ComboB = Null
End Sub

==========

Also if it matters at all, for ComboA as row source, I created a query to
use for this specifically, so that list contains only the fields I want. I
have like 100 field columns an only want a couple to show up in that list, so
that part works, but may be causing the other problem I'm having?

Thanks!!!!

Curtis

 

[Douglas J. Steele]

You're only capable of having one field selected in ComboA at a time, and
hence you can only be doing one Find at a time in ComboB's AfterUpdate
event.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[ComboA] & "] = '" & Me![ComboB] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.ComboB = Null

End Sub

 

[Guest]

You're only capable of having one field selected in ComboA at a time, and

hence you can only be doing one Find at a time in ComboB's AfterUpdate
event.

Yeah, I just didn't have the right code and my knowledge is limited. I can
figure things out but don't know VB well enough to simply pull the code out
of space or my brain.... That worked, thanks so much! I tried looking
online for answers for a long time, and you made it so easy...

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[ComboA] & "] = '" & Me![ComboB] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.ComboB = Null

End Sub

 

[Guest]

Curious, but is this possible?

For ComboB, when you start typeing in the name, is it possible to only have
the records show up that match the data you current entered.

For example, you select dba name from ComboA, you start typging in ABC and
you can click on the pull down and it will show all records that start with
ABC like ABC Pest Control, ABC Flowers, etc.

 

[Douglas J. Steele]

Try:

rs.FindFirst "[" & Me.[ComboA] & "] Like '" & Me![ComboB] & "*'"

(This assumes that you don't have the combo box's LimitToList property set
to True)

 

[Guest]

You mean this? Not working....

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] Like '" & Me![SearchFor] & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

 

[Douglas J Steele]

What value is showing up in Me![SearchFor]? Is it was you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You mean this? Not working....

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] Like '" & Me![SearchFor] & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

Try:

rs.FindFirst "[" & Me.[ComboA] & "] Like '" & Me![ComboB] & "*'"

(This assumes that you don't have the combo box's LimitToList property set
to True)

 

[Guest]

With that code above, it works like normal, but all the values are there, it
doesn't take any away that don't match, as if I didn't type in anything, no
values changed when I start typing.