Application-Defined or Object-Defined Error

[RexesOperator]

Something really weird is going on. This is Access 2003.

I have a procedure that uploads from Access to Excel. The button is on the main form frmTransactions, but the information being uploaded is on a subform frmTransactionsQe. The subform control is ctlQeSites.

I have a number of text boxes on the form. One is txtSITEID, the next is txtSITENAME etc. When I click on the Upload button I created, Excel is called up. The first time I run the procedure the correct value will be loaded into the appropriate cell. Say - txtSITENAME's contents will be transferred to "B9". The next time it is run, I get the Application-Defined error. When I check the code - the name of the text box has been changed.

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

should be

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

It looks like the code is stepping through to the next text box - except that isn't the next one in the tab view, so I don't know where it is getting the item from.

I have tried compact and repair, as well as importing everything to a new db. I have also tried commenting out all but one of the lines of code to see if this affecting more than just the first one - it is. I have also attached the original procedure BEFORE I ran it, and an image of the form design to show I am using the correct text box name. I have tried the db on different notebooks. I have tried deleting the text box from the form and creating a new one. I have tried other text boxes to see if they would load, and they didn't. However - the exact same code works in an earlier version of this db - on both notebooks

Here is the procedure:

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
objXLBook.ActiveSheet.Range("B10") = Forms!frmTransactionsMain.ctlQeSites.Form.cboSITEPROVINCE
objXLBook.ActiveSheet.Range("B11") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSITELATDEG
objXLBook.ActiveSheet.Range("B12") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSITELATMIN
objXLBook.ActiveSheet.Range("B13") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSITELATSEC
objXLBook.ActiveSheet.Range("B14") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSITELONDEG
objXLBook.ActiveSheet.Range("B15") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSITELONMIN
objXLBook.ActiveSheet.Range("B16") = Forms!frmTransactionsMain.ctlQeSites.Form.txtSITELONSEC
objXLBook.ActiveSheet.Range("B17") = Forms!frmTransactionsMain.ctlQeSites.Form.txtELEVATION
objXLBook.ActiveSheet.Range("B18") = Forms!frmTransactionsMain.ctlQeSites.Form.txtTOWERHT


Exit_OpenExcelFile_Click:
Exit Sub

Err_OpenExcelFile_Click:
MsgBox Err.Description
Resume Exit_OpenExcelFile_Click

End Sub

There are no spaces in the code - that is the forum thing again.

 

[RexesOperator]

Finally got this to work by importing the form with a working version from another copy of the db. I spent several hours on other aspects of the design and functionality of the form, so I was reluctant to do it this way. However the above code works the way I want it to. Now the question is why did the text box name get changed?

 

[RexesOperator]

Now this doesn't work again.

 

[boblarson]

What form is this on:

cmdUpLoadQetoExcel_Click


Is it on the same form as the subform you are trying to get the values from?

 

[RexesOperator]

On the main form. The excel file opens correctly but won't load the fields.

 

[boblarson]

Try this instead:

With objXLBook.ActiveSheet
   .Range("B9") = Me.ctlQeSites.Form.txtSITENAME
   .Range("B10") = Me.ctlQeSites.Form.cboSITEPROVINCE
   .Range("B11") = Me.ctlQeSites.Form.txtSITELATDEG
   .Range("B12") = Me.ctlQeSites.Form.txtSITELATMIN
   .Range("B13") = Me.ctlQeSites.Form.txtSITELATSEC
   .Range("B14") = Me.ctlQeSites.Form.txtSITELONDEG
   .Range("B15") = Me.ctlQeSites.Form.txtSITELONMIN
   .Range("B16") = Me.ctlQeSites.Form.txtSITELONSEC
   .Range("B17") = Me.ctlQeSites.Form.txtELEVATION
   .Range("B18") = Me.ctlQeSites.Form.txtTOWERHT
End With

 

[RexesOperator]

That worked (I think the other should have). Now let's see if it will keep on working

 

[boblarson]

That worked (I think the other should have). Now let's see if it will keep on working

What I've typically found is that if the code you are using is on the form you are referencing, you should use the ME keyword instead of trying to reference it with the full qualified referencing. Don't know why that is, but it seems to work when the other doesn't.

 

[RexesOperator]

Don't know why that is, but it seems to work when the other doesn't.

Hmm..logic in programming - what an odd concept.

Thanks for your help. I was doing a demo of the db for my manager yesterday when this thing tanked again.

Murphy's law.

 

[RexesOperator]

As an FYI - I finally got my manager (he knows VB - just not Access) to look at the code. What happened was I made a false assumption - when I closed the form I thought the code was being saved.

Once I saved the code AND the form (as he pointed out), everything worked the way I expected it to. At least now I know why the code was being changed (it reverted to the last saved version).

Another lesson learned.

I promise I will get this right.