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
3 1822
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
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: - Insert Into tbldelivery_items (short_name, serialnumber) Select serial_number, product_description From tblstock Where serial_number=" & "'" & SerialNumber & "'"
-
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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:
...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |