Combine multiple lines in query into single one with drop-down options

[Guest]

I have a query that pulls all kinds of information into a query, including Item Number and Vendor. Because each item may have more than one vendor, I will sometimes get multiple rows for the same item. What I'd like to do is have each item show a combo box with each possible vendor for that item, defaulting to the first vendor in the list. In this way, instead of three lines for an Item Number, I'll only have one, while giving the user the option of selecting from a list of vendors unique to that item.

Does this make sense? Is it even possible? I know how to have a query display a combo box with a static list, but not one that changes dynamically per line.

 

[Danny J. Lesandrini]

This isn't a clean, query solution, but I don't think one exists. You can, however,
create a new temp table with the data displayed how you'd like it, by running
some code. I wrote an article about this for Database Journal
http://www.databasejournal.com/features/msaccess/article.php/2247531
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

I have a query that pulls all kinds of information into a query, including Item Number and Vendor. Because each item

may have more than one vendor, I will sometimes get multiple rows for the same item. What I'd like to do is have each
item show a combo box with each possible vendor for that item, defaulting to the first vendor in the list. In this way,
instead of three lines for an Item Number, I'll only have one, while giving the user the option of selecting from a list
of vendors unique to that item.

Does this make sense? Is it even possible? I know how to have a query display a combo box with a static list, but

not one that changes dynamically per line.

 

[[MVP] S.Clark]

I think the answer is a dependent combo box. First the user selects a value
from the ItemNumber combobox, then you the developer update the rowsource
for the Vendor combobox.

Unfortunately, if you try to do this in a subform, there is a nasty bug that
causes the 2nd combobox to go blank.

So, explain more about your form, and maybe we have something for you.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I have a query that pulls all kinds of information into a query, including

Item Number and Vendor. Because each item may have more than one vendor, I
will sometimes get multiple rows for the same item. What I'd like to do is
have each item show a combo box with each possible vendor for that item,
defaulting to the first vendor in the list. In this way, instead of three
lines for an Item Number, I'll only have one, while giving the user the
option of selecting from a list of vendors unique to that item.

Does this make sense? Is it even possible? I know how to have a query

display a combo box with a static list, but not one that changes dynamically
per line.

 

[Guest]

Thanks! I was considering a solution like this, but wasn't sure how to go about it. Ironically, I almost named my subject the exact title of your article. It is hard to name it! Thanks again.

 

[Guest]

Danny's answer involves rebuilding a table, which is perfect, I think, if you're trying to list multiple values in one field separated by a comma or other delimiter (I'd been wondering about that too!). I took part of his idea, though, and ran with it for creating a dynamic combo box in the query.

Instead of opening the query directly, I dump the info into a table - modifying my query first to use GROUP BY totals for all fields, except my vendor field, which I set to FIRST; this allows me to shorten my table to one listing per item (instead of the multiple listings I was getting due to the multiple vendors per item). I create a form that displays the table in Spreadsheet view so that it will look like a query. I change the vendor field's type from Textbox to Combo Box in Design View. I then insert the following code in in the GotFocus event for that field:

Private Sub VENDORID_GotFocus()
Dim i As Integer

' ERASE THE CONTENTS OF THE COMBO BOX
For i = 0 To (Me.VENDORID.ListCount - 1)
Me.VENDORID.RemoveItem (0)
Next i

' CREATE A RECORDSET FOR OBTAINING THE VALUES FOR THE COMBO BOX
Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
Dim strVendor As String

Set db = CurrentDb()
sSQL = "SELECT VENDORID FROM IV00103 WHERE ITEMNMBR = '" & Me.ITEMNMBR & "'"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

' FILL IN THE COMBO BOX WITH VALUES
If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
strVendor = rst!VENDORID
Me.VENDORID.AddItem (strVendor)
rst.MoveNext

Do Until rst.EOF
strVendor = rst!VENDORID
Me.VENDORID.AddItem (strVendor)
rst.MoveNext
Loop
End If
End Sub