Prompt to Ask If User Wants to Create a Duplicate Record

D

DoveArrow

For example, let's say there's already a record for Mary Shelly, and I
have a new Mary Shelly that I'm trying to add to my database. Can I
make it so that Access will prompt me and say "There is already a
record matching this description. Do you wish to continue?"
 
R

Richnep

Hi Dove,

I'm assuming first and last name are in the same field. Create a
button or Label and add this code to the On Click event but make the
following changes to the code:

NAMEFIELD= The name of the field where the name is kept on the form
TABLEWITHUSERNAMES = The name of the table where the username is kept

Code:

Dim db As Database
Dim Rst As Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("TABLEWITHUSERNAMES", dbOpenDynaset,
dbSeeChanges)

Rst.MoveFirst
Do Until Rst.EOF

If Rst!NAMEFIELD = Forms![FORMNAME]![NAMEFIELD] Then GoTo line200

Rst.MoveNext
Loop

200
Msg = "That name already exists in the database. Do you want to
continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "DUPLICATE NAME" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then goto 300
If Response = vbNo then Me.Undo
Me.refresh

300
End Sub


Try that. I'm kinda newbie at VBA too and I used this code to check
for duplicates aginst the source table.
 
R

Richnep

Dove, -I FORGOT A GOTO STATMENT the code below is correct

I'm assuming first and last name are in the same field. Create a
button or Label and add this code to the On Click event but make the
following changes to the code:


NAMEFIELD= The name of the field where the name is kept on the form
TABLEWITHUSERNAMES = The name of the table where the username is kept



Dim db As Database
Dim Rst As Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("TABLEWITHUSERNAMES", dbOpenDynaset,
dbSeeChanges)

Rst.MoveFirst
Do Until Rst.EOF

If Rst!NAMEFIELD = Forms![FORMNAME]![NAMEFIELD] Then GoTo line200

Rst.MoveNext
Loop
goto 300


200
Msg = "That name already exists in the database. Do you want to
continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "DUPLICATE NAME" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then goto 300
If Response = vbNo then Me.Undo
Me.refresh

300
End Sub
 
D

DoveArrow

Dove, -I FORGOT A GOTO STATMENT the code below is correct

I'm assuming first and last name are in the same field. Create a
button or Label and add this code to the On Click event but make the
following changes to the code:

NAMEFIELD= The name of the field where the name is kept on the form
TABLEWITHUSERNAMES = The name of the table where the username is kept

Dim db As Database
Dim Rst As Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("TABLEWITHUSERNAMES", dbOpenDynaset,
dbSeeChanges)

Rst.MoveFirst
Do Until Rst.EOF

If Rst!NAMEFIELD = Forms![FORMNAME]![NAMEFIELD] Then GoTo line200

Rst.MoveNext
Loop
goto 300

200
Msg = "That name already exists in the database. Do you want to
continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "DUPLICATE NAME" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then goto 300
If Response = vbNo then Me.Undo
Me.refresh

300
End Sub
Click to expand...

That is amazing! Thank you.
 
Top