How to Perform a DLookUp for something Not in table

[nick404]

As title states..
I want to perform a DLookup for something NOT already in a table.
I want to add records via button but do not want to add it if a certain field is already included in that table.

First pass at possible code??

Set V = CurrentDb.OpenRecordset("tblCommodity") 
If DLookup("Commodity_PK", "tblCommodity", "Commodity_PK <>") Then
V.AddNew
V![Commodity_PK] = Me.txtCommodity.Value
V.Update
Else
End If
Set V = Nothing

[knarfreppep]

Won't DLookup return Null if what you look for isn't there?

E.G.
If IsNull(DLookup("lngID","tblItems","lngID = " & lngIDIWantToAdd)) then
'add the ID
End If

[nick404]

If i do:

If IsNull(DLookup("Commodity_PK", "tblCommodity", "Commodity_PK =" & Forms!frmAddDefect!txtCommodity.Value)) Then

Then I get an error "The expression you entered as a query parameter produced this error:" and the it has written whatever I typed in as the thing i want to add

[knarfreppep]

Try -

If IsNull(DLookup("Commodity_PK", "tblCommodity", "Commodity_PK ='" & Forms!frmAddDefect!txtCommodity.Value & "'")) Then

[nick404]

Awesome Thanks!

[knarfreppep]

Pleasure

You need to know when you're dealing with Commodity_PK being a string field -

"Commodity_PK = '" & Forms!frmAddDefect!txtCommodity.Value & "'" or "Commodity_PK = """ & Forms!frmAddDefect!txtCommodity.Value & """"

as opposed to a number field -

"Commodity_PK = " & Forms!frmAddDefect!txtCommodity.Value

[nick404]

Can you explain that a little more? Getting quite confused with all the " and ' s

[June7]

Can use apostrophe or doubled quote marks to delimit text parameters. So this:

DLookup("Commodity_PK", "tblCommodity", "Commodity_PK ='" & Forms!frmAddDefect!txtCommodity.Value & "'")

or this

DLookup("Commodity_PK", "tblCommodity", "Commodity_PK =""" & Forms!frmAddDefect!txtCommodity.Value & """")

Doubling a special character so it will be treated as text is called 'escaping' a special character.

I prefer the apostrophe and never use doubled quote to delimit parameters.