Creating New Query Dynamically to use as RecordSource on Form

[nole10]

Hi all,

I have created code to dynamically build a new SQL statement (I used faq705-2205 as a resource) and save it as a query in the current database. The intent is to be able to create a new query any time a user makes selections on a form and requests a 'report'. The report would just be showing the query results in a form for display. I used FAQ

I am attaching my code here. Currently I am just trying to use OpenQuery and can't even get that to happen.

I would like for the new query to be saved and then open the form that has that query name stored as it's RecordSource and display the new data each time.

I am sure I have been looking at it too long and can't see my own mistakes.

Thanks in advance for any help you can offer...

Donna

Private Sub cmd_ViewResults_Click()

On Error Resume Next

Dim ctl As Control
Dim sSQL As String
Dim sQryName As String
Dim bFirst As Boolean
Dim qryDef As QueryDef
Dim Db As DAO.Database

Set Db = CurrentDb()
sQryName = "qry_ReportResults"

sSQL = "SELECT field_names FROM tbl_Budget WHERE "

bFirst = True
'This section is where the code loops through each ComboBox
control on the form to collect parameters to build the WHERE clause.

With ctl
Select Case .ControlType
Case acComboBox
.SetFocus
If bFirst = True Then
sSQL = sSQL & ctl.Name & " = " & """" & ctl.Text & """"
bFirst = False
Else
sSQL = sSQL & " and " & BuildCriteria(.Name, dbText, .Text)
End If
End Select
End With
Next ctl

If ObjectExists("Query", sQryName) Then
Db.QueryDefs.Delete sQryName
End If

Set qryDef = Db.CreateQueryDef(sQryName, sSQL)

DoCmd.OpenQuery sQryName

'DoCmd.OpenForm "Budget Entries ALL", acNormal, , , acFormEdit

Set Db = Nothing

End Sub

 

[kjv1611]

Is there a reason that you have to save the query? You can just dynmically set it as the form's recordsource without ever saving a query object.

Private Sub SqlRecordSource
  Dim strSQL As String
  Dim frm as Form
  Set frm = CurrentDb.Forms("MyForm")

  strSQL = "SELECT RecordID, Name, State FROM MyTable WHERE RecordID > 4"
 
  frm.RowSource= strSQL [green]'Can't remember if should be RowSource or RecordSource[/green]
  DoCmd.OpenForm frm.Name
  Set strSQL = vbNullString
  Set frm = Nothing
End Sub

This sample code is assuming to be put in a Module, not a Form Module. If you want to put it in the Form Module, it may still work the same, but you can simply do away with the Form variable, and reference the Form directly.

At least I think that's correct.. not tested, just typed right here in tek-tips. Let us know if you try something like this, and run into any issues.

Also, let us know if indeed you do have to create the Query for some reason and THEN use it as your recordsource.

 

[dhookom]

I just change the SQL of a saved query and then open a form that has an unbound subform on it. Then set the Source Object property of the the subform control to the query.

    Me.sfrmCtl.SourceObject = "Query.qry_ReportResults"

Duane
Hook'D on Access
MS Access MVP

 

[nole10]

kjv1611 - No, there is no reason I need to save the query first. This was just a method I found to do it. I tried your method of just setting my sSQL variable = to the RecordSource of the form that I want to open and it didn't work.

Once I know the variable sSQL contains a valid query, I can't even use DoCmd.RunSQL sSQL to view the results of the query. I am confused.

dhookom - I am not sure what you are doing with the subform... My situation is that the user is selecting choices from two drop down boxes and clicking a button that builds the query from that form and then opens a different form using the new query as the RecordSource.

Donna

 

[kjv1611]

it didn't work.

How did it not work? What exactly did you do? Did you get an error message? Wrong Data? No data?

I can't even use DoCmd.RunSQL sSQL to view the results of the query.

Needing to see the results of a query separately (by itself) is one thing... using the data as a recordsource/rowsource is entirely a different thing.

For dhookom's code, he's simply setting the row/record source for a subform to a query. If you don't have a subform, just do the same, but with your form rather than subform.

 

[nole10]

I'm sorry, frustration is getting the better of me. I know I should have put more specific results.

When I set my sSQL variable = to the RecordSource of the form that I want to open, the form opens but all of the text controls have #NAME in them, no data. I have created a simple sql (select tbl_budget.* from tbl_Budget) to use as a test to determine if my syntax when building the query has problems (I can deal with those later ). The form still opens with no data. I do not get an error message either.

Does the form have to be open prior to setting the RecordSource value?

Thank you for working through this with me.

Donna

 

[nole10]

I forgot to add that, yes, I do understand that there are major differences between using the query as a recordsource and seeing the results of the query. I was just doing that as a test to make sure my SQL syntax was correct.

Thanks,

Donna

 

[dhookom]

I have an unbound form "frmResults" with a subform control on it named "subformctl". After creating a sql statement, I apply it to a saved query:

   CurrentDb.QueryDefs("qry_ReportResults").SQL = sSQL
   DoCmd.OpenForm "frmResults"

In the On Open event of frmResults, I set the SourceObject of the subform control.

   Me.subformctl.SourceObject ="query.qry_ReportResults"

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

I'm pretty sure it's your SQL syntax causing the issues.

I created a new form for testing, and pulled in data from an import errors table, then built a SQL statement pulling just the top 1 record from that table for comparison.

Table is an errors table - so it's got 3 fields: Error, Field, Row

The form's RecordSource is originally pointing to the table as recordsource. I run a shrot procedure to change it to a SQL string that is looking for the first record only, and it is updated immediately.

Here's the code I used:

Option Compare Database
Option Explicit

Sub FormRecSrc()
    Dim db As DAO.Database
    Dim frm As Form
    Dim strSQL As String
    
    Set db = CurrentDb
    
    Set frm = Form_TestingForm
    MsgBox frm.Name
    
'    Set frm = Forms(TestingForm)
            
    strSQL = "SELECT TOP 1 ['2010 data consolidated$'_ImportErrors].Error, ['2010 data consolidated$'_ImportErrors].Field, ['2010 data consolidated$'_ImportErrors].Row FROM ['2010 data consolidated$'_ImportErrors];"
    
    frm.RecordSource = strSQL
    
'    DoCmd.OpenForm "TestingForm"
    frm.Requery
End Sub

Doing this the way I mentinoed, yes, it does appear the form would have to be open. I'm sorta bumfuzzeled by that, b/c I would have thought you could change the recordsource type settings without the form being open..

I tried it with the form closed, and then opening the form, it had no effect. When I opened the form, and then ran the code to change the recordsource, it worked perfectly.

 

[dhookom]

You can't change properties of a closed form (or report).

Duane
Hook'D on Access
MS Access MVP

 

[nole10]

I changed the code to open the form first, then set the RecordSource of the form = sSQL and it worked perfectly with my simple SQL. Now I need to get to work on the syntax of my larger statement and figure out the rest. I will report back.

Thank you both so much for your continued assistance!

Donna

 

[nole10]

I fixed the syntax of the larger SQL statement that I am building and all is working. I guess my first couple hundred tries working with a bad SQL stmt messed up my thinking for the rest. By the time I finally decided to use a simpler test SQL stmt I had already messed with the other code so much, I got bogged down.

Biggest part of the equation that I was missing was to open the form first. The faq that I used as my initial resource was working on the same form and I since I didn't make that distinction between the example and my code, I didn't take that into consideration.

Thanks again, for the help!

Donna