Goto Record when Duplicate Record Exists

**rlsublime** · 03-22-2012, 01:45 PM

Currently have a database with 3 fields set as primary key so that duplicates will not be saved. I also inserted an On error message below to display an error message. The final step would be to take the user to the already existing record. Any ideas on how to accomplish this? Is there some code that I could add to the on error message to take the use to the already existing record. The three fields are Loan_Number, Investor_Number and Request_Type. Thanks for the help.

Err_Save_Record_Click:
Select Case Err
Case 3022
MsgBox "A record with this Loan Number already exists.", vbOKOnly, "Record Exists"
    
Case Else


Exit Sub
End Select

**Dennis7849** · 03-22-2012, 01:50 PM

The only thing needed is the sheet name and the cell address.

Sheets("sheetname").Select
Range("celladdress").Select

**rlsublime** · 03-22-2012, 01:56 PM

I forgot to mention that this is an access form refrencing a database. How would I be able to go to the record in this case? Thanks

**royUK** · 03-29-2012, 08:22 AM

Originally Posted by Dennis7849

The only thing needed is the sheet name and the cell address.

Sheets("sheetname").Select
Range("celladdress").Select

Access? That looks like Excel VBA to me, in which case I would use GoTo if I really needed to select a sheet/range

**split_atom18** · 03-27-2012, 05:24 PM

rlsublime,
Since I don't have long, gonna give you a short answer. This can be accomplished. The way that I personally would accomplish this is to avoid the error completely. I would have the 3 primary key fields on the form unbound, parse the data entered to see if it already exists. If it does load the rest of the data, if it doesn't then create a new record and enable the rest of the fields on the form.

If you need some more assistance on this let me know.

Hope this helps,
Dan

**arlu1201** · 03-29-2012, 06:48 AM

Dennis,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here