Changing Time Mask Problems

[JamesDC]

Hi,

So I'm working with an Access 2002 database for waste managemnt. The person in my role before my put together the program before he left and now I'm in charge of it. After updating a few areas based on the requests of the employees using the database I have come accross a problem with how dates are stored.

I have a form for the employees to use to input data, and this includes a box which is for the date. When initially tabbed over to or clicked on it brings up todays date. It is set to be in the form Short Date with the mask 99/99/0000;0;_.

I first ran into problems when I realized that if you just abbed over it, like the employees were trained to do, it would also include the time. Any entry entered with a time on the date would not get picked up by a query later on in the program.

I checked the code for this and found this:

Private Sub Date_Enter()

Date = DateAdd("h", -7, Now())

End Sub

Since this is adding the hour from 7 hours ago I assumed this was the problem. But when I removed this line, the date box would no longer automatically bring up today's date. I then added the following code after the hour line:

Date = Format([Date], "mm/dd/yyyy")

And this seemed to solve the problem. But now when you access an old record through the data entry system and click on the date, it will pop the date out into the full mask and allow you to edit it, even though the controls are set that after a record has been saved it cannot be edited or deleted. Before adding this line of code it would not do this, and therefore the record could not be edited once saved.

My question is, is there a way to stop this ability to edit old/saved records while still keeping the proper date format without the time problem?

Thanks for any help,

James

[maxamis4]

Off hand the only way I would do it is programmatically.

if date() >= YourDate then
   YourDate.locked = false
else
 
   Your Date.locked = True
end if
 

This will lock only records that are older than todays date. If you do this on current or on load, this should help you lock records from being modified.

[JamesDC]

I need every record to be locked. It was like this before I changed the problems with the time mask, but ever since it allows it to be changed.

I tried working with your code and it didn't help. But I realized what it is doing when it is popping the date out to the full mask, its popping it out to today's date. I checked on old entries, when I clicked on the date it would go as follows:
1/31/2007 -> Click On It -> 2_/6_/2007 -> Tab -> 2/6/2007

If I hit Escape instead of tab it goes back to 1/31/2007

Some more info on the form is that for each load of waste, 1 form will be filled out with employee number, shift, date, product, and weight. So the average shift will have the employee filling out anywhere from 5 to 20 forms, each creating a new record. Then as the day goes on and the shift changes, a new employee will take the position of recording wastes, creating more records on the same date, just with a different shift and employee number.

Thanks again for any help

[Rabbit]

Do you need them to be able to view older records as well as add new records at the same time?

[JamesDC]

Yes, we need to be able to view old records and put in new records at the same time.

[NeoPa]

Firstly, as a general rule, don't change items you don't understand.
I'm talking here about understanding why the developer stored his date as the current time - 7 hours.
If you think it is simply because he was bodging it to get it to work, then you have nothing to fear. If, on the other hand, he is using the time part of the field somewhere else in the project then losing that may cause you problems.
Assuming, for now, that you do want to standardise on Dates without the Time factor in your table, we will need to set you up with a one-off update query. It seems to me that you have a field called [Date] in a table called [YourTable].
If all the entries that have times associated are 7 hours short of the time they were added, and the date of that time is required, we need an update query whose SQL is :

UPDATE [YourTable]
SET [Date]=DateValue([Date]+#7:0:0#)
WHERE TimeValue([Date])>#0:0:0#

Change the names of field or table as appropriate.
This will leave you with a table of data which is in the format you require.

NB. Unless you have a good reason otherwise, you do not want to set the value to a string value (EG Format()). Your field is stored as a Date/time field and should always be treated that way. Making assumptions as to how it would convert to text can often lead to problems.

[JamesDC]

Hey NeoPa,

I've been through the program over and over again and there is never a reference to the time for a data entry. There are no queries that make reference to a time, nor any reports for reporting by hour. As this program is going to replace a paper dependant system, all of the earlier records had to be manually inserted by the developer or myself. Since these records were always previous to todays date the date was always manually put in, it was never allowed that the current date be the set date, and this bug was not noticed until today when I tried a sample run.

And yes, my [MainDB] table has the field [Date] which is Date/Time of Short Date with mask 99/99/0000;0;_.

All of my data is currently without the time part attached to it because it was mostly old manually input data or recently edited data to be without time. It's the actual input part that is my problem. I do not know why adding the Format line to get rid of the time addition now allows the altering of the Date in the data entry form.

Like I previously stated, before adding the Format line the date box on the form could not be edited, just like the rest of the text boxes on the form of an old/saved record. However now that the Format line has been added, the Date text box can be altered on old/saved records, after the date is altered the other text boxes (ie. Employee number, shift, product) can be altered as well.

Thanks,

James

[NeoPa]

James,
I would advise creating and running the update query anyway. If it tells you 0 records have been updated, then no harm done. If not, there was actually some work to be done.
As to your input problem, I can't see, from what you've posted, why it would behave as you describe.
Can you post how you try to disable editing of the records or controls on your form.

BTW my warning was a general one - not directed at you personally. Sounds like you had that well covered :)

