Selecting Current Record for After Update Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a recordset inside of a subform. I have a procedure that is
supposed to run whenever someone updates a date field in the subform. 80% of
the time it works fine, however, 20% of the time it crashes. After the date
has been updated the procedure starts, the program acts as if there is not
current record. I put in a test and it is coming up with BOF for that
recordset even though the user has entered the recordset, changed a date,
the cursor is still in the date field and the AFTER UPDATE event has been
triggered.

Is there any way to ensure that this does not happen? I was hoping that
there was some command I could use inside the procedure to make sure that the
field that has the cursor in it is selected as the current record. Also, is
there any reason why this only happens every 4th or 5th time I try?

Thanks,

Ryan
 
Does it fail on NEW records and not on existing ones that are being
modified?

What does your code look like?
 
John,

The first part of my code is below. The first end if statement is to
determine whether or not to run the subroutine. The next set of code you
will notice both a Recordset.MovePrevious and a Recordset.MoveNext statement
along with an IF statment to see if it is BOF. This is all my simplistic way
of trying to ensure that the current record is selected. It can be removed
if I can figure out the problem. The next set of code shows where the
problem started setting the bookmark. This is what originally caused the
problem, trying to set a bookmark when there was no current record. The
program then goes on to read in several values for each record, update dates
further down the record set and then return to the book mark location. I
left much of the code off to keep it short. Please let me know if you need
more information.

Ryan


Private Sub EndDate_AfterUpdate()
If Complete.Value = -1 Then
GoTo 15 'End Subroutine
End If
x = 1 'Loop Variable

If Recordset.BOF = False Then
Recordset.MovePrevious
End If
If Recordset.BOF = True Then
MsgBox "No record selected. Please highlight a line in the schedule and
re-enter date", vbOKOnly
GoTo 10 ' End Program - I still have not figure out how you can update a
date and not have a current record
Else
Recordset.MoveNext
End If


BMK = Recordset.Bookmark ' set a bookmark at current record so you can return
pTask(x) = Task.Value
pEndDay(x) = EndDay.Value
pEndDate(x) = EndDate.Value
pLink(x) = Link.Value
pComplete(x) = Complete.Value
 
If the record is a new record and (by definition) not saved then I believe
you don't have a current record and you don't have a bookmark in the
recordset for the not-yet-existinig record. Is that the case here?

I can't tell from the snippets of code you have posted what is happening.
 
John,

Sorry I missed this question on the last post. This would not involve
adding a new record. It would only involve updating a date field on an
existing record. I have included the full code below. In essence, the user
updates a date in a subform, after he does this, the code reschedules every
task after it based on the updated date. In runs down the subform
rescheduling dates avoiding weekends and holidays. It is very similar to the
"Workday" function in Excel.


Private Sub EndDate_AfterUpdate()
'Read in values of current record

'If altering a complete date on a complete item, do not update the rest of
schedule
If Complete.Value = -1 Then
'End Subroutine
GoTo 15
End If
x = 1 'Loop Variable


'Routine to ensure current record is selected
If Recordset.BOF = False Then
Recordset.MovePrevious
End If
If Recordset.BOF = True Then
MsgBox "No record selected. Please highlight a line in the schedule and
re-enter date", vbOKOnly
GoTo 10 ' End Program - I still have not figure out how you can update a
date and not have a current record
Else
Recordset.MoveNext
End If



BMK = Recordset.Bookmark ' set a bookmark at current record so you can return
'Read in all values for the record
pTask(x) = Task.Value
pEndDay(x) = EndDay.Value
pEndDate(x) = EndDate.Value
pLink(x) = Link.Value
pComplete(x) = Complete.Value
'If date changed is not linked then quit subroutine
If pLink(x) = 0 Or pComplete(x) = -1 Then
GoTo 10 ' task is either not linked or already complete so skip over
this record
End If
Recordset.MoveNext ' move to next record
x = x + 1


'Work day schedule routine which schedules dates ignoring weekends and
holidays
Do While Recordset.EOF = False 'Continue down schedule until End of File is
Reached
pLink(x) = Link.Value
pComplete(x) = Complete.Value
If pLink(x) = -1 And pComplete(x) = 0 Then
'Read in values for this record
pTask(x) = Task.Value
pEndDay(x) = EndDay.Value
TestDate = pEndDate(x - 1) ' Date
EndDayDiff = pEndDay(x) - pEndDay(x - 1)
If EndDayDiff >= 0 Then
StartDay = 1
StepDay = 1
Else
StartDay = -1
StepDay = -1
End If

'TEST FOR HOLIDAYS & WEEKENDS
For y = StartDay To EndDayDiff Step StepDay ' For each day that we
have to add, we must check if it is a weekend or a holiday
TestDate = TestDate + StepDay ' check the first day
5 If Weekday(TestDate) = 7 Or Weekday(TestDate) = 1 Then
'Saturday or Sunday
TestDate = TestDate + StepDay
End If
If Weekday(TestDate) = 1 Or Weekday(TestDate) = 7 Then 'Sunday
or Saturday
TestDate = TestDate + StepDay
End If
'Check for Holiday
Dim TestDateEqualsHoliday As Variant
TestDateEqualsHoliday = DLookup(TestDate, "[Holidays]",
"[HolidayDate]= #" & TestDate & "#")
If Not IsNull(TestDateEqualsHoliday) Then
' A date exists on the holiday table. I had Dlookup return the
'HolidayID, that way you can exactly know exactly what date
conflicts,
'and if you wanted, use that to display to the end user
'If found loop back up and verify that new date is not a
weekend or a consecutive holiday
TestDate = TestDate + StepDay
GoTo 5 ' This date is either a holiday or a weekend so move
skip to next date and check it
' Else the date doesn't exist.
End If
Next y

