VB code in Access not working

[Haze1023]

I have two sets of code that I used to copy and recreate records. They have
worked fine for years, and have been running ok with Access 2007 for several
months. All of a sudden they have stopped working. The funny thing is that
it is basically the same code, and it is breaking at two different points.
The code is supposed to copy the old form, update the employee data with new
information and then copy the linked records, changing the association. See
code is below. One of the copies will do everything except go to the new
record (which causes it to overwrite the old copy), the other will create the
new record and input all of the information, but will not run the SQL line to
insert the new lines into the records table. (records is a subform to the
Edit Menu and is linked by the workorder id). I think Access is trying to
make me crazy!

Private Sub ExtendClick()
Dim WOld As Integer
Dim WNew As Integer
Dim stLinkCriteria As String
Dim stDocName As String

'Get new LC
stLinkCriteria = "[EmployeeShortname]=" & "'" & Forms![Edit Form]![Employee]
& "'"
stDocName = "Employee"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden

proj = Forms![Edit Form]!Project
LC = Eval("Forms!Employee.txt" + proj)

'Extend WOA
Forms![Edit Form]!Extended_Chk = True
WOld = [Forms]![Edit Form]!WorkOrderID
Emp = Forms![Edit Form]!Employee
C = Forms![Edit Form]!CAM
ED = Forms![Edit Form]!EndDate
AM = Forms![Edit Form]!ApprovingMgr
sup = Forms![Edit Form]!ApprovingSupervisor

Forms![Edit Form]!Employee.SetFocus
DoCmd.GoToRecord , , acNewRec
Forms![Edit Form]!Employee = Emp
Forms![Edit Form]!Project = proj
Forms![Edit Form]!CAM = C
Forms![Edit Form]!StartDate = ED + 1
Forms![Edit Form]!ApprovingMgr = AM
Forms![Edit Form]!ApprovingSupervisor = sup
Forms![Edit Form]!EndDate = Forms![Edit Form]!StartDate + 89
Forms![Edit Form]!LaborCategory = LC
Forms![Edit Form]!Extended = WOld
WNew = [Forms]![Edit Form]!WorkOrderID

SQLString = "INSERT INTO Records ( Project, ChargeNumber, LaborCategory,
WorkOrderID ) SELECT Records.Project, Records.ChargeNumber, '" + LC + "',
[Forms]![Edit Form]!WorkOrderID AS Expr1 FROM Records WHERE
(((Records.WorkOrderID)=[Forms]![Edit Form]![Extended]));"

DoCmd.RunSQL SQLString
DoCmd.Close acForm, stDocName, acSaveNo

End Sub

 

[AccessVandal via AccessMonster.com]

I'm going to assumed that the variables were not declared like "proj" and
"LC". Are these and others declared somewhere in the module?

A2007 is more sensitive. These problems may crop up in the future, that's why
you're seeing them now.

I have two sets of code that I used to copy and recreate records. They have
worked fine for years, and have been running ok with Access 2007 for several
months. All of a sudden they have stopped working. The funny thing is that
it is basically the same code, and it is breaking at two different points.
The code is supposed to copy the old form, update the employee data with new
information and then copy the linked records, changing the association. See
code is below. One of the copies will do everything except go to the new
record (which causes it to overwrite the old copy), the other will create the
new record and input all of the information, but will not run the SQL line to
insert the new lines into the records table. (records is a subform to the
Edit Menu and is linked by the workorder id). I think Access is trying to
make me crazy!

Private Sub ExtendClick()
Dim WOld As Integer
Dim WNew As Integer
Dim stLinkCriteria As String
Dim stDocName As String

'Get new LC
stLinkCriteria = "[EmployeeShortname]=" & "'" & Forms![Edit Form]![Employee]
& "'"
stDocName = "Employee"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden

proj = Forms![Edit Form]!Project
LC = Eval("Forms!Employee.txt" + proj)

'Extend WOA
Forms![Edit Form]!Extended_Chk = True
WOld = [Forms]![Edit Form]!WorkOrderID
Emp = Forms![Edit Form]!Employee
C = Forms![Edit Form]!CAM
ED = Forms![Edit Form]!EndDate
AM = Forms![Edit Form]!ApprovingMgr
sup = Forms![Edit Form]!ApprovingSupervisor

Forms![Edit Form]!Employee.SetFocus
DoCmd.GoToRecord , , acNewRec
Forms![Edit Form]!Employee = Emp
Forms![Edit Form]!Project = proj
Forms![Edit Form]!CAM = C
Forms![Edit Form]!StartDate = ED + 1
Forms![Edit Form]!ApprovingMgr = AM
Forms![Edit Form]!ApprovingSupervisor = sup
Forms![Edit Form]!EndDate = Forms![Edit Form]!StartDate + 89
Forms![Edit Form]!LaborCategory = LC
Forms![Edit Form]!Extended = WOld
WNew = [Forms]![Edit Form]!WorkOrderID

SQLString = "INSERT INTO Records ( Project, ChargeNumber, LaborCategory,
WorkOrderID ) SELECT Records.Project, Records.ChargeNumber, '" + LC + "',
[Forms]![Edit Form]!WorkOrderID AS Expr1 FROM Records WHERE
(((Records.WorkOrderID)=[Forms]![Edit Form]![Extended]));"

DoCmd.RunSQL SQLString
DoCmd.Close acForm, stDocName, acSaveNo

End Sub