Change Subform Query RecordSource from Combo

klopstan

New Member
Joined
Dec 10, 2009
Messages
7
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

New Member
Joined
Dec 10, 2009
Messages
7
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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

New Member
Joined
Dec 10, 2009
Messages
7
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 :)
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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.
 

Forum statistics

Threads
1,081,748
Messages
5,361,041
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top