Change Subform Query RecordSource from Combo

[klopstan]

Hi Guys,
was just wondering if anyone can provide some suggestions for a problem I have. I don't know if what I'm trying to do is even possible, as I can't find a specific example on the forums or in my Access 2000 Bible.

I have a Main form (frmQuery) and Subform (frmQuerySub).

The Main Form has a combo box (cboQuery) which has a value list of premade querys (qryAllContracts, qryAllByDate etc bound to column 1 as a value list). What I would like to do is change the recordsource of the subform based on the query selected from the main form combo.

The AfterUpdate of the combo box is set to the following:

Private Sub cboQuery_AfterUpdate()
​

Me.frmQuery!frmQuerySub.Recordsource = Me.cboQuery

EndSub

​

However I receive "Compile Error - Method or data member not found" with me.frmQuery being higlighted in the vba debugger.

Any suggestions would be greatly appreciated, and hopefully as I build my knowledge I'll be able to return the favour to other forum members

 

[klopstan]

Ok, I managed to figure out how to change the subform recordsource by using the following:

Private Sub cboQuery_AfterUpdate()
If Not IsNull(Me.cboQuery) Then
Me.frmQuerySub.Form.RecordSource = Me.cboQuery
Me.frmQuerySub.Form.Requery
End If
End Sub

​

Although, when the query is changed to something other that the original recordsource, the subform maintains the original fields which display #Name?, whilst successfully populating the fields in the current query.

Just looking for hints to remove the redundant fields on the selection of a new query?

I don't expect to be given a solution, but a push in the right direction to better my understanding would be great!

 

[xenou]

I got a bit stuck wondering if all your queries have the same field names, number of fields -- are these actually completely different queries? How does the subform display fields if the names of the fields are changing? How many different queries do you have? Is the combobox the names of the queries or the actual SQL Statements? And, finally, what is your aim with this form? I recall seeing something like this in a book but I have to confess I've never tried this before so I may need to test it out on this side ...

ξ

 

[klopstan]

As a background I have two linked tables in a One to Many relationship, tblCompany (lists company details) and tblContracts (contains details of contracts related to various companies).

Here is a description of my DB and what I'm attempting to do.

1. Forms

I have a main form (frmQuery) and subform (frmQuerySub). frmQuery has a unbound combo box (cboQuery) and the subform displayed as a datasheet with it's initial source set to qryAll (see description)

2. Controls

The unbound combo on the main form cboQuery contains the following in the rowsource:

"qryAll";"All Records";"qryContracts";"All Records By Date Range";qryMicrosoftTotalByRange;Total Of All MS Contracts,

​

column count is 2 and the bound column is 1. the query names are typed exactly as they are displayed in Access. So basically the combo lists 3 query options and displays the description of the querys in the adjacent column.

The AfterUpdate option of cboQuery uses the following code:

Private Sub cboQuery_AfterUpdate()
If Not IsNull(Me.cboQuery) Then
Me.frmQuerySub.Form.RecordSource = Me.cboQuery

End If
End Sub

​

3. Queries

a) qryAll lists all fields in both tables (figured it would be a good default query to display to the user when the form is opened)

b) qryContracts shows less fields than qryAll, but still pulls fields from both tables

c) qryMicrosoftByDateRange also shows less fields than qryAll but lists all contracts for Microsoft between a date range specified by the user with the following code as a query criteria: Between [Enter Start Date (dd/mm/yyyy)] And [Enter End Date (dd/mm/yyyy)]


Now, I can select any of the 3 queries, and the subform datasheet records change to reflect this, however, the fields from the default query (qryAll) are still listed with #Name? where they are not included by the other 2 query's, so what I would like to do is ensure that only the fields listed in each individual query are displayed when they are selected from the combo.

I though that changing the record source of subform would regenerate the fields, but it appears as though all fields are inherited from the recordsource the subform is initially set to.

I hope that wasn't too much of a convaluted rant!

If you think posting my Access example would be better, I would be happy to do so. And regardless of outcome, thanks for taking the time to even look at my question

 

[xenou]

Not sure. i've tried hiding the columns not being used and can't get to it ("width" seems to apply to form view, not datasheet view, and same for "visible"). I'm assuming your subform is a datagrid view.

I guess you could create your query sources so that they all have the same number of fields. The All fields would have all fields. Any fields not use by the other queries would have a NULL or an empty string in those fields:

query "all Fields" : Select field1, field2, field3 From Table1
query "some Fields" : Select field1, field2, "" as field3 From Table1

At least this way, you'll have blanks instead of errors in those fields.