Open Form with SQL

[roccogrand]

Would someone please tell me why this code is not working. I have tried all
the combinations that I can think of, as indicated by the remarks.

I get an 3075 error, no data, and no form with the code I think should work.

Thanks a million.

LDN

Private Sub cmdOpenMaster1_Click()
Dim MyQueryCode As String
Dim stDocName As String
Dim strSQL As Variant
stDocName = "frmMaster1"

MyQueryCode = Me.QueryCode

Select Case MyQueryCode

Case Is = 1312
'strSQL = "SELECT * FROM tblTasks WHERE ((tblTasks.Company) = ALL)"
strSQL = "SELECT * FROM tblTasks "
strSQL = strSQL & "WHERE [Company] = ALL"
strSQL = strSQL & "ORDER BY tblTasks.DueDate;"

'strSQL = strSQL & "ORDER BY tblTasks.DueDate"
'Doesn't work without the semicolon either

Debug.Print MyQueryCode
'My coded value from the previous form looks OK = 1312
Debug.Print strSQL
'The query looks OK'

'DoCmd.OpenForm stDocName
'Works but not filter or data

DoCmd.OpenForm stDocName, , , strSQL
'This is what I want to work

'DoCmd.RunSQL strSQL
'DoCmd.OpenQuery "qryTasksCompanyALL"
'These don't work

DoCmd.Requery

 

[John W. Vinson]

Case Is = 1312
'strSQL = "SELECT * FROM tblTasks WHERE ((tblTasks.Company) = ALL)"
strSQL = "SELECT * FROM tblTasks "
strSQL = strSQL & "WHERE [Company] = ALL"
strSQL = strSQL & "ORDER BY tblTasks.DueDate;"

'strSQL = strSQL & "ORDER BY tblTasks.DueDate"

Assuming that Company is a Text field, and you want to select those records
where Company contains the text string ALL, you will need to delimit it with
quotemarks:

strSQL = "SELECT * FROM tblTasks "
strSQL = strSQL & "WHERE [Company] = 'ALL'"
strSQL = strSQL & "ORDER BY tblTasks.DueDate;"

If you're expecting a criterion of ALL to retrieve all records, it won't work.
Perhaps if you would explain what you are trying to accomplish (which is not
immediately obvious, since "don't work" isn't very informative!) we might be
able to help.

 

[roccogrand]

Thanks John but could there be any other reason why I get a 3075 with this
statement?

strSQL = "SELECT * FROM tblTasks "
strSQL = strSQL & "WHERE [Company] = 'ALL'"
strSQL = strSQL & " ORDER BY tblTasks.DueDate;"

I am using Access 2007. Does it use a different variant of SQL?

LDN

Case Is = 1312
'strSQL = "SELECT * FROM tblTasks WHERE ((tblTasks.Company) = ALL)"
strSQL = "SELECT * FROM tblTasks "
strSQL = strSQL & "WHERE [Company] = ALL"
strSQL = strSQL & "ORDER BY tblTasks.DueDate;"

'strSQL = strSQL & "ORDER BY tblTasks.DueDate"

Assuming that Company is a Text field, and you want to select those records
where Company contains the text string ALL, you will need to delimit it with
quotemarks:

strSQL = "SELECT * FROM tblTasks "
strSQL = strSQL & "WHERE [Company] = 'ALL'"
strSQL = strSQL & "ORDER BY tblTasks.DueDate;"

If you're expecting a criterion of ALL to retrieve all records, it won't work.
Perhaps if you would explain what you are trying to accomplish (which is not
immediately obvious, since "don't work" isn't very informative!) we might be
able to help.

 

[Albert D. Kallal]

You using a "where" caluse that is Is standards SQL syntax, but you don't
need the actual select art of the statement.

we're assuming that this form you have is already bound to your table
tbltasks?

(by the way, you confused the original question a little bit, because you
have code that tries to open a query, and you also have code that tries to
open a form, which is it you're trying to do?).

I am going to assume that you want to open your form that is based on
tblTasks, but restricted to a given criteria that you have

Private Sub cmdOpenMaster1_Click()

Dim stDocName As String
Dim strSQL As Variant


stDocName = "frmMaster1"

strSQL = "Company = 'ALL' "
DoCmd.OpenForm stDocName, , , strSQL

the above will open the form, and restrict the records to what you need.

You seem to be hinting that you want to open a query, but what are you users
is going to do with this query? in their planning to print it, it makes
sense to build a report and format it correctly in base it on tblTasks.

You then use the above filter idea to restrict that report based on you
conditions you supply using the "where" clause.

so build a report that correct the formats and displays the data you want,
and then encode builder restrictions for that report and use the above idea
(note that both the open form and the open report have the same ability to
filter, and the syntax is the same as above)

 

[roccogrand]

Thanks Albert,

If I understand you correctly, I need to bind the form to the table tblTasks
and then use the criteria in the OpenForm event to filter on 'ALL' and the
other criteria. Correct?

Will the second part of strSQL be:
strSQL = strSQL & "ORDER BY 'DueDate' "

If so, when do does one use the semicolon and not use it?

Or can one combine both parts of the SQL together like:
strSQL ="Company = 'ALL' AND ORDER BY 'DueDate' "

So I don't need to do anything like set the db to currentdb and the like?
That is where I would have gone next. But I don't fully understand these
statements and was dreading that.

Thanks to you and John, I am hoping that this does the trick.

LDN

 

[roccogrand]

Never mind Albert,

Now that I have the right terminology, I have been able to find the right
threads.

LDN