Solved

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

Posted on 2008-06-17
11
274 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 500 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now