Subform query doesn't update columns when new query is selected

[Goochag]

Hello All,

I'm working on a Database in Access 2007-2010 format. I have 20+ queries that show different columns depending on the criteria set in each query.

I.E.
Query 1 - FirstName | LastName | CareerGoals |

Query 2 - FirstName | LastName | MaritalStatus |YearInCollege|


I've set up a Main form that has a combobox that contains all the names of the queries I have built and set up a subform query that displays the query results.

PROBLEM: The columns do not change/update when a new query is selected. Only the initial query columns are shown.

So far this is all that I have in the VBA code in the Main form:

Private Sub Combo_AfterUpdate()
If Not IsNull(Me.Combo) Then
Me.Subform.Form.RecordSource = Me.Combo


Me.Subform.Form.Requery
End If
End Sub


Any ideas or workarounds would be great. Basically all I would like is for the subform to dynamically change its columns depending on which query is selected from the combobox. This is my first post so bear with me.

I appreciate your time. Let me know if you need anymore information. Thanks!

[June7]

Need to address the subform container control.

I always give the container control a name different from the object it holds, like ctrDetails.

Is the object actually named 'Subform'? And the combobox is named Combo? Neither is at all informative.

Me.ctrDetails.Form.RecordSource = Me.Combo

Why have 20 queries as opposed to just showing all the data on the form? Why a form/subform arrangement? Is the main form bound to data?

[Goochag]

Good point June7. I haven't named the container and the combobox just because I was testing out functionality.

