473,327 Members | 1,997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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
3 1822
Scott Price
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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

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

Similar topics

0
by: Frances | last post by:
Hi All, I'm having a problem trying to add a record to a simple Access 2000 db (db is very similar to an address book but with more info than the usual address, phone, etc.). The database is...
2
by: Daniel | last post by:
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data...
3
by: Tom Mitchell | last post by:
All: I'm stumped on a query. How do I find duplicates in a table where one of the duplicates has values is a certain field and the other doesn't. For example, I have the following table: ...
1
by: Chip | last post by:
Just installed Access 2000 on a new WinXP computer and imported (converted) a single user Access 97 database. When I try to manually "copy" a record, the "cut", "copy", and "paste" options...
1
by: GGerard | last post by:
Hello I am working with Access 2000 I have two tables joined on a one to many relationship between two fields: Table1:FieldID (one) is joined to Table2:FieldMyID (many) Field Properties...
5
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example...
19
by: davidgordon | last post by:
Hi, I need some pointers/help on how to do the following if it possible: In my access db, I have the following: Tables: Products, Sub-Assembly, Product-Pack Table, Products
2
by: Swinky | last post by:
I hope someone can help...I feel like I'm walking in the dark without a flashlight (I'm NOT a programmer but have been called to task to do some work in Access that is above my head). I have...
7
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.