Duplicate Check Validation for a field - Not too difficult 3

[Tamrak]

Hello,

I tried to find the topic that was previously posted. I could not find one.

I have an easy question that might save you a lot of time. I created a Form, using a Form Wizard. There is a field called "Ticket" which represents ticket number. This field is in the Text format, 6 characters. Database name is "Observation."

I set the property value for this field to be unique, index and no duplication allowed. I set this field as a primary field.

What I would like to do is:

1. When the user enters 6 digit alpha-numeric, if the duplication is found, I would like to have a pop-up message right away such as " This Ticket Number has already existed in the database. Please re-enter another number."

What is happening now is-> the user has to enter to the last field, (we have 42 fields), before this duplication error appears. That wastes a lot of time for us because we have to redo or key in the same information.


I would like to do it in the property set. I know that the Visual basic will resolve this problem with function. I am not familiar with function, but I can copy the code into the module.

Please instruct of what I should do to set up the property for this field. I know there is "before update" and "after update."

Thank you.

 

[LarryDeLaruelle]

Tamrak:

I would suggest using the DLookup Function. Help will give you the details.

Trigger this from the On_Exit event of the PK field.

If the number already exists, you can provide a user prompt (message box), reset the field to null and set focus to the field for a new entry.

Hope this helps.

Larry De Laruelle
larry1de@yahoo.com

 

[Rohdem]

HI

On the after update event of the ticket field, save the record. This will cause Access to error immediately if the number already exists.

Private Sub Ticket_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
End Sub

You can then trap this error to display a custom error message using the on error event of the form

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If (DataErr = 3022) Then
MsgBox " This Ticket Number has already existed in the database. Please re-enter another number."
Response = acDataErrContinue
End If
End Sub Mike Rohde
rohdem@marshallengines.com

 

[MichaelRed]

Larry,

Just curious, but why OnExit as opposed to BeforeUpdate? I have used the latter A LOT for validation, but not hte former, so I am interested in your choice. Also, I really can't use help to "HELP" me understand, as I recently "X"-graded to 2K and even (especially) the help is helpless!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Hi Tamrak,

This would be placed in the "After update" event of your Ticket field:
(In design view, double-click on the ticket field to pop up its properties dialog. Find the events tab, then find "after update". Select "event procedure" and then click "..." just beside. It will open Visual Basic and automatically label a procedure for you). Between the title and the "end sub" paste this:

Dim SQL1 As String, Rs As Recordset, Db As Database, Ctl As Control
Set Db = CurrentDb()
If IsNull(Me.YourFieldName) Or Me.YourFieldName = "" Then
MsgBox CurrentUser() & ", please add a ticket number.", vbInformation, "Ticket number required..."
Set Ctl = Me.YourFieldName
Response = acDataErrContinue
Ctl.Undo
Ctl.SetFocus
Else
SQL1 = "SELECT YourTableName.* FROM YourTableName" 'check for dup names
Set Rs = Db.OpenRecordset(SQL1, dbOpenDynaset)
If Rs.RecordCount = 0 Then
Rs.Close
GoTo NewTicket
End If
Rs.MoveFirst
Do Until Rs.EOF
If Rs!YourFieldNameInTable = Me.YourFieldName Then
Rs.Close
MsgBox "There is already a ticket with number ''" & Me.YourFieldName & "''. Please chose an alternate.", vbInformation, "Ticket Number Exists!"
Me.YourFieldName = Empty
Exit Sub
End If
Rs.MoveNext
Loop
Rs.Close

NewTicket:
SQL1 = "SELECT YourTableName.* FROM YourTableName"
Set Rs = Db.OpenRecordset(SQL1, dbOpenDynaset, dbAppendOnly)
Rs.addnew
Rs!YourFieldNameInTable = Me.YourFieldName
Rs.Update
Rs.Close
End If

While you are still in "VB" do a find/replace for these within the code:

YourFieldName: change it to your field name.. (ticket?)
YourFieldNameInTable: change to "ticket" too?
YourTableName: change to the name of your table where the field lives. Not the database name.

Small warning: if your field or table names are like:
My Field Name , you must write them in as My_Field_Name .

This is made for use in Access 97. If you use 2000 let me know 'cause there's one other step to do. Enjoy! Gord
ghubbell@total.net

 

[Tamrak]

Hello Gord,

I am using Access 2000 to perform the calculation.

The name of the table is: Observation. The name of the database is also Observation.

I tried to use the code from the earlier post, Rohdem. It sent me to the debug or end option without the pop-up message.

I feel that I need one more step because it is Access 2000.

Thank you for everyone who posted in this issue.

 

[Rohdem]

You may have to go into the references (under the tools menu when you are at a code window) and enable the DAO Object library and move it up the priority list. I haven't used 2000 alot, but I know the whole ADO versus DAO thing can be a sticking point. Mike Rohde
rohdem@marshallengines.com

 

[ghubbell]

Ya I'll say thanks too. When I started writing and there was nothing in this thread. A copy paste later and whammo! Talk about service from the Tek-Tips team!

The 2000 step is this: In any Visual basic module (the one you planted the code in will do), on the Menu bar find Tools-References. A dialog will pop up and if you don't have Microsoft DAO 3.6 Object Library checked you will have to do so. Close the dialog and try to compile. It won't? Tools-References again. Click on 3.6 again and move its "priority" up a notch or two. Try again. If it compiles your home free. Let us all know how you do! Gord
ghubbell@total.net

 

[Tamrak]

Hello Gord and Rohdem,

Thank you for all of your suggestions. Everything works fine.

I will be able to use both of your inputs into two different databases.

Thanks again.

Tamrak