The queries essentially filter a table in order to only show the desired results. Is there a better way to do it? (There probably is, I'm just not aware of it). The form/subform arrangement idea was used because I would like to show another form with the query at the same time.

Some background information:
I'm trying to simplify a scholarship awarding process. We receive applications in the form of csv which I import into a Student table (FirstName, LastName, YearInCollege, GPA, CareerGoals, etc).

The queries are based on scholarship criteria (Students that have a 3.5 or higher, Students that are studying banking, Single Parent students, etc) which I then use to query the student table and only show students that meet the criteria.

I also have a Scholar Awarded table that keeps track of the student, what scholarship they receive, and how much money they are rewarded.

I would like to be able to see the Criteria for the scholarship, the students that fit that criteria, and a way to enter in who receives the scholarship all at the same time (preferably). If that's not possible it's not the end of the world, it would just be easier to not jump between screens.

I hope this helps clarify some. All ideas are welcome. Thanks again!

[June7]

Setting the form RecordSource can be made to work but still need textboxes bound to fields. How do you want to manage that? This is using unbound forms to manage data and takes a lot of code. I've never designed this way.

The alternative is still to show all data. Can be organized on tab control.

It's possible. Use subforms and/or comboboxes/listboxes to view/select info.

[Xipooo]

You could create a subform for each query and add them all to your main form. Then hide all except the one picked from the drop down list.

I would also wait to load the data in the subform until it is selected otherwise all of the data will be loaded and that could make for a slow load time.

A caveat to this would be to actually set the control source of the subform object to the selected form. IIRC though this would mean dropping the main form into design mode, change the settings, then reopen the form and that can be messy when you want to maintain the values you set in the main form.

[Goochag]

Yeah I'm not sure the best way to handle it. I thought viewing the form as a datasheet and setting the form RecordSource = Combobox would work, but like I said earlier the column names don't update in the query subform when I select a different query.

I'll look into trying to show all data and use your suggestions above to make it work. I appreciate your help June7. If you have any other ideas or thoughts I'd love some more insight.

[June7]

I have a form that loads about 65,000 records with virtually no lag. Maybe several million would be an issue - don't expect to ever get anywhere near that many.

A lot depends on network connections.

[Xipooo]

Goochag, is there any reason you can't use a listbox with headers to display your data? Does it HAVE to be a subform?

It sounds like you're just displaying data.

I have a form that loads about 65,000 records with virtually no lag. Maybe several million would be an issue - don't expect to ever get anywhere near that many.

A lot depends on network connections.

Heh, one of my clients went from Access BE to MS SQL linked tables.... 12 subforms all with calculations in one main form. Open time went from 2 seconds to nearly a minute. Ever since then I've always kept an eye towards the potential for data migration and it's effects on UI times.

[Goochag]

Goochag, is there any reason you can't use a listbox with headers to display your data? Does it HAVE to be a subform?

It sounds like you're just displaying data.

That's not a bad idea Xipooo.

No it doesn't have to be a subform. I just would like to be able to have everything (or close to everything) on one page where it would be easier to look the students that fit the criteria and award those students at the same time.

What type of vba code would I have to do to perform both of those actions? Maybe just an example just to help me grasp the concept would be great.

[Xipooo]

That's not a bad idea Xipooo.

What type of vba code would I have to do to perform both of those actions? Maybe just an example just to help me grasp the concept would be great.

Well using a listbox is easy as pie. You can use the Query field names as your headers for the listbox. So all you would need is the VBA which swaps out the rowsource and does a requery on the listbox. All your headers would autopopulate with the field names. Nothing you aren't already doing really.

If you NEED subforms, then you will want to make a subform for each and every query you want to offer. Then in the combobox the value that gets returned should be the name of the subform you want to view. From there you just run a sub like this that goes through your list and makes all the subforms invisible, but then sets the one selected to visible.

private sub showSubForm()
  dim x as integer
  for x = 0 to me.<combobox>.ListCount - 1
    me(me.<combobox>.ItemData(x)).Visible = False
  next
  me(me.<combobox>.Column(0)).Visible = True
end sub

That's assuming the values in your combobox are the names of the subform controls. It's a little trickier if you want to have a more generic value that is visible to the user, but the subform name is in a hidden column on the combobox. The above code would just have to be altered to used Column(1) for both the values of the subforms to hide and the one to show.

Honestly I don't really like this approach. If you can do it in a listbox, that would be far more preferable and faster.

[June7]

What about ColumnCount and ColumnWidths properties?

[Xipooo]

What about ColumnCount and ColumnWidths properties?

A yes you are right. My mistake. WPF does autosizing but Access listboxes do not. Sorry for my confusion I got my wires crossed a little. Ironically forms in datasheet view also can autofit.

He could make a table of all of the possible queries that he will want to display in the listbox and have two additional fields on the table. One field holds semicolon delimited inch values (1";2";1.25") and a second field that holds the total column count. Then in the after Update event he would change the ColumnWidths and ColumnCount properties.

And then there's this: http://www.lebans.com/autocolumnwidth.htm

[ItsMe]

I have dynamically updated listbox and combobox's widths, etc. Kinda 6 to 1 when you compare Subform objects to Listbox controls. If I am not dynamically adjusting/updating a listbox (I can post some code for the string to assign widths if desired), I simply use one container and assign different forms, along with dynamicaly assigning the container's RecordSource.

So either you write code to dynamicaly update listbox properties or spend time creating form objects and creating code to dynamicaly update subform containers.

[Xipooo]

I have dynamically updated listbox and combobox's widths, etc. Kinda 6 to 1 when you compare Subform objects to Listbox controls. If I am not dynamically adjusting/updating a listbox (I can post some code for the string to assign widths if desired), I simply use one container and assign different forms, along with dynamicaly assigning the container's RecordSource.

So either you write code to dynamicaly update listbox properties or spend time creating form objects and creating code to dynamicaly update subform containers.

Can you do the assignment of a subform to a container control without putting the form into design view? I know you have to with Reports and subreports but I've never had the need to try it on a form.

[June7]

Yes, I just tested by setting the SourceObject property to a table.

Me.Child0.SourceObject = "Table.Airports"

and a query

Me.Child0.SourceObject = "Query.AirportsQuery"

and a form

Me.Child0.SourceObject = "Form.AirportData"

Didn't even need the Requery.