[JamesDC]

Sorry for the long reply, people need to sleep sometimes :)

I think I can make my question simpler, is there a way to make it so that once a record is saved the date of the record cannot be changed? I find that if I click the date of an old record it will automatically pop it out to today's date, which could potentially ruin some data.

As far as my form is set up, I have Allow Edits and Allow Deletions off and Allow Additions on. As for my Date entry box, the property Locked is set to no, but turning it to yes does nothing to help my problem.

All the other fields are fine, being as once they're saved they cannot be edited, unless one goes into the main database (which is locked behind the scenes and requires a proceudre to enter it). I believe it has something to do with the formating of a date number which allows this, but so far I've made no progress in stopping the ability to change an old date.

[NeoPa]

James,
Changing the focus of a question half way through is actually not a simplification at all.
In this case, the history of the thread leads us (me) to believe that your 'simpler' question is missing the point anyway. If my guess is correct, then your issue is with the code behind the form rather than the settings thereon (as the issue is with your VBA code setting the value of the field after it has been loaded from the table).
Also, you haven't responded to my last post (#8). I need to know the number of the records updated.
Remember, because you may not understand all that is said in the thread is no reason to suppose that no progress is being made. Sometimes it is simply difficult to make progress from the other side of a web page as communication is so limited. Sometimes you may not appreciate what progress has been made. In this case, trying to clarify exactly what is causing the problem.

Lastly, don't feel any obligation to post back frequently.
You're right we all have to sleep and I'm certainly not sitting around idly waiting for your next reply ;)
Do it in your own good time.

I've reread this and it may sound a little critical to you. It is not meant to. I simply needed to express the situation as clearly as possible in words. I do understand that you're being as helpful as you can and I'm looking forward to working further on your problem :)

[JamesDC]

Hey,

No hard feelings about sounding critical. I know how hard it can be to help ppl with things without actually being there.

As for the amount of records changed, none were for the fact that they were all just a date with no time attachment due to the manual input.

I have attached the full background code for the data entry form:

Option Compare Database
 
 
 
Private Sub Area_Enter()
SendKeys "+^'", True
End Sub
 
Private Sub Date_Enter()
 
    Date = DateAdd("h", -7, Now())
    Date = Format([Date], "mm/dd/yyyy")
 
End Sub
 
Private Sub Employee_Name_Click()
SendKeys "+^'", True
End Sub
 
Private Sub Employee_Name_Enter()
SendKeys "+^'", True
End Sub
 
Private Sub GoBackMainMenu_Click()
On Error GoTo Err_GoBackMainMenu_Click
 
    DoCmd.Close
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Main Menu"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_GoBackMainMenu_Click:
    Exit Sub
 
Err_GoBackMainMenu_Click:
    MsgBox Err.Description
    Resume Exit_GoBackMainMenu_Click
 
End Sub
Private Sub GoToFirst_Click()
On Error GoTo Err_GoToFirst_Click
 
 
    DoCmd.GoToRecord , , acFirst
 
Exit_GoToFirst_Click:
    Exit Sub
 
Err_GoToFirst_Click:
    MsgBox Err.Description
    Resume Exit_GoToFirst_Click
 
End Sub
Private Sub GoToPrev_Click()
On Error GoTo Err_GoToPrev_Click
 
 
    DoCmd.GoToRecord , , acPrevious
 
Exit_GoToPrev_Click:
    Exit Sub
 
Err_GoToPrev_Click:
 
    Resume Exit_GoToPrev_Click
 
End Sub
Private Sub GoToNext_Click()
On Error GoTo Err_GoToNext_Click
 
 
    DoCmd.GoToRecord , , acNext
 
Exit_GoToNext_Click:
    Exit Sub
 
Err_GoToNext_Click:
 
    Resume Exit_GoToNext_Click
 
