Linking Combo Boxes!! Laymans terms!! 1

[dreandre]

Hello All...

I'm tasked with building an Inventory data base for my company.

I have a form called Assignments used to link equipment with company employees. I have four combo boxes; Category, Manufacturer, Model, and Serial Number. These four items are related and together would represent 1 inventory item. Therefore, I would like to link the combo boxes to remove the possibility of wrong data entry. I would like the first box to display possible categories. The second, to filter based on the first selection and display Manufacturers within that catagory. The third box to display Models by selected manufacturer, and then the final box to display the available serial numbers of items that meet the criteria.


The tables involved are TblAssignments, TblItems, TblCategory and TblManufacturer. The Items table is the center table in the relationship. It's Primary Key joins to tblAssignment and it joins Foreign Keys from the Manufacturer and Catagory table.

I'm a beginner with access and as such, I don't know how to use VBA code... if this can be accomplished with query builder and sql statements that would be good.... Unless you have the time to teach VBA in the process!!

Thanks ahead for you input!

Andre

 

[Rolliee]

Andre,

We would need to know the structure of your tables - the fields to send the code to you.


Rollie E

 

[dreandre]

I'm not sure this is the information you are asking for...

The Items table is called tblItems. In this table the needed fields are called pkItemsID, fkCategoryID, fkManufacturerID, txtModel, and txtSerialNumber.

The Assignment Table is called tblAssignment. It is joined many to one to the Items table (fkItemID many to pkItemsID one).

The Catagory table is called tblCategory. It is joined one to many to the Items table (pkCategoryID on to fkCategory many).

The Manufacturer table is called tblManufacturer. It is joined one to many to the Items table (pkManufacturerID one to fkManufacturerID many).

The prefixes I'm using are:
tbl = Table
frm = Form
pk = Primary Key
fk = Foreign Key
txt = Text data type
cbx = Combo box

 

[Rolliee]

If you would like the user to pick these in order, you may do so by making all but the first pick visible = false. Set up the first combobox with the table/ query you want. On the click event, use the proper column of that combo box to set up the next one:
SQL = "SELECT * FROM 'tblAssignment' WHERE CatID = "
SQL = SQL & me.combobox1 & ""

me.combobox2.RowSource SQL

Also make that cbo visible. Do this throughout the process.

As each box comes up, it contains only the items pertainent to the cbo1 than cbo1 and cbo2 and then cbo1 and cbo2 and cbo3


Rollie E

 

[willir]

Andre
It's late so I will be brief.

For data integrity, aside from defining formal realtionships, I like to define my links in the table design. Treating your "assignments" as a type of transaction file with foreign keys pointing to the items, category, etc., the foreign keyes reference each of the master records.

In the table design mode, the top part defines field name and data type. The bottom part defines the details. For example, item_id is the primary key tblItem. In tblAssignments, item_id data type is number, defined as a long integer.

Now, note the second tab, "Lookup". Change the display control from text box to either combo or list box. Define your query which references the tblItem. For example tblItem.Item_Name (ascending), tblItem.item_id. Bind column 2. (Hint: Use DISTINCT or DISTINCTROW where appropriate -- see help for differences)

When you define your form, Access will automaticaly create the combo box form for you.

Since your are linking numeric keyes, changes to the item names, part numbers, etc will be unaffected, and data integrity will be protected.

Hope this is helpful, and not too complex or too simple.

Richard

 

[jsalter]

Dreandre,

I am trying to solve the exact same problem...filtering the choices for the second list box based on the value of the first list box. I understand about makeing the second and third list not visable then visable. I don't understand how you do the filtering. I don't understand Rolliee's SQL statements because I don't know how to use SQL statements.

What if there is a way to put a variable name in the criteria line of a select query grid, then make the value of the variable name = the value of the first list box based on the first list box's "on click event".

Rollie, could you help with this and/or explain "cook book" style how to use the SQL statements that you gave Dreandre ?

Thanks, Jim Salter

 

[jsalter]

Dreandre,

Rollie,

Maybe the FAQ answers my question and Dreandre's. FAQ # 702-681 seems to give a complete and understandable explination. It is under section 9, item e. Click the FAQ tab at the top of this page and scroll to 9e.

When I try this I may need a wee bit of help or information from you Rolliee, if you don't mind checking this thread over the next few days.

Dreandre, Can we work on this directly with each other ? Please telephone me at 478 318-1475 and let's exchange e-mail addresses. Thanks, Jim Salter

 

[dreandre]

Hey Jim... Rollie...

Thanks so much for you contributions...

I still have yet to test your suggestion Rollie... I've been pulled away from the project for a few days. I will read the FAQ that you suggested Jim... I'll try it all out and then post how it works. I'll call you in a day or two Jim to exchange emails.

Thanks again...

 

[dreandre]

Ok!! Thanks to you guys I got this to work!!!

I’ll try to detail my steps as brief and accurate as I can.

The first thing I did was add my Combo Boxes to my form tblAssignments. I named them respectively, CboCategory, CboManufacturer, CboModelNumber, and CboSerialNumber.

Starting with the first Combo box, CboCategory, in properties, I went to row source and used Query Builder. I added the tables, TblCategory and TblItems (because the Items table links all of the fields needed). I right clicked and went to Query Properties I set property Unique Values=yes.

In the first column, I added TxtCategory from the Catagory table and sorted by Ascending.

Next I went in properties to After Update and used the Code Builder. It opened up into Visual Basic (which I know nothing about). I looked for an entry that looked like this:

Private Sub CboCategory_AfterUpdate()

In the next line after this line, I tabbed once and added:

CboManufacturer.Requery

The entire piece of code there looked like this:

Private Sub CboCategory_AfterUpdate()
cboManufacturer.Requery

End Sub

Next, I went to the second combo box, CboManufacturer I went to row source and used Query Builder. I added the tables, TblManufacturer, TblCategory and TblItems (because the Items table links all of the fields needed). I right clicked and went to Query Properties I set property Unique Values=yes.

In the first column, I added TxtManufacturer from the Manufacturer table and sorted by Ascending.

In the second column I added TxtCategory but I removed the check from the Show row. I right clicked in the Criteria row and selected the build option. In the first of the 3 lower boxes I browsed into the form folder and selected my current form TblAssignment. This populated the second box where I picked the name of my first combo box, CboCategory. I then clicked the paste button and clicked OK.

At this point, when running the form, I have two successfully linked Combo Boxes. The procedure that I used for the remaining boxes were similar. I had to remember to set the After Update to requery the next combo box. And in the Query builder, you are adding the field you want to see, and the field you want to filter by (not shown), and then using the build in criteria to filter it.

Thanks Again Everyone!!!

 

[GailD1]

Hello all...

I've followed the instructions provided in this thread and my setup is very similar to Andre's. When entering new data, his procedure works fine.

However, when I try to scroll through previous records the 2nd combo box is blank until I reselect the 1st combo box entry.

Thanks in advance for your help.