Query name variable used in place of name

[exaccess]

Hi to All,
I have defined a recordsource based on a predefined query. Now I intend to use the SQL statement which works to produce other recordsources based on other queries. More concretely here is my code as of now:

Dim J1 As Long
    Dim SQLRecordSource As String    
    J1 = Forms(ParName).MemID
SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
            "INNER JOIN [ActiveMembersQy] ON " & _
            "[MembersTbl].[MemID] = [ActiveMembersQy].[MemID] " & _
                " WHERE [ActiveMembersQy].[MemID] = " & J1 & ";"

In the future instead of writing it like this:

 Select Case QryName
Case "NonMembersQy"
            SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
            "INNER JOIN [NonMembersQy] ON " & _
            "[MembersTbl].[MemID] = [NonMembersQy].[MemID] " & _
                " WHERE [NonMembersQy].[MemID] = " & J1 & ";"
        Case "ByFirstNameQy"
            SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
            "INNER JOIN [ByFirstNameQy] ON " & _
            "[MembersTbl].[MemID] = [ByFirstNameQy].[MemID] " & _
                " WHERE [ByFirstNameQy].[MemID] = " & J1 & ";"
        Case "ByLastNameQy"
            SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
            "INNER JOIN [ByLastNameQy] ON " & _
            "[MembersTbl].[MemID] = [ByLastNameQy].[MemID] " & _
                " WHERE [ByLastNameQy].[MemID] = " & J1 & ";"
END SELECT

I wish to write it like this (which does not work):

            SQLRecordSource = "SELECT DISTINCTROW MembersTbl.* FROM MembersTbl" & _
            "INNER JOIN QryName ON " & _
            "MembersTbl.[MemID] = QryName.[MemID] " & _
                " WHERE QryName.[MemID] = " & J1 & ";"

Is this possible ie put the name of the variable that holds the name of the query in quotes or sometimes put the name of the query instead of the contents of the name. If there is a contents function for variables in VBA that can be used by I doubt whether it exists.

 

[pbaldy]

You mean like

"INNER JOIN " & VariableName & " ON " & _

 

[exaccess]

You mean like

"INNER JOIN " & VariableName & " ON " & _

I wrote it like this and it works.

SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
            "INNER JOIN " & QryName & " ON " & _
            "[MembersTbl].[MemID] = " & QryName & ".[MemID] " & _
                " WHERE " & QryName & ".[MemID] = " & J1 & ";"

 

[pbaldy]

This should help spot the spaces problem and the brackets problem:

http://www.baldyweb.com/ImmediateWindow.htm

By the way, the semicolon terminator is optional in Access.