moving to next record using vbRetryCancel

G

Guest

Hi,
I have a data entry form used for gathering payroll info. I have code in the
Before Update event of the form that checks Empl#, BudgetCode, and WorkDates
against a history file to see if that person has already been paid for that
job. I'm OK to that point - my code works. When it finds a record that
matches in history, a msgbox pops up to tell them its a possible duplicate
and check this batch in history. The problem is that there are legitimate
reasons that a person could have more than one pay record on a particular day
so I have to be able to bypass this. I want a vbRetryCancel msgbox to return
focus to the WorkDate field if they hit retry but accept the record and go to
the next record if they hit cancel. I can get the set focus part but I can't
get it to go to the next record. DoCmd.GotoRecord ,, New Record gives me a
runtime error saying I can't go to that record. (Because the form is
DataEntry??) I can get passed the warning but I have to manually move to
another field and hit enter a couple of times when the msgbox pops up again
but it will eventually go to the next record - of course this looks sloppy as
hell. What am I doing wrong here? How can I get to the next record when they
hit cancel?

TIA
Lynne
 
D

David W

Dont know if this will help or not but try this, if it doesnt please post
your code.


Cancel = False
DoCmd.GotoRecord ,, New Record
 
G

Guest

David W said:
Dont know if this will help or not but try this, if it doesnt please post
your code.


Cancel = False
DoCmd.GotoRecord ,, New Record
thanks for the quick response. I tried the DoCmd and it didnt work. It said
it couldn't go to that record. I think it's because I'm in the Before Update
event. I though maybe what I needed was an Exit Sub but I can't get this to
work either. It doesn't seem to be seeing the Else part. If I put vbCancel
first it goes to the next record even if I click retry. If I put the vbRetry
first it sets focus even if I click cancel. I'm lost.
Here's the code. Don't laugh - it works - mostly.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varkey As Variant
Dim varkey2 As Variant
' Prompt for missing date
If IsNull(STARTDATE) Then
MsgBox "Please enter work date."
STARTDATE.SetFocus
' Check for proper date
Else
If (STARTDATE) > (ENDDATE) Then
MsgBox "Your Work End Date is prior to your Work Start Date. Please
correct."
STARTDATE.SetFocus
' Check for work date in history
Else
varkey2 = (DLookup("[tblhistory]![Budget Code]", "[tblHistory]", "
((([tblHistory]![EMPL NUMBER] = Forms![NEWSbprearn]![EMPL NUMBER]) and
(Forms![NEWSBPREARN]![StartDate] = [tblHistory]![startdate])) or
(([tblHistory]![EMPL NUMBER] = Forms![NEWSbprearn]![EMPL NUMBER]) and
(Forms![NEWSBPREARN]![StartDate] between [tblHistory]![startdate] and
[tblHistory]![enddate]))) "))
' Check for budget code match
varkey = (DLookup("[tblhistory]![remote batch number]", "[tblHistory]", "
((([tblHistory]![EMPL NUMBER] = Forms![NEWSbprearn]![EMPL NUMBER]) and
(Forms![NEWSBPREARN]![StartDate] = [tblHistory]![startdate])) or
(([tblHistory]![EMPL NUMBER] = Forms![NEWSbprearn]![EMPL NUMBER]) and
(Forms![NEWSBPREARN]![StartDate] between [tblHistory]![startdate] and
[tblHistory]![enddate]))) "))

If (varkey2) = BUDGET_CODE And Not IsNull(varkey) Then
MsgBox "This employee already has a payment in the history file for
this work date. Check batch number " & varkey, vbRetryCancel, "Invalid Date"
If vbRetry Then
STARTDATE.SetFocus
Else
If vbCancel Then
Exit Sub

End If
End If
End If
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top