syntax for opening table, adding record ??

[expatCanuck]

Greetings -

Once upon a time, a decade or so ago, I was MS-certified in Access.
Haven't used it in a while, and now I need to.

I have a subform which contains a list of records related to the record displayed
in the parent form. Also on the subform is a comboBox providing the user with
a list of records available for association with the parent.

I can write the VBA needed to read the pkID of the parent record, as well
as the pkID of the record to be associated.

I'd like to take these two known values and insert them as a new record in the junction/intersection table that links the parent & children on these forms.

Can anyone remind me (or point me to a decent example) of the necessary syntax to insert a record into a table, vConcordance, specifying the necessary data?

Don't know if it's more straightforward to write & execute the SQL and execute that statement, or open the (database object and?) table object and add the record that way.

All help welcome.

Thanks kindly.

- Richard

 

[expatCanuck]

here's the SQL answer

Dim SQLstr As String
Dim parentId As Long
Dim childId As Long

parentId = Me.Parent!txtParentId
childId = Me.cbChildLookup.Value

SQLstr = "INSERT INTO vConcordance ( parentId, childId )"
SQLstr = SQLstr & " VALUES ("
SQLstr = SQLstr & CStr(parentId) & ", "
SQLstr = SQLstr & CStr(childId) & ");"

' MsgBox SQLstr ' for dev, to inspect the SQL string generated '

DoCmd.RunSQL SQLstr

 

[expatCanuck]

and here's the recordset approach

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTable/QueryNameHere)

With rst
.AddNew
!FieldName1 = "Whatever"
!FieldName2 = "Whatever2"
!FieldName3 = "Whatever3"
.Update
End With

rst.Close
Set rst = Nothing