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

J

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
 
B

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.
 
G

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


boblarson said:
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.


Janis said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top