Subform/Subform Control (Access 2000)

[swan]

I have a main form (DeliverySlipMain), with a subform (DeliverySlipSub22) with an embedded subform (DeliverySlipSub22a). My problem is that a control (cont) that is on the subform (DeliverySlipSub22) requires the value within the control (cont) of the embedded subform (DeliverySlipSub22a). This is accomplished by the following coding on the "Got Focus" of the field on the subform (DeliverySlipSub22):

Private Sub Cont_GotFocus()
Forms![DeliverySlipMain]![DeliverySlipSub22].Form![Cont] = Forms![DeliverySlipMain]![DeliverySlipSub22].Form![DeliverySlipSub22a].Form![Cont]

End Sub

This information is required because the information contained within the subform (DeliverySlipSub22a) contains the default info set up for the customer, but I have to allow the shipper to override this information to change and save with the delivery record without comprising the default or to allow the default.

When tabbing into the field, the following error is received:

Run-time error

[johnhutchison]

If you do what you want in the onfocus event, the value will be reset anytime you revisit the control.

So if you use the same form for reviewing old deliveries, you could easily lose the actual data.


If you want this to be a default value, try setting the default value of the control. Display the properties box and navigate to the right control. Go to the default value property, then click the three dots that appear to display the expression builder. Navigate through the loaded forms to find the control on the right subform, and the expression builder will write the expression for you.

[swan]

True as you said, the record could be reset if the user tabbed into the field again. The majority of the shipments are using the default set up, but there are at times when it has to be changed and this information is then saved to the deliverydetails table and can be viewed with full documentation as to what was actually done at the time of shipment.

But by using the On-Focus, it allows for the default to be used, and saved to the current record, or to be over-ridden and changed to allow for the current shipment if changes need to be made (half loads, container changes, etc)

I will say that there are actually 4 fields, using all the same coding representing the container type, container number, tare weight and unit weight. When the shipping documents are printed the information is calculated and consolidated and reflected on the documents.

Since the error is represented on all 4 fields, finding the correct coding on 1 can be carried over to all of them.

As for setting the default value, it does not work. - Any other suggestions. Thank you.

[johnhutchison]

If the default does not work, then you probably need code.

So it only happens once, put a test for isnull. (for this to work, the field cannot have a defult value.)

The word me refers to the current form, so you can use that to shorten the code considerably.

Private Sub Cont_GotFocus()
edited by john to remove a 'not' that should not be there on the next line
if isnull(me![cont]) then
me![Cont] = me![DeliverySlipSub22a]![Cont]
end if

The inner subform is called DeliverySlipSub22a but what is the subform control called?

If you have the form in design view (with the properties box on display) and click once on a subform, the properties box tells you about the subform control. One of its properties is its source object. This tells you what subform is actually displayed int he subofrm control. The subform and the subform control may not have the same name.
I have just tested the above code, and it works as is when DeliverySlipSub22a is the name of the subform control.

[swan]

Using your test code, it returned Me![Cont]=Null error code. I think if you don't mind, I will zip a small file so that you can actually see what is happening? Thanks again for your input.

[HansV]

Yes, you can attach a zipped copy of your database so that John or others can investigate the problem directly. See <post#=401925>post 401925</post#> for instructions.

[swan]

Here is a small zipped version of my problem. Thank you.

[HansV]

Your subform DeliverySlipSub22 is a datasheet, so the sub-subform DeliverySlipSub22a acts as a subdatasheet. You only have access to it when the subdatasheet is expanded. So you could save DeliverySlipSub22 with all subdatasheets expanded, or force them to be expanded (RunCommand acCmdSubdatasheetExpandAll). The code will work then.

But I don't like the design. If the use clicks in Cont, it is filled with the default value. The user can then edit this. So far so good. But if the user happens to click in Cont again, the edit will be undone because the code enters the default value again.
I would use the After Update event of JobID and BatchNo. If both are filled in, set the value of NoCont, Cont, Tare and Unit:

Private Sub Batch_No_AfterUpdate()
SetValues
End Sub

Private Sub JobID_AfterUpdate()
SetValues
End Sub

Private Sub SetValues()
If IsNull(Me.Batch_No) Or IsNull(Me.JobID) Then
Me.NoCont = Null
Me.Cont = Null
Me.Tare = Null
Me.Unit = Null
Else
Me.NoCont = DLookup("NoCont", "DeliverySlipDetailsa", _
"Job=" & Chr(34) & Me.Job & Chr(34))
Me.Cont = DLookup("Cont", "DeliverySlipDetailsa", _
"Job=" & Chr(34) & Me.Job & Chr(34))
Me.Tare = DLookup("Tare", "DeliverySlipDetailsa", _
"Job=" & Chr(34) & Me.Job & Chr(34))
Me.Unit = DLookup("Unit", "DeliverySlipDetailsa", _
"Job=" & Chr(34) & Me.Job & Chr(34))
End If
End Sub

[swan]

Thank you. I took your suggestion about the set values. Everything works great. Again - thank you.