If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how do I alert user (ASAP) if record already exists?
I have a form with Security Data with SSN being the primary key (no
duplicates). This form is pretty busy with many fields, SSN being at the top. I would like to alert the user with a message if the Record for a certain SSN already exists before they populate the rest of the fields. Currently there is a message that record exists but it doesn't show until you try to save the record. I would like to alert the user as soon as SSN is entered. Any ideas would be appreciated. Thanks! ynj |
#2
|
|||
|
|||
how do I alert user (ASAP) if record already exists?
In the BeforeUpdate event of the SSN control use the DCount function to
determine if the record exists. Something like this: Private Sub txtSSN_BeforeUpdate(Cancel As Integer) If DCount("SSN","MyTable","[SSN] = '" & Me.txtSSN & "'")0 Then Msgbox "SSN already exists." Cancel=True Endif End Sub If it finds a record with that SSN, it will display a messagebox and not let the user exit the textbox until they correct it. HTH, Barry |
#3
|
|||
|
|||
how do I alert user (ASAP) if record already exists?
Very easy to do. Use the Before Update event of the text box where you enter
the SSN. Use the DLookup to see if the SSN exists and if it does, cancel the event and warn the user: If Not IsNull(DLookUp("[SSN]", "TableName Here", "[SSN] = '" & Me.txtSSN & "'")) Then MsgBox "SSN " & Me.txtSSN & " Is Already in the Table" Me.txtSSN = Null Cancel = True End If In the code above, [SSN] is the field in your table where you store the SSN, TableName Here should be the name of the table where the SSN is stored, and Me.txtSSN is the name of the text box where you enter the SSN. The DLookup function will return Null if the critera entered does not return a match in the table, so if Null is returned, the SSN is not in the table and you can proceed. If it does return a value, the message box is presented, the SSN text box is cleared, and the event is canceled so the cursor stays in the SSN field. Now, the question is, is this a Data Entry form? I mean, is it for adding new records only? If you want to find existing SSN records and make modifications, the code above would not be the way to do it. You would need to modify it to ask the user if they want to edit the record or try again. "ynjramey" wrote: I have a form with Security Data with SSN being the primary key (no duplicates). This form is pretty busy with many fields, SSN being at the top. I would like to alert the user with a message if the Record for a certain SSN already exists before they populate the rest of the fields. Currently there is a message that record exists but it doesn't show until you try to save the record. I would like to alert the user as soon as SSN is entered. Any ideas would be appreciated. Thanks! ynj |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple user entry in one record | Kyle | New Users | 3 | February 21st, 2006 05:09 PM |
Creating a New Record in Sub Form_Current | Peter Hallett | Using Forms | 6 | January 27th, 2006 03:03 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
Putting code in a subform? | Maury Markowitz | General Discussion | 14 | October 25th, 2004 11:32 AM |