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  1841 
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 one table, 36 fields. The record_id field is an
autonumber field (long int) and primary key. The rest of the fields
comprise of 30 text fields, 3 memo fields, 1 date field and 1 currency
field. Text fields vary in number of characters allowed. ...
|
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 in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I...
|
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:
tblItems
ItemID
ItemLabel
|
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 immediately grey out, and don't come
back until I restart Access. MS' terminally inscrutable 'help' doesn't
help, and I've tried everything I can think of. Been using this database
for 5 years in Access 97--what's changed?
|
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 setting:
FieldID : Field Size - Long Integer
New Values - Increment
| | |
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 I'm adding a customer. The Customer fields are mostly foreign
keys that refer to primary keys in other tables, left join instead of
junction tables at this point. So, when I want to add a customer
record, I also need to add records to the other...
|
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 code that will successfully copy a record and append the
information to a new record in the same table (parent table) within a
form. However, there are related child tables with primary keys (set
to Autonumber) stored in sub-forms. That information...
|
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 records. Should I design a new
form for this or can I somehow make this work in the same form.
Thanks in advance,
john
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
