Preventing Duplicate Records

[rjshrader]

I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader

[ruralguy via AccessMonster.com]

Have you tried using DCount() of DLookup() before saving?
http://www.mvps.org/access/general/gen0018.htm

rj*******@gmail .com wrote:

>I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200801/1

[paii, Ron]

<rj*******@gmai l.comwrote in message
news:d1******** *************** ***********@c4g 2000hsg.googleg roups.com...

I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader

Put a unique index on the CustomerID and StatusType fields in the table.

[rjshrader]

On Jan 16, 9:55*pm, "ruralguy via AccessMonster.c om" <u12102@uwe>
wrote:

Have you tried using DCount() of DLookup() before saving?http://www.mvps.org/access/general/gen0018.htm

rjshra...@gmail .com wrote:

I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2008...- Hide quoted text -

- Show quoted text -

I have played with both and understand how to use DCount to retrieve
the number of occurrences of a record and with DLookup to retrieve a
value. I added a MSGBOX (variable) to display the results so I could
see what was being retrieved or counted....but I do not understand how
to use them to check for the duplicate.

[rjshrader]

On Jan 17, 8:05*am, "paii, Ron" <[e-mail] te:

<rjshra...@gmai l.comwrote in message

news:d1******** *************** ***********@c4g 2000hsg.googleg roups.com...

I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader

Put a unique index on the CustomerID and StatusType fields in the table.- Hide quoted text -

- Show quoted text -

Unique indexes will not work because I may have the same CustomerID
being used on different days. I just want to prohibit duplicate
entries on the date. If I put indexed on the date, then I would only
be able to enter one entry per day...regardles s of CustomerID.

[ruralguy via AccessMonster.com]

In your SaveCommand button, check for duplicates before saving and don't save
if you find one.

rj*******@gmail .com wrote:

>On Jan 16, 9:55Â*pm, "ruralguy via AccessMonster.c om" <u12102@uwe>
wrote:

>Have you tried using DCount() of DLookup() before saving?http://www.mvps.org/access/general/gen0018.htm

[quoted text clipped - 23 lines]

>>
- Show quoted text -

I have played with both and understand how to use DCount to retrieve
the number of occurrences of a record and with DLookup to retrieve a
value. I added a MSGBOX (variable) to display the results so I could
see what was being retrieved or counted....but I do not understand how
to use them to check for the duplicate.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

[ruralguy via AccessMonster.com]

Please post your DCount() code so we can see it.

rj*******@gmail .com wrote:

>On Jan 16, 9:55Â*pm, "ruralguy via AccessMonster.c om" <u12102@uwe>
wrote:

>Have you tried using DCount() of DLookup() before saving?http://www.mvps.org/access/general/gen0018.htm

[quoted text clipped - 23 lines]

>>
- Show quoted text -

I have played with both and understand how to use DCount to retrieve
the number of occurrences of a record and with DLookup to retrieve a
value. I added a MSGBOX (variable) to display the results so I could
see what was being retrieved or counted....but I do not understand how
to use them to check for the duplicate.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200801/1

[ruralguy via AccessMonster.com]

Excellent! Have fun with the rest of the project.

rj*******@gmail .com wrote:

>On Jan 17, 10:10Â*am, "ruralguy via AccessMonster.c om" <u12102@uwe>
wrote:

>Try:
ScanCount = DCount("StatusD ate", "tblStatus" , "CustomerID Â*= '" &

[quoted text clipped - 40 lines]

>>
- Show quoted text -

That seems to work. Looks like the inclusion of the following code at
the end resolve the problem. I still have so much to learn...I was not
aware that I could have AND other criteria.

Many thanks to you and to all for their suggestions.

AND [YourDateField] =#" & Date() & "#")

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200801/1

[paii, Ron]

<rj*******@gmai l.comwrote in message
news:e3******** *************** ***********@s13 g2000prd.google groups.com...
On Jan 17, 8:05 am, "paii, Ron" <[e-mail] te:

<rjshra...@gmai l.comwrote in message

news:d1******** *************** ***********@c4g 2000hsg.googleg roups.com...

I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is clicked.

CustomerID, StatusType are values that are manually entered by the
user; StatusDate is automatically filled with the current date that
the record is saved.

I would like to use code behind the cmdSave button to check the table
for duplicate dates....I only want one record entered per day per
customer.

Hope I have explained my situation...I appreciate any help or
resources you can point me to.

Many thanks in advance - RJ Shrader

Put a unique index on the CustomerID and StatusType fields in the table.-

Hide quoted text -

>
- Show quoted text -

Unique indexes will not work because I may have the same CustomerID
being used on different days. I just want to prohibit duplicate
entries on the date. If I put indexed on the date, then I would only
be able to enter one entry per day...regardles s of CustomerID.

You can create a combined index based on both fields and have 1 record per
day for each CustomerID

[Donald Calloway]

ruralguy via AccessMonster.c om wrote:

Excellent! Have fun with the rest of the project.

rj*******@gmail .com wrote:

>On Jan 17, 10:10 am, "ruralguy via AccessMonster.c om" <u12102@uwe>
wrote:

>>Try:
ScanCount = DCount("StatusD ate", "tblStatus" , "CustomerID = '" &

[quoted text clipped - 40 lines]

>>- Show quoted text -

That seems to work. Looks like the inclusion of the following code at
the end resolve the problem. I still have so much to learn...I was not
aware that I could have AND other criteria.

Many thanks to you and to all for their suggestions.

AND [YourDateField] =#" & Date() & "#")

You could also use the following psudo code to search duplicates
returned for a dynaset based on appropriate sql code, which would yield
more than one record if a duplicate record is found:

me.RecordSource = "Appropriat e SQL Code here to return a dynaset"
'Determine if more than one record is returned
if me.RecordsetClo ne.RecordCount 1 then
'Do whatever if more than one record is returned
Else
'Do whatever if only one record (or no records) are returned.
End If