Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preventing records from changing. (Access 2000)

    This is a design question.
    If I have an Invoice table, with records for each item on the invoice. How would I prevent items from being added to the invoice after the invoice has been paid?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preventing records from changing. (Access 2000)

    Hi Cath,

    You can't do this at the table or query level in Access, only at the form level.

    One way to do it would be to have a main form for the invoices, and a subform for the items per invoice. Use code to check the "paid" status in two places: in the AfterUpdate event of the control that determines the status, and in the On Current event of the form as a whole. Set the Locked property of the subform to True if the invoice has been paid, and to False if it hasn't.

    Post back if this is not clear enough

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing records from changing. (Access 2000)

    Hans
    "Set the Locked property of the subform to True if the invoice has been paid, and to False if it hasn't."
    Where would be the best place to do this - as a macro, or through code? Or is it just a question of style?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preventing records from changing. (Access 2000)

    As I wrote, the place where you should do this is in the AfterUpdate event of the Invoice control and in the OnCurrent event of the form. The method to do it can be a macro or a VBA procedure. I always use VBA, because it is much easier to debug and to maintain than macros. But in the end, it's up to you.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing records from changing. (Access 2000)

    <P ID="edit" class=small>(Edited by D Willett on 29-May-03 07:46. )</P>Here's a little bit of code I use for invoicing purposes.
    On my Main form, I have a text which shows an invoice No.
    If the invoice No is >0 then I call this code to prevent any additions or edits to the file.

    Private Sub LockAllControls()
    Dim ctl As Control
    For Each ctl In controls
    Select Case ctl.ControlType
    Case acTextBox, acCheckBox, acComboBox, acToggleButton, acListBox, acOptionGroup, acSubform
    ctl.Locked = True
    Case acCommandButton, acTabCtl
    ctl.enabled = False
    Case Else
    End Select
    Next ctl
    End Sub

    The main form has an On Current event which the following code checks this text box:

    'This code checks to see if the file is invoiced and locks various controls:-

    If Forms!frmDetails!txtInvoiceNo >0 Then Call LockAllControls


    The main portion of the code, goes in the forms module, basically under "Option Compare Database" paste the code, then whichever condition you want to meet, you can call the code "LockAllControls"
    from the OnLoad or Current property.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing records from changing. (Access 2000)

    Thanks Dave and Hans
    In the end I used a macro, and just in case anyone else trips over this while following the thread. I had to set a true and false condition, or else I successfully locked the form and couldn't unlock it for unpaid records. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •