SQL - Prompts for fields 1

[mickeyg]

I have the following strSQL (executed onClick) but it keeps prompting me for title, keyword1, keyword2, etc.

' Create SELECT statement.
strSQL = "SELECT Publications.InventoryLevel, Publications.InternetAvail, "
strSQL = strSQL & "Publications.Title, Publications.Keywords, "
strSQL = strSQL & "Publications.ResourceType, Publications.ContributorName, "
strSQL = strSQL & "Publications.ContributorPhone, Publications.ContributorUnit, "
strSQL = strSQL & "Publications.PublisherDistributor, Publications.PublisherRegion "
strSQL = strSQL & "FROM Publications INNER JOIN tblSubject ON Publications.Title = tblSubject.Title "
strSQL = strSQL & "WHERE (Publications.Title=[Forms]![frmSearch]![txtTitle]) OR "
strSQL = strSQL & "(tblSubject.Subject=[Forms]![frmSearch]![txtKeyword1]) OR "
strSQL = strSQL & "(tblSubject.Subject=[Forms]![frmSearch]![txtKeyword2]) OR "
strSQL = strSQL & "(tblSubject.Subject=[Forms]![frmSearch]![txtKeyword3]) OR "
strSQL = strSQL & "(tblSubject.Subject=[Forms]![frmSearch]![txtKeyword4]) OR "
strSQL = strSQL & "(tblSubject.Subject=[Forms]![frmSearch]![txtKeyword5]) OR "
strSQL = strSQL & "(tblSubject.Subject=[Forms]![frmSearch]![txtKeyword6]) "

What am I doing wrong?

Thanks,
Mickey

 

[ghubbell]

Hi Mickey...that's a handful!

all of your lines like:

strSQL = strSQL & "(tblSubject.Subject=[Forms]![frmSearch]![txtKeyword6])

should be something like:

strSQL = strSQL & " (tblSubject.Subject= '" & [Forms]![frmSearch]![txtKeyword6] & "')"

Gord
ghubbell@total.net

 

[RickSpr]

No, this should work the way you've coded it, provided frmSearch is open at the time the SQL statement is executed. If frmSearch is not open, the [Forms]... values won't exist, so Access will treat them as query parameters and prompt for their values. Is that your problem?

What are you doing with strSQL, after you've generated it? Rick Sprague

 

[ghubbell]

Hi Mickey and Rick!

To the Northwinds Db please! open the form "orders subform" and let it sit. Now open a new module and paste this in:

Public Sub Test()
On Error GoTo Err1
Dim Sql As String, rs As Recordset, db As Database
Set db = CurrentDb()
'Sql = "SELECT [Order Details].ProductID FROM [Order Details] WHERE ((([Order Details].ProductID)=[Forms]![Orders Subform]![ProductID]))"
Sql = "SELECT [Order Details].ProductID FROM [Order Details] WHERE ((([Order Details].ProductID)= " & [Forms]![Orders Subform]![ProductID] & &quot)"
Set rs = db.OpenRecordset(Sql)
If rs.RecordCount > 0 Then MsgBox "This works fine."
rs.Close

Exit1:
Exit Sub

Err1:
If Err.Number = 3061 Then
MsgBox "Access cannot read this sql statement...."
Else
MsgBox Err.Number & " " & Err.Description, vbInformation
End If
Resume Exit1
End Sub

Toggle between the two choices and watch the reaction. Notice clearly my mistake in the original with those "little quotes" (again!).
Rick's No is justified if you are building the SQL to be used as the record source for say, another form or a report, however if I was to do that I'd save it as a query and call the query instead. (faster!) Gord
ghubbell@total.net

 

[RickSpr]

Ok, Gord, I'll admit that you got me there. Apparently the VBA expression service, which normally would be called to evaluate the form control reference, isn't being called in this case. But why? I could evaluate it in the debug window, both independently and as part of a DLookup call. Is it because, in this case, the SQL statement is being executed by a DAO method, rather than via Access' mediation?

