Setting subform SourceObject & RecordSource

[llkhoutx]

I'm programmatically setting subform sourceobject and sometimes the subform recordsource for ~75 subforms.

Case 1 - When only I set the subform's sourceobject with the recordsource preset, everything works fine.

Relevant code is: Me!sfrmDetail.SourceObject=sfrmName

Case 2 - When I set the subform and then it's recordsource programmatically I see no records although records exist. New records are added corrrectly, but when I switch suforms and come back to the original subform all records are missing in the subform, although they are in the underlying table.

In this latter case, I set the subform sourceobject, then the subform recordsource. Debugging the subform shows the correct record source, but no records are displayed.

Relevant code is: Me!sfrmDetail.SourceObject=sfrmName
Me!sfrmDetail.form.RecordSource="SELECT * FROM tbP29" & me!cboSCat-17 & " WHERE [FileID]=" & Forms!frmDetail!FileID

This appeared to be a requery problem, but requerying the subform doesn't give me anything.
Me!sfrmDetail!Refreshing, Repainting and Requery do not help.

Using the real sfrmName does not help.

I'm obviously missing something simple here.

Any help would be appreciated.

 

[pono1]

LLK,

Subforms have multiple personalities.

That is, when a form is a linked (or nested) subform it isn't a form. It's a control. But when you open a subform in Design view from the Access system window, it is a form, with form properties exposed in the prop dialog, including the recordsource property. Contrast this to when you right-click the edge of a subform that's embedded in a main form, selecting properties from the context menu and getting a different set of props, including the Source Object property but not including the RecordSource property.

Given this, I would guess Access is confused because you are pulling it in two different directions, asking it to play two roles simultaneously.

When you set the source object property you are saying this to Access: "Please bind my many-form (nested subform) to my one-form (master form) for me, using the linked child and master fields to pull related records." Access obeys. But then your code changes gears, asking Access to bind your subform to a query as though it weren't a control on a form but a form in its own right. Access balks.

That's my best guess as to what's happening.

And my best suggestion is this: Don't do that.

Regards,
Tim

 

[llkhoutx]

Got my original code working, i.e. dynamically setting subform SourceObject and subform RecordSource.

With all the screwing around I was doing to attempt to make it work, in one of my atempted fixes I'd bound the main form to a dummy table although it was not required and forgot about it.

That binding messed up the subform because I inadvertently created a conflicting FileID on the main form which wasn't patently obvious to me. I accidently stumbled onto it, removed it, and everything worked properly.

I knew that my problem was something simple. My stupidity!

Thank you for your comments.

 

[cheer]

I have created one main form, under this main form, there is one subform (subform name: SubForm_Production_Schedule) and one button. When this button is clicked, I need the data to display on subform base on certain criteria. Below is the part of the code under the button click event

Me!SubForm_Production_Schedule.SourceObject = "Table.Production_Schedule"

Me!SubForm_Production_Schedule.Form.RecordSource = "select * from Production_Schedule where Login='" & txtOperator.Value & "'"

Me!SubForm_Production_Schedule.Requery

Me.Refresh

Me.Repaint

However, there isn't any data appear after the button is clicked. When I search back the table Production_Schedule, in fact there are more than 100 rows of data available. The 2nd time I click the same button, data only appear in subform.

Anyone can advise the what goes wrong to the above coding ? I am using the MS Access 2000.

 

[John Big Booty]

I think you are missing some vital parts in your reference to the subform;

Me!SubForm_Production_Schedule[B][COLOR="Red"].From[/COLOR][/B].SourceObject = "Table.Production_Schedule"

Me!SubForm_Production_Schedule.Form.RecordSource = "select * from Production_Schedule where Login='" & txtOperator.Value & "'"

Me!SubForm_Production_Schedule[B][COLOR="Red"].From[/COLOR][/B].Requery

Bookmark this link for future reference, it gives you the correct syntax for referring to Sub Forms and their controls

 

[cheer]

I have tried putting the "form" but there is an error prompted. Check the link, there isn't any "form" word .

 

[John Big Booty]

Sorry, my bad However you will need the .Form in the requery line.

 

[cheer]

I have tried, it doesn't work.

My new code as below

Me!SubForm_Production_Schedule.SourceObject = "SubForm_Production_Schedule"

Me!SubForm_Production_Schedule.Form.RecordSource = "select * " & _
"from Production_Schedule where Login='" & txtOperator.Value & "'"

Me!SubForm_Production_Schedule.Form.Refresh

Me!SubForm_Production_Schedule.Form.Repaint

Me!SubForm_Production_Schedule.Form.Requery

Me!SubForm_Production_Schedule.Form.Recalc

Me.Refresh

Me.Repaint

Me.Requery

I notice when I press the button, the subform is refresh with data, however, in less than 1 second, the subform become empty again. Why ?

 

[John Big Booty]

Any chance you could post a copy of your DB?

 

[cheer]

No problem.

Focus to the form name frmProduction_Schedule

 

[Galaxiom]

It sounds like the data is loading then being filtered by something.

Perhaps the original form is loading and the data disappears when the record source is changed because there is a problem with that part. Try commenting out the record source line and see how the behaviour changes.

Do you have Master Link Field and Child Link Field set in the subform control properties? That might cause this effect if the field were not valid.

Also you would normally be better off starting a new thread rather then resurecting one from six years ago. Many of us don't always take so much notice of a thread that has a lot of replies.

 

[cheer]

It sounds like the data is loading then being filtered by something.

Perhaps the original form is loading and the data disappears when the record source is changed because there is a problem with that part. Try commenting out the record source line and see how the behaviour changes.

Do you have Master Link Field and Child Link Field set in the subform control properties? That might cause this effect if the field were not valid.

Also you would normally be better off starting a new thread rather then resurecting one from six years ago. Many of us don't always take so much notice of a thread that has a lot of replies.

Thanks for the advice. Master Link and Child Link field is set nothing, I don't use this.

You also mention filter which I do not have the coding too. You can download the code I've attached above.

I have comment the recordsource, it is same as earlier.

Any other advice ?

 

[Galaxiom]

I am unable to get to any of the design in your sample.

 

[cheer]

I am unable to get to any of the design in your sample.

Press Shift key, don't release.

Double click the file and wait till the whole thing is COMPLETELY loaded.

Look for the form frmProduction_Schedule

 

[Galaxiom]

I can't spot any problems but the code is quite convoluted and i don't have time to look too far. Can only come up with one idea.

Have you checked the user login is spelt exactly the same as the names you expect to pull from Production_Schedule.Login field. No trailing spaces creeping in somewhere for example.

I would recommend you change to storing the users with an ID number rather than a username. Of course display the name but it is faster and easier to work with a numeric ID in the production records.

Not 100 percent sure but I think the requery is automatic if you change a record source.

Also I can't see why you would need to reset the form when you close it. If it is because it prompts for saving changes then use a vbSaveNo argument on the Close command.

 

[cheer]

I have detected the problem, I should include the ResetSubForm under the Form_load event. Anyway, thanks