Lock records

[SueBK]

I have an invoicing database. I have a yes/no field "Invoiced", which is checked after an invoice is issued. I want to lock the other fields after this is ticked. As a minimum, I want to lock the "Invoice ID" field so costs can't end up on two invoices.

I only enter data through a form, so I'm thinking that locking the text box on the form is probably sufficient (just trying to prevent accidental overwrites, rather than create huge security barriers).

I'm thinking the code I need is an after update:

if [Invoiced] is yes then [invoiceID] is disabled
if [Invoiced] is no then [invoiceID] is enabled.

But I don't know how to actually write that. Appreciate your help.

----
I do actually know how to write the code above (duh), but realise now it won't work, 'cause my list of costs show in a datasheet view and [invoiceid] on every record is disabled.

I really just want to be able to lock down single records against change.

 

[PNGBill]

One way you can approach this is to have the Form set to not allow Edits, Deletions and Additions.

Add a command Button to "Add New Invoice" and this will set the form to allow Additions but not Edits or Deletions. You shouldn't see any existing records in this mode.

Another Button will allow Edit of an Invoice that is displayed on the Form but the AllowEdit is inside an "If Then" that checks for the Invoice Status field on the TableInvoiceHeader and will only allow Edits if the Status field is "Null" eg.

On all views, except Add new Invoice, The Control - Text Box for Invoice Number is set to Locked so the Number can not changed, if such an occurrence is possible.

Code to do these actions is quite basic once you have the form set up.

 

[PNGBill]

I just noticed you refer to a List of Costs in Datasheet view - like continuous form.

You can click on a Cost and Invoice it ?

and you don't want it Invoiced Twice, as if anyone would

I guess each records has it's own "Invoice" button that then opens a form to allow the Invoice to be raised. ?

The Query that is the Datasource for this invoice can also have the Criteria set to only show records where the check field is "what ever" so when the button is clicked, No invoice Form will display.

Do you have the Invoice Form setup so when the button is clicked, it displays an invoice ready to go for that Cost? or do you have a button on the Form Header and you have to type in the values?

 

[SueBK]

Funny how when you're buried in something every thing seems straight forward and logic - til you try to explain.

I have a datasheet: [invoiceID], [weekID], [person], [hours], [cost]. All costs recored against a particular [invoiceid] drop into that invoice. Invoice #1 is for Week 1 through 4, Invoice 2 for Week 5 to 8, etc. but occassionally, we might have a missed cost for say week 4, but we've already moved onto Invoice 2. I want to ensure that once I've issued an invoice, the fields (particularly [invoiceid] & [weekid] can't be changed.

It's not elegant, but what I've done:
* created a query to join my invoice register (which has a yes/no [issued] field) and my cost inputs table, and filtered to [issued] = no
* run my datasheet form off that query, so I only see costs for unissued invoices
* copied my forms & subforms (where the lack of elegance shows) and assigned them to original tables (ie unfiltered data), but then locked them.

If I want to see past costs - click the "View Inputs Costs"; if I want to edit/add costs click the "Edit Inputs Costs" which will then only show me costs not yet invoiced.

I'm certain there's a 'neater' way, but for my purposes - this works fine.

 

[PNGBill]

All resolved then ??