Prevent saving dup entry of date/time

  • Thread starter Thread starter Jan Il
  • Start date Start date
J

Jan Il

Hi all! Windows XP, Access 2002 XP

I have an application that has a text box in which a special date format in
entered to provide a running balance figure. The date format is mm/dd/yyy
hh:nn" P", or like 03/18/2005 12:01 P. The running balance is based upon
the time of each entry being different by 1 minute or more, in order to
create a unique value when entries are made for the same date.

What I want to do is set up the index, or form control, if necessary, so
that it will not allow a duplicate date and time entry to be saved. I have
tried to set this in the table for that field to No duplicates, however, it
will not allow the same date to be saved, even if though the time is
different.

I'd appreciate any suggestions as to how I might need to set the index for
this field that would allow the same date to be saved if it also has a
different time. It seems that it will not recognize the time extension
difference at this point.

Jan :)
 
hi,
I hope you don't have the running balance in your table
but you could use DLookup to test for duplicates. If it
finds a duplicate date/time then you could have code to
display a message about duplicates then exit sub. this
will give you time to fix it (or...) before adding your
record to the table.
 
hi,
I hope you don't have the running balance in your table
but you could use DLookup to test for duplicates. If it
finds a duplicate date/time then you could have code to
display a message about duplicates then exit sub. this
will give you time to fix it (or...) before adding your
record to the table.
 
Hi :-)

hi,
I hope you don't have the running balance in your table
but you could use DLookup to test for duplicates. If it
finds a duplicate date/time then you could have code to
display a message about duplicates then exit sub. this
will give you time to fix it (or...) before adding your
record to the table.
Click to expand...

No, the balance is not stored. Yes, I would prefer to have some form of
notice of a duplicate before the user moves to the next field. I would
rather not have them go through the entire entry process and ready to close,
then find out there is a problem. It's like going to the supermarket on a
holiday ever after a long day, and after standing in a long line for over
half an hour, the person in front of you tells you the register is closing
after them. I have been the user of a program like that, and after several
times in a short period due handwriting hieroglyphics, the user is ready to
do some serious hair pulling, and not their own. :-)


Thank you very much for your time and information. I'll give it a go and
see if it will work.

Jan :)
 
Hi all! Windows XP, Access 2002 XP

I have an application that has a text box in which a special date format in
entered to provide a running balance figure. The date format is mm/dd/yyy
hh:nn" P", or like 03/18/2005 12:01 P. The running balance is based upon
the time of each entry being different by 1 minute or more, in order to
create a unique value when entries are made for the same date.

What I want to do is set up the index, or form control, if necessary, so
that it will not allow a duplicate date and time entry to be saved. I have
tried to set this in the table for that field to No duplicates, however, it
will not allow the same date to be saved, even if though the time is
different.
Click to expand...

Eh?

What's the DefaultValue property? A Date/Time value filled using Now()
will be accurate to the second, and unless you enter two records
within one second (as you might, with an Append query) they will be
unique. I suspect you may be filling the field using Date().

The format of the field is ABSOLUTELY IRRELEVANT to this issue. A
date/time field is stored as a double float number, a count of days
and fractions of a day (times) since an arbitrary start point. As
such, it's accurate to some tens of microseconds, and a unique index
should work fine.

How are you in fact filling the value into the field?

John W. Vinson[MVP]
 
Hi John :-)

John Vinson said:
Eh?

What's the DefaultValue property? A Date/Time value filled using Now()
will be accurate to the second, and unless you enter two records
within one second (as you might, with an Append query) they will be
unique. I suspect you may be filling the field using Date().
Click to expand...

Yes...I have tried to use the Now(), however, some entries will be retro
dates, and that format did not work. If all transactions were dated the
same as the entry date, there would be no problem with using Now() or even
Date(). However, that is not always the case, thus the date/time format
that has been implemented in order to have the running balance.
The format of the field is ABSOLUTELY IRRELEVANT to this issue. A
date/time field is stored as a double float number, a count of days
and fractions of a day (times) since an arbitrary start point. As
such, it's accurate to some tens of microseconds, and a unique index
should work fine.

