afterUpdate event, can't find the field referred to by the form

[Janis]

I have one more problem with this update form sub. The debugger also stops
on the line to input the fields from the form into the spreadsheet. It says
Microsoft cannot find the field form referred to in this expression? It
stopped on the first line starting with Range("a1").

tia,

-------------

Private Sub Form_AfterUpdate()


'*******************************************************************
'Purpose: To update an Excel spreadsheet with each subsequent record
'*******************************************************************



Const STR_DIRECTORY_PATH = "C:\Test\"
Const STR_Filename = "emp.xls"

Dim lngLastError As Long
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim i As Integer
Dim EndRow As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset


'Check if directory exists if not create it

If Dir(STR_DIRECTORY_PATH) = "" Then
MkDir STR_DIRECTORY_PATH
End If


Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("emp.xls")

Set wks = appExcel.Worksheets("Employees")
wks.Activate

EndRow = Range("A65536").End(xlUp).Select

Range("a1").Offset(0, EndRow + 1).Value = Forms![frmEmployees]!Form![ID]
Range("a2").Offset(0, EndRow + 1).Value =
Forms![frmEmployees]!Form![FirstName]
Range("a3").Offset(0, EndRow + 1).Value = Forms![frmEmployees]!Form![Salary]


Set dbs = Nothing
End Sub

 

[boblarson]

Be sure to EXPLICITLY refer to your objects. Otherwise not only might it not
work properly, but it will keep the connection to Excel open (even if you
attempt to close it) until Access closes.

So, use

wks.Range("A1")...

instead (my sample is abbreviated but you just need to use the wks object
before items, including your EndRow.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[George Nicholson]

Set wks = appExcel.Worksheets("Employees")

shouldn't that be?
Set wks =wbk.Worksheets("Employees")

as well as all Range references should be wks.Range

--
HTH,
George

Be sure to EXPLICITLY refer to your objects. Otherwise not only might it
not
work properly, but it will keep the connection to Excel open (even if you
attempt to close it) until Access closes.

So, use

wks.Range("A1")...

instead (my sample is abbreviated but you just need to use the wks object
before items, including your EndRow.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

I have one more problem with this update form sub. The debugger also
stops
on the line to input the fields from the form into the spreadsheet. It
says
Microsoft cannot find the field form referred to in this expression? It
stopped on the first line starting with Range("a1").

tia,

-------------

Private Sub Form_AfterUpdate()


'*******************************************************************
'Purpose: To update an Excel spreadsheet with each subsequent record
'*******************************************************************



Const STR_DIRECTORY_PATH = "C:\Test\"
Const STR_Filename = "emp.xls"

Dim lngLastError As Long
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim i As Integer
Dim EndRow As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset


'Check if directory exists if not create it

If Dir(STR_DIRECTORY_PATH) = "" Then
MkDir STR_DIRECTORY_PATH
End If


Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("emp.xls")

Set wks = appExcel.Worksheets("Employees")
wks.Activate

EndRow = Range("A65536").End(xlUp).Select

Range("a1").Offset(0, EndRow + 1).Value = Forms![frmEmployees]!Form![ID]
Range("a2").Offset(0, EndRow + 1).Value =
Forms![frmEmployees]!Form![FirstName]
Range("a3").Offset(0, EndRow + 1).Value =
Forms![frmEmployees]!Form![Salary]


Set dbs = Nothing
End Sub