Running A Query that picks different Fields according to a Form 2

[AlisonEmmett]

I have a feeling this will be difficult to explain...
I have a table with structure as follows:
Server Text
Date Date
Stat1 Value
Stat2 Value
Stat3 Value

I have a form with combo boxes for Server and Stat
Thus the user chooses a particular server and a particluar Statistic (Stat1 or Stat2 or Stat3).
I then want to run a query based on those two choices. I can't write the query easily becasuse I don't know which Stat field will be chosen - and I don't want to have three queries and three buttons if I can avoid it.

Possibly I need some VB that changes my query to the appropriate Stat field before the query is actually run. Is this possible ?. Thus my query might be "Select Server,Avg(Stat1) from table; - but if the combo box choice is Stat2, then the query needs to change to "Select Server, Avg(Stat2) from table;

Does anyone understand the dilemma ???

any help gratefully received - I can't work out the code [sig][/sig]

 

[Amiel]

You can (re)create a query definition (see 'QueryDefs' in the reference) prior to running it based on the users selections. This is more of a coding thing that typically goes like the following.

1. delete the old query definition.
2. create a new on based on user input (from data on form).
3. execute it.

This is probably considered routine functionality. Your best bet is to check the reference under 'QueryDefs' to get a good understanding on how it can be done.

More? let me know. |-I [sig]<p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br> [/sig]

 

[kctaylordotcodotuk]

The basis of what you need is: -

txtSQL = &quot;SELECT Server, Avg(&quot;
txtSQL = txtSQL & Me.cboStat
txtSQL = txtSQL & &quot AS Stat FROM tblServer GROUP BY Server HAVING Server = '&quot;
txtSQL = txtSQL & Me.cboServer & &quot;';&quot;

Where tblServer is your table, cboStat is the combo box for Stat and cboServer is the combo box for Server. txtSQL could be one line but I've split it to try and make it easier to read. Once txtSQL has been built up you can then apply it to whatever you need. Without more details about how you are using your form, it's hard to be specific about this. I will put an example on my website at

http://www.kctaylor.co.uk/downloads.htm

under the heading Dynamic Field Selection very soon. [sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]

 

[AlisonEmmett]

kctaylordotcodotuk

That is great !. I now have a function in a module run from a button in my form that produces the string txtSQL which looks like the SQL I need - but I can't work out how to actually run it. I tried adding DoCmd RunSQl &quot;txtSQL&quot; at the end of the module, but it fails to recognise it as SQL

Here is my function
Function dynamic_test()

Dim txtSQL As String

txtSQL = &quot;Select Server, Avg(&quot;
txtSQL = txtSQL & Form_danform.StatChoice
txtSQL = txtSQL & &quot As Stat FROM Stats GROUP BY Server HAVING Server = '&quot;
txtSQL = txtSQL & Form_danform.ServerChoice & &quot;';&quot;

DoCmd.RunSQL &quot;txtSQL&quot;


End Function

Any ideas how I can get it to actually run ??

thanks in advance for any help

[sig][/sig]

 

[jagilman]

try this, here is your old code, add the new line indicated.

Function dynamic_test()

Dim txtSQL As String

txtSQL = &quot;Select Server, Avg(&quot;
txtSQL = txtSQL & Form_danform.StatChoice
txtSQL = txtSQL & &quot As Stat FROM Stats GROUP BY Server HAVING Server = '&quot;
txtSQL = txtSQL & Form_danform.ServerChoice & &quot;';&quot;

Debug.Print &quot;tstSQL&quot; '<< add this line

DoCmd.RunSQL &quot;txtSQL&quot;

End Function

after you run the function, go to the debug window, copy the sql statement and paste it into the sql view of a new query. Click run and see if it works. If it does, then the first step is ok.

Step 2 change the function so it reads like this

Public Function dynamic_test() As String
Dim txtSQL As String

txtSQL = &quot;Select Server, Avg(&quot;
txtSQL = txtSQL & Form_danform.StatChoice
txtSQL = txtSQL & &quot As Stat FROM Stats GROUP BY Server HAVING Server = '&quot;
txtSQL = txtSQL & Form_danform.ServerChoice & &quot;';&quot;

Debug.Print &quot;tstSQL&quot; '<< add this line

dynamic_test = &quot;txtSQL&quot;

End Function

Now if you need to use this query as the recordsource for a form that you will open, then it will look like this.

DoCmd.OpenForm &quot;yourFormName&quot;, , , , , acHidden

Forms(&quot;yourFormName&quot.RecordSource = dynamic_test
Forms(&quot;yourFormName&quot.Visible = True
Forms(&quot;yourFormName&quot.SetFocus

[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[kctaylordotcodotuk]

Alison,
Yes the answer is to set the recordsource, as jagilman has kindly pointed out.
In the example I put on my site I do this in the function that you've called dynamic_test, though as jagilman has shown, there are other ways to do this. A lot depends on whether you are wanting to display the values in the same form or not.
I called the function from the Form Load event and the AfterUpdate event of both combo boxes, but this does rely on default values being set in the combo boxes. Strictly speaking the function should also have some error trapping in case the user sets the combo boxes to Null. [sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]

 

[AlisonEmmett]

I seem to be finally getting somewhere with this using the QueryDefs hence:

Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef(&quot;StatQ1&quot;, txtSQL)

I just need the code to delete my querydef before I regenerate it - I'd be very grateful if someone could let me know it (Access help being singularly unhelpful as usual)


[sig][/sig]

 

[kctaylordotcodotuk]

dbs.QueryDefs.Delete &quot;StatQ1&quot; ? (untested) [sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]

 

[Amiel]

&quot;...finally getting somewhere...&quot;, and it is far more robust too!

Here's the code:

Private Sub ModifyTheDataSource()

Dim db As DataBase
Dim qd As QueryDef
Dim txtSQL As String

Set db = DBEngine.Workspaces(0).Databases(0)
'(1) delete existing query
On Error Resume Next
db.QueryDefs.Delete (&quot;StatQ1&quot
On Error GoTo 0
'(2) Now build the query statement based on items found on the form.

' using your statement here...
txtSQL = &quot;Select Server, Avg(&quot;
txtSQL = txtSQL & Form_danform.StatChoice
txtSQL = txtSQL & &quot As Stat FROM Stats GROUP BY Server HAVING Server = '&quot;
txtSQL = txtSQL & Form_danform.ServerChoice & &quot;';&quot;
[red]Set qd = db.CreateQueryDef(&quot;StatQ1&quot;, txtSQL)[/red]

'Refresh form that uses StatQ1 as data source. New data appears. Done!
Me.Requery

End Sub

If this (code) will be being called from 'Form_danform' use 'Me![StatChoice]' instead.

If [StatChoice] is a string, embed it with quotes using:
&quot;Select server, Avg(&quot;&quot;&quot; & Me![StatChoice] & &quot;&quot;&quot as Stat...&quot;

if it's numeric use:
&quot;Select server , Avg(&quot; & Me![StatChoice] & &quot as Stat...&quot;

have fun ! [sig]<p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br> [/sig]

 

[AlisonEmmett]

Thankyou - it all seems to work nicely ! [sig][/sig]