How are you in fact filling the value into the field?
Click to expand...

The field date format is mm/dd/yyy hh:nn" P". Or, when the entry is made, as
of today, it would be 03/18/05 12:01 P, which would be recorded in the
table as 03.18.2005 12:01 P. If there were another entry for the same date,
it would then be entered as 03/18/05 12:02 P. This would them allow the
entry to be recorded and the current balance updated to new balance. Here
is the SQL so that you might be able to see how the date format works with
the process.

PARAMETERS [Forms]![frmCheckingRecFilter]![TxtDate1] DateTime,
[Forms]![frmCheckingRecFilter]![TxtDate2] DateTime;
SELECT T.BeginBal, T.CheckNo, T.TransactionDate, T.Transaction,
T.CheckDBTAmt, T.DepositAmt, T.TransactionType, T.Comment, (SELECT
SUM(Nz(DepositAmt, 0) - Nz(CheckDBTAmt, 0) + Nz(BeginBal,0))
FROM MyCheckRegister T1
WHERE T1.TransactionDate <= T.TransactionDate) AS RunningBalance
FROM MyCheckRegister AS T
WHERE (((T.TransactionDate) Between
[Forms]![frmCheckingRecFilter]![TxtDate1] And
[Forms]![frmCheckingRecFilter]![TxtDate2])) OR
((([Forms]![frmCheckingRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCheckingRecFilter]![TxtDate2]) Is Null))
ORDER BY T.TransactionDate DESC;

So far, this is the only date format method that has worked to provide the
running balance, given the fact that some transactions are dated prior to
the current date of entry, and will need to be recorded according to the
transaction date, not the current date.

Thank you very much for your time and assistance, I truly appreciate it. :-)

Jan :)
 
The field date format is mm/dd/yyy hh:nn" P". Or, when the entry is made, as
of today, it would be 03/18/05 12:01 P, which would be recorded in the
table as 03.18.2005 12:01 P. If there were another entry for the same date,
it would then be entered as 03/18/05 12:02 P. This would them allow the
entry to be recorded and the current balance updated to new balance. Here
is the SQL so that you might be able to see how the date format works with
the process.
Click to expand...

Ok... just so you manually enter a different (chronological)
transaction date and time for each transaction, I don't see what the
problem might be.

John W. Vinson[MVP]
 
John Vinson said:
Ok... just so you manually enter a different (chronological)
transaction date and time for each transaction, I don't see what the
problem might be.
Click to expand...

The problem is, even if the user now and then does not remember to enter a
minute time difference in the Transaction date field, the entry is saved but
it creates a duplicate date and time entry. This prevents the update of the
running balance to record the new balance. Thus, you will have and incorrect
balance amount.

Jan :)
 
The problem is, even if the user now and then does not remember to enter a
minute time difference in the Transaction date field, the entry is saved but
it creates a duplicate date and time entry. This prevents the update of the
running balance to record the new balance. Thus, you will have and incorrect
balance amount.
Click to expand...

Put a unique index on the field, to prevent entering duplicate times
in the first place.

Or, use a manually-maintained incrementing number field rather than
trying to maintain a timestamp (which isn't really a timestamp). You
can do this by setting the BeforeInsert event of the Form to use
DLookUp to find the largest existing sequence number and increment it.

John W. Vinson[MVP]
 
Hi John :-)

John Vinson said:
Put a unique index on the field, to prevent entering duplicate times
in the first place.

Or, use a manually-maintained incrementing number field rather than
trying to maintain a timestamp (which isn't really a timestamp). You
can do this by setting the BeforeInsert event of the Form to use
DLookUp to find the largest existing sequence number and increment it.
Click to expand...

All indexes I have tried so far have not worked. I'll try the DLookup and
see if it will work.

Thank you very much for your time and help. :-)

Jan :)
 
Back
Top