BryanEaton
Technical User
Hello,
I am having some trouble with indirection. I read the thread from Aug 7 02 which referred to a control on a form, I am trying to apply it to populating a table from another table. I am in Access 97.
Source Table Plan has these fields:
Contract - Text 10 eg 1231231234
CC - Text 8 eg 8110
Name - Text 40 Fred
WEF - Date 04/21/03
StatusCode - Text 1 P
PlanHours - Single 40
Target Table StaffPlan has these fields:
ContractNum - Text 10
CC - Text 8
EmpName - Text 40
StatusCode - Text 1
TotalPlan - Single
then a field for every week in field WEF from table "Plan" in yymmdd format eg '030407' '030414' '030421' etc - each of which is Single to hold the hours from the plan table.
Here is my code segment:
Dim MyWeeksHours As Field
Set rst = dbs.OpenRecordset("Plan", DB_OPEN_DYNASET)
Set rstTarget = dbs.OpenRecordset("StaffPlan", DB_OPEN_DYNASET)
With rst
.MoveFirst
Do Until .EOF
rstTarget.MoveFirst
Do Until rstTarget.EOF
If ![CONTRACT] = rstTarget![ContractNum] And ![CC] = rstTarget![CC] And ![Name] = rstTarget![EmpName] Then
rstTarget.Edit
MyWeeksHours = Format(![WEF], "yymmdd"
rstTarget!MyWeeksHours = ![PlanHours]
rstTarget.Update
End If
rstTarget.MoveNext
Loop
rstTarget.close
.MoveNext
Loop
.close
End With
Set dbs = Nothing
It falls over on the
MyWeeksHours = Format(![WEF], "yymmdd"
part saying "Object variable or With block variable not set"
I am sure that I am overlooking something really simple...
MyWeeksHours should hold the field name that I am trying to set.
Many thanks
Bryan
I am having some trouble with indirection. I read the thread from Aug 7 02 which referred to a control on a form, I am trying to apply it to populating a table from another table. I am in Access 97.
Source Table Plan has these fields:
Contract - Text 10 eg 1231231234
CC - Text 8 eg 8110
Name - Text 40 Fred
WEF - Date 04/21/03
StatusCode - Text 1 P
PlanHours - Single 40
Target Table StaffPlan has these fields:
ContractNum - Text 10
CC - Text 8
EmpName - Text 40
StatusCode - Text 1
TotalPlan - Single
then a field for every week in field WEF from table "Plan" in yymmdd format eg '030407' '030414' '030421' etc - each of which is Single to hold the hours from the plan table.
Here is my code segment:
Dim MyWeeksHours As Field
Set rst = dbs.OpenRecordset("Plan", DB_OPEN_DYNASET)
Set rstTarget = dbs.OpenRecordset("StaffPlan", DB_OPEN_DYNASET)
With rst
.MoveFirst
Do Until .EOF
rstTarget.MoveFirst
Do Until rstTarget.EOF
If ![CONTRACT] = rstTarget![ContractNum] And ![CC] = rstTarget![CC] And ![Name] = rstTarget![EmpName] Then
rstTarget.Edit
MyWeeksHours = Format(![WEF], "yymmdd"
rstTarget!MyWeeksHours = ![PlanHours]
rstTarget.Update
End If
rstTarget.MoveNext
Loop
rstTarget.close
.MoveNext
Loop
.close
End With
Set dbs = Nothing
It falls over on the
MyWeeksHours = Format(![WEF], "yymmdd"
part saying "Object variable or With block variable not set"
I am sure that I am overlooking something really simple...
MyWeeksHours should hold the field name that I am trying to set.
Many thanks
Bryan