Referencing a control on a subform on a Tab

[RexesOperator]

Please bare with me on this. I am usinging Access 2003. I apoligize for asking this again but ..

I did this right once before but I had to redesign the form. Somehow this has got messed up. I am using the syntax I have seen in Bob Larson's post on this.

I am using a command button (cmdUpLoadQEtoExcel) on the main form to load an excel spreadsheet. The text boxes I need to load are on a subform on a tab page. The main form is frmTransactionsMain. The subform name is frmQeSites and the control "container" is ctlQeSites. I want to load txtSITENAME into cell "B9".

ATM I have:

Private Sub cmdUpLoadQEtoExcel_Click()
On Error GoTo Err_OpenExcelFile_Click

Dim objXLApp As Object
Dim objXLBook As Object

Set objXLApp = CreateObject("Excel.Application")

Set objXLBook = objXLApp.Workbooks.Open("C:\Q11.xls")

objXLApp.Visible = True
objXLApp.UserControl = True

objXLBook.ActiveSheet.Range("B9") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSITENAME

Exit_OpenExcelFile_Click:
Exit Sub

Err_OpenExcelFile_Click:
MsgBox Err.Description
Resume Exit_OpenExcelFile_Click

End Sub

The excel file opens correctly - with no data. I'm getting an "Application not definer or object not defined error"

 

[georgedwilkinson]

What line does it error on?

You know you have a superfluous space in a line right?

objXLBook.ActiveSheet.Range("B9") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSITEN AME

 

[RexesOperator]

What line does it error on?

You know you have a superfluous space in a line right?

That's a forum issue. The name is spelled correctly. I've never used the debugger in Windows. The excel spreadsheet opens correctly so I'm assuming the problem is with the reference to the control

 

[boblarson]

1. Are you absolutely sure that the subform container is named ctlQeSites and not named something else.

2. If not using a subform, you don't refer to the tab control.

 

[RexesOperator]

1. Are you absolutely sure that the subform container is named ctlQeSites and not named something else.

Yes - the control name is ctlQeSites and the subform name is frmQeSites. After reading the example on your site and the other references in the forum I made sure the control and the subform had two different names. I did lie about the subform name - it is frmTransactionsQe.

2. If not using a subform, you don't refer to the tab control.[/QUOTE]

This much I know - I have seen this in your answers to other questions on this topic many times.

 

[RexesOperator]

I have also tried creating a new db and importing the objects as well as compact and repair db. WRT determining what line that is the problem, I am assuming it is the control reference, since Excel opens the way I expect it to. It's just that the data is not getting across to it. The error seems to occur when the procedure tries locate the data in Access - hence my reasoning that it is the control reference that is the problem.

 

[CraigDolphin]

Just on the off-chance...have you enabled the VBA reference for the Excel Object Library?

 

[RexesOperator]

Yes - it is selected.

 

[CraigDolphin]

Oh well. Worth asking the obvious sometimes Speaking of the obvious: we've covered the form and subform control names, but what about the textbox control txtSITENAME ?

And assuming that you have already checked that, have you tried debugging the value returned by the reference (Forms!frmTransactionsMain.ctlQeSites.Form.txtSITENAME) and see what you get?
eg

Msgbox Forms!frmTransactionsMain.ctlQeSites.Form.txtSITENAME,vbinformation

 

[RexesOperator]

Maybe you can spot something. I have stripped out the db, but left the code intact. I have commented out the fields that I eventually went to load (once I get this right).

 

[boblarson]

Okay, first of all, you still didn't have the text box correctly referred to. It ISN'T txtSiteName, it is txtSiteID. So the final code should have:

objXLBook.ActiveSheet.Range("B9") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSiteID

 

[RexesOperator]

Thank you Bob. That was it. I should send you over to my relatives in Oregon for your favourite brew - tea, coffee, ale whatever. (Are you near Portland? - they are in Beaverton.) If you're ever in Toronto, I could treat you to a Tim Horton's coffee.

 

[boblarson]

Thank you Bob. That was it. I should send you over to my relatives in Oregon for your favourite brew - tea, coffee, ale whatever. (Are you near Portland? - they are in Beaverton.) If you're ever in Toronto, I could treat you to a Tim Horton's coffee.

Actually I am in a suburb of Portland, although pretty far on the other side away from Beaverton. I do have to go to Beaverton occasionally for my current project that I'm working on (big one that our company is doing for NIKE). But, I'm not social enough to feel comfortable to go see people I don't know for something like that but the thought is nice.

Thanks though...

 

[RexesOperator]

Anytime you're in Toronto pm me and we can get a coffee.

Can I impose on you for one more question? I copied over the changes to the code, but Excel doesn't open now. I made sure the file is in the C:\I don't think anything has changed, but your eyes are better than mine:

Private Sub cmdUpLoadQEtoExcel_Click()
On Error GoTo Err_OpenExcelFile_Click

Dim objXLApp As Object
Dim objXLBook As Object

Set objXLApp = CreateObject("Excel.Application")

Set objXLBook = objXLApp.Workbooks.Open("C:\Q11.xls")

objXLApp.Visible = True
objXLApp.UserControl = True

etc ...

 

[boblarson]

Make sure you don't have any "rogue" Excel instances open due to the problems with the debugging. So, open Windows Task Manager and then see if any Excel instances are running and end them.

 

[RexesOperator]

I did that. And for good measure I did a restart.

 

[RexesOperator]

Eureka!

I had a .Value that I didn't need on one of the fields being uploaded. I remembered from an earlier post of mine that you answered that it wasn't necessary. I took that out and it works like a charm. I can even download from Excel now thanks to the info from Mike375.

And to think the fee for this project (unfortunately NOT paid to me) was going to be $50,000 cdn for this.

You guys are way underpaid.

confused:I would be interested in knowing how to reference a linked subform as in this thread - http://www.access-programmers.co.uk/forums/showthread.php?t=157838&highlight=linked+form.)

 

[boblarson]

confused:I would be interested in knowing how to reference a linked subform as in this thread - http://www.access-programmers.co.uk/forums/showthread.php?t=157838&highlight=linked+form.)

I just posted an answer there for you.