Append Query - limiting the update to the data on the current record

[Meryl]

I would like to create a record in a new table from an
existing one when the user pushes a button on a form.

For example:
I have a Prospective Donors table with contact
information. Once a donation is received I would like to
create a record in the Donor Table with all the contact
information.

I am trying to do this with an append query from a form.
Right now, when the user pushed the command button ALL
the records in the Prospective Donor table are appended
to the Donor table. I don't know how to limit the append
to the current record on the form.

Meryl

 

[Michel Walsh]

Hi,




DoCmd.RunSQL "INSERT INTO otherTable( listOfFields) VALUES(
listOfForm!FormName!ControlName ) ;"



should do. Note that using DoCmd, you can use Forms!FormName!ControlName
without delimiters. If you use CurrentDb.Execute, instead of DoCmd.RunSQL,
you have to map out the values with the right delimiters:


CurrentDb.Execute "INSERT INTO otherTable( textField, dateField)
VALUES( """ & varStringValue & """, " & Format( myDateValue,
"\#mm-dd-yyyy\#") & ") ;"


compared to

DoCmd.RunSQL "INSERT INTO otherTable( textField, dateField) VALUES
(FORMS!FormName!ControlWIthString, FORMS!FormName!ControlWithDate) ;"





Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer (MVP)]

You might also consider just adding a field to your table DONORS (which would
hold both prospective and current donors). The field would be set to True when
prospective donor became a real donor. That way you don't have to transfer
information from one table to another.

 

[Meryl]

Hi,

I don't know VBA code, so I'm not sure where to put what
here. Can you give me some help? It's not clear to me
what I'm supposed to substitute. "Other Table" should be
the table name that I want to insert the record
into. "List of fields" are the field names in the other
table that I'm updating, correct? What is the syntax? Do
I just list them with commas? (Sorry I seem so dumb
here). I have no idea what the last part is supposed to
look like.

Meryl
PS - Can you recommend a good book to help me learn this?

 

[Michel Walsh]

Hi,


You do it in the after update event for the form, since at that moment, you
know the modifications have been accepted in the database.

You were right in your assumptions. An example can be:


CurrentDb.Execute "INSERT INTO Table1( itemID, quantity ) VALUES( 1010, 123)
; "


that would add a record in table1, with values 1010 and 123 for itemID and
quantity, respectively. If table1 has other fields, they will get their
autonumber value or their default values, as usual, unless you include them
into the list of fields, and match them with a value in the VALUES list.


John L. Viescas books cover a large span (on either Access and SQL).
Take a look at them at your local bookstore. For SQL in itself, take a look
at "SQL Queries for Mere Mortals", at Addison-Wesley, as example.


Hoping it may help,
Vanderghast, Access MVP