Message Boxes

[Maggie23]

My form has two fields that do not allow duplicates. When I go to save a record if either field is a duplicate the message comes up "can't go to specific record". I need a clearer message than that to tell users who are even more novice than I am that they have entered a duplicate in one or both of the fields. Does someone have an easy way to do this? Is there a way to just change that message?
Thanks for the help.

 

[Rob.Mills]

I would setup an event in the control's BeforeUpdate event to check if the value already exists in the table. If it does you can cancel the update and give a custom message to the user.

Example:

If Not IsNull(DLookUp("Field","Table","Field=" & Me!Control)) Then

Cancel = True

MsgBox "This value already exists."

Me!Control.Undo

End If

 

[Maggie23]

Thanks for the help but still having a problem. I entered the code in the control of the AccessionID field on my form as a BeforeUpdate event as follows:

Private Sub ID__BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("AccessionID", "Booklist", "AccessionID=" & Me!Control)) Then
Cancel = True

MsgBox "This number already exists."

Me!Control.Undo

End If

End Sub

My field is "AccessionID" and the underlying table is "Booklist" I get the error message that "Access can't find the field 'Contrtol' referred to in your expression". I don't understand what it is telling me.
Thanks for further help.

 

[Rob.Mills]

You'll need to replace Control with the actual name of the control (textbox, combobox, etc.)

 

[Maggie23]

I'm making progress but ...

I used the undo code to validate the uniqueness of one field - works fine. I wanted to use it on another field but get "data type mismatch in criteria". I copied the code from the first field and pasted it to the next field's event procedure. The only change was the field name and the form control. The only other difference is the working field is numeric and the non-working field (BarcodeNo) is text with a default value. The default is actually a string that preceeds 5 more characters user adds.

This is the code I used:

Private Sub Barcode_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("BarcodeNo", "Booklist", "BarcodeNo=" & Me!BarcodeNo)) Then
Cancel = True

MsgBox "This number already exists."

Me!Barcode.Undo

End If

Would the default string make a difference? I really need to use it.
Thanks for any add'l help.
Maggie

 

[Rob.Mills]

You're thinking is correct. The string values need to be handled differently as do date values too.

string values must be contained in single quotes. Dates must be contained in the # sign.

So this is what the line should look like for the string value:

If Not IsNull(DLookup("BarcodeNo", "Booklist", "BarcodeNo='" & Me!BarcodeNo & "'")) Then

It's hard to read but if you look closely there's a single quote followed by a double quote after the equal sign and then at the end is a double, single, and double

If it were a date value make it like this:

If Not IsNull(DLookup("BarcodeNo", "Booklist", "BarcodeNo=#" & Me!BarcodeNo & "#")) Then

This should get it working for you now.

 

[Maggie23]

Thanks

Rob
Thanks so much for your help. Everything seems to be working great. You've been a major stress reducer!!
Maggie