Look for Duplicate Values...

[Guest_imported]

Hi:

I have a form that automates a series of events (i.e. there is an ID number (primary key) on form 1 that is carried over and inserted on form 2 (in the load event) when the button is clicked. The button also looks for required fields & prompts the user if they are blank, opens form 2 and closes form 1). However, my latest problem lies in the fact that when form 1 is closed and the primary key contains a duplicate value, the record is not saved. That being the nature of primary keys it's a good thing, but the user is not prompted (before the form is closed and record lost) if they have duplicated the key. Basically, I don't know the function that would go back and check for duplicate values.....can you please help??? I also need the user to be prompted when duplication occurs. I hope that makes sense!! I am severely strapped for time, so any help you could give me would be GREATLY appreciated!!

Here is the code from form 2 (if you could please tell me where to put your suggestions in the existing code, that would also be fantastic....it is usually a lot of trial and error before I get things correct.) Thanks!


Option Compare Database
Option Explicit
Private Sub Form_Load()
Me!txtRPSID = Me.OpenArgs


End Sub


Private Sub OpenSectionIII_Click()

' Check to make sure RPS Certification Number has been entered

If IsNull(Me![RPS ID]) Then
If MsgBox("'RPS Identification Number' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "Error! A required field has not been entered!&quot = vbCancel Then
DoCmd.Close

Else
Me![txtRPSID].SetFocus
End If


Else

' Open the next form prior to closing this form so as to pass variable
DoCmd.OpenForm "Section III: Commercial Operation Date", , , , acFormAdd, , Me![txtRPSID].Value
DoCmd.Close acForm, "Section II: Fuels, Energy Resources and Technologies", acSaveYes

End If

End Sub

 

[netconfused]

I would add code to the before update event for the field that they are entering the primary key value. Much nicer to the user to do it while they are on the field that needs changed.

There you should be able to use the DoCmd.FindRecord to see if the record already exists for that value and if so then abort the event and give an error message. OR you cuold do it as a macro instead of VBA code.
Glenda