NEED HELP! Adding a button on the form to move a record from a table to another

[IslamGaber15]

I'm trying to add a button on the form to move a record from the table Not Received to the table Received. However, i have two Queries 1- qryDeleteNotReceived. 2-qryAppendReceived when I try to use them i get this message:

Kindly I need help

 

[June7]

Instead of "moving" records, why don't you just change the value of a field to indicate change in status?

That's a prompt for input because Access doesn't know what the reference is, perhaps form is not open or the pathing is incorrect.

 

[IslamGaber15]

Instead of "moving" records, why don't you just change the value of a field to indicate change in status?

That's a prompt for input because Access doesn't know what the reference is, perhaps form is not open or the pathing is incorrect.

Dear June7 thank you for your reply. Problem solved. I deleted all the relations between tables and the button I made to transfer the records is now working.

​

​

 

[Gasman]

Well that was not the cause of the problem. probably because you missed .Form from the subform syntax?

 

[GPGeorge]

Dear June7 thank you for your reply. Problem solved. I deleted all the relations between tables and the button I made to transfer the records is now working.

​

​

You still have a significant table design problem.

The proper design of tables in a relational database application is NOT to have the same records in two different tables.

You should have one table. It should not be named "Received" nor "Not Received", though. It should be named to reflect the entity being stored in that table. Let's call it tblProducts, for example, or tblPurchaseOrder. Or whatever other entity you are tracking, not its status.

In this table you need to add a field called "ReceivedDate". This is a date field. It stores the date on which that entity was received.

When you want to see if something has been received, or not received, you query that table for Null ReceivedDates, or non-Null ReceivedDates.
The WHERE clause of the query can be filtered to show only records where there is a value in the ReceivedDate field, or it can be filtered to show only records where there is not a value in the ReceivedDate field.

Given the presence of this particular table design flaw, it might be a good idea to review the rest of the tables for proper Normalization. There may be additional problems that you need to resolve.

Can you provide a copy of the current accdb to help you review its design?

Thank you.

 

[The_Doc_Man]

Given the above advice, may I respectfully add in that you probably should study a bit about Database Normalization. You would recognize that you don't move things around so much as you just update status values, then use queries to select items in a given status (or NOT in a given status).

If you use this forum's SEARCH facility, you can just search for "Normalization" because this IS a database forum. For a general web search you must use "Database Normalization" because normalization is used in other disciplines as well, including chemistry, math, pharmacology, psychiatry, and international relations.

IF you do a web search, start by reading articles originating from .EDU sites. Once you are more comfortable, look at some of the .COM sites. They often have very good articles but also often have something to sell.

From a practical viewpoint, the less you have to move something, the more stable it stays. Updating a status field is FAR preferable to copying from one table to another. And such a move would violate normalization rules as well.