Using Queries and Forms to move data from one table to another

[cbramsey]

Here is what I have:

Issued Checks Table (this table contains all checks that have been issued but not cashed)
Check Number


Check Date
Owner Number
Check Amount

Void Checks (This table contains all checks which need to be voided)
Check Number
Check Date
Owner Number
Check Amount

Currently we are manually entering data into the void checks table through a form

What I would like to do is be able to enter the check number in the Check Voids Form and have it populate the rest of the data for the check from the Issued Checks table into the Void Checks table. I know this has got to be fairly easy to accomplish but my brain is fried right now.

Thanks in advance for any help

[June7]

Don't 'move' records, just change the value of a field and apply filter criteria. Field could be a yes/no type named IsVoid or maybe better a date type DateVoid. Yes, the field will be false (or null) for most records but this is simplest and most efficient approach.

[cbramsey]

We have to move them to the other table because it triggers work flow for the different departments that have to process data.

[June7]

How does 'moving' trigger work flow? Changing value in field can be a trigger. I would NOT do what you describe.

But if you really must, example syntax for one approach:
CurrentDb.Execute "INSERT INTO VoidTable(field1, field2, field3) SELECT fiield1, field2, field3 FROM tablename WHERE ID=" & Me.ID

Select a check record from an UNBOUND combobox list and run code in its AfterUpdate event.

Then to remove record:

CurrentDb.Execute "DELETE FROM tablename WHERE ID=" & Me.ID

[cbramsey]

In the void request table, our treasury department, revenue department, bank representative and owner relations group all have to enter data to verify procedures have been followed for the void. The issued checks table is linked to our sql database and we can't update that table per audit procedures.

[cbramsey]

I guess "moving" data is not the right term, more along the lines of copying, we are not editing the Issued Checks table

[June7]

The issued checks table is a linked SQL table you cannot modify nor edit the record? Okay, I guess that is reasonable justification. Then ignore the DELETE action.

[orange]

I tend to agree with June re approach. In addition I would not use names with embedded spaces --these will lead to syntax errors at some point.

Seems the only thing different in IssuedChecks vs VoidChecks is the name of the table.
One table with a status field may be more appropriate. Is there any other Status that might be applied to a check?

What makes fields unique in each of these tables?

Can you tell us a little more about your workflow and related "triggers"?