View Poll Results: Which you prefere? Women or computers?

Voters
3. You may not vote on this poll
  • What is a Computer?

    1 33.33%
  • Computers

    1 33.33%
  • Women

    1 33.33%
  • What is a woman?

    0 0%
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Sep 2003
    Posts
    69

    Exclamation Unanswered: Lock Forms in Access

    I have made a little invoice program in Access. Everuthing works ok but i still have a big problem. Old invoices still can be changed even when they are finalised. Does anybody have a sollution to lock some of the records. I know you can not lock records in Access (only full tables) but it should be possible to lock the form on some of the records. Eg make one table with record numbers of the first table that are finalised , and the numbers appearing in this second table should be blocked in the form. Anybody an idea ?

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Lock Forms in Access

    Originally posted by west
    I have made a little invoice program in Access. Everuthing works ok but i still have a big problem. Old invoices still can be changed even when they are finalised. Does anybody have a sollution to lock some of the records. I know you can not lock records in Access (only full tables) but it should be possible to lock the form on some of the records. Eg make one table with record numbers of the first table that are finalised , and the numbers appearing in this second table should be blocked in the form. Anybody an idea ?
    If you're using a form, could you have the BeforeUpdate event look at

    the invoice number, or whatever you use to indicate finalized, and then

    disallow edits to those records?

    Gregg

  3. #3
    Join Date
    Sep 2003
    Posts
    69

    Re: Lock Forms in Access

    Originally posted by basicmek
    If you're using a form, could you have the BeforeUpdate event look at

    the invoice number, or whatever you use to indicate finalized, and then

    disallow edits to those records?

    Gregg
    Thanks for your answer. I have tried allow.edit=false but it actually locked the whole database.
    Maybe if i can find a function to compare each time the record number with numbers in a table and if it's not in the table to set the allow.edit=true. Any idea how i could do that?

  4. #4
    Join Date
    Sep 2003
    Posts
    69
    I meant :

    MeAllowEdits=False , That works but i should find a way to look up the invoicenumber in a table and if it exists in the table to set the Me.allowEdits on false.

    Any idea how i can check if a number on a form exists in a table?

  5. #5
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15

    Re: Lock Forms in Access

    you have several options here, but I think the previous poster's idea is the best one. You needn't fool around with table locking etc. when you can just as easily prohibit a user from changing a "finalized" invoice via code.

    You might try something like this:

    first, rather than building another table of "finalized" invoice's you could just add a field to the invoice table that indicates that "this invoice is finalized and cannot be changed". Call it something like "done" or "finalized" and make it a yes/no field.

    then on your form you can put some code in the BeforeUpdate event of the relevant controls to determine if the current control can be changed.

    ----------------------------------------------

    (assuming the form has fields called "invoice_date" and "finalized")

    Private Sub invoice_date_BeforeUpdate(Cancel As Integer)

    if me![finalized]= -1 then '-1 means yes

    'tell the user why this field can't be updated
    msgbox "You can't update Invoice Date because this invoice has been finalized",,"Cannot Update"

    'cancel the update event
    cancel = true

    'this undoes whatever the user just did
    sendkeys = "{ESC}"

    end if


    End Sub

    ----------------------------------------------

    Also, you might want to set the enabled and locked properties of the control "finalized" to No and Yes, respectively so users can see it but can't change it.

  6. #6
    Join Date
    Sep 2003
    Posts
    69
    Your idea seem to me very good but there still one little problem.

    When i switch the finalised radio on true i can't save anymore. The program see the finalized=true and would not accept any changes. I still can switch it on in the table but the problem is then that when you disable the finalised the program can accept any changes so you can change anything you want. I still look for a sollution for this problem. Maybe i can make the finalized invisible but i think there shoul be a better sollution for that. Maybe a password protected button Finalized.

    Any idea?

  7. #7
    Join Date
    Sep 2003
    Posts
    69
    Thanks you for helping me with followin little problems:


    1/ finalized is off and you want to switch it on. You click on finalized and save. Before saving, the program checks if the finalized is on. The finalized is on because we just switched it on and the program cancels the save procedure. => So it's impossible to finalize the invoice.

    2/ Supose we switch the finalized on in a table so we don't deal with the problem above. The invoice is finalized and can not be saved. You set finalized off and save. The program checks if finalizd if of or not and because it's of it will save the changes. I should be able to password protect this button so nobody can set it off.


    Private Sub invoice_date_BeforeUpdate(Cancel As Integer)

    if me![finalized]= -1 then '-1 means yes

    msgbox "You can't update Invoice Date because this invoice has been finalized"

    cancel = true

    sendkeys = "{ESC}" 'Doesn't work !!!

    end if

    End Sub

  8. #8
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by west
    Thanks you for helping me with followin little problems:


    1/ finalized is off and you want to switch it on. You click on finalized and save. Before saving, the program checks if the finalized is on. The finalized is on because we just switched it on and the program cancels the save procedure. => So it's impossible to finalize the invoice.

    2/ Supose we switch the finalized on in a table so we don't deal with the problem above. The invoice is finalized and can not be saved. You set finalized off and save. The program checks if finalizd if of or not and because it's of it will save the changes. I should be able to password protect this button so nobody can set it off.


    Private Sub invoice_date_BeforeUpdate(Cancel As Integer)

    if me![finalized]= -1 then '-1 means yes

    msgbox "You can't update Invoice Date because this invoice has been finalized"

    cancel = true

    sendkeys = "{ESC}" 'Doesn't work !!!

    end if

    End Sub
    I havn't been following the progress of this thread but just got an Email indicating it had progressed. Forgive me for not studying all the details of what you have done, but it still looks like you may have a problem.

    Just one thing that I do when I use Cancel = True for a beforeUpdate event. I also undo the current record or control. Me.Undo or if a control that supports it, ControlName.Undo. Solves some problems for me.

    Sorry if I jumped in when you already had it solved but on the off chance that I might be able to help, here I am.

    Best of luck

    Gregg

  9. #9
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I use the On current event for the form to determine the status of the record (I use a status field rather than a yes no, so I can see if it was registered, printed, entered, deleted, voided, completed, finalized, ...) If it is not Entered, then I set the me.allowedits = 0. That way, I don't have to have code all over the place or mess around with send keys

    but that is just my 2 cents

  10. #10
    Join Date
    Sep 2003
    Posts
    69
    Can you please be a little more specific maybe with a little example?
    Is On Current a function or one of the "properties" like Before update? (Sorry but i have a dutch access )

    And for Gregg, thank you for helping but i didn't find a sollution yet. With , thank you for helping me i meant : Thanks for helping me once more with the followin issues..... So please, if you are not out of ideas try to help me. Two sollutions is better than none.

  11. #11
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Form_onCurrent is an event in the Properties window of the form.

    Private Sub Form_OnCurrent()
    if Status = "Entered" then
    me.allowedits = -1
    else
    me.allowedits = 0
    end if

  12. #12
    Join Date
    Sep 2003
    Posts
    69
    Your idea is good but i still have one little problem. In my invoice form i have a subform with the detail of the invoice. When i set the me.allowedits on false for the head form i still can make changes to the subform.



    Private Sub Form_Current()
    If Status = "finalized" Then
    Me.AllowEdits = False
    Else
    Me.AllowEdits = True
    End If

    End Sub

    i have copied the same program code to the properties of the subform but it still make no difference.

  13. #13
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    in the subform, try if Parent!Status = ...

    In Access 97, the subform gets locked up when the master form has edits locked

  14. #14
    Join Date
    Oct 2003
    Location
    Tampa
    Posts
    10
    Why not use an unbound form...... with SAVE button.

    Use a recordset to "Browse" the records....make sure to include a status field (whether Finalized or Not)


    If edits are made to a record that has been finalized, you could display your MsgBox----

    If edits are made to a record that has NOT been finalized, create a Sub Procedure to SAVE the record.


    The down side of this method is that you have to have a SAVE button, or something, because unlike a BOUND form, the edits will not affect the table, only the Recordset.

  15. #15
    Join Date
    Sep 2003
    Posts
    69
    Originally posted by jmrSudbury
    in the subform, try if Parent!Status = ...

    In Access 97, the subform gets locked up when the master form has edits locked
    In the subform i tryed :

    Private Sub Form_Current()
    If Bestelbon!Status = "finalized" Then
    Me.AllowEdits = False
    Else
    Me.AllowEdits = True
    End If
    End Sub

    and got a fault 424 "object required". The whole second line is marked in yellow.
    Without the bestelbon! (which is the headform) i get no fault but it doesn't do what it's supposed to do. Very strange but if i switch the false and true in the subform, sometimes when i click on the headform on a finalized record he locks the subform. But i know that's not programming anymore but superstition.

    So:

    If i set the subform on "fulltime" me.allowedits = false it works but then i can never change the subform.

    If i use the if condition to check the status field in the headform it doesn't work. I get strange situation like when switching the fase and true sometimes works.

    If i just lock the headform , the subform won't follow.

    bestelbon!status won't work either

    Maybe if i declare status as a public variable instead of private ?

Posting Permissions

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