How to check if data already exists in a table? 1

[kahless]

I am trying to construct an if statement which will perform a function on a string if that string does not does not already exist in a table, so that duplicates can are avoided. I am using the code:

If IsNull(DLookup("[ID]", "testtable", [ID] = IDtest))

- [ID] is the field to check
- 'testtable' is the table
- IDtest is the string which should not match [ID]

The idea is that Dlookup checks to see if there is already an instance of IDtest in the table, but the code is not working. Can anyone find fault in the code or have any other sugestions?

Cheers

 

[jrbarnett]

Kahless

String variables have to be delimited with apostrophes or double quote marks, so you need to change your code to:

If IsNull(DLookup("[ID]", "testtable", "[ID]='" & IDtest & "'")) Then
 ' record doesn't exist, so add it
Else
 ' Your record exists
End If

John

 

[kahless]

Thanks for the quick reply. Works perfectly!

Ta

 

[fadMiS]

Hi John,

I have more complicated problem. Let assume that we have same table "testtable" but with ID and PayDate fields. Before i store data in table I need to check both fields at the same time, whether in table is already stored data for any ID and PayDate. It can be solved having ID and PayDate as primary keys (system gives duplicate error) but I would like to have it done programatically with dlookup or any other function cretaed in VBA.

fad

 

[jrbarnett]

fadMIS

This is a separate problem, please start a new thread within this forum.

Thank you.

John

 

[fadMiS]

John

I started new thread called Check table before data entry and I would appreciate any comment from you.

fad

 

[gazolba]

Just bear in mind that DLOOKUP is really slow. Its faster to run an SQL.

 

[fadMiS]

gazolba,

I would appreciate if you write function with SQL statements that does it.

fad