microsoft.public.access

Comments inline...

--

Ken Snell



"Jeff C" wrote in message
news:D2F0A299-D1A0-41B2-A540-DD6BB4F5E0EC@microsoft.com...
> The Query "QReports" is using the textbox on the form as the criteria for
> the
> department.

OK.



>
> The code is placed in the onopen event of the form and I get a run time
> error "Object Required" on that particular line.

Open event of a form is way too soon to try to write data to and read data
from a form's controls. Most/all of the controls and data are not available
to the form during the Open event; hence, you're getting the Object Required
error because the object does not exist (has not been instantiated) yet. The
Load event is the soonest event where I'd try to do this.

But, why are you using the opening of a form to trigger this code? It
probably would be better to open the form and click a button on the form to
run the code -- that will ensure that everything is ready to go.

Or, let's back up and consider running the code in the procedure that is
opening the form. It's possible to avoid the need for the query to read a
value from the form's control, and instead provide the value directly to the
query -- all in VBA code. Tell us more about your process and setup, and we
can help you make this more efficient and useful, I believe.



>
> I Do apprreciate your help.
> --
> Jeff C
> Live Well .. Be Happy In All You Do
>
>
> "Ken Snell (MVP)" wrote:
>
>> If you're opening a recordset, why bother to write the value of the Dept
>> field to the form just so that you can then use it in the
>> TransferSpreadsheet action? Do it directly:
>>
>>
>> DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
>> "U:\DocDirectReports\PACPAJ01\" & QDepts![Dept] & ".xls"
>>
>>
>>
>> Now, define "hanging here" for us -- are you getting an error message
>> from
>> VBE? is nothing happening? more details, please.
>> --
>>
>> Ken Snell
>>
>>
>>
>>
>>
>> "Jeff C" wrote in message
>> news:80B1456A-7163-47F6-9DCA-319F16F4F713@microsoft.com...
>> > Thanks guys
>> >
>> > I now have :
>> >
>> > Dim Dept As ADODB.Recordset
>> > Set Dept = New ADODB.Recordset
>> >
>> > Dept.Open "QDepts", CurrentProject.Connection, adOpenStatic
>> >
>> > Do Until Dept.EOF
>> >
>> >
>> > Me.txtDept.Value = QDepts![Dept]
>> >
>> > On Error Resume Next
>> > DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
>> > "U:\DocDirectReports\PACPAJ01\" & [Forms]![FRpts].[txtDept] & ".xls"
>> > On Error Resume Next
>> > Dept.MoveNext
>> > Loop
>> > End Sub
>> >
>> >
>> > I am hanging here now: Me.txtDept.Value = QDepts![Dept]
>> >
>> > --
>> > Jeff C
>> > Live Well .. Be Happy In All You Do
>> >
>> >
>> >
>>
>>
>>