Create new record with condition that it won't create duplicate (1 Viewer)

[phual]

I'm working on a database which uses a form to filter results from a table. The plan is to have three unallocated fields (filter fields) at the top of the form (in the header) and the corresponding fields from the table underneath. The user enters the details in the filter fields and the results are filtered to show full or partial matches - this works fine.

What I want is to be able to click an 'add' command button that will create a new record with the values from the filter fields (the idea being that it acts as both a search function and forces the user to check that they are not creating duplicates). However, I need to be able to prevent the add button from working if there is a duplicate (by checking the manually assigned customer ID, which is included as one of the filter fields).

The way I was thinking of doing this was to add a StopMacro action at the start of the macro that creates the new record, but place criteria on that step that check that a record with that Customer ID exists. I'll be damned if I can figure out how though!

Stuart

 

[RuralGuy]

Your "Add" button should do a DCount or DLookup on the whole table before actually adding the record.

 

[phual]

I don't suppose that you could elaborate on that please? I was playing around with DCount without success using the following code (with many variations):

DCount([customer_ID],"tblCustomer",[Customer_ID]=[Forms]![frmListCustomers]![Filter_Customer_ID])<>0

where Customer_ID is in the form 1234A

Stuart

 

[RuralGuy]

All three parameters of the Domain functions are strings. This is a good link for the syntax. Since you are executing from the form there is no reason to go through the Forms collection.
If DCount("customer_ID","tblCustomer",[Customer_ID]= '" & Me.Filter_Customer_ID & "'") = 0 Then
'...go ahead and create the record
Else
'...Message the user
End If

 

[phual]

Your help and a little more playing got the desired result. Remembering that this is a macro and not a module, I eventually put the following criteria in the macro:

DCount("Customer_ID","tblCustomer","Customer_ID='" & [Filter_Customer_ID] & "'")=0

Thanks

Stuart

 

[RuralGuy]

Excellent! Glad to hear you got it sorted.