SQL in VBA error in syntax?

voskouee

Registered User.
Local time
Today, 04:21
Joined
Jan 23, 2007
Messages
96
I get the error that is attached as a picture here when i run this query on vba.

any ideas?


Set RsType = CurrentDb.OpenRecordset("SELECT SOBP,SOBPDEBIT FROM RFGDebitNotes ;")
Set RsTbl = CurrentDb.OpenRecordset("SELECT * FROM " & RsType!sobpdebit & " ;")


strsqlDBl = " SELECT * FROM " & RsType!sobpdebit & " ORDER BY " & RsTbl!Credits & " Desc;"
strsqlDBm = " SELECT * FROM " & RsType!sobpdebit & " ORDER BY " & RsTbl!Debits & " Desc;"
 

Attachments

  • vba error.jpg
    vba error.jpg
    12.2 KB · Views: 138
Last edited:
You cannot reference the recordset you just created in code in another SQL statement...it must be compiled into the DB for the SQL statement to recognize it. Using Nested SQL statments will get you where you want to go.
 
can you explain with an example what you mean? before i added that line everything works. should i change that too?
 
First off, just to make sure we are on the same page, you know that:
Code:
Set RsType = CurrentDb.OpenRecordset("SELECT SOBP,
                                             SOBPDEBIT 
                                        FROM RFGDebitNotes ;")
produces a recordset, not a single answer, correct?
whereas:
Code:
strsqlDBl = " SELECT * FROM " & RsType!sobpdebit & 
                 " ORDER BY " & RsTbl!Credits & " Desc;"
is looking for single selection criteria, not recordsets.

Seems to me you either need to change the RsType, and RsTbl to include criteria or convert them to DLookups
 

Users who are viewing this thread

Back
Top Bottom