Solved After update Combo box

[shery1995]

Hi All

I have following VBA code on Combo box after update but it does not work and showing message syntax error:

Private Sub cboTransaction_AfterUpdate()
    Dim SQL As String
    
    SQL = "Select Transaction.[ClientID], Transaction.[MatterID], Transaction.[TrxDate], Transaction.[TrxMode], " _
        & "Transaction.[Description], Transaction.[DebitOffice], Transaction.[CreditOffice], Transaction.[DebitClient], " _
        & "Transaction.[CreditClient] From Transaction WHERE Transaction.[ClientID]= " & nz(me.cboTransaction.Column(0),0) " _
        & "AND Transaction.[MatterID]= " & nz(Me.cboTransaction.Column(1),0) & " " _
        & "ORDER BY Transaction.[TrxDate] DESC;"
    
    Me.subMatterTransactions.Form.RecordSource = SQL
    Me.subMatterTransactions.Form.Requery
        

End Sub

Code tags added by moderator.

Note:-

MatterID are Numbers and
TrxMode and description are Text. The criteria is to display records on sub form based on meeting selection of combo box 2 columns value.

I will really appreciate for any help.
Kind regards

 

[Gasman]

Put the sql into a string variable and debug.print it, to see what you have, not what you think you have. Plus you can copy and post back here if you still cannot see the error.
Could also use code tags?

 

[Gasman]

Is there one too many quote marks at the end of this line:-

& "AND Transaction.[MatterID]= " & nz(Me.cboTransaction.Column(1),0) & " " _

Not that noticed, but of course a debug.print would show that?
The fact that me was not in proper case originally might be a clue?

 

[CJ_London]

Almost right Unc

there is one too many " on the third line

.....WHERE Transaction.[ClientID]= " & nz(me.cboTransaction.Column(0),0) " _

 

[SHANEMAC51]

Private Sub cboTransaction_AfterUpdate()
    Dim SQL As String
    
    SQL = "Select Transaction.[ClientID], Transaction.[MatterID], Transaction.[TrxDate], Transaction.[TrxMode], " 
    SQL=SQL & " Transaction.[Description], Transaction.[DebitOffice], Transaction.[CreditOffice], Transaction.[DebitClient], " 
    SQL=SQL & " Transaction.[CreditClient] "
SQL=SQL & " From Transaction WHERE Transaction.[ClientID]= " & nz(me.cboTransaction.Column(0),0)  
    SQL=SQL & " AND Transaction.[MatterID]= " & nz(Me.cboTransaction.Column(1),0) 
    SQL=SQL & " ORDER BY Transaction.[TrxDate] DESC;"
    
    Me.subMatterTransactions.Form.RecordSource = SQL
    Me.subMatterTransactions.Form.Requery
        

End Sub

Code tags added by moderator ...

 

[CJ_London]

there is no need to requery when assigning a new recordsource to a form - it will requery automatically

 

[shery1995]

Thank you all for your help and suggestions. Finally got it work the way I wanted. Here is the query:

Private Sub cboTransaction_AfterUpdate()
    Dim SQL As String
    
    SQL = "SELECT tblClientMatter.MatterID, tblClientMatter.ClientID, tblTransaction.TrxDate, tblTransaction.TrxMode, tblTransaction.Description, tblTransaction.DebitClient, tblTransaction.CreditClient, tblTransaction.DebitOffice, tblTransaction.CreditOffice " _
        & "FROM tblTransaction INNER JOIN tblClientMatter ON (tblTransaction.ClientID = tblClientMatter.ClientID) AND (tblTransaction.MatterID = tblClientMatter.MatterID) " _
        & "WHERE tblTransaction.ClientID =" & nz(Me.cboTransaction.Column(0), 0) & " AND tblTransaction.MatterID =" & nz(Me.cboTransaction.Column(1), 0) & " " _
        & "ORDER BY tblTransaction.[TrxDate] DESC;"

    Me.subMatterTransactions.Form.RecordSource = SQL
    Me.subMatterTransactions.Form.Requery
        
End Sub

Code tags added by moderator