SQL Statement with Global Variable

[igendreau]

What is the syntax for writing a SELECT statement with a WHERE clause when I want to refer to a global variable.

I basically want to do:

SELECT tblProducts.ProductName FROM tblProducts WHERE tblProducts.ProductName = globalVariable

That syntax gives me an error every time. If I take the WHERE clause off it works just fine. And I put a msgbox for trouble shooting, and I can definitely access the right value for the globalVariable, just not through my statement.

Thanks for the help!

[CreganTur]

When you talk about a global variable, are you talking about a VBA variable that has global scope that you want to use as a part of your SQL statement?

Are you trying to run this SQL statement as a Query in Access, or are you trying to run in it VBA? Are you using it to open a recordset?

Input... INPUT! :)

[igendreau]

Okay, here is what I've got. I'm working in a database created by someone else. First the global variable.

There is a login form. When the user logs in, two global variables are set. One gets the value of the group the user belongs to for security purposes. The one I'm concerned with is globalSecurityUser, which is assigned the user name of the user logged in. I have no problems accessing the globalSecurityUser value from any form.

Now the complicated part, my SQL string. On the main Account screen is a list box. It can show data in a variety of ways (All Accounts, All Accounts with Sales Info, etc...). The different views of data are selected in a combo box. Rather than have all the different SQL strings in a module or in the form's code, the author has created a tblView table. It has a field that contains the entire SQL string for each view. So when you select a view from the drop down, it access the tblView table, grabs the applicable SQL statement, and sets the list box's row source to that string. Here is the statement I need to modify:

SELECT tblAccount.AccountID, tblAccount.AccountName, tblLookupSales.Name, tblAccount.AccountStatus, FormatAddress([Address1],[Address2],[City],[StateName],[PostalCode]) AS Address FROM (tblAccount LEFT JOIN tblLookupState ON tblAccount.StateID = tblLookupState.StateID) LEFT JOIN tblLookupSales ON tblAccount.SalesID = tblLookupSales.SalesID ORDER BY tblAccount.AccountName;
FROM tblAccount LEFT JOIN tblLookupState ON tblAccount.StateID = tblLookupState.StateID

That statement is almost exactly what I need. It brings in all the correct info. I just need to customize it to limit the selected records to records where tblLookupSales.Name is equal to the value of the globalSecurityUser variable. So basically it would give me all the data of the above statement, but only for the Sales Rep who is currently logged in.

As you can see from my first post, I thought about creating a query that handles all the joins, and then just writing a basic SELECT... FROM... WHERE... statement, but I can't seem to get the syntax correct for either.

How's that for input? lol! Thanks again for the help. It is much appreciated...

[OBP]

You need to add a Where Clause to the end of that statement and the syntax is the hard part, so you will have to play around with it to get it right.
Basically it will be

WHERE tblLookupSales.Name = " & globalSecurityUser

but you can try also

WHERE tblLookupSales.Name = '" & globalSecurityUser & "' "

or

WHERE tblLookupSales.Name = """ & globalSecurityUser & """"

You might also get away with

WHERE Name = " & globalSecurityUser
if Name is not used in any of the other Tables

Odd is the fact that your current code gets away without using and " in it as the code I have provided is usually in this format
SQL = "SELECT Que_CallNumber.* " & _
"FROM Que_CallNumber " & _
"WHERE DealerCode = " & Me.Dealercode

[DarkSprout]

Or place the Public Variable in your SQL via a Public Property Get

Like this:=
... WHERE tblProducts.ProductName = ppProductName();"

and add this in a Module:
Public Property Get ppProductName() As String
On Error Resume Next
ppProductName = globalProductName
End Property

[igendreau]

Thanks, I'll give those suggestions a try... and by the way, here is the code attached to the AfterUpdate on the combo box that initiates the lookup in the tblView table:

sSQL = DLookup("ViewSQL", "tblView", "ViewID = " & Me.ComboView.Column(0))
Call RequeryMainList("", "", sSQL, 1)
and the RequeryMainList sub....

