Message Box for duplicate entry 1

[THWatson]

The table holds a list of names, each of which has a RollNbr. The RollNbr cannot have a duplicate.

The first entry box in the form is this RollNbr. If the user enters a number that already exists, I would like a message box to advise the user of this, before she continues.

What is the code I put in the Before Update event?

I realize that, with a duplicate in the RollNbr, the record will not save because a system message will occur saying "You cannot go to to the specified record" but this is unclear as to what the problem is.

I am using Access 2000.

Thanks.

Tom

 

[jbehrne]

Hi Tom,

Try using the Dlookup statement (see Access VBA help for more info). If the Dlookup returns a value then you would display a msgbox stating that the value has already been entered.

Something like this

TempVar = Dlookup([Field1],"Table",[Field1] = [TextBoxNameOnForm])

If TempVar > 1 Then
MsgBox "You have entered a duplicate value. Enter another value.", vbOkOnly, "Duplicate Value Found"
Me!TextBoxNameOnForm = ""
Me!TextBoxNameOnForm.SetFocus
End If

HTH,
jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[THWatson]

Hi Jean-Paul

Thanks. I'll try this solution.

By the way, this is an entirely different database than the other one I am still working on.

Tom

 

[THWatson]

Jean-Paul

Following your example, and checking the VBA Help for DLookup, here's the code I entered...

Private Sub RollNbr_BeforeUpdate(Cancel As Integer)
Dim varTemp As Variant
varTemp = DLookup("[RollNbr]", "tblRollInfo", "[RollNbr] = Forms![frmRollInfo]![RollNbr]&quot

If varTemp = Forms!frmRollInfo!RollNbr Then
MsgBox "You have entered a duplicate value. Enter another value.", vbOKOnly, "Duplicate Value Found"
Me!RollNbr = ""
Me!RollNbr.SetFocus
End If
End Sub

I get the Message Box alright, but then the code fouls out. I get a message saying "You must save the field first before you can use a Go To Control or SetFocus"

If I take out the two lines that set the RollNbr to "" RollNbr.SetFocus then I get the message but it doesn't set the focus back to the RollNbr field.

Any ideas?

Tom

 

[jbehrne]

Hi Tom,

Try switching the lines from:

Me.RollNbr = ""
Me!RollNbr.SetFocus

To:

Me!RollNbr.SetFocus
Me!RollNbr = ""

- That way it shouldn't need to save the record in order to change the value back to null. Let me know what happens,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[THWatson]

Jean-Paul

Nope. I still get the same message.

Run time error code 2108
"You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method."

I notice it doesn't talk about saving the record, rather about saving the field.

Tom

 

[jbehrne]

Hi Tom,

You might want to try running the code on the field's "After Update" event. I have the same code running on several forms (on the "after update" event) with no problem - my guess is that it doesn't like to run with the "Before Update" event.

HTH,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[THWatson]

Jean-Paul

Well, I'm stumped!

I tried the code in the After Update event. Same results. I get the Message Box alright but then run time errors.

If I have the Me!RollNbr.Set Focus line in the code, I get a run-time error that I explained before, and it points me to the SetFocus line.

If I take out the SetFocus line, I get a run time error that says "invalid use of Null" and it points me to the DLookup Line.

I have tried changing the variable type to Long Integer, because that is the field type in the table. Same results.

I'm about ready to abandon ship on this one. Is your code identical to what I posted?

Tom

 

[THWatson]

Jean-Paul

Here's something interesting. I find that if I take out the RollNbr.SetFocus line, and the RollNbr = "" line, and put in "Cancel = True", then the message comes up and the cursor goes back to the correct box by itself.

Here's the code that works...
Private Sub RollNbr_BeforeUpdate(Cancel As Integer)
Dim varTemp As Variant
varTemp = DLookup("[RollNbr]", "tblRollInfo", "[RollNbr] = Forms![frmRollInfo]![RollNbr]&quot

If varTemp = Forms!frmRollInfo!RollNbr Then
MsgBox "That Roll Number already exists! Please re-enter.", vbOKOnly, "Duplicate Roll Number Found"
Cancel = True
End If
End Sub

Odd, methinks. At least, I don't understand it.

Tom

 

[aflat]

Hi Tom,

Try using the duplicate record constant "3022". In the save icon on your form, place the following type of code in the error event.

Err_cmdSaveRecord_Click:
If Err.Number = 3022 Then
Beep
MsgBox "You are trying to add a record that matches a record" & vbCrLf _
& "that already exists." & vbCrLf _
& vbCrLf _
& "Check existing record or change/delete this new record.", vbOKOnly _
+ vbExclamation, "Duplicate record!"
'Add code here to resolve duplicate
Else
MsgBox Err.Description
End if

aflat

 

[THWatson]

Aflat

Thanks. I'll try that.

Do you have any idea why the SetFocus doesn't work? I was wondering if perhaps it has to do with the fact that the "name" of the control is the same as the control itself - in both the table and the form - and Access gets confused. This happened because I simply made the from from the table.

It's the only explanation I can think of. Because I, like Jean-Paul, have used SetFocus without trouble in other situations.

Tom