Append query update the wrong field (1 Viewer)

[Frankybear99]

Hi All
I have created an append query and one of the field I am using is coming from a Combo Box on the table (I think the problem is here) so when I click on View is showing the correct information but when I click Run, it is updating no what I see on view if not the ID field related to that information

The original table call PRODUCT has 3 fields ID / PRODUCT & CATEGORY - On the table design CATEGORY is a Combo BOX linking with another table call CATEGORY and on this table I have 2 fields = ID and CATEGORY
This table CATEGORY has ID=1 / CATEGORY = METAL & ID=2 / CATEGORY= WOOD

Then I have another table call PRODUCT2 with the same fields as the original table PRODUCT - I have checked and all fields are the same format as ShortText

Then I have created an append query to update some products from PRODUCT to PRODUCT2

When I view this query is showing the following CATEGORY METAL & WOOD which if correct but when I press RUN and I go to view the table PRODUCT2, It shows CATEGORY 1 & 2 instead of showing METAL and WOOD

Please could you help me to solve this issue.
sorry I am a little bit new and I cannot fix it
Thanks so much

 

[arnelgp]

your using Category as Lookup table.
you have it set up that you are Selecting ID and Category column from Category table.
you need to Set your Combos' Bound Column to 2 (the category field, not 1, the ID).

or you need additional step on your query to fix that.
instead of using Forms!yourForm!CategoryCombo, you need to use
Dlookup:

Dlookup("Category", "tblCategory","ID = " & Forms!yourForm!CategoryCombo)

 

[Pat Hartman]

Then I have created an append query to update some products from PRODUCT to PRODUCT2

Append queries do no update existing records, they add new records.

The product table should show the CategoryID. The text description is in the category table. The combo box is bound to the CategoryID so that is what is being stored even though the combo shows the text value.

 

[Frankybear99]

your using Category as Lookup table.
you have it set up that you are Selecting ID and Category column from Category table.
you need to Set your Combos' Bound Column to 2 (the category field, not 1, the ID).

or you need additional step on your query to fix that.
instead of using Forms!yourForm!CategoryCombo, you need to use
Dlookup:

Dlookup("Category", "tblCategory","ID = " & Forms!yourForm!CategoryCombo)

Thanks it works with Dlookup