Duplicate Entry

[injanib via AccessMonster.com]

Hi all,

I can't figure out what the problem is with the following procedure. It is
supposed to look a table and determine if an entry already exist for a value
entered in a combo box and ask to add if a record doesn't already exist. The
problem is that no matter what it will add a new entry in the table. Even if
I select an entry from the combo box, it adds a duplicate record. It does
however promps if the entry is a new value and doesn't already exist in the
table. Any help is appriciated.

here is the code.

Private Sub cboCustomer_AfterUpdate()
Dim rst As Recordset

If DCount("*", "CustomerTable", "Customer = '" & Forms!Contract.cboCustomer
& "'") < 1 Then
If MsgBox("Do you want the database to save this new customer?", vbYesNo,
"Add New Customer?") = vbYes Then
Me.cboCustomer = StrConv(Me.cboCustomer, 3)

Set rst = CurrentDb.OpenRecordset("CustomerTable")
With rst
.AddNew
!Customer = Me![cboCustomer]

.Update
.Close
End With
Set rst = Nothing
Me.cboCustomer.Requery

Else
Me.txtCompany.SetFocus


End If
Else
Exit Sub

Me.txtCompany = Me.cboCustomer.Column(2)
Me.txtCell = Me.cboCustomer.Column(3)
Me.txtEmail = Me.cboCustomer.Column(4)
Me.txtAddress = Me.cboCustomer.Column(5)
Me.txtCity = Me.cboCustomer.Column(6)
Me.txtCountry = Me.cboCustomer.Column(7)

End If

End Sub

 

[Daryl S]

Try adding the square brackets around your customer fieldname in your If
statement:

If DCount("*", "CustomerTable", "[Customer] = '" & Forms!Contract.cboCustomer
& "'") < 1 Then

 

[injanib via AccessMonster.com]

Thanks for your response, but that didn't do it.

I am not sure if it matters, but the record source of the form is a query
that is based on the CustomerTable and another table.

Try adding the square brackets around your customer fieldname in your If
statement:

If DCount("*", "CustomerTable", "[Customer] = '" & Forms!Contract.cboCustomer
& "'") < 1 Then

Hi all,

[quoted text clipped - 45 lines]

End Sub

 

[Steve Schapel]

Injanib,

First of all, I should point out that you have posted this question in a
Macros newsgroup, but it is not related to macros. This is a VBA procedure.

Anyway, that aside, I can't quite make sense of it. Maybe I am
misunderstanding, but it looks like the combobox's Row Source is the same
table that you are trying to see if there is already a matching record. So
if there is no matching record, it won't be in the combobox. Right? So is
this a snake swallowing its own tail?

 

[injanib via AccessMonster.com]

Thanks Steve,

First of all, I am sorry for posting this on the wrong forum. I must not have
paid full attention.
Second, the Row Source of the combo box is the same table where it looks for
an existing record.

One thing I have noticed is that if I enter a new value in the combo box
field, meaning that entering something that doesn't appear in the dropdown
list, It will prompt me if I want to add this new value in the table as it
should. If I select yes, it will add it there twice.

If I select a value from the drop-down list, it won't prompt for anything,
however it will still add another entry in the table for that value. So the
nex time I expand the drop-down list, there will be an extra entry for the
last value I selected.

The Record Source of the form housing the Combo Box is a Query that is based

Injanib,

First of all, I should point out that you have posted this question in a
Macros newsgroup, but it is not related to macros. This is a VBA procedure.

Anyway, that aside, I can't quite make sense of it. Maybe I am
misunderstanding, but it looks like the combobox's Row Source is the same
table that you are trying to see if there is already a matching record. So
if there is no matching record, it won't be in the combobox. Right? So is
this a snake swallowing its own tail?

Hi all,

[quoted text clipped - 51 lines]

End Sub

 

[Steve Schapel]

Injanib,

.... One of the two tables is the table that is the Row
Source of the Combo Box.

I feel this is the key to the problem. Somehow, entering the Customer into
the combobox is adding a new record with that customer name to the table,
because the form's Record Source is based on that table. And then, your
code is adding that same person again. Of course, at the time that the code
runs, the customer entered on the form has not been added to the table,
because the record has not yet been saved... this happens afterwards. So at
that time, the code detects no such customer in the table, and adds them,
and then when the new record on the form is saved, they are added again.

So, it looks like you need to re-think the process and the structure of what
you are trying to achieve here.

 

[Daryl S]

You have your code in the AfterUpdate event. If you put it in the
BeforeUpdate event, then you can Cancel the update if you don't want the
record added. If the combo box adds a record that the form is also adding,
then you will get duplicate records, though adding an appropriate unique
index on the table can prevent this.