Find and copy record from one table to another in Access 2000

[Jiwei06xie]

Dear Experts,

As I am a beginner of Access 2000, could I ask for some help regarding the following situation?

I have two tables called tblDelivery and tblStock, both having a text field SerialNumber. What I am trying to do is to search in tblStock using SerialNumber to see if there is a matching record. if so, I need to cope some relevant fields to tblDelivery, and then delete that record from tblStock.
If no record found, showing a warning message. I roughly know that I need a Select query to find if there is record in tblStock, and then an update/append query or maybe a delete query. I have no idea about writing the code.

Any help is much appreciated!

Kindest regards,

Jay

[Scott Price]

Welcome to the Scripts, Jay!

May I suggest going to a bookstore (or Amazon.com) and getting a comprehensive book on the subject? (Access 2000 Developers Handbook is a good one!)

This will help you to understand the concepts as we help you through the process!

Yes, you will need a SELECT query. No, we will not write the code for you! You will appreciate that we don't wish to take jobs away from people who make a living doing this :-)

Make a start by reading up a bit on queries: An excellent resource (in addition to the book I mentioned) is our Access Articles and Tutorials section.

Once you feel confident enough to start, we'll be more than happy to help troubleshoot for you the parts that you are having trouble with.

Kind Regards,
Scott

[Jiwei06xie]

Thank you Scott for your reply.

I did manage to write a query to find what I want, but failed to insert into the 2nd table with error message like "it didn't add 1 record to the table due to key violation".

What I did is to create a form, where I can type in serialnumber, and a command button. When I give a serialnumber and click the command button, it should run the follwoing query:

[CODE=sql]Insert Into tbldelivery_ite ms (short_name, serialnumber) Select serial_number, product_descrip tion From tblstock Where serial_number=" & "'" & SerialNumber & "'"
[/CODE]
It found the record but failed to insert into the 2nd table, where there is Autonumber as Primary Key.

Any ideas?

Best wishes,

Jay

Welcome to the Scripts, Jay!

May I suggest going to a bookstore (or Amazon.com) and getting a comprehensive book on the subject? (Access 2000 Developers Handbook is a good one!)

This will help you to understand the concepts as we help you through the process!

Yes, you will need a SELECT query. No, we will not write the code for you! You will appreciate that we don't wish to take jobs away from people who make a living doing this :-)

Make a start by reading up a bit on queries: An excellent resource (in addition to the book I mentioned) is our Access Articles and Tutorials section.

Once you feel confident enough to start, we'll be more than happy to help troubleshoot for you the parts that you are having trouble with.

Kind Regards,
Scott

[Scott Price]

That's quick progress :-)

What's the name of your primary key field?

Are you trying to use the serial number as the primary key?

Regards,
Scott