problems with CurrentDB.Execute

[Guest]

I am working with a sql statement that works fine as a query. We are trying
to pull up information on a subform after the user enters criteria. Once we
try to put it into VBA to do this, we get errors. This is our vba code:


Private Sub cmdView_Click()

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum)WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value


CurrentDb.Execute (strSelectSQL)
frmCheckSub.Requery

End Sub

When we get the error and click on Debug, the CurrentDb.Execute line is what
is actually highlighted. to start with, I thought maybe there was a problem
with the SQL stmt, but once we got it working as a query, I figure that's not
the case.

Anyone have any suggestions? Help is greatly appreciated!

thanks!
Seren

 

[Guest]

The Execute method is only for Action (Update, Append, MakeTabke, Delete)
Queries.

If this is the Record Source for your form, which it appears it is, then
this is what you would want to do once you have built your SQL:

With Forms!frmCheckSub
.RecordSource = strSelectSQL
.Requery
End With

 

[Guest]

ok, we tried that. it gave the error, then, that frmCheckSub could not be
found. If i change it to the following:

With Form_frmCheckSub
.RecordSource = strSelectSQL
.Requery
End With

I get no errors... but I also don't get any data.

this is a subform that shows records for the week containing the day the
user enters (which we've not worked out yet). if the user doesn't find a
record for any day in that week, they are then given the option to add a
record. After all fields are filled in, we requery to show the new record
included in the subform. What we're trying to do with *this* sql statement
is the initial view of that week.

at this point, like I said, I get nothing in the subform...


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."

The Execute method is only for Action (Update, Append, MakeTabke, Delete)
Queries.

If this is the Record Source for your form, which it appears it is, then
this is what you would want to do once you have built your SQL:

With Forms!frmCheckSub
.RecordSource = strSelectSQL
.Requery
End With

I am working with a sql statement that works fine as a query. We are trying
to pull up information on a subform after the user enters criteria. Once we
try to put it into VBA to do this, we get errors. This is our vba code:


Private Sub cmdView_Click()

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum)WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value


CurrentDb.Execute (strSelectSQL)
frmCheckSub.Requery

End Sub

When we get the error and click on Debug, the CurrentDb.Execute line is what
is actually highlighted. to start with, I thought maybe there was a problem
with the SQL stmt, but once we got it working as a query, I figure that's not
the case.

Anyone have any suggestions? Help is greatly appreciated!

thanks!
Seren

 

[Guest]

Then the problem is with the SQL. For example, this line has incorrect syntax:
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value
Assuming tblResults.[date] is a date field, it should be:
cboOperationDetail.Value & " AND tblResults.[date] = #" & txtDate.Value & "#"

A text representation of a date is enclosed in # like text is enclosed in "

ok, we tried that. it gave the error, then, that frmCheckSub could not be
found. If i change it to the following:

With Form_frmCheckSub
.RecordSource = strSelectSQL
.Requery
End With

I get no errors... but I also don't get any data.

this is a subform that shows records for the week containing the day the
user enters (which we've not worked out yet). if the user doesn't find a
record for any day in that week, they are then given the option to add a
record. After all fields are filled in, we requery to show the new record
included in the subform. What we're trying to do with *this* sql statement
is the initial view of that week.

at this point, like I said, I get nothing in the subform...


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."

The Execute method is only for Action (Update, Append, MakeTabke, Delete)
Queries.

If this is the Record Source for your form, which it appears it is, then
this is what you would want to do once you have built your SQL:

With Forms!frmCheckSub
.RecordSource = strSelectSQL
.Requery
End With

I am working with a sql statement that works fine as a query. We are trying
to pull up information on a subform after the user enters criteria. Once we
try to put it into VBA to do this, we get errors. This is our vba code:


Private Sub cmdView_Click()

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum)WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value


CurrentDb.Execute (strSelectSQL)
frmCheckSub.Requery

End Sub

When we get the error and click on Debug, the CurrentDb.Execute line is what
is actually highlighted. to start with, I thought maybe there was a problem
with the SQL stmt, but once we got it working as a query, I figure that's not
the case.

Anyone have any suggestions? Help is greatly appreciated!

thanks!
Seren