By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

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

P: 17
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
Oct 5 '07 #1
Share this Question
Share on Google+
3 Replies


Scott Price
Expert 100+
P: 1,384
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
Oct 5 '07 #2

P: 17
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:

Expand|Select|Wrap|Line Numbers
  1. Insert Into tbldelivery_items (short_name, serialnumber) Select serial_number, product_description From tblstock Where serial_number=" & "'" & SerialNumber & "'"
  2.  
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
Oct 5 '07 #3

Scott Price
Expert 100+
P: 1,384
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
Oct 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.