Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using a combo box to query fields within a table in MS Access 2003

Posted on 2008-06-17
11
Medium Priority
?
322 Views
Last Modified: 2008-06-19
What I'm trying to do is have a combo box drop down a list of parts from my 'Item' table in Access 2003.  This list will be filtered by the 'Class' that each item is, specifically trying to show Printers ONLY, and have this list be in ascending order.  The Class types that would be triggered by the 'LIKE' command are NEW PRINTER and OLD PRINTER. So I 'figure LIKE %printer' would do.  Eventually, I would like this combo box to drop down and filter JUST the printers from the 'Item' table, so when I clicked on any printer I would have let's say 10 or so text boxes associated with that printer to show the part numbers in each text box making up the parts of that given printer.  The parts are in the 'Item' table as well, under 'Class'  ASSY PARTS
All in all, this is how I would like the combo box to function.  

Can anyone help me fix this issue in my combo box so that I may click a printer from my drop-down box, and have its associated parts displayed in text boxes below the combo?

Thanks.
0
Comment
Question by:jdol2949
  • 6
  • 4
11 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 21805083
The wild card in Access is '*' not '%'

LIKE "*Printer"
0
 

Author Comment

by:jdol2949
ID: 21805109
I'm querying a table on our SQL server, and the * wild card does not suffice for that.  The % is working.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21805311
How many different classes, and within Printer, for example how many sub-classes?  I see a combobox to get the right class, a list box with multi-select - none - populated with all the items in that class, and on selecting one item in the list, the click event of the list box populates the 10 or so text boxes with the info for that item.  Clear so far?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jdol2949
ID: 21805547
Yes, somewhat clear.   there's about 7 or 8 classes, with really the only three major classes here being NEW PRINTER, OLD PRINTER, and ASSY PARTS .   The combo box is actually set up to query an 'Item' table and filter out all of the other parts except those specifically associated with NEW PRINTER, and OLD PRINTER, which is why I used the */%printer wild card.  I don't need a combo box to select the class, just the printer.  The click event of the combo box would populate each of the 10 text boxes with parts associating to that printer type, which are all categorized under the class ASSY PARTS.  I would like the text boxes only to spit out the associated parts for each printer selected in the combo box.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21805912
In the fullness of time you are going to want to go the route of selecting a class which populates a listbox, click on a listbox item, and populate the textboxes.  Why not go that way now and have a design that will grow with user requirements, rather than hit the wall, junk what you have, and start over.

That said, if all the Assy Parts are in the Items table as well as all the Old and New Printers, there must be a master field that is populated with the PrinterID so one can recursively call the Items table with the PrinterID and get all the Assy Parts?  I guess my question would be why do you want to see the parts in ten or more textboxes, rather that neatly listed in a list box?  As printers may have varying numbers of assy parts, you will have to deal with the fact that some text boxes may be null - and on and on and on.  

Time to look at the structure of the Items table.
0
 

Author Comment

by:jdol2949
ID: 21806249
The Items table structure cannot be changed.. it is the current database that this company runs off of, which was created within a program called ServiceWorks.  The listbox idea is a good suggestion, and I will try to change my format to use that.  The current 'Item' table (which again was pulled from a server SQL database created by another program) has the following columns: ItemNmbr, Manufacturer, Class, Description, Price, Cost, Status
Under the 'ItemNmbr' column is where all the part numbers are listed, including the printers themselves.  The class column distinguishes the different type of 'Items'.  I want ItemNmbr 'A' to get selected from a drop-down menu and have some sort of a list populated with all of the parts relating to ItemNmbr A posted on it.  Hopefully, this could be distinguished by the 'Class' column.  If this is not possible, please let me know what I can do to make it work.

Thanks,

Ryan
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21807210
You need a column which I called Master in which you insert the ItemNmbr of the Master Item to which the assembly belongs:

In reality you could dispense with b.Master in the SELECT clause and get just the two fields a.ItemNmbr and b.ItemNmbr.  I would guess you would use the Description field to be better able to correlate the data.

Notice the Class field is not of much use here.
With these two fields in the Items table:
 
ItemNmbr    Master
Printer123
Printer124
Drum1234    Printer123
Toner1234   Printer123  
 
SELECT a.ItemNmbr, b. ItemNmbr, b.Master from Items a INNER JOIN Items b ON a.ItemNmbr=b.Master
 
Should return:
 
Printer123  Drum1234   Printer123
Printer123  Toner1234  Printer123 

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21807415
I guess I should have asked before - how do you currently associate the ten or so assembly's with the parent printer?
0
 

Author Comment

by:jdol2949
ID: 21807529
Ok. So update on the question, employees within the company will be entering in the part number information themselves.  This information will be stored in a table called 'tblBOMItem'.  Where I come in is that I have to create two combo boxes where the first box selects the 'Class', and the second box would then show all available items from a query that relate to the Class selected.  I forgot where I found this earlier on EE, but this is previously posted response is similar to what I need to do now:

Example:
Combobox 1 RowSource: SELECT [], Item.Class FROM Item;
Combobox 2 RowSource: qryItemTbl

Open qryYourQueryName and in the field that you want to link to the 1st combobox, type this in the criteria:
=Forms!frmName.FieldNameToLink


Then put this on your after update for the first combobox:
Private Sub ItemNumCombo1_AfterUpdate()
    Me.ItemNumCombo2.Requery
End Sub

My problem is not selecting the class, I have this as my first combo box row source statement:
SELECT DISTINCT Class FROM Item ORDER BY Class

My problem is getting the second box to show all of the parts that tie into the selected class.  I'm using a query view called 'qryItemTable', which currently populates all the information within the ItemNumber and Class columns.  Within the design view of the query, I entered:
=Forms!BOMForm.Class in the 'Criteria' field of the query.  Is this where my problem lies in tying the two combo boxes together?
0
 
LVL 44

Accepted Solution

by:
GRayL earned 1500 total points
ID: 21813663
From the way the question was framed, ie. Printers Old, Printers New, and Assy Parts all in the same table, In order get a list of assembly parts applicable to a specific printer, the table needed a field named Master or Parent in which the AssyParts listed the ItemID for a specific printer.  As far as I am concerned I answered the question.  The fact this the asker's subsequently disclosed 'business rules' prohibit implementing the solution is not relevant.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question