Expert: Geoff - 2/24/2004

Question
Thanks Geoff for your reply and follow-up. Unfortunately I have lost your reply to my folloup but did have a print of it therefore I have made the changed as follows but having an error message with the "VALUES"

DoCmd.SetWarnings False
sSQL = "INSERT INTO [APPLICATIONS]( SSN, FNAME, LNAME, RECEIVED, CODE, CLOSED, INITIALS ) & _
" VALUES('"& Me.SSN & "'," & Me.FNAME & "," & Me,LNAME & ", " & Me,RECEIVED & ", " & Me,CODE & ", " & Me,CLOSED & ", " & Me,INITIALS );"
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

The "VALUE line gets highlted in red and a compile error: "End of Statement" show up. Any idea? Thanks and sorry for the delay in answering (was sick with flue a few days!).
-------------------------
Followup To
Question -
Hello,
I would like to create an update query to append a current record (a record that I am currently entering in a form) from that form which has it's record source to Table A to another table, named Table B. Both tables have the same fields and the primary key is for both the field ID which is Autonumber.

Form A is the form which will have the current record to copy into Table B.
Is there a way I can copy the current record, meaning the one I have just typed into Table B?
I have tried to create the query but having some problems. I believe it is do to with the fact the my form A has not been closed therefore the data has not been stored yet into it's table.
But if I close it how will the data be copied?
I have tried to add a criteria to a field (ssn) of my form A, like [forms]![formname]![ssn] hoping that it would clone the data but still cannot make it working.
Could it be something to do with the AutoNumber PK?
Do you have some solution? Tanks.
Answer -
Hi JohnPaul

1. Try canging the design of Table B so that its PK is Number LONG instead of autonumber.

2. The form's Current event ocurrs each time you change records, so this is an ideal time to copy the record through. The code could pick up the values directly from the form..

Sub Form_Current()

Dim sSQL as String

  DoCmd.SetWarnings False
  sSQL = "INSERT INTO table_B( Aaa, Bbb, Ccc . . ) & _
         " VALUES ( '" & Me.Aaaa & "', " & Me.Bbbb & ", " & Me,Ccc ..... & " );"
  DoCmd.RunSQL sSQL
  DoCmd.SetWarnings True

End Sub

In the above Aaa is a text item and Bbb/Ccc are numeric..

I hope this helps

Kind regards

Geoff


Answer
Hi John Paul

Try inserting a quote (") at the end of the INSERT line (my typo), plus you may need single quotes (') in the VALUES line to delineate text - I have inserted them around LNAME & FNAME. Numerics don't need the single quotes. Also change out commas for periods.

DoCmd.SetWarnings False
sSQL = "INSERT INTO [APPLICATIONS]( SSN, FNAME, LNAME, RECEIVED, CODE, CLOSED, INITIALS )" & _
" VALUES('"& Me.SSN & "', '" & Me.FNAME & "', '" & Me.LNAME & "', " & Me.RECEIVED & ", " & Me.CODE & ", " & Me.CLOSED & ", " & Me.INITIALS );"
DoCmd.RunSQL sSQL

Have another go and feeback if you need more help

Kind regards

Geoff

DoCmd.SetWarnings True