• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Append query update the wrong field (1 Viewer)

Frankybear99

New member
Local time
Today, 21:37
Joined
Aug 17, 2020
Messages
2
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

error reading drive A:
Local time
, 05:37
Joined
May 7, 2009
Messages
10,627
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

Super Moderator
Staff member
Local time
Today, 17:37
Joined
Feb 19, 2002
Messages
29,803
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

New member
Local time
Today, 21:37
Joined
Aug 17, 2020
Messages
2
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
 

Users who are viewing this thread

Top Bottom