'Change date value based on updated dates
pEndDate(x) = TestDate
EndDate.Value = pEndDate(x)
x = x + 1
Recordset.MoveNext
Else
'If record is not linked don't read in, move to next
Recordset.MoveNext
End If
Loop

10 'Goto for return to bmk and end subroutine
Recordset.Bookmark = BMK
15 ' Goto for end subroutine
End Sub
 
I also noticed today that the same thing is happening when I click a "yes/no"
box in the same subform. I have a similar peice of code w/ a bookmark that
crashes due to no current record. The thing that caught my eye was that the
focus was on the check box I clicked when it crashed. So I opened the
subform, and I used the scroll bar to adjust the view to see the proper
record. Once I let go of the scroll bar, the focus kicked over to the check
box column. If the focus was on the check box I clicked, the program crashed
due to " No current record" for the bookmark. If the focus was on the check
box of a different record than the one I clicked, the program did not crash.

Any suggestions why this might be occuring?

RyanJLH said:
John,

Sorry I missed this question on the last post. This would not involve
adding a new record. It would only involve updating a date field on an
existing record. I have included the full code below. In essence, the user
updates a date in a subform, after he does this, the code reschedules every
task after it based on the updated date. In runs down the subform
rescheduling dates avoiding weekends and holidays. It is very similar to the
"Workday" function in Excel.


Private Sub EndDate_AfterUpdate()
'Read in values of current record

'If altering a complete date on a complete item, do not update the rest of
schedule
If Complete.Value = -1 Then
'End Subroutine
GoTo 15
End If
x = 1 'Loop Variable


'Routine to ensure current record is selected
If Recordset.BOF = False Then
Recordset.MovePrevious
End If
If Recordset.BOF = True Then
MsgBox "No record selected. Please highlight a line in the schedule and
re-enter date", vbOKOnly
GoTo 10 ' End Program - I still have not figure out how you can update a
date and not have a current record
Else
Recordset.MoveNext
End If



BMK = Recordset.Bookmark ' set a bookmark at current record so you can return
'Read in all values for the record
pTask(x) = Task.Value
pEndDay(x) = EndDay.Value
pEndDate(x) = EndDate.Value
pLink(x) = Link.Value
pComplete(x) = Complete.Value
'If date changed is not linked then quit subroutine
If pLink(x) = 0 Or pComplete(x) = -1 Then
GoTo 10 ' task is either not linked or already complete so skip over
this record
End If
Recordset.MoveNext ' move to next record
x = x + 1


'Work day schedule routine which schedules dates ignoring weekends and
holidays
Do While Recordset.EOF = False 'Continue down schedule until End of File is
Reached
pLink(x) = Link.Value
pComplete(x) = Complete.Value
If pLink(x) = -1 And pComplete(x) = 0 Then
'Read in values for this record
pTask(x) = Task.Value
pEndDay(x) = EndDay.Value
TestDate = pEndDate(x - 1) ' Date
EndDayDiff = pEndDay(x) - pEndDay(x - 1)
If EndDayDiff >= 0 Then
StartDay = 1
StepDay = 1
Else
StartDay = -1
StepDay = -1
End If

'TEST FOR HOLIDAYS & WEEKENDS
For y = StartDay To EndDayDiff Step StepDay ' For each day that we
have to add, we must check if it is a weekend or a holiday
TestDate = TestDate + StepDay ' check the first day
5 If Weekday(TestDate) = 7 Or Weekday(TestDate) = 1 Then
'Saturday or Sunday
TestDate = TestDate + StepDay
End If
If Weekday(TestDate) = 1 Or Weekday(TestDate) = 7 Then 'Sunday
or Saturday
TestDate = TestDate + StepDay
End If
'Check for Holiday
Dim TestDateEqualsHoliday As Variant
TestDateEqualsHoliday = DLookup(TestDate, "[Holidays]",
"[HolidayDate]= #" & TestDate & "#")
If Not IsNull(TestDateEqualsHoliday) Then
' A date exists on the holiday table. I had Dlookup return the
'HolidayID, that way you can exactly know exactly what date
conflicts,
'and if you wanted, use that to display to the end user
'If found loop back up and verify that new date is not a
weekend or a consecutive holiday
TestDate = TestDate + StepDay
GoTo 5 ' This date is either a holiday or a weekend so move
skip to next date and check it
' Else the date doesn't exist.
End If
Next y

'Change date value based on updated dates
pEndDate(x) = TestDate
EndDate.Value = pEndDate(x)
x = x + 1
Recordset.MoveNext
Else
'If record is not linked don't read in, move to next
Recordset.MoveNext
End If
Loop

10 'Goto for return to bmk and end subroutine
Recordset.Bookmark = BMK
15 ' Goto for end subroutine
End Sub



John Spencer said:
If the record is a new record and (by definition) not saved then I believe
you don't have a current record and you don't have a bookmark in the
recordset for the not-yet-existinig record. Is that the case here?

I can't tell from the snippets of code you have posted what is happening.
Click to expand...
Click to expand...
 
Back
Top