I have a form [form1] and a field [number]. The number is set as the key and to not allow duplicates. However there is not a message telling me that the number is duplicated until after i have completed the entire form and either move on to the next record or close it. I need something to run AfterUpdate on the field [number] that will let me know that it is already in the database.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
No Duplicates
- Thread starter chad4828
- Start date
Hi
You can do that by adding code in after update event of the Number CONTROL, but in a multi user environment it will not be reliable since another user may add the number in question before you update your record (which unless you explicitly do it happens when you move off the current record
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
You can do that by adding code in after update event of the Number CONTROL, but in a multi user environment it will not be reliable since another user may add the number in question before you update your record (which unless you explicitly do it happens when you move off the current record
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Thread starter
- #3
chad4828
After entering the control number, but before updating the record, you can use DLookUp
Assumptions
- YourTable - name of table to check
- YourCtlID - the name of the field you want to check. This value is numeric. This is not an autonumber field.
- Me.YourCtlID - name of the control object on the form. "Me" references the local form. This value is numeric. The end user can enter a number into this control.
For the AfterUpdate event procedure
If this control value is using an autonumber, then as indicated by Ken, sequential autonumbers in a multiuser shared database do not work well.
Access creates the autonumber as soon as you start entering data. The control for the autonumber is not advanced until the current record is committed or written to the tabse. Two or more users enter start entering data in the same form before any of the data entry has been completed by the end users, both / all end users will have grabbed the same autonumber.
A simple work around for this is to use a random autonumber, but an even better answer is to use a system generated number...
Richard
After entering the control number, but before updating the record, you can use DLookUp
Assumptions
- YourTable - name of table to check
- YourCtlID - the name of the field you want to check. This value is numeric. This is not an autonumber field.
- Me.YourCtlID - name of the control object on the form. "Me" references the local form. This value is numeric. The end user can enter a number into this control.
For the AfterUpdate event procedure
Code:
If (DLookUp("[YourCtlID]", "YourTable", "[YourCtlID] = " & Me.YourCtlID)) Then
MsgBox "Number already exists: " & Me.YourCtlID, vbOk
Me.YourCtlID = Me.Dirty
End IfIf this control value is using an autonumber, then as indicated by Ken, sequential autonumbers in a multiuser shared database do not work well.
Access creates the autonumber as soon as you start entering data. The control for the autonumber is not advanced until the current record is committed or written to the tabse. Two or more users enter start entering data in the same form before any of the data entry has been completed by the end users, both / all end users will have grabbed the same autonumber.
A simple work around for this is to use a random autonumber, but an even better answer is to use a system generated number...
Richard
- Thread starter
- #5
Hi
It will not work reliably in a multi-user environment, my comments had nothing to do with autonumbers
Consider this:
User A input their "unique Number" (say 10), and tabs off the control, the after update event fires and the number is checked for uniqueness, all OK at this point, but remember the record has not at this point been written to the recordset
User A is interupted by a telephone call, during this interuption..
User B inputs the "unique number" (say 10), and tabs off the control, it will again be regarded as OK, since at this point User A's record has not been comitted
User B saves their record
User A finishes telephone call and commits their record bang we have a duplicate record
It will be necessary to have error trapping code in the onerrro event of the form to trap this error and request the user to eneter a new number
D
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
It will not work reliably in a multi-user environment, my comments had nothing to do with autonumbers
Consider this:
User A input their "unique Number" (say 10), and tabs off the control, the after update event fires and the number is checked for uniqueness, all OK at this point, but remember the record has not at this point been written to the recordset
User A is interupted by a telephone call, during this interuption..
User B inputs the "unique number" (say 10), and tabs off the control, it will again be regarded as OK, since at this point User A's record has not been comitted
User B saves their record
User A finishes telephone call and commits their record bang we have a duplicate record
It will be necessary to have error trapping code in the onerrro event of the form to trap this error and request the user to eneter a new number
D
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Status
Similar threads
- Locked
- Question
