Solved Autofill from combo box not filling all fields

[M Costumes]

I'm working on a multiple item form where I want the user to be able to select an item from a cbo box, it autofill some fields, and the user can fill in the other fields as necessary. I got it mostly working, except one field does not auto-fill, so I'm beginning to wonder if it is how I have the forms and tables set up.

table: Inventory
RecordID (autonumber)
InventoryID (text)
SetID (text)
RentalRate (currency)
other fields...

table: RentalInventory
RecordID (autonumber)
InventoryID (text)
SetID (text)
RentalRate (currency)
Discount1
Discount2
other fields....

The form record source is table RentalInventory. On the form, I have cboInventoryID with the following row source:
SELECT Inventory.RecordID, Inventory.InventoryID, Inventory.SetID, Inventory.RentalRate
FROM Inventory;

Once the InventoryID is selected, it auto-fills the SetID as it should, but it does not fill the RentalRate. I have another thread going asking about the VBA for this, as I first thought that's where the issue lied, but I'm wondering if it's how I have it set up in general. Any thoughts? I'm very new to all of this, so thank you for your patience with me. Thanks!

update: the RentalRate does not necessarily need to be stored in table RentalInventory. It does need to be displayed on the form so the user has it as a reference to fill out the other information.

 

[pbaldy]

What is the column count property of the combo? It needs to be 4.

 

[M Costumes]

What is the column count property of the combo? It needs to be 4.

It's set to 1 because I only want col. 1 displayed in the combo. If I change it to 4, it displays all four values (as expected), auto-fills the SetID field, but not the RentalRate field, giving me run-time error 2448 (which is what my other thread is about).

 

[pbaldy]

You control that with the column widths property. The count has to be 4 to get values from other columns. Your column widths property would look like:

1;0;0;0

 

[M Costumes]

You control that with the column widths property. The count has to be 4 to get values from other columns. Your column widths property would look like:

1;0;0;0

Even with that change it still won't auto-fill the RentalRate. The RentalRate does not necessarily need to be stored in the RentalInventory table, just displayed on the form so the user has it as a reference to fill out the other information.

 

[arnelgp]

advise to read more on Access Combobox/Listbox.
Column Count should be The Same with your Number of columns in Combo's RowSource.

 

[pbaldy]

What's the control source of txtRentalRate? It sounds like it has a formula in it or something.

 

[pbaldy]

The RentalRate does not necessarily need to be stored in the RentalInventory table, just displayed on the form so the user has it as a reference to fill out the other information.

By the way, you could use the first method here if you aren't storing the value:

Autofill

 

[MajP]

table: Inventory
RecordID (autonumber)
InventoryID (text)
SetID (text)
RentalRate (currency)
other fields...

table: RentalInventory
RecordID (autonumber)
InventoryID (text)
SetID (text)
RentalRate (currency)
Discount1
Discount2
other fields....

I do not know what these tables represent, but at first glance they send up alarms. However, if you can justify why you store like values in two tables it may make sense. Normally this is not correct.

You have Rental inventory that copies fields InventoryID, SetID, RentalRate. This may be OK if the these are default values that can be changed. However, does it not make sense to just store the inventory RecordID and relate the two tables? Then all of this code also goes away.

 

[M Costumes]

table: Inventory
RecordID (autonumber)
InventoryID (text)
SetID (text)
RentalRate (currency)
other fields...

table: RentalInventory
RecordID (autonumber)
InventoryID (text)
SetID (text)
RentalRate (currency)
Discount1
Discount2
other fields....

I do not know what these tables represent, but at first glance they send up alarms. However, if you can justify why you store like values in two tables it may make sense. Normally this is not correct.

You have Rental inventory that copies fields InventoryID, SetID, RentalRate. This may be OK if the these are default values that can be changed. However, does it not make sense to just store the inventory RecordID and relate the two tables? Then all of this code also goes away.

Table Inventory is a list of every item we have available to rent. Table RentalInventory lists which items are assigned to what rental--looks like I forgot to mention RentalID as a field in that table. Over it's lifetime, one item can get assigned to many RentalID's, so the tables are related.

The PK Inventory.RecordID is linked to FK RentalInventory.InventoryID

So if I'm understanding you correctly, because they are linked, I can get rid of SetID and RentalRate from table RentalInventory. And when I build the form, the form will be based on both tables--as I need to pull info from Inventory for the combo box and to auto-fill, and RentalInventory for the other fields that the user will fill in?

 

[M Costumes]

What's the control source of txtRentalRate? It sounds like it has a formula in it or something.

I've messed with it a bunch, but right now it's set to =[RentalRate]

 

[pbaldy]

I've messed with it a bunch, but right now it's set to =[RentalRate]

That's a formula, so you wouldn't be able to place a value there. Did you try the formula from my link? Or just remove the control source and see if your code works.

 

[M Costumes]

Oh, things were all kinds of messed up so I deleted the from and started over. My tables were not normalized, so I fixed that first. I also had to adjust the column count for the combo box. Using one of the methods from @pbaldy 's blog post they shared, it works now. Thank you all!

 

[pbaldy]

Happy to help!