I'd like to know why this didn't work, to corect my understanding of when form control references can be used in SQL statements. Rick Sprague

 

[ghubbell]

Not to get you Rick! Corrected My oops! This inconsistency between VB, SQL, QBE grids Ugggh! Drives me nuts!! That's why I'm always at war with those little quotes and such. I've read somewhere the formalities (I'll try and dig it out) but it still doesn't make it any easier for you and I (and Mickey!). Geeze I'm anxious to see if Access XP will handle some of this "fussyness" at least a little better. If they would only pick one way.... X-) Gord
ghubbell@total.net

 

[RickSpr]

Gord, the "fussyness" isn't going to go away in XP, because it makes good sense. You just have to remember how the SQL statement gets built and, separately, executed by Jet.

First of all, you can use either apostrophes (aka "single quotes&quot or quotes (aka "double quotes&quot in the SQL statement. But you have to realize that, when the SQL statement is being manipulated as a string literal in VBA code, VBA itself sees the quotes as delimiters of the literal. So in code, if you're using a literal to build the SQL string, and you want a quote to make it into the string, you have to double it, because when the VBA compiler sees a double quote in a literal it turns it into a single quote in the string you're assigning the literal to. Because this gets complicated to keep track of, it's usually easier to use apostrophes in the SQL statement.

Now forget about VBA a minute, and just think of SQL statements. In a SQL statement, a text constant has to be surrounded by either apostrophes or quotes, so that the statement interpreter doesn't mistake it for a column name. So, for example,
SELECT a FROM tbl WHERE col1='abc'
will compare the value of col1 to the constant value 'abc', while
SELECT a FROM tbl WHERE col1=abc
will compare the value of col1 to the value of column abc.

Once you realize where the apostrophes (or quotes) have to wind up in the SQL statement, you then just have to make sure they make it into the correct position within the string you're building in VBA. When you're coding the VBA part, forget about the meaning of the SQL statement, and just concentrate on concatenating pieces of the string, making sure your apostrophes and/or quotes occur in the right place in the resulting string.

If you code something like
[Forms]![formname]![controlname]
as a separate part of the expression outside of a string literal, you'll be inserting the value of the control into the string. If you code it inside a string literal, you'll be inserting the actual text of the form control reference into the string. When Access later hands that string's value over to Jet as an SQL statement to be executed, Jet won't understand that form reference, and will ask Access to try to interpret it. (This is what's called the "expression service" that Access provides to Jet.)

So you see, you can either have Access evaluate the form control expression while it's building the SQL statement (by leaving it outside the string literals), or you can pass the form control expression to Jet, which will then ask Access for the value while Jet is interpreting the SQL statement.

Why have it both ways? Usually, you'll get the same results either way, but not always. Say the control reference is to a text box on a form, and the user can key a search value into the text box. The SQL statement gets built at form Open time, and assigned as the form's Record Source. If the SQL statement contains the value of the control reference (the form control expression is outside the quotes), an empty string will be hard-coded into the record source SQL statement. Unless the SQL statement gets rebuilt and reassigned to the Record Source property every time the user changes the text box, the form will show the same records all the time (if any match the empty string), because the empty string is "hard-coded" into the SQL statement.

But suppose the SQL statement contains a reference to the control (the form control expression is inside the quotes, part of the literal from which the SQL statement was built). In this case, if the user changes the text box and refreshes the form, Jet will reevaluate the SQL statement and ask Access for the value of the text box again, thus changing the rows displayed. And you don't have to have code that rebuilds the Record Source property in response to the user's entry.

Guess I got kind of long winded here, but I know how aggravating it is when a misunderstanding makes software seem to behave arbitrarily, so I was hoping I could shed some light and make your satisfaction with Access better. Rick Sprague

 

[ghubbell]

And Indeed you have! Thanks again Rick! Gord
ghubbell@total.net

 

[mickeyg]

Thanks...I have it working now.
I just had to be careful to not 'zig' when I should "zag."

Mickey