Append Query Current Record Only

[KCC47]

I need some assistance setting the criteria for an append query. After the user enters information on the transaction_entry_frm, then there is a command button that saves the record and is supposed to append the primary key, transaction_id, to an invoice_tbl. The command button should only append the current record, but I cannot seem to get that to work properly.



The append query also has a expression to take fields from the transaction_tbl and concatenate them into a single field in the invoice_tbl.

I would appreicate any asssitance.

[June7]

Before I download and go on scavenger hunt through your db, why doesn't it work? What happens - error message, wrong results, nothing? Post the code for analysis.

What objects are involved?

[KCC47]

My apologies. The append inquiry is located on a command button in the transaction_entry_frm. When clicked, it appends all records to the invoice_tbl even if there are already there with matching transaction_id. It also indicates an error that there is a data type mismatch in the criteria expression.

"[Forms]![Transaction_Entry_Frm]![Transaction_ID]"

This is the criteria at present.

[June7]

Tried opening the db, get 'Unrecognized database format'. My 2007 is not recognizing, don't have 2010 here.

What is the code behind the button? Some questions can be quickly answered if the info is just provided up front. You show the control reference within quote marks. The reference to control is a variable. Don't enclose variables within quote marks, otherwise you just get the literal text of the variable name, not the value of the variable. Concatenate variables. Something like:

CurrentDb.Execute "INSERT INTO Invoices(transaction_id) VALUES(" & [Forms]![Transaction_Entry_Frm]![Transaction_ID] & ")"


I am distressed by "take fields from the transaction_tbl and concatenate them into a single field in the invoice_tbl". Why would you save discrete data as a concatenated string into a single field?


If you are getting data type mismatch error, could be because of enclosing the control reference in quotes.

[KCC47]

Removing the quotation marks seems to have fixed the issue.

I needed a way to create an invoice # that would mean something to end users, so I choose to concatenate three pieces of informatoin, each of which have significance to the users.

Thank you

[June7]

Okay, but if you are using an autonumber transaction ID as PK/FK, it is not necessary to duplicate those other fields. They can always be retrieved by query that joins on the ID fields and the compound identifier constructed when needed. But then again I guess it won't hurt for you to save as long as there is absolutely no way those 3 values will ever be edited, making the saved compound value meaningless.