Form / sub Form not behaving as wanted

[Damob9K]

Hello all,

I had a form that was a split form type with the datasheet at the bottom ..
But I didn't like it as I couldn't get the main form to resize, it always filled the screen, I think because the datasheet was always wider than the screen.

So .. I redid the form, and used a subform instead.

The form itself is an output for an advanced search form that has multiple drop down selections, which then generates a SQL statement based on the users choices. This could generate 1 result or many.

The idea of the split form was that if there were many results, the user can scroll through them using up and down arrows I created on the main form, and then view / edit the detail in the top (form) part of the main form.

This all worked fine btw.

But now that I have redone this form using a subform at the bottom instead of the split form, the issue I have is that I don't know how to link the subform to the SQL output.
What I get now is if say I make selections in the search form that gives me 4 results, my output form tells me that I have 4 results, and the top (form) part shows the first record of the 4, but the subform will only show that 1 record, not all 4.

If I use the arrows to scroll down through the 4 records, they show in the subform, but that is not what I want.

I think it is do to with the main / child linking, but I don't know how to make it show all the results the SQL statement outputs.

Sorry if I have made it sound more confusing than it needs to be !!

Any ideas ?

Many thanks

Damob

 

[CJ_London]

It sounds like you have a form with three functions:

• search function
• summarise search results (in a subform)
• edit a record selected from search results

For this to work, all the search function controls must be unbound and each search control afterupdate event modifies the recordsource or filter of the search results subform - note that the subform linkchild and linkmaster properties should be blank

Then in the subform you need some code - perhaps on a control click event or the form current event which sets the filter of the mainform to the appropriate field.

Alternatively if you don't want code in the subform you can put the record details part of the main form into another form and use that as subform on the mainform - note the main form will now need to be unbound (no recordsource). So you now have two subforms, called say sfDetail and sfSearch.

Assuming your id key is called myPK In the sfDetail subformcontrol set the linkchild property to myPK and in the linkmaster put sfSearch.form.myPK

 

[Damob9K]

Hi CJ_London,

Thanks for the reply, I think I understand most of what you said !

Just to clarify (I knew my description was a bit iffy)

The search function is handled by a separate form, when the search button on that from is clicked it opens another form which shows the relevant records:

DoCmd.OpenForm "SearchDetailForm", , , sqlStatement

So the form "SearchDetailForm" is the one that was a split form but now has a subform in it.

And the record source is from the SQL statement i guess.

So how do I tell the subform to apply the filter from the form "SearchDetailForm" ?

Sorry I'm no expert, actually still a beginner still trying to learn, and if that is what you have explained in your reply, then I have not understood it well enough

Cheers

Damob

 

[CJ_London]

move your sqlstatement to the openargs parameter

docmd.OpenForm "SearchDetailForm",,,,,,sqlStatement

then in the SearchDetailForm form open event pass the openargs to the where condition of the subform

 me.sfSearch.form.filter=me.openargs
 me.sfSearch.form.filter=true

where sfSearch is the name of the subform control (not the name of the form which is its sourceobject)

Then in the search subform current event put the following code

 parent.filter="recID=" & recID
 parent.filter=true

where recID is the name of the field that identifies a specific record

 

[Damob9K]

Hi CJ_London,

Thanks for the reply and help, that all makes sense to me now.

I have tried to implement the changes but I think that my current set of forms that relate to that part of the DB are so messed up with previous attempts to get things to work, that I'm going to make a copy of the DB and re-do this part fresh - with your recommendations.

I have the mother of all headaches at the moment so am going to postpone staring at my screen till later on this evening or tomorrow.

Many thanks for your help, I will update on my progress as soon as I am up to the task !

Cheers

Damob