B
Bill R via AccessMonster.com
I have a query that is based on records in tblDates, along with 2 other
queries and a table. It is ORDER BY StartDate DESC. It is not updateable.
The records are evaluated to determine whether a pair of start and finish
dates are missing while subsequent pairs are present, meaning that dates for
a mandatory prior phase were not entered. If those dates are missing, I do
the following:
Do
if(isnull(.fields("StartDate").....
rs.moveprevious 'moves to the previous record which is actually the phase
subsequent to the current one
nxtStart = rs.fields("StartDate")
AddPhDates(dmsid, locid, duration, nxtStart)
rs.movenext 'move back to the current phase
rs.movenext 'move to the next (prior) phase
end if
Loop
This works fine. The function AddPhDates takes the start date of the next
phase and adds a record to tblDates based on a calculation backwards from the
start date of the next phase, using duration, and includes the current
location id (locid) and item id (dmsid), and start and finish dates.
The problem arises when the very next record in the query is also missing
dates.
AddPhDates uses .addnew and .update and tblDates is updated accordingly, but
the rs in the calling procedure doesn't show the updated dates, because it
hasn't been requeried. If I requery it, I will end up going back to the
beginning of the recordset and running the same records through the procedure
every time there are 2 successive records missing dates. I'm using a named
query in my database and I'm trying to avoid writing the sql string in the
calling procedure and updating the string every time this situation occurs,
because the string is very long and complicated.
Thanks,
Bill
queries and a table. It is ORDER BY StartDate DESC. It is not updateable.
The records are evaluated to determine whether a pair of start and finish
dates are missing while subsequent pairs are present, meaning that dates for
a mandatory prior phase were not entered. If those dates are missing, I do
the following:
Do
if(isnull(.fields("StartDate").....
rs.moveprevious 'moves to the previous record which is actually the phase
subsequent to the current one
nxtStart = rs.fields("StartDate")
AddPhDates(dmsid, locid, duration, nxtStart)
rs.movenext 'move back to the current phase
rs.movenext 'move to the next (prior) phase
end if
Loop
This works fine. The function AddPhDates takes the start date of the next
phase and adds a record to tblDates based on a calculation backwards from the
start date of the next phase, using duration, and includes the current
location id (locid) and item id (dmsid), and start and finish dates.
The problem arises when the very next record in the query is also missing
dates.
AddPhDates uses .addnew and .update and tblDates is updated accordingly, but
the rs in the calling procedure doesn't show the updated dates, because it
hasn't been requeried. If I requery it, I will end up going back to the
beginning of the recordset and running the same records through the procedure
every time there are 2 successive records missing dates. I'm using a named
query in my database and I'm trying to avoid writing the sql string in the
calling procedure and updating the string every time this situation occurs,
because the string is very long and complicated.
Thanks,
Bill