append query to a look-up field on a table

[John1Chr]

Hi all,

I'm trying to append data from a warehouse and on one of the fields I want to keep the field as a look-up. I try using "" in the field to trick it but I keep getting a validation error. Any suggestions?

 

[dhookom]

If it's a lookup field (IMHO never a good idea) you would need to insert a value of the same data type and possibly in the table used for the lookup.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[John1Chr]

Yes, what we're trying to do is have a table that looks up a field on another query/table instead of manually typing it in.

We keep pulling in financial transactions by acceptance date but it might have the wrong account number and we have another field called correct account number and it has a look-up to a warehouse query to allow the clerk to assign the correct account number without typing.

When we try to append to the look-up field it is not possible.

 

[dhookom]

Let's say you want to append to the Orders table in Northwind. You can't insert [blue]"Alfreds Futterkiste"[/blue] into the CustomerID field. You would need to insert [blue]"ALFKI"[/blue]. If you only have the full name value, you will need to link to the Customers table so you can use the CustomerID field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[John1Chr]

What the individual wants to do with the record is append data from the warehouse on Monday for the whole previous weeks financial transactions. There are 3 fields on the table for notes to make just-in-case the transaction has different needs and adjustments. These fields I'm able to fake by appending with "". There is one field;however, that the individual wants to be able to "drop in" the correct account number if the account number is false but leave the old account number in its original field. This field uses a look-up field to make it easier/more accurate for the clerk to insert the correct account number.

I would design the database differently if it were mine.