By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Changing Time Mask Problems

JamesDC
P: 66
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
Feb 6 '07 #1
Share this Question
Share on Google+
18 Replies


maxamis4
Expert 100+
P: 295
Off hand the only way I would do it is programmatically.

Expand|Select|Wrap|Line Numbers
  1. if date() >= YourDate then
  2.    YourDate.locked = false
  3. else
  4.  
  5.    Your Date.locked = True
  6. end if
  7.  
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.
Feb 6 '07 #2

JamesDC
P: 66
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
Feb 6 '07 #3

Rabbit
Expert Mod 10K+
P: 12,365
Do you need them to be able to view older records as well as add new records at the same time?
Feb 6 '07 #4

JamesDC
P: 66
Yes, we need to be able to view old records and put in new records at the same time.
Feb 6 '07 #5

NeoPa
Expert Mod 15k+
P: 31,487
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 :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [YourTable]
  2. SET [Date]=DateValue([Date]+#7:0:0#)
  3. 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.
Feb 6 '07 #6

JamesDC
P: 66
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
Feb 6 '07 #7

NeoPa
Expert Mod 15k+
P: 31,487
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 :)
Feb 6 '07 #8

JamesDC
P: 66
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.
Feb 7 '07 #9

NeoPa
Expert Mod 15k+
P: 31,487
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 :)
Feb 7 '07 #10

