Solved After update Combo box (1 Viewer)

shery1995

Member
Local time
Today, 09:12
Joined
May 29, 2010
Messages
71
Hi All

I have following VBA code on Combo box after update but it does not work and showing message syntax error:
Code:
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
 
Last edited by a moderator:

Gasman

Enthusiastic Amateur
Local time
Today, 09:12
Joined
Sep 21, 2011
Messages
14,037
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

Nifty Access Guy
Staff member
Local time
Today, 09:12
Joined
Jul 9, 2003
Messages
16,243
Rename your table "Transaction" to tblTransaction.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:12
Joined
Jul 9, 2003
Messages
16,243
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

Nifty Access Guy
Staff member
Local time
Today, 09:12
Joined
Jul 9, 2003
Messages
16,243
Is there one too many quote marks at the end of this line:-
Code:
& "AND Transaction.[MatterID]= " & nz(Me.cboTransaction.Column(1),0) & " " _
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:12
Joined
Sep 21, 2011
Messages
14,037
Is there one too many quote marks at the end of this line:-
Code:
& "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

Nifty Access Guy
Staff member
Local time
Today, 09:12
Joined
Jul 9, 2003
Messages
16,243
For an alternative to debug, this is a method I often employ:-

 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:12
Joined
Jul 9, 2003
Messages
16,243
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)

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

CJ_London

Super Moderator
Staff member
Local time
Today, 09:12
Joined
Feb 19, 2013
Messages
16,553
Almost right Unc

there is one too many " on the third line

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

SHANEMAC51

Active member
Local time
Today, 12:12
Joined
Jan 28, 2022
Messages
310
Code:
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 ...
 
Last edited by a moderator:

CJ_London

Super Moderator
Staff member
Local time
Today, 09:12
Joined
Feb 19, 2013
Messages
16,553
there is no need to requery when assigning a new recordsource to a form - it will requery automatically
 

shery1995

Member
Local time
Today, 09:12
Joined
May 29, 2010
Messages
71
Thank you all for your help and suggestions. Finally got it work the way I wanted. Here is the query:

Code:
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
 
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:12
Joined
Jul 9, 2003
Messages
16,243
Here's another way of building your SQL:-


Part 2

 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:12
Joined
Jul 9, 2003
Messages
16,243
Finally got it work the way I wanted.

Did you miss this comment by Chris?

 

Users who are viewing this thread

Top Bottom