Can't stop user from editing form

[theSizz]

I’m having a problem getting the AllowEdits property to work.

Here is the issue. I have a form - frmOrders . The forms Form_Current event has the following line of code:

AllowEdits = False

This works fine and when the user tries to edit a field on the form he is unable to. However, if the user clicks in or enters the control cboLookup1 and then navigates to any other control on the form he is able to edit the form. In other words the form has now reset itself to allow edits in any and all fields.

There are 4 control properties named cboLookup1, cboLookup2, cboLookup3, and cboLookup4. If the user clicks or enters any one of these fields and then exits to another field the form then allows edits.

There is code associated with these fields.

Here is the code that is in the LostFocus event on the combo box control cboLookUp1.

Private Sub cboLookUp1_LostFocus()

    Dim varExtCost As Variant
    Dim varItem As Variant
    Dim varCost As Variant
    
    varItem = DLookup("descript", "tblItems", "[ItemNo]=cbolookup1")
    If (Not IsNull(cboLookUp1)) Then Me![txtItem1] = varItem
    varCost = DLookup("cost", "tblItems", "[ItemNo]=cbolookup1")
    If (Not IsNull(cboLookUp1)) Then Me![txtCost1] = varCost
    

    varExtCost = Nz(txtQty1, 0) * Nz(txtCost1, 0)
    If (Not IsNull(txtQty1)) Then Me![txtExt1] = varExtCost
    
End Sub

The other 3 controls have the same code.

I know it has something to do with this code because I’ve removed the code and the form will stay read only if this code does not fire. Yes, I’ve tried putting AllowEdits = False statements in the LostFocus events and that doesn’t help.

If anyone can offer any help it would be greatly appreciated.
I've attached the file in case anyone would like to recreate the issue.
Thanks for your time.

 

[Oldsoftboss]

After looking at you Db, you need to normalise your tables, then what you are trying to achive would need virtually NO code at all.

Dave

 

[theSizz]

Sorry, but I don't understand how normalizing the table would solve the edit issue.

 

[boblarson]

As mentioned on Utter Access, the solution was to put it on the After Update event of the combo box.

As for the normalization - it might, or might not have anything to do with it, but if it isn't normalized you should do so as you will start finding yourself trying to do band-aid solutions for various problems that would not appear should it be normalized.

 

[Oldsoftboss]

As mentioned on Utter Access....

I think you need to get outdoors a bit more.

 

[Oldsoftboss]

This is a start to normalisation (well the subform anyway). The fields on the main table that can calculate a total should. You should try to avoid storing calculated values wherever possible.

The Invoice Amount now calculates from the subform.
Hopefully you will be able to use calculated fields to do all the work for you.

In the example I have put a button to lock/edit the forms.

HTH
Dave

 

[theSizz]

thanks for the example oldsoftboss.
I will review the db that you attached and try to implement the changes in the db I am working on.
Once again thanks for your time and help.

 

[theSizz]

Hi softoldboss
I’ve been working with the example file you uploaded. Thanks for the lessons.
I’ve got a few questions that I hope you will have time to answer.
In the form “frmOrders” OnCurrent event the code reads”

Private Sub Form_Current()
On Error GoTo Err_Form_Current

    Me.AllowEdits = False
    Me.sfmOrderItems.Form.AllowAdditions = False
    Me.sfmOrderItems.Form.AllowAdditions = False
    Me.Command81.Caption = "Edit"

Exit_Form_Current:
   Exit Sub

Err_Form_Current:
    MsgBox Err.Description, , " UniformDeduct"
    Resume Exit_Form_Current

    
End Sub

Is that repeated line “ Me.sfmOrderItems.Form.AllowAdditions = False “ a typo and should read
“Me.sfmOrderItems.Form.AllowEdits = False” ?

Also when I try to enter a new record the form “frmOrders” won’t accept data entry into the sub form. In other words I can’t enter any quantities for items. I checked to see if the controls were locked but it doesn’t appear that way. The other controls on the main form will accept data entry when entering a new record. If I click the lock/edit button then I can enter data into the sub form but I’m sure this is not how you intended it to work.

Also is there a way to make the tab order go from the main form to the sub form and then back to the main form. The reason being is that the way the tab order works is not the natural sequence for data entry and once the cursor goes through the sub form a new record is brought up.

Lastly, I’m having trouble updating the balance due control. I’ve wrote code that will update the control when the user edits any field on the main form, but I can’t figure out how to update the balance due when the user edits a field on the sub form.

Once again I really appreciate your time and any help you can offer would be appreciated.
I am uploading a copy of your file with the work I’ve done so far.

Thanks in advance

 

[Oldsoftboss]

Quote...
Is that repeated line “ Me.sfmOrderItems.Form.AllowAdditions = False “ a typo and should read
“Me.sfmOrderItems.Form.AllowEdits = False” ?

Yes, typo, you are correct.

Quote...
If I click the lock/edit button then I can enter data into the sub form but I’m sure this is not how you intended it to work.

Yes it is. It's your Db, so simply set the subforms AllowAdditions Property to yes and remove the code that locks that part.

Quote...
I’ve wrote code that will update the control when the user edits any field on the main form, but I can’t figure out how to update the balance due when the user edits a field on the sub form.

You shouldn't need any code with calculated controls. As soon as you move the focus it will update.

I will have a look and get back to you.

 

[Oldsoftboss]

Try this...

I wasn't sure whether a deduct should be added or subtracted, so you can edit the balance due as you wish.

Dave

PS. I would put the deduct and payment fields in subforms also. This way you can have as many as you like. (Part of normalization)

 

[theSizz]

Hi Dave,
I downloaded the Db you uploaded this morning. Thank you very much.

Here’s the reason why I store calculated controls such as Balance Due in the DB.

I have another form that searches the DB for all employees that owe a balance.
If Balance due was a calculated balance and not included in the DB as a field in a record, how would I search for outstanding balances that are owed?

I’m guessing I would create a record set but I have no idea how to do this or even what the code should look like. Any suggestions or help you can offer?

I noticed that you removed the BeforeUpdate event code that did the lookup for the employee’s first and last name fields on the frmOrders. I assume it would be better to do this on the form. So I entered the lookup code directly into each control but it doesn’t populate the controls. If I put the form in the design view mode and then put it back into the form view mode it updates the fields any idea what’s causing this. I've tabbed through the record and come back to it after it's saved and it still doesn't populate the fields.

I'm really trying to understand normalization and how to form DB relationships. The examples you've provided have offered me some insight and I am trying to understand this.

Thanks in advance for any help or suggestions you can offer.
I've attached the DB version 6 if you can look at it I would appreciate it.

 

[theSizz]

Does anybody know why the lookup code located in frmOrders in the text controls named txtFirst and txtLast don't work in the attached file?

Here's my question from the previous message.

So I entered the lookup code directly into each control but it doesn’t populate the controls. If I put the form in the design view mode and then put it back into the form view mode it updates the fields any idea what’s causing this. I've tabbed through the record and come back to it after it's saved and it still doesn't populate the fields.