Preventing Duplicate Records

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

Re: Preventing Duplicate Records

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

rjshrader@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.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Preventing Duplicate Records

wrote in message
news:d1f12b65-6304-4c4b-a93d-c5358997a80d@c4g2000hsg.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.

Re: Preventing Duplicate Records

On Jan 16, 9:55=A0pm, "ruralguy via AccessMonster.com"
wrote:
> Have you tried using DCount() of DLookup() before saving?http://www.mvps.o=
rg/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.comhttp://www.accessmonster.com/Uwe/Forum=
s.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.

Re: Preventing Duplicate Records

On Jan 17, 8:05=A0am, "paii, Ron" wrote:
> wrote in message
>
> news:d1f12b65-6304-4c4b-a93d-c5358997a80d@c4g2000hsg.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...regardless of CustomerID.

Re: Preventing Duplicate Records

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

rjshrader@gmail.com wrote:
>On Jan 16, 9:55 pm, "ruralguy via AccessMonster.com"
>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

Re: Preventing Duplicate Records

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

rjshrader@gmail.com wrote:
>On Jan 16, 9:55 pm, "ruralguy via AccessMonster.com"
>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.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Preventing Duplicate Records

On Jan 17, 9:38=A0am, "ruralguy via AccessMonster.com"
wrote:
> Please post your DCount() code so we can see it.
>
> rjshra...@gmail.com wrote:
> >On Jan 16, 9:55=A0pm, "ruralguy via AccessMonster.com"
> >wrote:
> >> Have you tried using DCount() of DLookup() before saving?http://www.mvp=
s.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.comhttp://www.accessmonster.com/Uwe/Forum=
s.aspx/databases-ms-access/2008...

ScanCount =3D DCount("StatusDate", "tblStatus", "CustomerID =3D '" &
Me.txtCustomerID.Value & "'")
Msgbox (ScanCount)

I then have this code that runs before saving the record to the table

If ScanCount > 0 then
msg =3D "Customer ID can only be scanned once per day; this ID number
has already been scanned today."
Style =3D vbInformation
Title =3D "Failed Scan Attempt"
Response =3D MsgBox(msg, Style, Title)
Me.txtCustomerID.SetFocus
Exit Sub
End If


What I find is that I will scan the customers id number yesterday and
then when I go to scan it again today I get the error message that I
have generated. After thinking about it it makes sense that I have
scaned it and if it is > than 0 (which it is) return th error message.
I'm just not getting how to check for a certain date I guess as
apposed to a count of the total number of dates.

Re: Preventing Duplicate Records

Try:
ScanCount = DCount("StatusDate", "tblStatus", "CustomerID = '" &
Me.txtCustomerID.Value & "' AND [YourDateField] =#" & Date() & "#")
...using YourDateField name of course.

rjshrader@gmail.com wrote:
>On Jan 17, 9:38 am, "ruralguy via AccessMonster.com"
>wrote:
>> Please post your DCount() code so we can see it.
>>
>[quoted text clipped - 18 lines]
>>
>> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.asp x/databases-ms-access/2008...
>
>ScanCount = DCount("StatusDate", "tblStatus", "CustomerID = '" &
>Me.txtCustomerID.Value & "'")
>Msgbox (ScanCount)
>
>I then have this code that runs before saving the record to the table
>
>If ScanCount > 0 then
>msg = "Customer ID can only be scanned once per day; this ID number
>has already been scanned today."
>Style = vbInformation
>Title = "Failed Scan Attempt"
>Response = MsgBox(msg, Style, Title)
>Me.txtCustomerID.SetFocus
>Exit Sub
>End If
>
>What I find is that I will scan the customers id number yesterday and
>then when I go to scan it again today I get the error message that I
>have generated. After thinking about it it makes sense that I have
>scaned it and if it is > than 0 (which it is) return th error message.
>I'm just not getting how to check for a certain date I guess as
>apposed to a count of the total number of dates.

--
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.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Preventing Duplicate Records

On Jan 17, 10:10=A0am, "ruralguy via AccessMonster.com"
wrote:
> Try:
> ScanCount =3D DCount("StatusDate", "tblStatus", "CustomerID  = '" &
> Me.txtCustomerID.Value & "' =A0AND [YourDateField] =3D#" & Date() & "#")
> ..using YourDateField name of course.
>
>
>
>
>
> rjshra...@gmail.com wrote:
> >On Jan 17, 9:38=A0am, "ruralguy via AccessMonster.com"
> >wrote:
> >> Please post your DCount() code so we can see it.
>
> >[quoted text clipped - 18 lines]
>
> >> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Fo=
rums.aspx/databases-ms-access/2008...
>
> >ScanCount =3D DCount("StatusDate", "tblStatus", "CustomerID  = '" &
> >Me.txtCustomerID.Value & "'")
> >Msgbox (ScanCount)
>
> >I then have this code that runs before saving the record to the table
>
> >If ScanCount > 0 then
> >msg =3D "Customer ID can only be scanned once per day; this ID number
> >has already been scanned today."
> >Style =3D vbInformation
> >Title =3D "Failed Scan Attempt"
> >Response =3D MsgBox(msg, Style, Title)
> >Me.txtCustomerID.SetFocus
> >Exit Sub
> >End If
>
> >What I find is that I will scan the customers id number yesterday and
> >then when I go to scan it again today I get the error message that I
> >have generated. After thinking about it it makes sense that I have
> >scaned it and if it is > than 0 (which it is) return th error message.
> >I'm just not getting how to check for a certain date I guess as
> >apposed to a count of the total number of dates.
>
> --
> 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.comhttp://www.accessmonster.com/Uwe/Forum=
s.aspx/databases-ms-access/2008...- Hide quoted text -
>
> - 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] =3D#" & Date() & "#")

Re: Preventing Duplicate Records

Excellent! Have fun with the rest of the project.

rjshrader@gmail.com wrote:
>On Jan 17, 10:10 am, "ruralguy via AccessMonster.com"
>wrote:
>> Try:
>> ScanCount = DCount("StatusDate", "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.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Preventing Duplicate Records

wrote in message
news:e3e8b131-9940-49a0-a649-a491dfa36110@s13g2000prd.google groups.com...
On Jan 17, 8:05 am, "paii, Ron" wrote:
> wrote in message
>
> news:d1f12b65-6304-4c4b-a93d-c5358997a80d@c4g2000hsg.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...regardless of CustomerID.

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