How to write an Append Single Record SQL in VBA

[cer5yc]

I need to write an append single record SQL statement in VABA and I have no idea how to do it. I have a button on my "Contract Data" form that the user clicks to save a record. When the user clicks the button I want the ID # (which is the primary key and an Autonumber) to append itself to my "Financial Data" table, thus creating a new record in the Financial Data form with the same primary key number. However, I only want the append query to append a single record - the record that was just created. Can someone please tell me how to write the code. Thanks!

Pertinent Info:
Table where data is coming from "Data"
Field where data is coming from "ID"
Table to append data to "Financial Data"
Field to append data to "Financial ID"

Private Sub SaveRecord_Click()
On Error GoTo SaveRecord_Click_Err

On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord

DoCmd.RunSQL NEED TO APPEND THE RECORD HERE

If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If


SaveRecord_Click_Exit:
Exit Sub

SaveRecord_Click_Err:
MsgBox Error$
Resume SaveRecord_Click_Exit

End Sub

 

[dbanker]

try this

First create a generic query and save it as any name you wish

'Define the varibles being used
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("Name of generic query")

StrSQL = "INSERT INTO Financial Data (Financial ID) VALUES ('" & ME![ID] & "');"
CurrentProject.Connection.Execute strSQL

 

[cer5yc]

What do you mean by create a generic query?

 

[PHV]

DoCmd.RunCommand acCmdSaveRecord
DoEvents
DoCmd.RunSQL "INSERT INTO [Financial Data] (ID) VALUES (" & Me![ID] & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbanker]

I'm familiar with Microsoft Access, so using it all you have to do is use query wizard to create a query connected to any table ( it will change when the SQL statement is run) and then save it as any name you desire. If you are using a different database engine that may be made differently.