Sub-Form Locking HELP!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form "Invoice" that contains 2 subforms "Payment Details" (keeps a
record of how people are paying - it has a "PaymentType" & "PaymentAmount") &
"Sales Details" (keeps a record of what people are buying). I want to lock
the "Sales Details" when "Payment Details" have been selected and entered -
so that sales personnel cannot go back and edit or delete the sale. I have
tried:
In the after_update of the PaymentAmount:
If PaymentAmount > 0 Then
Forms!SalesDetails.Locked = True
But that did not seem to work - it says it can't find the form. I tried
replacing it with me, form, etc. but it does not seem to work - any Advise?
 
Hi Sar,

Firstly I would recommen using the Me.ControlName method to come up with
Me.PaymentAmount. The control name can then be selected from the list that
appears and makes typos less likely. Secondly, the subform control is
contained within the Invoices form and has to be referred to as such so you
have to refer to the subform control Locked property as follows:

Forms!Invoice.SalesDetails.Locked

The whole line should then read as:

If Me.PaymentAmount > 0 Then Forms!Invoice.SalesDetails.Locked = True

You could also use:

Me.Parent.SalesDetails.Locked from within a subform to refer to the subform
lockec property.

Hope this helps.

accessjunky
 
Hi Sar,

Apologies I missed a space out. The line should have read:

If Me.PaymentAmount > 0 Then Forms!Invoice.[Sales Details].Locked = True

However, this alone will probably not work as you want. There are 2 issues:

1. If the Sales Details subform is locked it will remain locked even if the
PaymentAmount is set back to zero.

So this can be solved by attaching the following to the After Update event
of the PaymentAmount field:

If Nz(Me.PaymentAmount) > 0 Then
Forms!Invoice.[Sales Details].Locked = True
Else
Forms!Invoice.[Sales Details].Locked = False
End If

------
The Nz function evaluates a null, should it occur, as zero and avoids a
possible error.

A single line alternative is to the above code is:

Forms!Invoice.[Sales Details].Locked = Nz(Me.PaymentAmount) > 0

------
Here Nz(Me.PaymentAmount) > 0 evaluates to either true or false and sets the
Locked property correspondingly.

2. If If the Sales Details subform is locked on one Invoice record then it
will continue to be locked for all other Invoices until it is unlocked again.
We need to check the value of the PaymentAmount each time a different Invoice
record gets the focus. Attach the following to the On Current event of the
Invoice form:

If Nz(Me.Payment_Details!PaymentAmount) > 0 Then
Forms!Invoice.[Sales Details].Locked = True
Else
Forms!Invoice.[Sales Details].Locked = False
End If

-----
Again a single line option is:

Forms!Invoice.[Sales Details].Locked = Nz(Me.Payment_Details!PaymentAmount)
0
Click to expand...

-----
The only issue with this second segment of code is where all of the
following occurs:

1. There is more than one Payment record
2. The first listed PaymentAmount = 0 (which is the one that will have the
focus and be tested on moving to another Invoice record)
3. Subsequent Payment records for that Invoice have non-zero values.

The Payment Details subform would not be locked in this case.

If this is an issue then you may need to check the total PaymenyAmount for
the Invoice. There are 2 feasible ways of doing this - less preferable, less
efficient but easier to understand is the DSum function. The preferred
method would be o use a recordset.

However, if the above solution is sufficient there may be no need to pursue
those routes. Let me know.

Good Luck

accesjunky.
 
Back
Top