Query with dynamic RecordSource

[TheWedgie]

Howdy all - is it possible to create a query with a dynamic RecordSource that can be changed on load?

Basically I've taken over an event medical centre management DB from a retard, and it currently creates a new table for each event - so all my reports/forms that I've added to make it work have had to had some funky RecordSource selection stuff added - ie the data entry screen is actually a main form with a dropdown listing all the tables, then a subform whose recordsource changes based on the dropdown.

I can do the same with Reports - use their OnLoad event to set RecordSource based on a dropdown on a form...

However, I can't seem to do the same with Queries... is it even possible?

(I need to create a Report with a heap of Count fields to count x number of patients etc, and the only way I know is with a Query)

I can't change the DB setup, it's too much work currently - a new version is in the works but I need to get a couple of features added to the current one in the meantime.

 

[DCrake]

If I understand what you want then yes you can do this. Take a look at Querydefs and the .Sql statement, whereby you use vba to construct the SQL for the query then use this to re create the query with the new sql thus maintianing the integrety of the name of the query.

 

[TheWedgie]

If I understand what you want then yes you can do this. Take a look at Querydefs and the .Sql statement, whereby you use vba to construct the SQL for the query then use this to re create the query with the new sql thus maintianing the integrety of the name of the query.

Looks like it could be a winner - thanks, will have a play.

Assuming I basically use the Querydefs to create the query, and set my report's RecordSource to the Query name..?

 

[DCrake]

As long as your naming conventions for field names match those of your report controls then yup that's what you need to do.

So if you are grouping by company name and your grouping in the report is by CompName then make sure your alias name of the company name is CompName

 

[TheWedgie]

Yeah - what I've been doing is creating Forms & Reports based off a test event table filled with data, then changing the RecordSource stuff...

As an aside, this is how retarded the previous guy was - half the field names have spaces and slashes in them!

 

[DCrake]

Good luck with your rewrite

 

[TheWedgie]

Here's the test code, needs a bit of tidying up, but it works!

Thanks again DCrake!

Private Sub Command117_Click()
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
Dim EventName As String

EventName = Forms!frmEventReport.cmbEventName.Value

On Error Resume Next
'set variable values
Set dbs = CurrentDb
strQueryName = "qryConditionCount"
'delete old query, if it exists
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT [" & EventName & "].[Trauma/Condition  Category], [" & EventName & "].[Trauma/Medical Condition],  Count([" & EventName & "].[Trauma/Medical Condition]) AS  [CountOfTrauma/Medical Condition] FROM [" & EventName & "] GROUP  BY [" & EventName & "].[Trauma/Condition Category], [" &  EventName & "].[Trauma/Medical Condition];"

Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

DoCmd.OpenReport "repEventReport1", acViewPreview


End Sub