Sub RequeryMainList(Letter As String, Optional filtertext As String, Optional EntireSQL As String, _
Optional ResetNumbering As Integer)
On Error GoTo Err_RequeryMainList
'Requeries the account list in a number of ways:
' - If Letter exists, we modify where clause appropriately
' - If FilterText exists, we modify where clause appropriately
' - If EntireSQL exists, we modify entire rowsource
'Then modifies the sorting if needed
'Also sets the text to diplay number of records
Dim sql As String
Dim fff As Integer
Dim ggg As Integer
Dim sqlwhere As String
Dim numofrecs As Long
Dim doneflag As Integer

If (filtertext <> "") And NoFilterConditions() Then
GoTo Exit_RequeryMainList 'skip entire routine if filtering with no filter
End If

sql = ""

'Possibly modify sql with where clause changes or entire new sql statements
If ((Letter <> "") Or (filtertext <> "")) Then
sql = DLookup("ViewSQL", "tblView", "ViewID = " & Me.ComboView.Column(0)) 'set sql back to base query
'build new where clause (might be partial where clause in the end)
If (Letter <> "") Then
Call BlankOutFilterFields
sqlwhere = FilterByLetter(Letter)
Else
sqlwhere = FilterByFilter() 'note: we don't use FilterText (instead we grab values from form)
End If

'modify existing HAVING or WHERE clauses (modifies having if query does grouping)
doneflag = 0
'look for GROUP BY clause (if so use HAVING instead of WHERE)
fff = InStr(1, sql, "GROUP BY")
If (fff <> 0) Then
sqlwhere = "HAVING " & Right(sqlwhere, Len(sqlwhere) - 5)
'look for existing HAVING clause
fff = InStr(1, sql, "HAVING ")
If (fff <> 0) Then ' found a HAVING clause
'Merge in new criteria into HAVING clause
sql = Left(sql, fff - 1) & sqlwhere & " AND " & Right(sql, Len(sql) - fff - 6)
doneflag = 1
End If
Else
'look for existing WHERE clause
fff = InStr(1, sql, "WHERE ")
If (fff <> 0) Then ' found a WHERE clause
'Merge in new criteria into WHERE clause
sql = Left(sql, fff - 1) & sqlwhere & " AND " & Right(sql, Len(sql) - fff - 5)
doneflag = 1
End If
End If

'no HAVING or WHERE clause so add a new one (either before ORDER BY or at end of sql)
If (doneflag = 0) Then
fff = InStr(1, sql, "ORDER BY")
If (fff <> 0) Then 'found an ORDER BY clause
sql = Left(sql, fff - 1) & sqlwhere & Right(sql, Len(sql) - fff + 1)
Else
sql = sql & " " & sqlwhere
End If
End If

ElseIf (EntireSQL <> "") Then
If (ResetNumbering = 1) Then
Call BlankOutFilterFields
End If
Me.MainList.ColumnCount = Me.ComboView.Column(1)
Me.MainList.ColumnWidths = Me.ComboView.Column(2)
sql = EntireSQL
End If

'Next apply sorting if specified in ComboSort
'strip out the ORDER BY clause (if one exists)
'(we expect the ORDER BY to be the last part of the sql)
If (Me.ComboSort > 0) Then
If (sql = "") Then 'if sql hasn't changed use current sql
sql = Me.MainList.RowSource
End If
fff = InStr(1, sql, "ORDER BY")
If (fff <> 0) Then
sql = Left(sql, fff - 1)
End If
'tack on the new ORDER BY clause
sql = sql & " " & Me.ComboSort.Column(1)
End If

' good for debugging
'MsgBox sql

Me.MainList.RowSource = sql
Me.MainList.Requery

'Set text field to show number of records (and filtering if applicable)
numofrecs = Me.MainList.ListCount - 1
If (numofrecs < 0) Then
numofrecs = 0
End If

If (ResetNumbering = 1) Then
Me.txtTotalRecs.Value = numofrecs
End If

If (Me.txtTotalRecs.Value = numofrecs) Then
Me.txtRecordCount = "Total Records: " & Format(numofrecs, "#,##0")
Else
Me.txtRecordCount = "Total Records: " & Format(Me.txtTotalRecs.Value, "#,##0") & " (Filtered to: " _
& Format(numofrecs, "#,##0") & " records)"
End If


Exit_RequeryMainList:
Exit Sub

Err_RequeryMainList:
MsgBox Err.Description
Resume Exit_RequeryMainList

End Sub