Dont allow a duplicate using VBA

[gheyman]

I have code that adds a new record to a table.

I need to run an IIf statement and only do this if a record doesn't already exist with the same field value "PID_Number"

Set rec = CurrentDb().OpenRecordset("tblRFPManager")
    
    rec.AddNew
    rec("PID_Number") = Me.PID_Number
    rec("Last_UserChangeDate") = Now
    rec("Last_UserId") = GetUserLogin()
    rec("PID_Creator") = GetUserID()
    rec("PID_Owner") = rec("PID_Creator")
    rec.Update
    rec.Close

HELP PLEASE!!!!!

 

[xenou]

First run a select query to see if a record exists for that PID_Number. If yes (doesn't already exist - select returns a record), don't add, if no (exists - select does not return any records) then add.

A more robust solution might also place a unique index on the table so that even if you try to add a duplicate the insertion will fail. You can use this approach without even checking whether or not the PID exists, as long as you are able to handle the error if it does.

 

[gheyman]

Thanks, ill have to do some research on running the select query. Not sure how to do all that, yet. I was hoping there would be some type of IIF statement to check first.

 

[gheyman]

what about a Dcount or Dlookup? Would that work in a nested IIF?

 

[stumac]

Just change your recordset to only include records where the PID matches your form, then check if it has any records. Doing it this way means you can also include an else within your IF to update the current record if required:

Set Rec = CurrentDb().OpenRecordset("select * from tblRFPManager where PID_Number = " & Me.pid_Number)
    
    If Rec.EOF Then
        Rec.AddNew
        Rec("PID_Number") = Me.pid_Number
        Rec("Last_UserChangeDate") = Now
        Rec("Last_UserId") = GetUserLogin()
        Rec("PID_Creator") = GetUserID()
        Rec("PID_Owner") = Rec("PID_Creator")
        Rec.Update
        Rec.Close
    End If

 

[gheyman]

Thanks, Stumac.

I'm getting an error: Too few parameters. Expected 3.

 

[xenou]

Test this line first:

Set Rec = CurrentDb().OpenRecordset("select * from tblRFPManager where PID_Number = " & Me.pid_Number)

also test it with a known and expected result using a hard value:

Set Rec = CurrentDb().OpenRecordset("select * from tblRFPManager where PID_Number = [B][COLOR="#FF0000"]1[/COLOR][/B]")

Then once that's out of the way, work on the update part. One thing at a time.

 

[stumac]

Ok so a couple of things to check:

The GetUserID and GetUserLogin actually exists as functions - try typing the following at the beginning of your code.

Msgbox GetUserID()

Msgbox GetUserLogin()

Do you get 2 message boxes displaying what you expect these values to be?

Your form (where this code should be stored) has a field called Pid_Number.

 

[gheyman]

Thanks - will test these. Don't think its the Update part because that worked before. The problem is the program allowed the user to click the button more than once creating duplicates.

 

[gheyman]

Will check now