I have been battling with a Access form and subform.

The information on the master form is brought in via visual basic (with a combo box as the catalist). There is a subform which I need to
run through a sql query and I do this through the querydefs function, the problem I have is that the query updates fine but regardles of what I try I can not get the subform to update with the revised information in the query.

I have been looking at the forums and tried many ideas, alas none have worked.
If anyone can help I will be very grateful.

After you update the data on the main form, try using:

Me.subformname.SourceObject = MySQLCriteria

Where you replace "subformname" with your subform's real name and MySQLCriteria is a string containing your SQL statement.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for the advice, just a little unclear as to how to dispaly the sql statement.

I create the query via: CurrentDb.QueryDefs("showsales").SQL = strsql

this is done through a function called salesquery, which compiles the string.

So I have used: (in line with your suggestion) and it does not appear to work.
Me.showsalesfrm.SourceObject = salesquery(strsql)

Assuming that you can't relate the Subform to the parent through the sub form Property Sheet, Data tab, Link Child Fields and Link Master Fields properties, then you will have to requery the specific instance of the subform. Please note that a subform may be open on one or more instances of a single parent form or even a variety of different parent forms so it is not enough to specify a requery of the form that is placed as a subform. You must specify the specific instance of the subform that needs to be requeried. Using a single saved querydef is not practical in an environment where the form may be open on more than one place at one time. You would then be better off to directly set the RecordSource sql string (2nd syntax below).

Assume the source object form is named 'frmSales' and the name you have given the subform control on a parent form is 'sfrmSales', you can requery the subform with code from the parent with the following syntax:

Me.sfrmSales.Form.Requery

Generally speaking, assigning the recordsource property will also force a requery. An alternate syntax would be:

Me.sfrmSales.Form.RecordSource = CurrentDb.QueryDefs("showsales").SQL = strsql

or

Me.sfrmSales.Form.RecordSource = strsql


or

Me.sfrmSales.Form.RecordSource = "showsales"

where 'showsales' is the name of the querydef

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Is SalesQuery is a function that creates an SQL string based on strSQL? If so, this should work provided that the function type that SalesQuery puts out is string and is a valid SQL query. Also, are you putting it on the Form's AfterUpdate event?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

thanks for the suggestions guys, i have got round the problem by using the Me.subformname.SourceObject suggestion and just pulling a different form in the subform box.

Cheers