RecordSet vs Recordsource for dynamically binding data (1 Viewer)

[Martynwheeler]

Hi there,

I am new to access and am trying to create a question db for my students. I have most of the db working but I am wondering about some general concepts and trying to get the best approach.

When setting the data for a form at run time I can set

Me.RecordSource = some SQL

However, this approach means that I have to hard code the sql string into my event rather than using a saved query with the parameters passed to it (or maybe I just can't figure out how).
It works but for large joins the sql string is several lines long and difficult to debug/edit.

The other approach is to set the forms recordset directly.

Me.RecordSet = some recordset

The latter approach allows me to send parameters to a saved query via querydefs. This all works for a foem

If I try the same for a report it says that it is no longer supported.

So a few questions:

In general, what is the preferred approach, setting the recordset or recordsource?

Is it possible to set the recordsource using a saved query with parameters?

Do I have to hard code the sql with where clauses to the recordsource?

Hope that makes sense.
Thanks in advance

Martyn

Thanks

 

[June7]

Cross-post https://stackoverflow.com/questions...-to-dynamically-setting-object-data-in-access

 

[Uncle Gizmo]

If I try the same for a report it says that it is no longer supported.

I'm not sure what you mean by that?

I use a form, and within the form I build my SQL statements with VBA code, parameters picked from combo boxes m, text boxes and the like on the form. I then save the SQL statement as a text string within a custom property within the form.

A command button on the form opens the report and the report reaches out to the form and extracts the SQL from the custom property and places it within its own record source.

I explain it in a Blog on my website here:-

Pass Info In to a Report - Nifty Access

Passing Information to a Report Often, I see examples where people pass information between Forms and Reports using Opening Arguments "openArgs" ... I did this myself until I happened upon an article, I think it was from "Smart Access" which explained a very satisfying way of Passing information...

www.niftyaccess.com

 

[theDBguy]

If I have to pick between the two choices in your question, I would use the RecordSource instead. You don't have to use SQL. You should be able to use the name of a saved query as well.

 

[Pat Hartman]

Welcome to AWS

Not sure you are not considering bound forms.

I bind all my forms and reports to querydef's. Except for very small tables, all the querydef's have selection criteria. Sometimes, there are search options on the form. The user picks or types the values and presses the find button. All that button does is to requery the form. Sometimes if the searches are complicated, I create separate search forms. Those create SQL on the fly. The select clause is fixed and so saved as a querydef but the Run button there builds the where clause based on the selected options and then uses
Select * From myquerydef
Where (criteria string built with vba)

Sometimes, I have alternate querydefs if there are standard sets of criteria and so I can switch the name of the querydef in the RecordSource from qry1 to qry2.

The technique is less important when the BE is Jet or ACE but it is very important when the BE is SQL Server or other RDBMS because in order to let the server do the heavy lifting, you have to use criteria to reduce the returned recordset size as much as possible. This means that you would also never use form Filters since that technique requires you to bind the form to full tables or queries with no criteria and all filtering id done locally. This a very expensive technique once you move to a RDBMS.

Access attempts to "pass through" every query so if your BE is SQL Server, et al, you need to understand what would cause Access to force the server to download entire tables and process the where clause locally.

 

[The_Doc_Man]

When dealing with .RecordSource and .Recordset, if you alter one, you automatically alter the other. You can put any SELECT query you want in a .RecordSource though if the form's fields don't match the fields of the chosen .RecordSource, the form might not behave very well. Therefore I have to say that your concern is out of order. Pick the .Recordsource BEFORE you design the form's appearance and displayed content. Binding an SQL statement to a form after-the-fact is "putting the cart before the horse" - as the old saying goes.
Then, if you want to change order of appearance or if you want to filter some part of that recordset, there are form properties to allow you to change the sort order of the returned data or to impose selectivity. They can be dynamically imposed but will be much simpler to maintain.

 

[Martynwheeler]

Thank you for all the responses. If I provide some examples maybe this will make my question clearer.

so on one form a user can select from the whole bank of questions to put into a quiz with name "testquiz1". If I wish to view the selection of questions that I have put into "testquiz1" I can then press a button that takes the quiz name from the parent control and opens a child form with the questions that belong to "testquiz1".

This is where I have a choice;

1. I can bind the child form in design mode to a query:

SELECT Question.question_id, Exam_Year.year_date, Paper.paper_name, Question.question_number, Quiz_has_question.question_number, Quiz.quiz_id, Quiz.title
FROM Quiz INNER JOIN ((Paper INNER JOIN (Exam_Year INNER JOIN Question ON Exam_Year.year_id = Question.year_id) ON Paper.paper_id = Question.paper_id) INNER JOIN Quiz_has_question ON Question.question_id = Quiz_has_question.question_id) ON Quiz.quiz_id = Quiz_has_question.quiz_id
ORDER BY Quiz_has_question.question_number;

and open the subform with a WhereCondition based on the Quiz.quiz_id field

This opens the form in filtered view (it has basically the result from the query with all possible quizzes in there and applied a filter). Problem is the user can unfilter the view which is unsatisfying.

2. I can dynamically assign the records in the form_open event of the child form based on the selection in the parent form. Within this there are two more approaches:

A. set the child form's recordset directly using a parameterised query

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qrySelQuizQuestions")
    qdf("quiz") = Forms("frmSelectQuestions")![cmbSelectQuiz]
    Set rs = qdf.OpenRecordset()
    Set Me.Recordset = rs

SELECT Question.question_id, Exam_Year.year_date, Paper.paper_name, Question.question_number, Quiz_has_question.question_number, Quiz.quiz_id, Quiz.title
FROM Quiz INNER JOIN ((Paper INNER JOIN (Exam_Year INNER JOIN Question ON Exam_Year.year_id = Question.year_id) ON Paper.paper_id = Question.paper_id) INNER JOIN Quiz_has_question ON Question.question_id = Quiz_has_question.question_id) ON Quiz.quiz_id = Quiz_has_question.quiz_id
WHERE (((Quiz.quiz_id)=[quiz]))
ORDER BY Quiz_has_question.question_number;

B. set the form's recordsource

I can't seem to figure out how to do this using the parameterised query

I need something like

Me.RecordSource = "qrySelQuizQuestions" & "WHERE quiz_id = " & Forms("frmSelectQuestions")![cmbSelectQuiz]

but this fails.

Hopefully this makes it more clear?

 

[Gasman]

You would need to prefix that with "Select * FROM " if you wanted to do it that way, as that syntax is just nonsense. You are saying

qrySelQuizQuestions" & "WHERE quiz_id = 1

If the criteria is text you would need to surround with ' and if dates #

 

[Martynwheeler]

You would need to prefix that with "Select * FROM " if you wanted to do it that way, as that syntax is just nonsense. You are saying

qrySelQuizQuestions" & "WHERE quiz_id = 1

If the criteria is text you would need to surround with ' and if dates #

I realise that is nonsense, I just was wondering if there was some way of adding parameters? I can do it with a string replace:

Me.RecordSource = Replace(db.QueryDefs("qrySelQuizQuestions").sql, "[quiz]", Forms("frmSelectQuestions")![cmbSelectQuiz])

 

[Josef P.]

Note:

Problem is the user can unfilter the view which is unsatisfying.

You can deactivate manual filter changes in the form properties.
FormRef.AllowFilters = False

 

[ebs17]

but for large joins the sql string is several lines long and difficult to debug/edit

If you want to read and understand SQL statements, you would format them. You do the same with your VBA code.
Your query could also be presented like this.

SELECT
   Question.question_id,
   Exam_Year.year_date,
   Paper.paper_name,
   Question.question_number,
   Quiz_has_question.question_number,
   Quiz.quiz_id,
   Quiz.title
FROM
   Quiz
      INNER JOIN
         (
            (Paper
               INNER JOIN
                  (Exam_Year
                     INNER JOIN Question
                     ON Exam_Year.year_id = Question.year_id
                  )
               ON Paper.paper_id = Question.paper_id
            )
            INNER JOIN Quiz_has_question
            ON Question.question_id = Quiz_has_question.question_id
         )
      ON Quiz.quiz_id = Quiz_has_question.quiz_id
ORDER BY
   Quiz_has_question.question_number
;

You can add even more clarity using table aliases.

For your example above: You regularly link the main form and subform via an ID, imitating the relationships between tables used. This means that the content of the subform is always automatically filtered to the ID of the main form.
You can also create such a link between the ComboBox of the main form and the subform.

There are other practical options for passing parameters to a query beyond those mentioned:
- Parameter table included in query
- public function (can be processed directly by Jet/ACE)

Overall: Before you deal with the questions mentioned above, you should be clear about what you are using as a backend.

 

[arnelgp]

you can also use Tempvars as Criteria to your Query.
see this demo, query qryParam1 uses Tempvars to Filter FName (firstname) field.
when the tempvars variable does not exists, it show all records.

Removing Tempvars variable that does not exists does not give you any error messages.
when the Form Unload it destroys the variable (regardless if it exists or not).

 

[Gasman]

I realise that is nonsense, I just was wondering if there was some way of adding parameters? I can do it with a string replace:

Me.RecordSource = Replace(db.QueryDefs("qrySelQuizQuestions").sql, "[quiz]", Forms("frmSelectQuestions")![cmbSelectQuiz])

I thought you were trying to add criteria to a saved query?

So

Me.RecordSource = "SELECT * FROM qrySelQuizQuestions " & "WHERE quiz_id = " & Forms("frmSelectQuestions")![cmbSelectQuiz]

I would make a string variable strSource, assign your string to that, then you can debug.print it to ensure it is correct, then use that variable in your recordsource assignment.

 

[GK in the UK]

Q. In general, what is the preferred approach, setting the recordset or recordsource?

As you have discovered you can open a Recordset from a QueryDef with the parameters passed in. But if you apply filters Access asks for the arguments so it doesn't really work.

Q. Is it possible to set the recordsource using a saved query with parameters?

There is no built-in Access way of setting a Recordsource to a QueryDef with parameters. Querydef.Sql returns the 'bare' query.
You can build the SQL yourself in code, put form control references in the query, or put Tempvar references in the query.

I tend to avoid building Sql in code as well as avoiding control references in queries so built a function to return the SQL with the parameter placeholders replaced. So you would build the query as if you're going to use it for QueryDef.Parameters in the usual way but use the function to return the fully qualified query and assign it to a Recordsource.

A recordsource is assigned like this (example with numeric, text and date):

Me.Recordsource = fQdfSql("q_ViewReconciliations", _
                                "ID", txtNominalID, _
                                "Batch", CSql(strBatchRef, sdt_text), _
                                "Session", CSql(mSessionDt, sdt_date))

As many parameter pairs as you want. The only difference between using my function and QueryDef.parameters is that the arguments have to be pre-formatted if they are strings or dates. CSql is a custom function from MajP and is available in these pages, or you can of course pre-format them in the call.

(Thinking aloud: I wonder if I could put the CSql function within fQdfSql which would simplify the call?)

If you filter the form, which might include setting column filters in a datasheet form, it all works because Access has been delivered the query with criteria.
Don't forget setting a Recordsource requeries the form, you don't need to do both.

' returns the qdf sql with the parameters replaced,
' if no params are passed in we simply get qdf.sql
' replaces all instances of the parameter name
' NOTE date and string parameters must be pre-formatted

Public Function fQdfSql(strQueryName As String, ParamArray avarParamList()) As String
On Error GoTo Err_Handler
 
  Dim i As Long
  Dim lngLBound As Long
  Dim lngUBound As Long
  Dim qdf As dao.QueryDef
  Dim strSql As String


  lngLBound = LBound(avarParamList)
  lngUBound = UBound(avarParamList)


  If (lngUBound - lngLBound + 1) Mod 2 <> 0 Then
      ' this is really a coding error, not a run time error
      MsgBox "Uneven parameter list, you must pass in a name and a value for each parameter", , "fQdfSql"
  Else
      Set qdf = CurrentDb.QueryDefs(strQueryName)
      strSql = qdf.SQL
      For i = lngLBound To lngUBound Step 2
          strSql = Replace(strSql, "[" & avarParamList(i) & "]", avarParamList(i + 1))
      Next
      fQdfSql = strSql
  End If
 
Exit_Handler:
  Exit Function
Err_Handler:
  Call LogError(Err.Number, Err.Description, "fQdfSql", , ShowErrors)
  Resume Exit_Handler
End Function

 

[Martynwheeler]

Q. In general, what is the preferred approach, setting the recordset or recordsource?

As you have discovered you can open a Recordset from a QueryDef with the parameters passed in. But if you apply filters Access asks for the arguments so it doesn't really work.

Q. Is it possible to set the recordsource using a saved query with parameters?

There is no built-in Access way of setting a Recordsource to a QueryDef with parameters. Querydef.Sql returns the 'bare' query.
You can build the SQL yourself in code, put form control references in the query, or put Tempvar references in the query.

I tend to avoid building Sql in code as well as avoiding control references in queries so built a function to return the SQL with the parameter placeholders replaced. So you would build the query as if you're going to use it for QueryDef.Parameters in the usual way but use the function to return the fully qualified query and assign it to a Recordsource.

A recordsource is assigned like this (example with numeric, text and date):

Me.Recordsource = fQdfSql("q_ViewReconciliations", _
                                "ID", txtNominalID, _
                                "Batch", CSql(strBatchRef, sdt_text), _
                                "Session", CSql(mSessionDt, sdt_date))

As many parameter pairs as you want. The only difference between using my function and QueryDef.parameters is that the arguments have to be pre-formatted if they are strings or dates. CSql is a custom function from MajP and is available in these pages, or you can of course pre-format them in the call.

(Thinking aloud: I wonder if I could put the CSql function within fQdfSql which would simplify the call?)

If you filter the form, which might include setting column filters in a datasheet form, it all works because Access has been delivered the query with criteria.
Don't forget setting a Recordsource requeries the form, you don't need to do both.

' returns the qdf sql with the parameters replaced,
' if no params are passed in we simply get qdf.sql
' replaces all instances of the parameter name
' NOTE date and string parameters must be pre-formatted

Public Function fQdfSql(strQueryName As String, ParamArray avarParamList()) As String
On Error GoTo Err_Handler

  Dim i As Long
  Dim lngLBound As Long
  Dim lngUBound As Long
  Dim qdf As dao.QueryDef
  Dim strSql As String


  lngLBound = LBound(avarParamList)
  lngUBound = UBound(avarParamList)


  If (lngUBound - lngLBound + 1) Mod 2 <> 0 Then
      ' this is really a coding error, not a run time error
      MsgBox "Uneven parameter list, you must pass in a name and a value for each parameter", , "fQdfSql"
  Else
      Set qdf = CurrentDb.QueryDefs(strQueryName)
      strSql = qdf.SQL
      For i = lngLBound To lngUBound Step 2
          strSql = Replace(strSql, "[" & avarParamList(i) & "]", avarParamList(i + 1))
      Next
      fQdfSql = strSql
  End If

Exit_Handler:
  Exit Function
Err_Handler:
  Call LogError(Err.Number, Err.Description, "fQdfSql", , ShowErrors)
  Resume Exit_Handler
End Function

That is exactly what I was looking for, thank you

 

[cheekybuddha]

Is this form intended to be read-only

Q. In general, what is the preferred approach, setting the recordset or recordsource?

As you have discovered you can open a Recordset from a QueryDef with the parameters passed in. But if you apply filters Access asks for the arguments so it doesn't really work.

Q. Is it possible to set the recordsource using a saved query with parameters?

There is no built-in Access way of setting a Recordsource to a QueryDef with parameters. Querydef.Sql returns the 'bare' query.
You can build the SQL yourself in code, put form control references in the query, or put Tempvar references in the query.

I tend to avoid building Sql in code as well as avoiding control references in queries so built a function to return the SQL with the parameter placeholders replaced. So you would build the query as if you're going to use it for QueryDef.Parameters in the usual way but use the function to return the fully qualified query and assign it to a Recordsource.

A recordsource is assigned like this (example with numeric, text and date):

Me.Recordsource = fQdfSql("q_ViewReconciliations", _
                                "ID", txtNominalID, _
                                "Batch", CSql(strBatchRef, sdt_text), _
                                "Session", CSql(mSessionDt, sdt_date))

As many parameter pairs as you want. The only difference between using my function and QueryDef.parameters is that the arguments have to be pre-formatted if they are strings or dates. CSql is a custom function from MajP and is available in these pages, or you can of course pre-format them in the call.

(Thinking aloud: I wonder if I could put the CSql function within fQdfSql which would simplify the call?)

If you filter the form, which might include setting column filters in a datasheet form, it all works because Access has been delivered the query with criteria.
Don't forget setting a Recordsource requeries the form, you don't need to do both.

' returns the qdf sql with the parameters replaced,
' if no params are passed in we simply get qdf.sql
' replaces all instances of the parameter name
' NOTE date and string parameters must be pre-formatted

Public Function fQdfSql(strQueryName As String, ParamArray avarParamList()) As String
On Error GoTo Err_Handler

  Dim i As Long
  Dim lngLBound As Long
  Dim lngUBound As Long
  Dim qdf As dao.QueryDef
  Dim strSql As String


  lngLBound = LBound(avarParamList)
  lngUBound = UBound(avarParamList)


  If (lngUBound - lngLBound + 1) Mod 2 <> 0 Then
      ' this is really a coding error, not a run time error
      MsgBox "Uneven parameter list, you must pass in a name and a value for each parameter", , "fQdfSql"
  Else
      Set qdf = CurrentDb.QueryDefs(strQueryName)
      strSql = qdf.SQL
      For i = lngLBound To lngUBound Step 2
          strSql = Replace(strSql, "[" & avarParamList(i) & "]", avarParamList(i + 1))
      Next
      fQdfSql = strSql
  End If

Exit_Handler:
  Exit Function
Err_Handler:
  Call LogError(Err.Number, Err.Description, "fQdfSql", , ShowErrors)
  Resume Exit_Handler
End Function

Perhaps use the BuildCriteria function to also account for different datatypes (ie string and datetime)

 

[Pat Hartman]

I've never named a column with the name of a table that defines it. If it is even allowed, it has to be confusing at a minimum.

This is how you open a recordset that requires a parameter. When I use querydefs with parameters in code, I name the parameter "EnterXXX", "EnterZZZ", etc to make it clear what you want if you open the querydef directly, Also, it is more concise than using Forms!yourform!yourcontrolname.

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset

Set db = Currentdb()
Set qd = db.Querydef!yourqueryname
    qd.Parameters!EnterQuiz = Me.cboQuiz
Set rs = qd.OpenRecordset

    Do until rs.EOF
        ....
        rs.MoveNext
    Loop

This is how you bind a querydef to the RecordSource:

Me.RecordSource = "yourquerydefname"

In this case, the parameter should reference a control on the form:

Where somefield = Forms!yourform!somefield

 

[ebs17]

In this case, the parameter should reference a control on the form:
Where somefield = Forms!yourform!somefield

But you should compare like with like:

Me.RecordSource = "yourquerydefname"
' xxx
Set rs = CurrentDb.Openrecordset("yourquerydefname")
Set Me.Recordset = rs

In contrast, you cannot simply assign a real parameter query as a RecordSource.

The correct difference: A query is a definition of which data is to be loaded from where and how it is to be processed. But she doesn't have any data yet. The data only comes when the query is called.

A recordset already has an executed query and immediately contains data as an object.

 

[Pat Hartman]

Except that there is no point to opening the recordset in VBA first unless you want to use it for something other than as the recordsource of the form. Don't just open the recordset using VBA because you can. If you don't have a specific process that requires a recordset, just bind the querydef to the form. Remember, Action queries are far more efficient for updating recordsets than VBA loops of a select query which is what you seem to be suggesting.

If you set the form's RecordSource to the querydef name, you can then use the RecordsetClone do do something with the recordset once the form has opened it if you need to do something with the recordset such as to copy records and insert rows in to the open RecordsetClone.

So, one method opens the recordset in VBA and then binds it to the form, the second binds the querydef to the form and later opens the recordsetclone. They are probably equivalent except that unless you have Loop processing to do, there is no reason to open either a recordset or the recordsetclone in VBA at all so most of the time
Me.RecordSource = "querydefname" ---- will be the optimal solution.

 

[Martynwheeler]

I've never named a column with the name of a table that defines it. If it is even allowed, it has to be confusing at a minimum.

This is how you open a recordset that requires a parameter. When I use querydefs with parameters in code, I name the parameter "EnterXXX", "EnterZZZ", etc to make it clear what you want if you open the querydef directly, Also, it is more concise than using Forms!yourform!yourcontrolname.

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset

Set db = Currentdb()
Set qd = db.Querydef!yourqueryname
    qd.Parameters!EnterQuiz = Me.cboQuiz
Set rs = qd.OpenRecordset

    Do until rs.EOF
        ....
        rs.MoveNext
    Loop

This is how you bind a querydef to the RecordSource:

Me.RecordSource = "yourquerydefname"

In this case, the parameter should reference a control on the form:

Where somefield = Forms!yourform!somefield

Where have I named a column with the same name as the table? I had not noticed?