How can I prevent to add double data

[fransjan]

Hi all.

I have a small problem:

I have a table with standard data (about 25 records).
When a button is pressed on a form a query is being run that adds the
records from this table to an other table as well as a date that is put
in a box on the same form.

This all works fine. If however this date (with its 25 records) is
already in the new table then it shouldn't be added.

I can't use the properties of the table itself because later on I want
to be able to add records that already exist.

I hope I am making sense so far still ;)

I think I should somehow check if the date that is filled in this box
is already present in the table. If not, then add the date, if so then
not.

Can anyone help me with this one?

With best regards,
Frans-Jan

[Squirrel]

Hi there,
Hope you can adapt this logic. This is a sub called from a command button
to do a compare
of the records loaded in the form against a date in a textbox on the form.
txtDate is the unbound
textbox on the form, SaveDt is the field in the table.

HTH -Linda

Private Sub cmdCheckDate_Click()
Dim rst As Recordset

Set rst = Me.RecordsetClone

rst.FindFirst "SaveDt = #" & Me!txtDate & "#"

If rst.NoMatch Then
MsgBox "OK to add data"
Else
MsgBox "This date is in the table."
End If
End Sub

<fr******@vanrooijen.nl> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Hi all.

I have a small problem:

I have a table with standard data (about 25 records).
When a button is pressed on a form a query is being run that adds the
records from this table to an other table as well as a date that is put
in a box on the same form.

This all works fine. If however this date (with its 25 records) is
already in the new table then it shouldn't be added.

I can't use the properties of the table itself because later on I want
to be able to add records that already exist.

I hope I am making sense so far still ;)

I think I should somehow check if the date that is filled in this box
is already present in the table. If not, then add the date, if so then
not.

Can anyone help me with this one?

With best regards,
Frans-Jan

[FJ]

Hi Linda,

Thanks for your help so far. I get the idea and agree with you that
this should work perfectly.

However... the table isn't loaded in any form. Any idea on what the
code should then look like?
Thanks already for the great help :)
Best regards,
Frans-Jan

[pietlinden]

don't enter data directly into the table. Do it through a form. Then
you can use the BeforeInsert event to do whatever complex checking you
want. Or just enforce uniqueness on multiple fields.

[Tim Marshall]

FJ wrote:

However... the table isn't loaded in any form. Any idea on what the
code should then look like?

If you absolutely insist on loading data directly into the table itself
(not adviseable, especially if others are going to add data and not just
you) alter the table properties (don't panic here - I remember you
saying that you wanted to add data from other tables later - read on).

Go to the date field in your table and changed the Indexed property for
the date to

Yes (No Duplicates)

Save the table and enter your data. Once you are happy with the data
you've added and are ready to bring in more data as you mentioned,
change the indexed property back to No.

As Pietlinden states though, you should designa form for entering data.
You can use Linda's code in the record before update event.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto