No editing after one week

[longshen]

Dear all,

We had used MS Access to create a database.
We have 3 forms that allow people to key in data. Every time after entering the data they will tend to edit it again due to some typo or wrong info. During this editing some people will accidentally edit others data.
We want to prevent people to edit any past data that is more than 1 week old but don’t know how.
We have tried putting an edit button but don’t know what expression or coding to use.
Can we simply use quries to make this work or do we have to create a code for this?
Can anyone kindly help us out?

 

[Andrew Fergus]

Hello and welcome to Mr Excel.
Does your data on the form include a date? Is there any way to tell if the data is a week old?
Andrew

 

[longshen]

hi Andrew

Thanks for replying.
Yes, there is a field call Date in each form.
How should i go about doing it?

 

[longshen]

hi Andrew

Sorry, forget to reply ur 2nd Question.
these forms are used 24 hrs a day, at any time people will key in data.
for exmaple, consider today the 1st day and i only allow people to edit the records for the past 7 days.

thank you...

 

[Andrew Fergus]

Hi Shen
Go into the form design (for each of the 3 forms, if necessary), Click Edit -> Select Form, Click View -> Properties -> Event Tab -> On Current -> click the 3 dots (...) to the right -> Code Builder -> Ok -> Enter the following :

Private Sub Form_Current()
    If IsNull(Me!Date) Or Me!Date > Date() - 7 Then
        Me!Field1.Enabled = True
        Me!Field2.Enabled = True
    Else
        Me!Field1.Enabled = False
        Me!Field2.Enabled = False
    End If
End Sub

Where I have used 'Field1' and 'Field2' be sure to use the field names from the form. You can add extra fields into this by inserting new lines after Field2.

Please note this only works for forms where the Default View = Single Form (under the Format tab), it doesn't work for forms where the Default View = Continuous Forms. I'm not sure how to get this to work for forms with a 'Continuous' view.

Also, you may (or may not) want to disable the 'Date' field on the form to prevent users from reopening old records by changing the date - or this might not be problem. You can automatically populate new date fields by using a default value of =Date().

With this set-up you won't need the 'Edit' button.

HTH, Andrew

Late Edit : I haven't tested this for a multi-user environment.

 

[longshen]

Hi Andrew
i had tried the coding that u provide. but it doesn't seen to work(no reponse) . My field, date setting is already in default as Date().
this is what i type in the code buider;

If (Me!DATE > DATE - 7) Then

Me!Machine_Status.Enabled = True
Me!Machine_Type.Enabled = True
Me!TabCtl342.Enabled = True
Me!Lot_A.Enabled = True
Me!Lot_B.Enabled = True
Me!Lot_C.Enabled = True
Me!Lot_ID.Enabled = True
Me!Quantity_Affected_A.Enabled = True
Me!Quantity_Affected_B.Enabled = True
Me!Quantity_Affected_C.Enabled = True

Else

Me!Machine_Status.Enabled = False
Me!Shift.Enabled = False
Me!Machine_Type.Enabled = False
Me!TabCtl342.Enabled = False
Me!Lot_A.Enabled = False
Me!Lot_B.Enabled = False
Me!Lot_C.Enabled = False
Me!Lot_ID.Enabled = False
Me!Quantity_Affected_A.Enabled = False
Me!Quantity_Affected_B.Enabled = False
Me!Quantity_Affected_C.Enabled = False

End If
End Sub

there are more fields which are not displayed above but already included in my coding.
pls spend a few mins to take a look...
thank you...

 

[Andrew Fergus]

Hi Shen
Please accept my apologies, I tested this where the field name was not actually 'Date' and then I changed the field name when I did my post - if I use the field name 'Date' it doesn't work either. Change this piece of code :

If (Me!DATE > DATE - 7) Then

To This :

If (Me!DATE > Now() - 7) Then

HTH, Andrew

 

[Norie]

Date is a reserved word in VBA and returns today's date.

That could be causing problems.

By the way Andrew are you sure this doesn't work on a continuous form.

 

[Andrew Fergus]

Thanks for the tip Norie - I think the two 'Dates' trip over each other and I got it to work using Now() instead of Date(). It's probably irrelevant to Shen but if I set the form to Continuous, it looks at the first record and applies the if condition to every record (much like the problem I had the other day with my question - thanks for solving that). If I change it to a single form, then it works perfectly. Is there something I am missing?
Andrew

 

[longshen]

Hi Andrew and Norie,

Thank You very much!!!
My problem is solved.