After Update macro convverted into vba code

[hfs]

Hi,

I have a combo box ,whose afterUpdate function is called with an event in vba ,which refreshes the subform and call an sql query on selecting "ALL".

the code is not working because the refresh code is converted from macro to vba code. I need help with this.

Private Sub cboTaskListName_AfterUpdate()
On Error GoTo cboTaskListName_AfterUpdate_Err
    TempVars.Add "CurrentUserID", Screen.ActiveControl
    On Error Resume Next
    DoCmd.Requery ""
    DoCmd.RunCommand acCmdRefresh

cboTaskListName_AfterUpdate_Exit:
    Exit Sub
cboTaskListName_AfterUpdate_Err:
    MsgBox Error$
    Resume cboTaskListName_AfterUpdate_Exit
    
    
    Dim db As DAO.Database
Dim SQL As String

 If Me.cboTaskListName = "**All**" Then

 Set db = CurrentDb()
 SQL = "SELECT tblDiscrepancy.DiscrepancyID, tblArea.AreaDesc,tblAuditGroup.AuditGroupDescription,tblObservedHeader.ObservedDate , tblDiscrepancy.DiscrepancyDesc, tblDiscrepancy.DiscrepancyVerifiedDate,tblDiscrepancy.DiscrepancyNote , tblObservedHeader.ObservedID, tblObservedHeader.ObservedBy, tblObservedHeader.ObservedArea,tblDiscrepancy.DiscrepancyAssignedTo , tblDiscrepancy.DiscrepancyVerifiedBy FROM (tblArea RIGHT JOIN (tblAuditGroup RIGHT JOIN tblObservedHeader ON tblAuditGroup.[AuditGroupID] = tblObservedHeader.ObservedBy) ON tblArea.AreaID = tblObservedHeader.ObservedArea) RIGHT JOIN (tblAuditStaff AS tblAssignedTo RIGHT JOIN (tblAuditStaff AS tblVerifiedBy RIGHT JOIN tblDiscrepancy ON tblVerifiedBy.AuditStaffID = tblDiscrepancy.DiscrepancyVerifiedBy) ON tblAssignedTo.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo) ON tblObservedHeader.ObservedID = tblDiscrepancy.DiscrepancyTest"
  End If
    
End Sub

this code gives an error which is

Microsoft Access
 
TempVArs can only store data .They cannot store objects.

 

[Pat Hartman]

For starters, nothing after the resume statement will ever get executed.

If you want to requery/refresh a control use:
Me.MyControl.Requery
or
Me.MyControl.Refresh

To do the whole form, use:
Me.Requery
or
Me.Refresh

Requery reruns the RecordSource/RowSource query and will include new rows and eliminate old rows.
Refresh does NOT rerun the query and so will not pick up new rows. It will pick up updates and will mark deleted records as #deleted#