Can't Get Subform to Work

[marcondes]

I have an OrderForm that carries 3 subforms (SenderSub, ReceiverSub, and LocationSub as a subform of ReceiverSub).

Here's the ideal scenario: A customer places an order and wants us to ship the merchandise overseas. As we enter the receiver's address the LocationSub reads the State from ReceiverSub and brings up all records from that particular state. So far so good. From LocationSub I scroll back and forth using record navagation to find the address closest to the receiver.

The problem occurs when I try printing my order (thru a report) the LocationSub info doesn't show.

The second part of the problem is that when I open that record again the LocationSub is displaying the 1st record for the state listed in ReceiverSub.

Complicated enough?

Please help.

Paul

 

[tunsarod]

So how close do you aim to deliver. Nearest street, village or town?

The reason your Locator subform resets to the first item in the list is because it's a list, which at best is filtered for say a country or a postcode. To expect it to reflect the selection that was made earlier when creating a record is unrealistic in the context.

To put it another way, the Subform can only show a specific item of data if it's record source is a table of data that's related to the record in the main form. In your case you seem to be using the sub form to provide a range of selection options, like a list box, which may be reduced in number by filtering according to the value of a currently selected item the Receivers subform.

I think you should be using two forms: a data-entry form to accept new Order details. This form will have all the subforms to provide address selection options. And a second form, without subforms, that can provide record browsing so previously created records can be searched for and reviewed. Such a form does not need select items listed in a subform since once created there is no need to alter the record's details.

I suggest that your data-entry form should contain unbound fields to hold the information you need from the Locator subform. You could use code either in the subform or on the main form that pastes the data from the subform's current record into a field or fields on the main form. When all the data is ready I would have a Select query that gathers all the information from the form. The Report can then use the SELECT query as it's record source. Likewise an APPEND query can be used to paste the data from the form to the Orders table as a permanent record which can be browsed using the Orders browse form.

Alternatively your form data can be pasted as a record using the APPEND query and then using the OrderID as a key the Report could directly access the record.

If you have a field called say, Country, and it was bound to the Country field in the underlying Orders table and you used VBA to paste a value from the Locator subform into the Coutry field it'll cause an error because you are trying to paste data into a bound text box control. Thats' why I suggest using an APPEND query to pick up the data off the data-entry form and post it as a new record.

Hope this helps.
Rod