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!!!!!

 

[gheyman]

This is the part of the code that's highlighted when it errors.

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

I assume the message is referring to this as too few parameters - no?

 

[stumac]

I was able to replicate the error where I had mis-spelled the field name in the SQL. Is PID_Number the name of your field in your table? Is it a standard number field? (it isn't a lookup field)?

 

[gheyman]

PID_Number is the name. It isn't an actual "number" and it isn't a lookup.

But with your help, I just solved the original problem of avoiding duplicates being entered by the user.

 If DCount("*", "TblRFPManager", "PID_Number" = [Text139]) = 0 Then
    
        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
    End If

I used the Dcount in the If statement.

 

[xenou]

This works fine for me:

Sub abcdefg()
    Dim rec As DAO.Recordset
    Set rec = CurrentDb().OpenRecordset("select * from TestTable where ID = 1")
    If Not rec.EOF Then
        Debug.Print rec.Fields(0).Value
    End If
End Sub

Nevermind....looks like you got it.
THis would have been helpful to know.

PID_Number is the name. It isn't an actual "number".

You should know that you write code for numbers and text differently when writing raw sql queries containing number or text conditions. And that is why I asked you to test your select query. Also a little unsure why you have changed from PID_Number to [Text139]!!

 

[gheyman]

Thanks, XENOU

Sorry, I didn't know the format of PID_Number would come into play. Good to know

I would have never gotten to a solution without all the help. Thanks

 

[xenou]

Okay but just to clarify, we are not talking about the format of the field - what matters is the datatype of the field. When you write raw sql text and date datatypes are treating differently:

select * from Table where X = 1
select * from Table where Y = 'a'
select * from Table where Z = #1/1/2017#