Move Records from one table to another if a specified field matches in destination table

[Jmkrull]

Hello,

So, I am creating a history tab for backorders and their actions taken. There are three actions: SHIP, CANCEL, HOLD. Right now, I have records moving no problem if they are SHIP or CANCEL based on adding simple criteria for that field if it exists. The issue that I'm having is that once a record has been set to CANCEL or SHIP and it has prior HOLD statuses, I want to clear out these hold statuses too. Ideally, I would have the query look to see if the Transaction ID exists as a SHIP or CANCEL in the destination (history) table from the transaction table. If it does, move all the hold records. If not, keep them there.

The next step then in another query, I would want to delete these out of the transaction table if the Transaction IDs can be found.

I tried this: =DLookUp("HOLD",[Master Data Table],[Master Data Table]![Sales Doc]=[History Records - SHIP & CANCEL]![Sales Doc])
- I am prompted to enter values for the sales doc and it isn't scanning the tables.

Can someone please help?

Thank you.

[NTC]

hmm well I have to start out with the harsh comment that it is fundamentally a bad design that moves records. the manipulation & use of flag/status fields is all that is needed.

on to your question; the prompt is due to incorrect syntax. DlookUp("fieldname","domainname") is the correct syntax when no Where clause is needed. But I don't think Dlookup is even the correct method if you seek multiple IDs - you should be using a query to establish the correct record set.

[orange]

Getting your tables and relationships set up to support your business facts is a critical part of database.
Having to move records from one table to another seems a poor design, as NTC has highlighted.
Can you tell us in 3-4 lines WHAT the business is; and can you attach a jpg of your relationships window?

Readers need something more than a history tab for backorders....
We want to help, but need some facts and context.

Good luck.

[Jmkrull]

Getting your tables and relationships set up to support your business facts is a critical part of database.
Having to move records from one table to another seems a poor design, as NTC has highlighted.
Can you tell us in 3-4 lines WHAT the business is; and can you attach a jpg of your relationships window?

Readers need something more than a history tab for backorders....
We want to help, but need some facts and context.

Good luck.

It works for the context. The way the users will use this report, those records don't need to be there as the current ones that wouldn't be moved are all that's needed. It just serves as a point for someone to look it up need be. Just go with me on this. I really don't want to hash out the entire business process. It's just how it needs to be...

With that said, I wasn't sure a DLookup was the thing to use as I've never had to move one thing from one table to another based on looking at something in the destination table. How that's done, I'm not sure. It's just the ideal state.

[Jmkrull]

Also, I created this database solely for this report from things linked to SAP and other stuff. It won't alter or manipulate anything other than what is needed for this one report. With that said, it just needs to function to get the desired results for this one thing, regardless of perceived design flaws...

[NTC]

You can accomplish what you seek using a query.

[Jmkrull]

So, I the user manipulates date in a make table, it's saved. Then a macro runs to clear out things flagged as shipped or cancelled based on user action to the history table (stuff can also be marked as held). Later in the day, some employee will re-run the macro to get the current list and lets say something that was previously marked as held ships or cancels, I want the helds moved to the history table as well based on the shipped or cancelled residing in the history table, essentially giving the users a fresh slate every time the macro refreshes the current table. I tried doing this with a query, however, there needs to be criteria existing to do this; hence, why I'm asking.