Autolookup in query not updating underlying table

[nugey67]

I have created a 'junction' table between 2 many to many tables:

1. Jobs table (details all jobs)
2. Jobparts table ('junction' table details Job ID, Part ID, quantity and unit price)
3. Stock table (details all stock).

The fields Job ID, Part ID and Unit price are each lookup fields from the corresponding fields in the jobs and Stock tables with quantity being a manual entry. This works fine.

I discovered you can automatically populate the unit price field using autolookup based on the entry of the Part ID field on the form. However having amended some pre-existing jobpart details randomly on the form when I checked back to the table they had not updated. Similarly when creating new entries while the form 'unit price automatically updated on entry of the 'Part ID' these details did not feed through into the table.

I assume it has to do with how the table was originally set up the field properties will need to be changed, but can some one talk me through what I would need to change and to what please?

I am fairly new to using Access and learn as I go. Thanks.

[jzwp11]

The fields Job ID, Part ID and Unit price are each lookup fields from the corresponding fields in the jobs and Stock tables with quantity being a manual entry. This works fine.

When you say that the fields are lookup fields are you saying that you have set up the fields in the table as lookups (combo/list box)?

If so, you do not want to do that. Check out this site for reasons why it is not a good idea. The lookups should be used on your forms.

I assume that you have a unit price field in both your stock table and your junction table. What is typically done, is include the unit price field from the stock table in the combo box in your subform. You would use an after update event of the combo box to populate the control tied to the unit price field of the junction table.

For example, let's assume that the row source of the combo box looks like this:

SELECT partID, partname, unitprice
FROM stock

The partID of the combo box will be the bound field and it would link to the partID field of the underlying junction table.

You can reference any field's value brought in by the combo box with an expression

me.comboboxname.column(x)

The me. is a shorthand for the current form. x denotes the column in the combo box's row source. Access starts numbering at 0, so if you want the unitprice, x=2.

So the code in the after update event of the combo box would look like this

me.NameOfUnitPriceControlInSubform=me.comboboxname .column(2)

As a general rule, it is best not to use spaces or special characters (#,&, + etc.; the underscore _ is OK) in your table or field names. Also, you want to avoid using reserved words as table or field names as well. Here is a list of reserved words.

[nugey67]

jzwp11,

Thank you for your reply, which is appreciated.

i have tried to follow through your prompts, but unfortunately trhey do not appear to work as hoped.

I have added an attachment of 4 screenshots:

1. Original design view of lookup field which confirms your assumption of the how the row source of the combo box looks in my original table.
2. Revised form showing control in properties table and expression (NB. when amending the PartID the form updates and shows the revised price on screen).
3. Revised design view of lookup field using your suggested expression.
4. Revised datasheet view which confirms that data is still not feeding through.

I did try to attach a copy of the database, but the zip folder is too large unfortunately.

Would you be so kin to look at the screenshot fields to see if you can establish where I am making a mistake. Any more help or step-by-step guide you can give i would be truly grateful.

Thanks

[jzwp11]

Can you provide the details of the part ID combo box on your form and the code you used in the after update event of that combo box

You could try to compact and repair the database to reduce its size and then zip it. That should allow you to post it without running into the size limitation.

[nugey67]

Hi again jzwp11,

I have managed to compact and repair the database and can now attach, another useful tip, thanks.

Hopefully you can now see how I have constructed the tables and form and guide me a little further on what i need to do differently.

Appreciated.

[jzwp11]

See the frmJobSheet in the attached. I used a form/subform design and in the after update event of the combo box in the subform, the price is entered in the bound selling price control after a selection is made and it is captured in the table. The item description is just displayed but not entered into the table since the control is unbound.

I noticed that you still had several lookups in your tables, I got rid of the ones I ran into. BTW, you might want to take a look at Allen Browne's site about dealing with inventory.

[nugey67]

Wow, that is really cool.

Sorry by I have a few more questions on what you have done to aid my understanding in relation to the frmJobparts, if it isn't too much to ask:

1. In design view the job number firled is detailed, but this does not show in the form view. is this because it has been created as a subform to the frmJobsheet?
2. I still do not really understand how you have set up the feed for the stock details in this subform as you say you haven't used a lookup yet that is how it looks to me when clicking on the stock field. Can you clarify, perhaps even provide a step-by-step guide.
3. The selling price is rounded up automatically so doesn't match the details shown when clicking and making a selection in the stock field.
4. How did you arrive at the control source expression for the part description field? Is there an automatic way of poopulating this or is it just general coding that you can pick up as you learn more?

I am really grateful for your time in this regard. Enjoy the rest of your weekend.

[jzwp11]

Sorry by I have a few more questions on what you have done to aid my understanding in relation to the frmJobparts, if it isn't too much to ask:

No problem, read on...

In design view the job number firled is detailed, but this does not show in the form view. is this because it has been created as a subform to the frmJobsheet?

I have it hidden in the subform, you can unhide it if you want but you do not want your users changing it because that is how the form-subform are linked.

I still do not really understand how you have set up the feed for the stock details in this subform as you say you haven't used a lookup yet that is how it looks to me when clicking on the stock field. Can you clarify, perhaps even provide a step-by-step guide.

It is OK to use lookups in your forms just not in the tables themselves. I used the combo box wizard to create the stock combo box in the subform. I used code in the After Update event of the combo box that essentially copies the price from the combo box into the selling price textbox. For the description control, I just use an expression in the textbox's control source that reference the corresponding field in the combo box's row source.

The selling price is rounded up automatically so doesn't match the details shown when clicking and making a selection in the stock field.

You have the datatype of the selling price field in tblJobParts set to long integer. You need to make it a currency datatype.

How did you arrive at the control source expression for the part description field? Is there an automatic way of poopulating this or is it just general coding that you can pick up as you learn more?

This is a well documented expression that is used to display combo box information. I explained how to use it in one of my earlier posts.