Exporting to Excel Problem Part II

  • Thread starter Thread starter G. Vaught
  • Start date Start date
G

G. Vaught

I have managed to get two of the three fields I need to export out to Excel.

What I need now is to get the date that is typed in by the user when the
form opens. After the date is type in the user sees the grouping number
total and production date.
I am coding everything via VBA under the form to send the values to the
Excel Spreadsheet.

I make a connection to the database, open the recordset and then invoke the
Select statement this way. If I can get away from the form and get the query
to group by Grouping, Sum the work_mcf and prompt the user for a date, that
would be great.

The select statement I am using now came from the SQL created when I type
for a parameter in the query.

My last hurdle is passing the date collected from the parameter prompt along
with the other values. The error I am getting is: No value given for one or
more required parameters.

Do I need to add a parameters command? If so, I am not sure how to add it to
the code I already have. If there is an alternative, I am open to
suggestions.

'Partial Code below

Set cnn = CurrentProject.AccessConnection
Set rst = New adodb.Recordset

With rst
Set .ActiveConnection = cnn
.Source = "SELECT IIf([Meter_Name]=""Boehm"",2,1) AS Grouping,
Sum(tblMeterInfo.work_mcf) AS SumOfwork_mcf, tblMeterInfo.ProductionDate
FROM tblMeterInfo GROUP BY IIf([Meter_Name]=""Boehm"",2,1),
tblMeterInfo.ProductionDate HAVING (((tblMeterInfo.ProductionDate)=[Enter
Start Date]));" .CursorType = adOpenDynamic
.Open

End With

' Other code here that sets the worksheets

'Code to pass values from above query to to Excel Spreadsheet
'Copy Values to cells in the worksheet.
rst.MoveFirst
XlSheet.Range("A2").CopyFromRecordset rst
rst.MoveNext
 
I tried this and the calculated field in my query comes back blank. I think
this explains why the SQL Query is not getting the value. I just need to
figure out why it not getting referenced.

Klatuu said:
Put a text box on your form for the user to enter the date. Include the
date
field in your query and reference the control in the query. For example,
lets say the control is named txtEntryDate. Now in your query builder,
use a
Calculated field that points to the control on the form. In the Field row
of
the query builder:

TheDate: Forms!frmMyForm!txtEntryDate

G. Vaught said:
I have managed to get two of the three fields I need to export out to
Excel.

What I need now is to get the date that is typed in by the user when the
form opens. After the date is type in the user sees the grouping number
total and production date.
I am coding everything via VBA under the form to send the values to the
Excel Spreadsheet.

I make a connection to the database, open the recordset and then invoke
the
Select statement this way. If I can get away from the form and get the
query
to group by Grouping, Sum the work_mcf and prompt the user for a date,
that
would be great.

The select statement I am using now came from the SQL created when I type
for a parameter in the query.

My last hurdle is passing the date collected from the parameter prompt
along
with the other values. The error I am getting is: No value given for one
or
more required parameters.

Do I need to add a parameters command? If so, I am not sure how to add it
to
the code I already have. If there is an alternative, I am open to
suggestions.

'Partial Code below

Set cnn = CurrentProject.AccessConnection
Set rst = New adodb.Recordset

With rst
Set .ActiveConnection = cnn
.Source = "SELECT IIf([Meter_Name]=""Boehm"",2,1) AS Grouping,
Sum(tblMeterInfo.work_mcf) AS SumOfwork_mcf, tblMeterInfo.ProductionDate
FROM tblMeterInfo GROUP BY IIf([Meter_Name]=""Boehm"",2,1),
tblMeterInfo.ProductionDate HAVING (((tblMeterInfo.ProductionDate)=[Enter
Start Date]));" .CursorType = adOpenDynamic
.Open

End With

' Other code here that sets the worksheets

'Code to pass values from above query to to Excel Spreadsheet
'Copy Values to cells in the worksheet.
rst.MoveFirst
XlSheet.Range("A2").CopyFromRecordset rst
rst.MoveNext
Click to expand...
Click to expand...
 
Yes, it is
Klatuu said:
Is the form still open when the query runs? It needs to be.

G. Vaught said:
I tried this and the calculated field in my query comes back blank. I
think
this explains why the SQL Query is not getting the value. I just need to
figure out why it not getting referenced.

Klatuu said:
Put a text box on your form for the user to enter the date. Include
the
date
field in your query and reference the control in the query. For
example,
lets say the control is named txtEntryDate. Now in your query builder,
use a
Calculated field that points to the control on the form. In the Field
row
of
the query builder:

TheDate: Forms!frmMyForm!txtEntryDate

:

I have managed to get two of the three fields I need to export out to
Excel.

What I need now is to get the date that is typed in by the user when
the
form opens. After the date is type in the user sees the grouping
number
total and production date.
I am coding everything via VBA under the form to send the values to
the
Excel Spreadsheet.

I make a connection to the database, open the recordset and then
invoke
the
Select statement this way. If I can get away from the form and get the
query
to group by Grouping, Sum the work_mcf and prompt the user for a date,
that
would be great.

The select statement I am using now came from the SQL created when I
type
for a parameter in the query.

My last hurdle is passing the date collected from the parameter prompt
along
with the other values. The error I am getting is: No value given for
one
or
more required parameters.

Do I need to add a parameters command? If so, I am not sure how to add
it
to
the code I already have. If there is an alternative, I am open to
suggestions.

'Partial Code below

Set cnn = CurrentProject.AccessConnection
Set rst = New adodb.Recordset

With rst
Set .ActiveConnection = cnn
.Source = "SELECT IIf([Meter_Name]=""Boehm"",2,1) AS Grouping,
Sum(tblMeterInfo.work_mcf) AS SumOfwork_mcf,
tblMeterInfo.ProductionDate
FROM tblMeterInfo GROUP BY IIf([Meter_Name]=""Boehm"",2,1),
tblMeterInfo.ProductionDate HAVING
(((tblMeterInfo.ProductionDate)=[Enter
Start Date]));" .CursorType = adOpenDynamic
.Open

End With

' Other code here that sets the worksheets

'Code to pass values from above query to to Excel Spreadsheet
'Copy Values to cells in the worksheet.
rst.MoveFirst
XlSheet.Range("A2").CopyFromRecordset rst
rst.MoveNext
Click to expand...
Click to expand...
Click to expand...
 
Back
Top