update rowsource in subform in vba

[pb21]

I have some code on the after update event of a combo, the combo gets a pk value of a group.

Code:
Forms![ftran query subform].RowSource = "SELECT FTRAN.FtranID, FTRAN.[AGENT-DSN], FTRAN.[GROUP-DSN], FTRAN.Date, FTRAN.Amount, FTRAN.[F Type], FTRAN.Cat From FTRAN WHERE (((FTRAN.[GROUP-DSN])=" & Me.CbGrpGroup.Value & "));"
Me.FTRAN_Query_subform.Requery


How do i successfully update the rowsource of the subform from the cb update event so that it only shows records that relate to the pk selected in the combo?. [group-dsn] in the table is the group id.

many regards in advance.
Peter

 

[namliam]

Forms![ftran query subform].form.requery
or
Forms![ftran query subform].requery

Not sure.

But if you are working from the combo on the main form why use Forms?

Me. should be able to get there as well and is much easier as it will complete your command ...

 

[pb21]

I have two combos the first is for the user to select the agent and then the second populates with group ids from that agent. The subform shows transactional records against the selected group, so i need to create a rowsouce (query string) that will amend the subform records dependent on the group value in the combo box.

Even with the amendment it says it cannot find the form (subform).

regards

 

[namliam]

Thats because your going Forms!Subform, which doesnt excist...

Me. or Forms!Mainform!subform (or something like that) should work...

Try building an expresion in a query with your forms open, now click your way to the form you need and copy that string that is build, it should look like my second thing but I am not 100% sure.

 

[pb21]

I am getting confused excuse my stupidity

i have:
stringsql = "SELECT FTRAN.FtranID, FTRAN.[AGENT-DSN], FTRAN.[GROUP-DSN], FTRAN.Date, FTRAN.Amount, FTRAN.[F Type], FTRAN.Cat From FTRAN WHERE (((FTRAN.[GROUP-DSN])=" & Me.CbGrpGroup.Value & "));"
Forms![frmgroupftraninput]![ftran query subform].RowSource = stringsql
Me.FTRAN_Query_subform.Requery

this gives error:
object does not support this property

 

[Rich]

Forms![MainForm]![SubForm].Form.RecordSource = "SELECT

 

[namliam]

Forms![frmgroupftraninput]![ftran query subform].RowSource = stringsql
Me.FTRAN_Query_subform.Requery

Nothing stupid about it. Rowsource is a form property, like Rich allready pointed out...

Instead of doing forms!... bla bla you could/should do
Me.FTRAN_Query_subform.Form.RowSource =

Should be slightly faster, because Access doesnt have to search for the form in the forms container. It knows it is allready open and active, alltho the difference is bearly measurable....