dynamic SQL using current RecordSource

[JoshuaAnthony]

Hi,

The following works for me:

strSQL = "SELECT * FROM [" & Me.RecordSource & "]"

but when I try to select certain fields from the recordSource, it doesn't seem to work. This is one of my failed attempts:

strSQL = "SELECT [" & Me.RecordSource.[ID Number] & "],[" & Me.RecordSource.[Field1] & "]  FROM [" & Me.RecordSource & "]"

Any suggestions to set me in the right direction?

Thanks,

Joshua

 

[Mile-O]

Dim strSource As String
strSource = Me.RecordSource

strSQL = "SELECT [" & strSource & "].[ID Number], [" & strSource & "].[Field1] FROM [" & strSource & "];"

I made the variable to store the reocordsource rather than navigate the form's class three times to get the recordsource.

 

[Mile-O]

Actually, if the recordsource is static then it would be even better to declare it as a Constant - it improves speed once the code is compiled.

Const strSource = "RecordSourceName"

strSQL = "SELECT [" & strSource & "].[ID Number], [" & strSource & "].[Field1] FROM [" & strSource & "];"

 

[JoshuaAnthony]

Thank you

 

[JoshuaAnthony]

BTW I also found that I don't even need to put in the record source! i.e.:

strSQL = "SELECT [ID Number], [Field1] FROM [" & strSource & "];"

 

[Mile-O]

BTW I also found that I don't even need to put in the record source!

You do, however, if you are selecting from more than one table.