Duplicate entries

[Guest]

Hi,

I've looked at a few threads but they just went over head. Basically we
have a mileage cliam form which is often filled in many times for the same
person. I would like to warn the entry staff that this is happenning before
they exit the form.

This can only be determined when the last field has been completed. The
fields on the form are: Staff Member (Combo Box based on concatenated
fields, forename &surname), Date Received, Month Claimed, Year Claimed.

Is there any chance I can do this?

 

[John Vinson]

Hi,

I've looked at a few threads but they just went over head. Basically we
have a mileage cliam form which is often filled in many times for the same
person. I would like to warn the entry staff that this is happenning before
they exit the form.

This can only be determined when the last field has been completed. The
fields on the form are: Staff Member (Combo Box based on concatenated
fields, forename &surname), Date Received, Month Claimed, Year Claimed.

Is there any chance I can do this?

Yes, and it's not even all that hard <g>...

There are actually two ways. One would be to do this at the table
level; and use the Indexes tool to create a unique index on the
StaffID (hopefully you're not assuming that names are unique, they
AREN'T), the date, month, and year fields.

Or... Open the Form in design view. On the Events tab find the "Before
Update" event; click the ... by it and invoke the Code Builder. Edit
the code to something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("ClaimID", "[Claims]", _
"[StaffID] = " & Me!cboStaffID _
& " AND [Date Received] = #" & Me!txtDateReceived & "#" _
& " AND [Month Claimed] = '" & Me!txtMonthClaimed & "'" _
& " AND [Year Claimed] = " & Me!txtYearClaimed) Then
MsgBox "This claim has already been entered", vbOKOnly
Cancel = True ' or take some other appropriate action
End If

Of course, use your own field, table, and control names.


John W. Vinson[MVP]

 

[Guest]

Hi again,

I've tried the code and I'm getting problems compiling it. I'm not much
good at VB so I can't really spot the problem either the majority of it is
red in the code builder. I put my field and table names in but like I say
I'm not much good. This is what I'm currently working with:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("Claim Number", "[Mileage]", _
"[Staff Number] = " & Me!cboStaff Number _
& " AND [Date Received] = #" & Me!txtDateReceived & "#" _
& " AND [Month Claimed] = '" & Me!txtMonthClaimed & "'" _
& " AND [Year Claimed] = " & Me!txtYearClaimed) Then
MsgBox "This claim has already been entered", vbOKOnly
Cancel = True
End If

Sorry I'm such a novice.

Ian

Hi,

I've looked at a few threads but they just went over head. Basically we
have a mileage cliam form which is often filled in many times for the same
person. I would like to warn the entry staff that this is happenning before
they exit the form.

This can only be determined when the last field has been completed. The
fields on the form are: Staff Member (Combo Box based on concatenated
fields, forename &surname), Date Received, Month Claimed, Year Claimed.

Is there any chance I can do this?

Yes, and it's not even all that hard <g>...

There are actually two ways. One would be to do this at the table
level; and use the Indexes tool to create a unique index on the
StaffID (hopefully you're not assuming that names are unique, they
AREN'T), the date, month, and year fields.

Or... Open the Form in design view. On the Events tab find the "Before
Update" event; click the ... by it and invoke the Code Builder. Edit
the code to something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("ClaimID", "[Claims]", _
"[StaffID] = " & Me!cboStaffID _
& " AND [Date Received] = #" & Me!txtDateReceived & "#" _
& " AND [Month Claimed] = '" & Me!txtMonthClaimed & "'" _
& " AND [Year Claimed] = " & Me!txtYearClaimed) Then
MsgBox "This claim has already been entered", vbOKOnly
Cancel = True ' or take some other appropriate action
End If

Of course, use your own field, table, and control names.


John W. Vinson[MVP]

 

[John Vinson]

Hi again,

I've tried the code and I'm getting problems compiling it. I'm not much
good at VB so I can't really spot the problem either the majority of it is
red in the code builder. I put my field and table names in but like I say
I'm not much good. This is what I'm currently working with:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("Claim Number", "[Mileage]", _
"[Staff Number] = " & Me!cboStaff Number _
& " AND [Date Received] = #" & Me!txtDateReceived & "#" _
& " AND [Month Claimed] = '" & Me!txtMonthClaimed & "'" _
& " AND [Year Claimed] = " & Me!txtYearClaimed) Then
MsgBox "This claim has already been entered", vbOKOnly
Cancel = True
End If

What are the datatypes of the fields? You need to use the right
delimiter: # for dates, ' for text (I assumed that the Month Claimed
was text such as 'September'), no delimiter for numeric.

And what error message are you getting? You do need another closing
parenthesis after txtYearClaimed; and square brackets around the

cboStaff Number

Do you in fact have the Name properties of your controls renamed to
[cboStaff Number], [txtDateReceived] etc.? These were MY GUESSES; you
need to use the actual Name property of the controls.

John W. Vinson[MVP]