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