Update Combo Box RowSource

I'm using the following code ('borrowed' and modified from TBÁrpi) to update the RowSource of a secondary combo box based on the selection in the primary combo box. TYPEEQUIP is the primary selection and MODEL is the secondary. Both combo boxes are on form frm_Inventory.

<code>
Private Sub TYPEEQUIP_AfterUpdate()
Dim strRowSource As String
strRowSource = "SELECT qry_HardwareType.model FROM qry_HardwareType WHERE qry_HardwareType.typeequipcode=frm_Inventory.TYPEE QUIP"
Me.MODEL.RowSource = strRowSource
Me.Repaint
End Sub
</code>

This will try to update the RowSource of MODEL, but instead of looking in frm_Inventory.TYPEEQUIP for the value to work with, I'm prompted for the value ("Enter Parameter Value" - "frm_Inventory.TYPEEQUIP" is the message in the parameter prompt box).

My VB skills are bad/nonexistent, so I may be missing something fairly simple. Also, this was originally provided as a solution to a similar question involving a list box instead the secondary combo box. Don't know if list vs. combo box matters here...

TYPEEQUIP has 2 columns in its RowSource. The first column, that will match values in qry_HardwareType.typeequipcode, is hidden, and the second displays a user friendly interpretation of the code. This field sucessfully stores the correct equip code in the table for the user friendly interpretation selected. This probably doesn't matter, just trying to provide as many possible causes as possible.

Any ideas on what I'm doing wrong?

Here is a very easy and clean way of doing what you are asking

look at frmsales

S-

You, my friend, are a demi-god. Like I said, something fairly simple that I'm not quite advanced enough to know much about. I've been tooling with that for days.

Appendix to Question:
Now, I have two other fields that I'd like to populate from the results of Combo2 (from the qry used by combo2). They each can only have 1 value for each outcome of Combo2 (They're the Manufacturer and Series of the selected Model [in Combo2]). I can get the values to display properly in a one line list box (fieldname.Requery in the Combo2 After_Update event), but the values don't get stored to the table unless I click on them. Is there a simple VB command to select the displayed values in a list box?

It sound like what you want to do is select your item from the first dropdown and instead of actually displaying items in the list box to be selected, you want to directly add that entire list (that would have shown in the listbox) to your table. IS this correct

S-

Correct. Adding the 'entire' list consists of one line, but yes, that is accurate. I want to commit the list options to the table.

how comfortable are you with VBA?

S-

can't write from scratch, but i can usually understand something if i see it.

Without knowing what your current forms look like. You have two options

option1 - full use of ADO or DAO
You will write VBA using DAO or ADO recordsets. You will use the criteria in the intial combobox to generate the criteria for the SQL Code you will use in VBA, You will openup a recordset, based on teh SQL code, take the information from this recordset (that you want to add to another table), close the recordset, open another recordset to the table you wish to add the information to, add the record, close the recordset and you are done. The following link as a sampel DB or mine uploaded on it that you can download to look at use recordsets in VBA. DB is called vbaunbound

http://forums.aspfree.com/showthread.php?t=23043

option2 - use queries and recordset
You create a query, that when run will add your record to the table, that will use the criteria based in yoru first cmbobox. Then you use VBA to execute the query in code.


Either option has it good and Bad points.

Let me know which on you would like to try and I will help you.

S-

I won't be adding that many records to make that much coding/extra work worthwhile (I've probably already crossed that line). I can deal with *click click* to get the values into the fields. The big use for what I'm building is to verify inventory - hopefully our records are accurate enough that I don't have to add anything!

Thanks for your help though.