Lock all 'old' records

[Sharky II]

Lock all 'old' records (older than user-allocated date)

Hi guys

I've been asked to provide a function where, once all records are 'finalised', they can be 'locked'.

I have a very simple db with two tables: 'Transactions' and 'TransactionItems', which have a one-to-many relationship.

There's a field called 'FinanceReportDate' in 'Transaction' which has format q/yyyy (but stores a full date).

I've been asked that once a user (the boss) 'decides', she is able to lock all records for that quarter (and all previous quarters).

Usability-wise, i was thinking that there could be an extra button on the switchboard, launching a form where the user can enter a date. This date indicates that all records with a FinanceReportDate older than the one given are 'locked'? Perhaps I could set some kind of global FormLockDate variable and on the 'on load' for the Transaction form, it could check that:

If FinanceReportDate isBefore FormLockDate
then Allow Edits = no!

Does anyone have a solution? I can't think how to achieve this and am stuck! Sorry for my silly pseudocode

Thank you!

 

[Sharky II]

Hi

From reading around, i am seeing that i could place some code in the Current event. I found this code which locks records where FinanceReportDate is older than 3 days. Would anyone know how to modify this so that i can set a 'global' variable (in a module?) set in another form, for the date/quarter by which to compare FinanceReportDate with?

Private Sub Form_Current()
 If DateDiff("d", Me.FinanceReportDate, Date) > 3 Then
  Me.AllowEdits = False
 Else
  Me.AllowEdits = True
 End If
End Sub

For now, i am thinking of creating a table that stores the 'comparison' date, then i could simply compare FinanceReportDate with it. Seems wrong to create a table for just one field though!

 

[MStef]

Look at "DemoLockOldRecA2000.mdb" (attachment, zip).
Look at "table1", "Form1" (Record Source),"MainForm" (VBA).
Open MainForm and try.
In Form1-Record source, change the sign < in >.
If you leave the date1 01.01.3000, no records will be presented.
Ignore the text what I wrote in the MainForm.

 

[Sharky II]

Thanks!

The example is cool, but it seems to require the form being left open to get the date from the unbound text box? It also seems to simply not show you the results which aren't greater/less than, as opposed to allow you to see them but stopping you from editing them - i think AllowEdits is the way to go.

I just don't know how to create/store a global variable (or whatever) to store this date value to compare to FinanceReportDate (the results of which will dictate if AllowEdits is yes or no). Creating an entirely new table just for that seems excessive.

Anyone able to give me some advice?

Thanks!

 

[G37Sam]

^ The DB shown does not lock any records

I wouldn't store it as a global variable, because it would reset everytime you run the database.

I'd store it in a separate table and use dlookup() to retrieve it

 

[Sharky II]

Hi, thanks again for replying. OK, i've put it in a table. I've got the following code, could somebody please correct the syntax? You can probably tell what i'm trying to do:

Private Sub Form_Current()
Dim q As Date
q = DLookup("AllowEditsDate", "AllowEditsDateTable", "ID=1")
If q > Me.FinanaceReportDate Then
  Me.AllowEdits = False
 Else
  Me.AllowEdits = True
 End If
End Sub

I set ID=1 because in the table there is only one record, and the ID=1! I wasn't sure if this was correct or not - but generally i obviously have a problem with the DLookup side of things!

Thank you!

 

[G37Sam]

Lose the ID=1 part, you don't really need a search critera in a table with one record

 

[Sharky II]

Thanks!

This actually worked, i had tried not including that part - i was doing the 'classic' spelling field name incorrectly mistake!

Thanks for your help on this!

Eddie

 

[Sharky II]

I'm going to experiment now, but can anyone tell me what the code will do if there is no date in the 'FinanceReportDate' field in the Transaction Table i.e. if the field is empty?

I guess it would make sense if the code ignored the records with the blank FinanceReportDate, instead of making them uneditable.

I've also added the > operator, as well as making it so deletions aren't allowed either.

Thank you!

Private Sub Form_Current()
Dim q As Date
q = DLookup("AllowEditDate", "AllowEditsDateTable")
If q >= Me.FinanaceReportDate Then
  Me.AllowEdits = False
  Me.AllowDeletions = False
 Else
  Me.AllowEdits = True
  Me.AllowDeletions = True
 End If
End Sub

 

[Sharky II]

Results are in: seems to work fine! Ignores the records with no 'Finance Report Date' - i.e. they are still editable.

Thanks again!

 

[G37Sam]

My pleasure

 

[Sharky II]

hi guys, perhaps you can help me a little further - i found a 'bug':

I open the form in 'edit' mode and allow the user to change the date (not in add because then you get a load of new dates). this seems to work fine and i can change it OK

However, when i want to remove the date completely so all records are editable, then the record obviously shows 'null'

Is there a way to say 'if null then allowedit ON for all records'? or whatever the proper way to write that.

If anyone could lend a hand with this, that would be awesome.

Thanks!

 

[Sharky II]

Edit: I've asked this question in the VBA forum where it's more relevant, so i guess i should just continue it over there: http://www.access-programmers.co.uk/forums/showthread.php?p=1094274#post1094274

Thanks!

-----

Anyone able to lend a hand for when the field is blank i.e. 'don't lock any forms'? It must be incredibly simple, i just don't know the VBA! The latest code is:

Private Sub Form_Current()
Dim q As Date
q = DLookup("AllowEditDate", "AllowEditsDateTable")
If q >= Me.FinanaceReportDate Then
  Me.AllowEdits = False
  Me.AllowDeletions = False
 Else
  Me.AllowEdits = True
  Me.AllowDeletions = True
 End If
End Sub

Thank you!

 

[gemma-the-husky]

sharky

the easiest way is to say something like this first

testdate=nz(testdate,0)


if that case, if it is null, it will set the date to 0, (which is 30/11/1899 from memory - or something very near to that date) - so effectively it will unlock all your records. It is easier then, as you will have a date value in all cases, rather than having to try and modify code to deal with a special case of "null"

nz is a nifty little function that casts any null to an appropriate value you want to allocate.

 

[Sharky II]

Hi Dave!

That's exactly what someone told me in the other thread - Thanks!

Eddie

 

[NikkCol]

Re: Lock all records to a project number

Can someone help me?
I have a database of project codes. Each project has a code assigned ie. CODE9989 example.
Once this project is closed I want to lock so that no edits or deletions can happen also I want it to be grayed out throughout the whole database. Also any associated records (ie, expenses, payments) assoiciated with this project to also be locked when this project number is selected from any form combo box.

1 How to do lock a project number throught the db
2. how do I make sure its grey out
3. when this project number is selected from any combo box throughout the database how do I make sure any related info is locked and grayed out as well per form.

NEED HELP

 

[G37Sam]

What I'd do is add a Yes/No field called RecordLocked

Set it to yes when you want to lock that record, and add your code to lock On Current Event