Lock Form based on value of combo box

[banpreet]

Hi guys, I have three forms. One form the customer fills out, the status of the form is automatically "Draft" and can not be changed by them. The second form, an approver looks over it and changes the form status from "Draft" to "Pending" from a combo box. Then the third form, there is another approver that signs the form and changes the status from "Pending" to "Final". How do I make it that when the value of the "Pending" and "Final" are chosen from the second and third form, lock the first form so no edits can be made to anything. I have this code that seemed to work at first but doesn't anymore. I put this code in the on load event.

If Me.Status = "Pending" Or Me.Status = "Final" Then
Forms!TR_Form.AllowEdits = False

End If

[ranman256]

in the table that has the combo choices, add a LOCK field T/F, add this field to the combo box,(2 columns)
you can hide this column by setting the column widths len=0: 1;0
when the user picks it, lock the form

sub combo_afterupdate()
  me.locked = combo.column(1)       'in vb code columns start with zero
end sub

[Bulzie]

Thought I had seen this same post before. https://www.accessforums.net/showthread.php?t=63304

So what changed to make that code not work anymore?

[banpreet]

I'm not sure but I added a revision button to the first form for customers field that makes a copy of the form and makes a copy of a record and adds to the table and changes the status from Final to Draft automatically, so the customer can fill out what ever he needs to change after its been approved from the approval process but the form needs change because mistakes happen.

[banpreet]

ranman, will the lock field lock only the first form, I do not want to lock the second and third form? Thanks

[banpreet]

what does the T/F mean too? Thanks

[ranman256]

it only locks the form you are on.
the T/F is a true/false field in the table where the combo values come from
[option],[lock]
pending, true
final, true
test, false

the lock value is tied to the combo option.

[banpreet]

thanks for that but that wouldn't work for me because the second and third form can't be locked, only the first form no edits should be made but they should have access to buttons.

[banpreet]

Does anybody else have any other code that would work? Thanks!

[Bulzie]

Seems to me your Approver 1 or 2 (2nd or third forms) should change the status back to Draft if they see errors and need the customer to change something. If you allow the customer to hit a button to then edit the information, you are defeating the propose of locking down the data. Unless I am not understanding what you are saying which is probably true, the key trigger to say if Customer Form1 can be edited is the status. If Draft it can be edited, if not then it cannot be edited. Is that still the case? If so then that code should still work. If on that form the customer can select multiple records, then that code might need to go in the OnCurrent event as you scroll through records, it would check to see the status of each record.

[banpreet]

Well customer fills out form one in draft status and sends it the approver, the approver changes it to pending to send it to the other approver who it will change it to final. Once in pending it starts the approval process so if a cusomter wants to edit information he can not. After its approved the form, people can view it in report and do their test. But sometimes there will be changes such as an estimate, and then since it was already approved, the customer will get an email that he can revise the form, so the user goes into his form and clicks the revise button making a copy of the form and record and adding 1 to the revision field and changing the status to draft byitself without waiting for the approver, so then the customer will have to go through the same approval process again. Company standards that we need to keep record of revisions, so under the same Test, there can be 1, 2, 3, or 4 revisions for the same test but making new records with the same id.

[banpreet]

Ok I switch the code on current and I think it worked! Thanks!

[ranman256]

Other forms are not affected