programmatically form-subform (1 Viewer)

[prodigy]

Dear friend,
I have been looking all over this forum for a topic like "form-subform". I was trying to catch up with the idea if a form-subform can be made to work programmatically.
I know on how to make a "form-subform" work when they are bound but what if they are unbound.
1) I have an unbound masterForm (FORM1) that gets its recordset on the Load-Event and populates the textBoxes (unbound) accordingly.
2) I also have an unbound subForm (FORM2) that gets its recordsource on the Load-Event. The recordsource is an SQL SELECT statement with criteria (eg. masterForm!textboxID).
3) I have an unbound childSubForm (FORM3) on the masterForm that gets its sourceObject as the subForm (FORM2) on the Load-Event of the masterForm.
4) I have attempted to create a link between the masterForm (FORM1) and subForm (FORM2) in the Load-event programmatically (eg. FORM3.LinkMasterField="ID", FORM3.LinkChildField="ID")
5) On the onCurrent-Event of the masterForm (FORM1) the SQL statement is assigned to the RecordSource of the subForm (FORM2).
6) Its working except when I navigate to the next record on the masterForm (FORM1) the subForm (FORM2) does not seem to display the corresponding record(s). The subForm (FORM2) is blank.

It seems that everything is in place, however, its not working. Am sure that something is wrong that I am not able to find out. Need help in this regard.

Regards,
Prodigy...

 

[Steve R.]

Yes. You have two options for working with a subform. One is from the mainform, but you will have to read-up on how to reference the controls in the subform. The other is to place code in the subform, but now you will need to read-up on how to reference the controls on the mainform from the subform.

From your question, it appears that you need to read-up on the FILTER property where the subform appears to be a filtered set based on a control of the mainform.

 

[prodigy]

pls be elaborate

Dear Ortaias,
Thanks for your time. I am sure that you know the answer to my query. However, I am afraid that I am not able to understand your resolution. If you could elaborately explain on how to go about or if you can just write me the steps.
Regards,
Prodigy.

 

[Pat Hartman]

The master/child links work with bound forms for unbound forms, you are entirely on your own. Since Access' biggest strength is bound forms, why did you choose to roll your own with unbound forms?

 

[prodigy]

Idea behind programmatical form-subform

Hi,
I had an idea that if my forms are bounded with tables then my forms would be heavy. In client-server type scenario when the forms are bounded then it pulls the entire table underlying through the netwok creating tracffic and loads of overhead and slowing the application. Thats the reason I was attempting to create things programmatically so that only the required record/data comes down through the network wire and not the entire table.
Regards,
Prodigy.

 

[Pat Hartman]

Best practice is to use forms obunt to queries that use selection criteria. That way, you get the minimum amount of data plus the maximum use of Access built in features.

Frequently, I create search forms that allow users to find the records they are looking for. The form builds a query based on the controls used to select the records. The query returns ONLY the selected rows. Then I use the where argument of the OpenForm method to open the form to a single row. Even though the update form is bound to an unqualified query, Jet appends the "where" criteria from the OpenForm method to the form's RecordSource query and passes that to the server.

Jet makes every effort to "pass through" as much as possible to the server for processing. Take a look at the optimizing Access for client/server article in the MSDN library to get an understanding of adjustments you need to make in your approach to make an efficient client/server app.