Combo box from lookup table

[jighead]

I am new to Access and VBA, and am having a problem getting a combo box to work as expected.

I have two tables:

tblInventoryTransactions
PK - TransactionID (autonum)
ProductID (int)
TransactionType (int)
etc

tblTransTypes
PK - TypeID (autonum)
TransactionName
Action

tblTransTypes is simply a lookup table that looks like:

1 Shipped Removal
2 NewPurchase Addition
3 CustReturn Addtion
etc

I am trying to create a form for inventory transactions, and I would like a combo box that drops down the values of tblTransTypes.TransactionName, and then populates tblInventoryTransactions.TransactionType with the corresponding TypeID.

If I simply set the RowSource of the Combo Box to tblTransTypes, or 'SELECT tblTransTypes.TypeID, tblTransTypes.TransactionName FROM tblTransTypes;' the list shows up as expected, but I can't actually select anything, which is understandable, because the lookup table should be static in this case.

If I go into design view for the RowSource query and add my tblInventoryTransactions, I have a relationship of tblTransTypes.TypeID -> tblInventoryTransaction.TransactionType, which adds an INNER JOIN to the SQL statement. Once the INNER JOIN is there, the combo box drops nothing, and will accept no input. I have tried reordering my fields in the query, but can't get this to do what I want.

How do I get a combo box to display values from the TransType lookup table, but insert the TypeID into my Transaction table?

Thanks

 

[dkinley]

If you are trying to store an autonumber ID as a foreign key into another table, the field type must be long (do this first).

I am not sure why you are doing a join if you are only doing a lookup against one table?

I think your problem is the formatting of the combo box. Have your Row Source have the following columns in this order, PK - TypeID, TransactionName, Action. In the Column Count, use 3. In the Column Widths, use 0";1";1.5", for the Bound Column, use 1. Last, set the control source of the combo box to the foreign key field.

Hope that helps,
-dK

 

[jighead]

If you are trying to store an autonumber ID as a foreign key into another table, the field type must be long (do this first).

I am not sure why you are doing a join if you are only doing a lookup against one table?

Yeah, if I only query the TransactionTypes table, and leave the InventoryTrans table out of the query, the list shows up

However, the problem seems to be more than just this combo box. This combo box is part of a form that should record inventory transactions. I built the form from this query:

SELECT tblInventoryTransactions.TransactionID, tblInventoryTransactions.TransactionType, tblInventoryTransactions.Quantity, tblInventoryTransactions.UnitCost, tblInventoryTransactions.TaxRate, tblVendors.VendorName, tblVendors.VendorLocation FROM tblVendors INNER JOIN tblInventoryTransactions ON tblVendors.VendorID=tblInventoryTransactions.VendorID;

Again the tblVendors is just a lookup table storing an autonum PK VendorID, VendorName, VendorCity, VendorPhone, etc.

When I open this query in datasheet view, I can enter text into the two Vendor fields, but cannot enter anything into any of the fields from the InventoryTransaction fields.

What can I check to see why I can't write through this query?

Thanks for the help!

 

[dkinley]

It could be the relationships or the type of query you are using. I am not that great at reading and interpreting to find a resolution, but can if I can manipulate the DB. Is there any way you can post it?

-dK