Append Query For Single Record

[burrina]

How can I make my append query use the selected record from my form? I don't want to do a everything, i.e. all records append.
This code fires on the click event, it is a checkbox. The append query which is named Qappendtoregister has NO criteria. Primary Key for table form is based on is EventID and Primary Key to table where data is going to is RegID .

Private Sub enter_Click()
DoCmd.Beep


If Me.Dirty Then Me.Dirty = False
DoCmd.SetWarnings False 'Purpose: Append Scheduled Event To Check Register.
DoCmd.OpenQuery "Qappendtoregister"
DoCmd.RunCommand acCmdSaveRecord
If Me.Dirty Then Me.Dirty = False
DoCmd.SetWarnings True
End Sub



If I use this code in the query, will it only then use one single record and only use the single records ID? What then when I want to do multiple records?

[Forms]![frmEvent]![EventID]

[June7]

Show the sql statement for Qappendtoregister.

[burrina]

INSERT INTO TReg ( EventID, Bank, EventStart, BalDate, Payee, FreqPayee, frequencyamount, Debit, credit, ChkNo, TransType, PeriodTypeID, [Memo] )
SELECT tblEvent.EventID, tblEvent.Bank, tblEvent.EventStart, tblEvent.MYDte, tblEvent.Payee, tblEvent.FreqPayee, tblEvent.FrequencyAmnt, tblEvent.Debit, tblEvent.credit, tblEvent.ChkNo, tblEvent.TransType, tblEvent.PeriodTypeID, tblEvent.Comment
FROM tblEvent
WHERE (((tblEvent.EventID)=[Forms]![frmEvent]![EventID]));

[June7]

That looks correct. Only the one record identified by the unique EventID should be retrieved and inserted.

[burrina]

So if I wanted to do all records, then? Where enter = true and nextschddte = Date() Then what would I do, anything different?

The ALL records is what throws me. Maybe do a separate IF Statement and use a different command button or checkbox? That should work huh?
DoCmd.RunCommandSelectAllRecords or something like that ?

[June7]

I would not use a query object. I would build the sql statement in VBA then run the statement with CurrentDb.Execute.

[burrina]

So, your saying instead of my append query run this: Is this syntax correct? I have never tried DB.Execute or using any sql ! Use on the OnClickEvent?

CurrentDb.Execute

INSERT INTO TReg ( EventID, Bank, EventStart, BalDate, Payee, FreqPayee, frequencyamount, Debit, credit, ChkNo, TransType, PeriodTypeID, [Memo] )
SELECT tblEvent.EventID, tblEvent.Bank, tblEvent.EventStart, tblEvent.MYDte, tblEvent.Payee, tblEvent.FreqPayee, tblEvent.FrequencyAmnt, tblEvent.Debit, tblEvent.credit, tblEvent.ChkNo, tblEvent.TransType, tblEvent.PeriodTypeID, tblEvent.Comment
FROM tblEvent
WHERE (((tblEvent.EventID)=[Forms]![frmEvent]![EventID]));


So, if i ran this would it then use as a single record or could I somehow if I wanted use a select all to do a batch process? Single is a good option to keep but I see situations where a select all could be used.

[June7]

Dim strFilter As String

Select Case Me.Combo63
Case "Today"
strFilter = " WHERE ..."
...
Case "All"
strFilter = ""
End Select

CurrentDb.Execute "INSERT INTO TReg (EventID, Bank, EventStart, BalDate, Payee, FreqPayee, frequencyamount, Debit, credit, ChkNo, TransType, " & _
"PeriodTypeID, [Memo]) SELECT EventID, Bank, EventStart, MYDte, Payee, FreqPayee, FrequencyAmnt, Debit, credit, ChkNo, TransType, PeriodTypeID, Comment " & _
"FROM tblEvent " & strFilter

[burrina]

Thank You.