Combo boxes

[aejester]

Hi,

I am working with a 'maintenance' access program.
I have a form that logs the work done to various things. What I want to do is, when the user selects an equipment type from the Equipment combo box, the inventory combo box should automatically fill with all the inventory items that belong to that equipment type. Also, after changing a equipment type, the inventory combo box should again change to show all the inventory items from that type.

I tried doing this but nothing happens.
Private Sub Combo34_AfterUpdate()
cboInventoryID.RowSource = "SELECT DISTINCTROW tblInventory.inventoryID, tblInventory.equipID, " & _
"tblInventory.EquipmentIdentifier FROM tblInventory " & _
"WHERE (((tblInventory.equipID)=" & Combo34.Value
End Sub

Whats wrong with this code, OR is there a better way to do this?

 

[jebry]

Hi!

You need to add cboInventoryID.Requery to actually change the display. It seems like this could be done in a query which could be used as the RowSource of the combo box without having to change the RowSource every time.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[scking]

Let's discuss this issue first. Since you are inconsistently using a naming convention I will assume the Combo34_AfterUpdate is the Equipment Combo Box you are referring to and you are trying to change the RecordSource property for the cboInventory combo box (How about cboEquipment for a name). It is important to know what the datatype is for the equipID. If it is text then you need to delimit the Combo34.Value otherwise you can do it the way you are.

Text
Private Sub Combo34_AfterUpdate()
cboInventoryID.RowSource = "SELECT DISTINCTROW tblInventory.inventoryID, tblInventory.equipID, " & _
"tblInventory.EquipmentIdentifier FROM tblInventory " & _
"WHERE (((tblInventory.equipID)='" & Combo34.Value & "';"
Me.cboInventory.Requery
End Sub

Numeric
Private Sub Combo34_AfterUpdate()
cboInventoryID.RowSource = "SELECT DISTINCTROW tblInventory.inventoryID, tblInventory.equipID, " & _
"tblInventory.EquipmentIdentifier FROM tblInventory " & _
"WHERE (((tblInventory.equipID)=" & Combo34.Value & ";"
Me.cboInventory.Requery
End Sub

This is assuming that the cboInventory has the ColumnWidth property set to be able to display the first column.

Steve King


Growth follows a healthy professional curiosity

 

[aejester]

The EquipmentID field is NOT visible. So how would i do it then? I don't want to display the id, just the name.

BTW, this is my boss's program, he never names any objects or labels etc etc etc. And I'm the lucky one who has to go in and fix problems. Annoying!

 

[scking]

The query fills the combo box in the order of the items requiested in the SQL. The combo box has a couple parameters which need to be set. The 'Columns' should be the number of items you are requesting in the SQL. and the 'ColumnWidth" should reflect the width of each of the columns. If you have to columns is could be 1",1" Which would set each of the two columns to 1 inch.

Sorry I didn't answer sooner by never got a notice.

Steve King Growth follows a healthy professional curiosity

 

[aejester]

I already fixed this problem thanx to a different post. It was a problem with my 'record source' statement. I can let u know the code if you want to know.

Thanx

Clint