End Sub
Private Sub GoToLast_Click()
On Error GoTo Err_GoToLast_Click
 
 
    DoCmd.GoToRecord , , acLast
 
Exit_GoToLast_Click:
    Exit Sub
 
Err_GoToLast_Click:
    MsgBox Err.Description
    Resume Exit_GoToLast_Click
 
End Sub
Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click
 
 
    DoCmd.GoToRecord , , acNewRec
 
 
Exit_NewRecord_Click:
    Exit Sub
 
Err_NewRecord_Click:
    MsgBox Err.Description
    Resume Exit_NewRecord_Click
 
End Sub
Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click
 
 
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
 
Exit_FindRecord_Click:
    Exit Sub
 
Err_FindRecord_Click:
    MsgBox Err.Description
    Resume Exit_FindRecord_Click
 
End Sub
 
 
Private Sub Product_Enter()
SendKeys "+^'", True
End Sub
 
Private Sub Shift_Click()
SendKeys "+^'", True
 
End Sub
 
Private Sub Shift_Enter()
SendKeys "+^'", True
End Sub
Private Sub Save_Click()
On Error GoTo Err_Save_Click
 
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_Save_Click:
    Exit Sub
 
Err_Save_Click:
    MsgBox "Record Already Saved!"
    Resume Exit_Save_Click
 
End Sub
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click
 
 
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
 
Exit_Command35_Click:
    Exit Sub
 
Err_Command35_Click:
    MsgBox Err.Description
    Resume Exit_Command35_Click
 
End Sub
 
Private Sub GoToShift_Click()
On Error GoTo Err_GoToShift_Click
 
    DoCmd.Close
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "ShiftSelect"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_GoToShift_Click:
    Exit Sub
 
Err_GoToShift_Click:
    MsgBox Err.Description
    Resume Exit_GoToShift_Click
 
End Sub
 
 

In there are sections for going through the various records, on the form, ie. the previous, first, last, and next buttons. As well as code for the various text boxes for data input.

I hope that this can shed some light on the problem,

James

PS: You don't sit idly around waiting for my reply? I'm shocked! I know I can't wait for your reply... but that's probably because I'm the one getting frustrated by the program :)

[NeoPa]

As for the amount of records changed, none were for the fact that they were all just a date with no time attachment due to the manual input.

As long as you ran the test that's fine (I can interpret the results ok ;)). My worry is building complex structures on other people's assumptions (especially remotely).
I will have to look at this later tonight if I get the opportunity.
I'm out till after 23:00 so will be restricted.
I will look at your (voluminous) code then :)

[JamesDC]

THanks :)

Keep in mind that most of that volumous code is not (in my opinion) related to the date problem.

[NeoPa]

(Quick visit to home PC before leaving for evening.)
No, I expect you're right.
It may nevertheless provide clues :) And will provide (some) context for the date control issue. We'll see what I can turn up.

[JamesDC]

I guess this is a kind of bump for my post.

After receiving a production excel file yesterday I realized what the -7 hours is for. The day is divided into 3 shifts, A from 7am-3pm, B from 3pm-11pm, and C from 11pm to 7am. The -7 hours is to make it so that shift C occurs on the previous day.

I also still cannot figure out a way to lock the date so that it cannot be changed :(

[NeoPa]

I've had a couple of bad days James. Sorry for leaving this aside for so long. Tonight is also an 'out' night but I hope to catch up at the weekend. It's a good idea to 'Bump' in the circumstances ;)

[NeoPa]

I've reread this thread from the beginning to get back up with it.
Here are some things to try (in order) :

• Replace your version of this procedure with this code.

  Private Sub Date_Enter()
      If IsNull(Me!Date) Then Me!Date = DateValue(DateAdd("h", -7, Now()))
  End Sub

• Enter =DateValue(DateAdd("h", -7, Now())) into the Default Value of the [Date] field in your table and remove the Date_Enter() function completely.
• Test and see how removing the Input Mask affects the situation.

[JamesDC]

Hey!

The code you gave me worked like a charm! And on my first try.

Thank you so much :)

Just one question, how does it actually work? How does it lock the date? Or is my old Format() command the cause of the changing date?

Thanks again,

James

[NeoPa]

If IsNull(Me!Date) Then Me!Date = DateValue(DateAdd("h", -7, Now()))

What this is actually saying is :
Only set the value of the [Date] field if it doesn't already contain a value. That way it is only used for new records, and existing records aren't corrupted.
Anyway, glad you're sorted :)