Run an Append Query in VBA Code in Access 2007 (1 Viewer)

[Pauline123]

Hi all - just wondering if someone could give me a hand.

Have a code set up that I am trying include an append query to allow me to populate another table.

Private Sub Command40_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant

On Error GoTo Err_Command40_Click

Set db = CurrentDb()

'make sure a selection has been made
If Me.PREmployeeList.ItemsSelected.Count = 0 Then
MsgBox "You Must Select an Employee"

Exit Sub

End If

'add selected value(s) to table
Set ctl = Me.PREmployeeList
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!EmployeeNo = ctl.ItemData(varItem)
rs!HPIDNo = Me.HolidayYearlycbo


rs.Update
Next varItem


DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub

I realised that I need to run an append query "qryHolAllocationAppend" but am having trouble finding the correct code to run the query. The code is run from a button on a form where there is a list of employees you select and a combo box where you pick the holiday year - any help would be appreciated. Pauline

 

[pbaldy]

You can use the Execute method or OpenQuery.