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?

 

[Uncle Gizmo]

Rename your table "Transaction" to tblTransaction.

 

[Uncle Gizmo]

You can remove most of the references to the table name when your SQL Statement is based on a single table. This trick depends on NOT just selecting the table name, but also the DOT “.” following it. You need to select the dot (.) to prevent your action removing the actual reference to the table in the FROM Clause.

See this YouTube at time index:- 1:47

 

[Uncle Gizmo]

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

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

 

[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?

 

[Uncle Gizmo]

a debug.print would show that?

I agree, It would be very helpful in diagnosis....

 

[Uncle Gizmo]

For an alternative to debug, this is a method I often employ:-

VBA - SQL Help required

Hi all, I have a small prob which i am not able to fix. while executing this code the following error message is give Private Sub Command5_Click() Dim asql As String DoCmd.SetWarnings (off) DoCmd.OpenQuery ("aqryEmpsch") DoCmd.OpenQuery ("aqrynassign") DoCmd.OpenQuery ("aqryleaveadd") Dim...

www.access-programmers.co.uk

 

[Uncle Gizmo]

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

The reason I am having trouble reading this code is that I put my ampersand in a different position!

I wasn't aware that you could put the ampersand in a different position!

I just found out when I was reading up on the continuation character ..

This is the way I prefer to do it (found in a another thread)

asql = "UPDATE TblEmpSch INNER JOIN tnassign ON TblEmpSch.EMPNO = tnassign.token " & _

 

[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

 

[Uncle Gizmo]

Here's another way of building your SQL:-

Part 2

 

[Uncle Gizmo]

Finally got it work the way I wanted.

Did you miss this comment by Chris?

Solved - After update Combo box

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]...

www.access-programmers.co.uk