changing subform's RecordSource errors out...

[blarson0]

Hi! I am trying to change the RecordSource for a subform based on what the user chooses from a combo box. code looks like this:

Set rst = db.OpenRecordset("SELECT LeftAssy FROM [Job Q] WHERE ( JOB = '" & Me![Combo0] & "' )&quot
If (rst!LeftAssy = "78364&quot Then
Me.RecordSource = "BTB Pick List Q"
sqlst = &quot;SELECT [Pick List].JOB, [Pick List].QNTY, Classification.[PART NO], Classification.DESCRIPTION, Classification.Class, Classification.Link, Classification.OP FROM [Pick List] INNER JOIN Classification ON [Pick List].COMPONENT = Classification.[PART NO] WHERE ((([Pick List].QNTY)<>0) AND ((Classification.Class)=&quot; & Chr$(34) & &quot;Major&quot; & Chr$(34) & &quot AND ((Classification.OP)=10)) OR ((([Pick List].QNTY)<>0) AND ((Classification.Class)=&quot; & Chr$(34) & &quot;Subassy&quot; & Chr$(34) & &quot AND ((Classification.Link)<>[Part No]) AND ((Classification.OP)=10));&quot;
ElseIf (rst!LeftAssy = &quot;70160&quot Then
Me.RecordSource = &quot;160 Pick List Q&quot;
sqlst = &quot;SELECT [Pick List].JOB, [Pick List].QNTY, [160 Classification].[PART NO], [160 Classification].DESCRIPTION, [160 Classification].Class, [160 Classification].Link, [160 Classification].OP FROM [Pick List] INNER JOIN [160 Classification] ON [Pick List].COMPONENT = [160 Classification].[PART NO] WHERE ((([Pick List].QNTY)<>0) AND (([160 Classification].Class)=&quot; & Chr$(34) & &quot;Major&quot; & Chr$(34) & &quot AND (([160 Classification].OP)=10)) OR ((([Pick List].QNTY)<>0) AND (([160 Classification].Class)=&quot; & Chr$(34) & &quot;Subassy&quot; & Chr$(34) & &quot AND (([160 Classification].Link)<>[Part No]) AND (([160 Classification].OP)=10));&quot;
ElseIf (rst!LeftAssy = &quot;70360&quot Then
Me.RecordSource = &quot;360 Pick List Q&quot;
sqlst = &quot;SELECT [Pick List].JOB, [Pick List].QNTY, [360 Classification].[PART NO], [360 Classification].DESCRIPTION, [360 Classification].Class, [360 Classification].Link, [360 Classification].OP FROM [Pick List] INNER JOIN [360 Classification] ON [Pick List].COMPONENT = [360 Classification].[PART NO] WHERE ((([Pick List].QNTY)<>0) AND (([360 Classification].Class)=&quot; & Chr$(34) & &quot;Major&quot; & Chr$(34) & &quot AND (([360 Classification].OP)=10)) OR ((([Pick List].QNTY)<>0) AND (([360 Classification].Class)=&quot; & Chr$(34) & &quot;Subassy&quot; & Chr$(34) & &quot AND (([360 Classification].Link)<>[Part No]) AND (([360 Classification].OP)=10));&quot;
End If
[Forms]![major 10 fm].RecordSource = sqlst


I am getting an error on the [Forms]![major 10 fm].RecordSource line. Error is #2450: Can't find the form 'Major 10 fm' refered to in code.

Any ideas?

Thanks!

-Brad

 

[thornmastr]

Remember, you are dealing with a record source for a subform.
You do not refer directly to the form, but to the subform container on the main form. The subform container usually is defined as childXXXX where XXXX is a number. I usually set a child forms container to child0, or, if it is a subform within a subform, child1.

To change the record source, the syntax is

Me.child0.SourceObject = &quot;your source object&quot; Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[blarson0]

Thanks for the reply Robert. I don't think that SourceObject is the variable I am looking for. I need to change the data that the subform uses, but keep the same subform. I guess I could make a different subform for each set of data, but it would be much better if I could do it dynamically.
Any thoughts?

Thanks!

-Brad

 

[thornmastr]

Brad,

Actually, your terminology is fine. But, before we get into full detail here I don’t think you mentioned what version of Access you are using. If you are using A2K or above it is not a problem. A97 has a problem changing the source object or record set associated with an active form.

So, since this explanation may get a bit lengthy, let me know what version of Access you are using. If you are using A97, if I still had A97 I would be willing to help you, but I no longer have it, so I will defer to someone who can actually prove their actions <sigh>. If you have at least A2K, we can do this.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[blarson0]

unfortuantly, we are still using 97... I think I will make multiple subforms. It will only be 6 more, not that big of a deal. Thanks for the help!

-Brad