Insert Query

  • Thread starter Thread starter Martina
  • Start date Start date
M

Martina

I am having trouble trying to work what the SQL code is to do the following.

I have a form called Approval and this form showns the data from a table
called ItemRequests, at the bottom of the form is a button called "Approve"
and when you click this button what i want to happen is certain fields shown
on the form (the info in these fields is stored in the ItemRequests table)
to be inserted into another table called ApprovedItemRequests. There is a
field in ItemRequests called RequestID which is autogenerated.

These are the fields from ItemRequests that I need to go into
ApprovedItemRequests;

Request ID (Autogenerated field)
LLOID (text)

I also need the current date to be put in a field in ApprovedItemRequests
when this other data is inserted in there.

What is the code I would use to do this???
 
If the RequestID is an Access Autonumber, you don't need to include it in
your Append Query.
Assuming LLOID is displayed in the textbox txtLLOID, the code could look
like this:
CurrentDB.Execute "INSERT INTO ApprovedItemRequests (LLOID) VALUES ('" &
txtLLOID & "')"
(Note that this is a simplification of the most general case - it doesn't
cover the possibility of an apostrophe in LLOID.)

I do find myself wondering, however, if you might not find it simpler just
to add an Approved checkbox to your ItemRequests table.

HTH
- Turtle
 
If you intend to relate the ItemRequests table and
ApprovedItemRequests table by the RequestID field in
ItemRequests and the Request ID field in
ApprovedItemRequests, don't autonumber the Request ID
field in ApprovedItemRequests.
The code for appending a field to ApprovedItemRequests
might look something like this, where [Request ID],
LLOID, and DateApproved are field names in a table named
ApprovedItemRequests; lngID and txt are variables into
which you have placed your data from the screen:

DoCmd.RunSQL("INSERT INTO ApprovedItemRequests ( [Request
ID], LLOID, DateApproved )
SELECT " & lngID & " AS [Request ID], '" & txt & "' AS
LLOID, Date() AS DateApproved;")

One other note. If possible, you may want to change your
ItemsRequested table and ApprovedItemsTable to remove the
space in the field name "RequestID". You would eliminate
the need for the brackets around the field name in the
above DoCmd statement, and your life will be much easier
if you ever need to move this to SQL Server...

Todd Lemen
-----Original Message-----
I am having trouble trying to work what the SQL code is to do the following.

I have a form called Approval and this form showns the data from a table
called ItemRequests, at the bottom of the form is a button called "Approve"
and when you click this button what i want to happen is certain fields shown
on the form (the info in these fields is stored in the ItemRequests table)
to be inserted into another table called
Click to expand...
ApprovedItemRequests. There is a
 
Back
Top