Verify that there are no duplicate records before saving new record.

[Tom K via AccessMonster.com]

Hi,

Im trying to verify that there are no duplicate records in my table before
saving a new record with my form to that table. I set up a parameter query to
use the info from my form to check if there was a matching record. The query
worked good. This is the SQL;

SELECT tblAddress.StreetNumber, tblAddress.Direction, tblAddress.StreetName
FROM tblAddress
WHERE (((tblAddress.StreetNumber)=[Forms]![frmNewAddress]![StreetNumber]) AND
((tblAddress.Direction)=[Forms]![frmNewAddress]![Direction]) AND ((tblAddress.
StreetName)=[Forms]![frmNewAddress]![StreetName]));

What I wanted to do is to have a command button on the form that would check
to see if there was a duplicate record. If there was it would display a
message box saying that this address already exist, and then it would not let
you save the record. If there were no matching records then it would save the
record, close the form, and move to the last record on the previous form.

I tried to include this into the comand button that closes the form. I tried
to use a macro to do this. I ran the query, and then I tried to use the
condition "Len ( [QDuplicateAddress]![StreetNumber] & [QDuplicateAddress]!
[Direction] & [QDuplicateAddress]![StreetName] ) <> 0". If the condition was
true then it would not save the record but would display a message, if false
then it would save the record and close the form.

When it got to the condition I got a error message "Can't find Automation
Object "QDuplicateAddress".

I'm completely lost, any help would be great.

Thanks,
Tom

 

[tina]

since you've written a query that works for you, use it in a DCount()
function to check for duplicates, as

If DCount(1, "QDuplicateAddress") > 0 Then
Msgbox "This address already exists."
Me.Undo
End If

DoCmd.Close
DoCmd.SelectObject acForm, "PreviousFormName"
DoCmd.RunCommand acCmdRecordsGoToLast

the above code assumes that you want the form to close, whether the record
is saved or not. notice that i did *not* include a specific "save record"
action. that's because the record will automatically be saved when the form
is closed, unless you specifically prevent it, or unless the form is
unbound.

hth

 

[Tom K via AccessMonster.com]

Hi Tina,

Thanks for the help. I just tried using "DCount("[StreetNumber] & [ Direction]
& [StreetName]","QDuplicateAddress")<>0" in a condition of a macro. The macro
displays a message if the condition is true. It will not save the record
unless the condition is false and there is no matching address record. The
"Len" statement I was trying to use was not working. You were right in trying
to use the "DCount".

This would work better in VBA, but my VBA skills are very poor. Im going to
try to learn VBA, if for any other reason I will not have to rely on macros
so much.

Thanks for the help

Tom

since you've written a query that works for you, use it in a DCount()
function to check for duplicates, as

If DCount(1, "QDuplicateAddress") > 0 Then
Msgbox "This address already exists."
Me.Undo
End If

DoCmd.Close
DoCmd.SelectObject acForm, "PreviousFormName"
DoCmd.RunCommand acCmdRecordsGoToLast

the above code assumes that you want the form to close, whether the record
is saved or not. notice that i did *not* include a specific "save record"
action. that's because the record will automatically be saved when the form
is closed, unless you specifically prevent it, or unless the form is
unbound.

hth

Hi,

[quoted text clipped - 29 lines]

Thanks,
Tom

 

[tina]

you're welcome

Hi Tina,

Thanks for the help. I just tried using "DCount("[StreetNumber] & [ Direction]
& [StreetName]","QDuplicateAddress")<>0" in a condition of a macro. The macro
displays a message if the condition is true. It will not save the record
unless the condition is false and there is no matching address record. The
"Len" statement I was trying to use was not working. You were right in trying
to use the "DCount".

This would work better in VBA, but my VBA skills are very poor. Im going to
try to learn VBA, if for any other reason I will not have to rely on macros
so much.

Thanks for the help

Tom

since you've written a query that works for you, use it in a DCount()
function to check for duplicates, as

If DCount(1, "QDuplicateAddress") > 0 Then
Msgbox "This address already exists."
Me.Undo
End If

DoCmd.Close
DoCmd.SelectObject acForm, "PreviousFormName"
DoCmd.RunCommand acCmdRecordsGoToLast

the above code assumes that you want the form to close, whether the record
is saved or not. notice that i did *not* include a specific "save record"
action. that's because the record will automatically be saved when the form
is closed, unless you specifically prevent it, or unless the form is
unbound.

hth

Hi,

[quoted text clipped - 29 lines]

Thanks,
Tom