An action query that locks processed records (1 Viewer)

[bevc]

Hi all, is it possible to create an action query that locks records? I have a subform that users will need to update, but I don't want them to be able to edit 'already processed' information.

Ideally what I'd like to do, is have a control button that brings up a dialogue box requesting what (invoice) number you wish to lock, then having it lock all records with that invoice number in the table behind the subform, so that they can still be viewed on the form, but can't be edited. I thought an action query might be the easiest way to do this.

I'm an Access newb and have very little clue about VBA or anything like that, if anyone could help that would be much appreciated.

 

[Minty]

The simplest route would probably be to have a flag on the invoice sub table indicating it's current state.
You would set the flag at various stages of the Invoice process, so use a number field to represent what state the invoice / sub record is at
1 = can be edited
2 = currently being edited - stops 2 people doing the same process
3 = Locked - can't be adjusted any more - viewing only.

 

[bevc]

Thanks very much for the response, that’s a brilliant idea! Is there a way of automating that for many records in the sub-table, without using VBA (or even using VBA)? The ‘invoices’ in my database (they aren’t actually invoices, but the principle applies) will relate to hundreds of records in my sub-table, and after an invoice has been checked it would save a lot of time if there was a way to automate it.

 

[Minty]

Provided you have criteria to select the invoices that can be queried, then you can use that query to update all records that match.

So you can either save a number of update query's to perform the action, or program them in VBA, which ever you are more comfortable with.

By default I would suggest setting all records to either a 1 or 3 and then look at how you process them afterwards.

 

[gemma-the-husky]

one issue with arbitrary things like this, is fixing an erroneous flag.

what if someone marks a record as locked, and then determines the lock was wrongly applied. In general it might be better to try and determine some external event that signifies a lock - such as a payment - so a paid item cannot be altered, but an unpaid one can.

alternatively, consider which features of an invoice may or may not be amendable, irrespective of a "lock" flag. In general most of an invoice ought to be "locked" anyway, I would have thought.

 

[bevc]

Thank you very much for your help, I added a field to the sub-table that designates the record as either 'pending' or 'complete', I created an update query with a parameter value that, I was quite surprised and gratified to realise, works exactly the way I intended it to.

My next challenge is to find a way to 'lock' the records once they are designated as complete, or in some other way make it obvious that they should be left alone. This isn't strictly necessary, but I won't be having much day to day interaction with the db, and I want it to be totally obvious to any user which records in the sub-form they shouldn't edit. If anybody has any suggestions I'd be very grateful.

 

[Minty]

I would consider setting your sub form to not editable if the record is complete, or less confusing for an end user simply only display pending records.
You could then display complete records by using a command button and at the same time lock all the fields.

 

[bevc]

I would consider setting your sub form to not editable if the record is complete, or less confusing for an end user simply only display pending records.
You could then display complete records by using a command button and at the same time lock all the fields.

Thank you very much, that is a great suggestion, but, um, how do I do that? (sorry, I am very new to this). Do I need to make an append query that creates a new table of 'complete' records?

 

[Minty]

At the moment your subform probably has its record source set to the whole invoice table.
You can either
a)Click the builder and use the query editor to filter it down to only Pending records, b)More complex but more flexible, use vba code attached to a button to set the record source. The button could toggle between Pending (Default) and Complete.

You certainly don't really want to copy them needlessly.

 

[Galaxiom]

Without a clear picture of your data model it s hard to comment but if the lock can be determined by some other data in the database then setting a lock flag would be superfluous.

One of the most configurable ways to enable and disable controls on a condition is with Conditional Formatting.

 

[bevc]

I'm still having problems with this - I have managed to set the form so pushing a button opens a filtered subform that shows 'complete' invoices that relate to that account (and is read only), which is exactly what I want, and another form that opens up for new or 'pending' invoices for data entry, but..........if you close that subform then re-open it, the line just completed as 'pending' has disappeared, although it is in the underlying table :banghead:

I do start to wonder at what point I give up and delete all the sub-forms, strip all the useless numbers I've inputted into my table while faffing around getting frustrated, and start again.