JamesDC
P: 66
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3.  
  4.  
  5. Private Sub Area_Enter()
  6. SendKeys "+^'", True
  7. End Sub
  8.  
  9. Private Sub Date_Enter()
  10.  
  11.     Date = DateAdd("h", -7, Now())
  12.     Date = Format([Date], "mm/dd/yyyy")
  13.  
  14. End Sub
  15.  
  16. Private Sub Employee_Name_Click()
  17. SendKeys "+^'", True
  18. End Sub
  19.  
  20. Private Sub Employee_Name_Enter()
  21. SendKeys "+^'", True
  22. End Sub
  23.  
  24. Private Sub GoBackMainMenu_Click()
  25. On Error GoTo Err_GoBackMainMenu_Click
  26.  
  27.     DoCmd.Close
  28.  
  29.     Dim stDocName As String
  30.     Dim stLinkCriteria As String
  31.  
  32.     stDocName = "Main Menu"
  33.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  34.  
  35. Exit_GoBackMainMenu_Click:
  36.     Exit Sub
  37.  
  38. Err_GoBackMainMenu_Click:
  39.     MsgBox Err.Description
  40.     Resume Exit_GoBackMainMenu_Click
  41.  
  42. End Sub
  43. Private Sub GoToFirst_Click()
  44. On Error GoTo Err_GoToFirst_Click
  45.  
  46.  
  47.     DoCmd.GoToRecord , , acFirst
  48.  
  49. Exit_GoToFirst_Click:
  50.     Exit Sub
  51.  
  52. Err_GoToFirst_Click:
  53.     MsgBox Err.Description
  54.     Resume Exit_GoToFirst_Click
  55.  
  56. End Sub
  57. Private Sub GoToPrev_Click()
  58. On Error GoTo Err_GoToPrev_Click
  59.  
  60.  
  61.     DoCmd.GoToRecord , , acPrevious
  62.  
  63. Exit_GoToPrev_Click:
  64.     Exit Sub
  65.  
  66. Err_GoToPrev_Click:
  67.  
  68.     Resume Exit_GoToPrev_Click
  69.  
  70. End Sub
  71. Private Sub GoToNext_Click()
  72. On Error GoTo Err_GoToNext_Click
  73.  
  74.  
  75.     DoCmd.GoToRecord , , acNext
  76.  
  77. Exit_GoToNext_Click:
  78.     Exit Sub
  79.  
  80. Err_GoToNext_Click:
  81.  
  82.     Resume Exit_GoToNext_Click
  83.  
  84. End Sub
  85. Private Sub GoToLast_Click()
  86. On Error GoTo Err_GoToLast_Click
  87.  
  88.  
  89.     DoCmd.GoToRecord , , acLast
  90.  
  91. Exit_GoToLast_Click:
  92.     Exit Sub
  93.  
  94. Err_GoToLast_Click:
  95.     MsgBox Err.Description
  96.     Resume Exit_GoToLast_Click
  97.  
  98. End Sub
  99. Private Sub NewRecord_Click()
  100. On Error GoTo Err_NewRecord_Click
  101.  
  102.  
  103.     DoCmd.GoToRecord , , acNewRec
  104.  
  105.  
  106. Exit_NewRecord_Click:
  107.     Exit Sub
  108.  
  109. Err_NewRecord_Click:
  110.     MsgBox Err.Description
  111.     Resume Exit_NewRecord_Click
  112.  
  113. End Sub
  114. Private Sub FindRecord_Click()
  115. On Error GoTo Err_FindRecord_Click
  116.  
  117.  
  118.     Screen.PreviousControl.SetFocus
  119.     DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
  120.  
  121. Exit_FindRecord_Click:
  122.     Exit Sub
  123.  
  124. Err_FindRecord_Click:
  125.     MsgBox Err.Description
  126.     Resume Exit_FindRecord_Click
  127.  
  128. End Sub
  129.  
  130.  
  131. Private Sub Product_Enter()
  132. SendKeys "+^'", True
  133. End Sub
  134.  
  135. Private Sub Shift_Click()
  136. SendKeys "+^'", True
  137.  
  138. End Sub
  139.  
  140. Private Sub Shift_Enter()
  141. SendKeys "+^'", True
  142. End Sub
  143. Private Sub Save_Click()
  144. On Error GoTo Err_Save_Click
  145.  
  146.  
  147.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  148.  
  149. Exit_Save_Click:
  150.     Exit Sub
  151.  
  152. Err_Save_Click:
  153.     MsgBox "Record Already Saved!"
  154.     Resume Exit_Save_Click
  155.  
  156. End Sub
  157. Private Sub Command35_Click()
  158. On Error GoTo Err_Command35_Click
  159.  
  160.  
  161.     Screen.PreviousControl.SetFocus
  162.     DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
  163.  
  164. Exit_Command35_Click:
  165.     Exit Sub
  166.  
  167. Err_Command35_Click:
  168.     MsgBox Err.Description
  169.     Resume Exit_Command35_Click
  170.  
  171. End Sub
  172.  
  173. Private Sub GoToShift_Click()
  174. On Error GoTo Err_GoToShift_Click
  175.  
  176.     DoCmd.Close
  177.  
  178.     Dim stDocName As String
  179.     Dim stLinkCriteria As String
  180.  
  181.     stDocName = "ShiftSelect"
  182.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  183.  
  184. Exit_GoToShift_Click:
  185.     Exit Sub
  186.  
  187. Err_GoToShift_Click:
  188.     MsgBox Err.Description
  189.     Resume Exit_GoToShift_Click
  190.  
  191. End Sub
  192.  
  193.  
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 :)
Feb 7 '07 #11

NeoPa
Expert Mod 15k+
P: 31,487
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 :)
Feb 7 '07 #12

JamesDC
P: 66
THanks :)

Keep in mind that most of that volumous code is not (in my opinion) related to the date problem.
Feb 7 '07 #13

NeoPa
Expert Mod 15k+
P: 31,487
(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.
Feb 7 '07 #14

JamesDC
P: 66
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 :(
Feb 9 '07 #15

NeoPa
Expert Mod 15k+
P: 31,487
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 ;)
Feb 9 '07 #16

NeoPa
Expert Mod 15k+
P: 31,487
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Date_Enter()
    2.     If IsNull(Me!Date) Then Me!Date = DateValue(DateAdd("h", -7, Now()))
    3. 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.
Feb 9 '07 #17

JamesDC
P: 66
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
Feb 12 '07 #18

NeoPa
Expert Mod 15k+
P: 31,487
Expand|Select|Wrap|Line Numbers
  1. 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 :)
Feb 12 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.