Autofill info in Subform

[mgstlucia]

I have a Form (frmsOrders) with a subform (frmOrdersDetails Subform). I am trying to automatically fill in the Item Description, Unit and Price when choosing the SKU# from a combobox. I have this working perfectly for the Customer and Address info in the main part of the form, but the same syntax does not work in the Subform.

I have read the 'How to refer to items in Subform' article in the How To section and many posts on the subject. I also had success with this in the past, but it is just not working this time. I'm thinking it had something to do with the way my forms were linked and the relationship I had. I fixed all of those things and the form is working the way I want except for this one thing.

This is what I have in the After Update Event in the Name combobox on the main form and it works fine:

Private Sub Name_AfterUpdate()
Me![Rank] = Me![Name].Column(2)
Me![Address1] = Me![Name].Column(3)
Me![Address2] = Me![Name].Column(4)
Me![City] = Me![Name].Column(5)
Me![State] = Me![Name].Column(6)
Me![ZipCode] = Me![Name].Column(7)
Me![CAPID] = Me![Name].Column(8)
End Sub

I changed the code in the After Update Event in the SKU combobox on the subform to this, but it is not working.

Private Sub SKU_AfterUpdate()
Me![frmOrderDetails Subform].Form![Item Description] = Me![SKU].Column(2)
Me![frmOrderDetails Subform].Form![Unit] = Me![SKU].Column(3)
Me![frmOrderDetails Subform].Form![Price] = Me![SKU].Column(4)
End Sub

Any help would be appreciated.
Thanks

[puppydogbuddy]

If the SKU combobox and the autofill fields are both on the subform, try changing this:

Private Sub SKU_AfterUpdate()
Me![frmOrderDetails Subform].Form![Item Description] = Me![SKU].Column(2)
Me![frmOrderDetails Subform].Form![Unit] = Me![SKU].Column(3)
Me![frmOrderDetails Subform].Form![Price] = Me![SKU].Column(4)
End Sub

To this

Private Sub SKU_AfterUpdate()
Me![Item Description].Value = Me![SKU].Column(2)
Me![Unit].Value = Me![SKU].Column(3)
Me![Price].Value = Me![SKU].Column(4)
End Sub

I am assuming that you are aware that column indexes in Access start with 0 for the first column on the left, then 1 for the next column and so forth.....so if the SKU is the first column of your combobox and description is the next column, the column index for the Description column would be 1, not 2 as shown below:

Me![Item Description].Value = Me![SKU].Column(1)

You may have already taken this into consideration; this is just in case you were not aware of how the column indexes are derived.

[mgstlucia]

Thank You. That worked but I'd like to understand why.

The problem now is that when I choose a 2nd and 3rd SKU#, the ItemDescription,etc. updates all of the items on the subform with the current one. I'm assuming that it is because the ItemDescription, Unit and Price are unbound controls. When I try to set the controlsource of the control the dropdown box only shows the fields for the OrderDetails table which is the recordsource of the subform. So, I tried to use the Expression Builder to point it to the Products table/ItemDescription field, but ended up with #Name? in the control when I open the form. This is what the controlsource looked like when I used the Expression Builder: [tblProducts]![Item Description].

I was aware of how the Combobox columns are indexed. I have a hidden column which is column 0, SKU# is column 1.

What should I do next?
Thanks

[puppydogbuddy]

Thank You. That worked but I'd like to understand why.

The problem now is that when I choose a 2nd and 3rd SKU#, the ItemDescription,etc. updates all of the items on the subform with the current one. I'm assuming that it is because the ItemDescription, Unit and Price are unbound controls. When I try to set the controlsource of the control the dropdown box only shows the fields for the OrderDetails table which is the recordsource of the subform. So, I tried to use the Expression Builder to point it to the Products table/ItemDescription field, but ended up with #Name? in the control when I open the form. This is what the controlsource looked like when I used the Expression Builder: [tblProducts]![Item Description].

I was aware of how the Combobox columns are indexed. I have a hidden column which is column 0, SKU# is column 1.

What should I do next?
Thanks

1. To answer your question as to why your syntax did not work: You used the "Me" operator as if Me was the main form, but the combobox and the autofill controls are both on the subform and the code would be executed from the subform....therefore Me in this case is the subform, not the Main form.

2.Yes, your problem with the SKU overlays was due to the fact that the subform is a continuous form, and the ItemDescription, Unit and Price controls were unbound. However, the combobox should be left unbound.

3. What you do next......
a. be sure the record source of the main form is set to the Table that has the fields that any controls are bound to (if any).
b. be sure the record source of the subform is set to the table that has the fields that all the subform controls (excluding the combobox) will be bound to.
c. bind (set the control source) of all the controls (except for the combobox) to the controls underlying fields.
c.the subform combobox is to be left unbound, but you need to set its row source to a query that outputs the fields that you want to display from both tables.

[mgstlucia]

Got it!

Thanks
Mary