populate field based on data in another table

  • Thread starter Thread starter ursula
  • Start date Start date
U

ursula

I am trying to populate a field in a table (cumulative)
based on data in another table (team). For each record
in 'cumulative' I have a shift and date and I want to find
the corresponding date and shift in 'team' and write this
team field value to 'cumulative'. The code runs, but is
just writing the shift (not team) to the team field
in 'cumulative. Any help is apreciated.

Ursula


Do While Not (rsCumulative.EOF)
Day = rsCumulative!Date
Shift = rsCumulative!Shift
rsTeam.FindFirst rsTeam!Date = Day
If Shift = rsTeam!Shift Then
rsCumulative.Edit
rsCumulative!team = rsTeam!team
rsCumulative.Update
Else
rsTeam.FindLast rsTeam!date = Day
team = rsTeam!team
rsCumulative.Edit
rsCumulative!team = rsTeam!Team
rsCumulative.Update
End If
rsCumulative.MoveNext
Loop
 
-----Original Message-----
I am trying to populate a field in a table (cumulative)
based on data in another table (team). For each record
in 'cumulative' I have a shift and date and I want to find
the corresponding date and shift in 'team' and write this
team field value to 'cumulative'. The code runs, but is
just writing the shift (not team) to the team field
in 'cumulative. Any help is apreciated.

Ursula


Do While Not (rsCumulative.EOF)
Day = rsCumulative!Date
Shift = rsCumulative!Shift
rsTeam.FindFirst rsTeam!Date = Day
If Shift = rsTeam!Shift Then
rsCumulative.Edit
rsCumulative!team = rsTeam!team
rsCumulative.Update
Else
rsTeam.FindLast rsTeam!date = Day
team = rsTeam!team
rsCumulative.Edit
rsCumulative!team = rsTeam!Team
rsCumulative.Update
End If
rsCumulative.MoveNext
Loop
.
Click to expand...


Hi Ursula,

I think your main problem with the code above is probably
in the .findfirst syntax, where you should probably have
a "criteria" string-expression.

But, here is an easier way to do the same thing, anyway:

Do While Not (rsCumulative.EOF)
Day = rsCumulative!Date
Shift = rsCumulative!Shift
Team = Dlookup("[Team]","rsTeamTable","[Date] = #" &
day & "# and [Shift] = """ & [shift] & """")

If Not IsNull(Team) Then
rsCumulative.Edit
rsCumulative!team = Team
rsCumulative.Update
End If
rsCumulative.MoveNext
Loop

And, unless there is some reason you really want to do
this using VB code, the whole process could be done even
faster and simpler by creating an "update" query that
links the two tables on their common fields.

regards,
michael.
 
Back
Top