Best strategy: dynamic recordsource or temporary table a recordsource? (1 Viewer)

[Etxezarreta]

Hello everyone,
I would like to ask for advice here: I use many "forms-tabular sub forms", mainly to generate lists of people that match with queries: the queries are directly written in VBA, using ADO and SQl code.
Then I need to generate reports that correspond to the subforms: I use the same code as in the forms basically: same SQl expressions that feed the report.RecordSource instead of the Me.subform.form.Recordset. This looks like double work doesn't it?
I was thinking in another way to do it: using the code from the forms to populate "temporary" tables, that will then populate the reports.
Considering the fact that I will have to migrate the back-end to SQl server, could you please help me:

- Are those two ways to create dynamic records the only ones?
- Which one would be the best to work with a SQL server hosted back-end?

Many thanks in advance.

Etxe.

 

[Gasman]

You could save the form sql recordsource code as a querydef then use that querydef for the respective report.?
So the report will always use the same query, just that you will change what that query does depending on what you have in the form at that time.?

HTH

 

[Etxezarreta]

You could save the form sql recordsource code as a querydef then use that querydef for the respective report.?
So the report will always use the same query, just that you will change what that query does depending on what you have in the form at that time.?

HTH

Hi Gasman,
Thanks for your answer.
I am a bit reluctant to use querydefs, as it may slow down the process when the tables are on SQL server (I am not sure about it though).
I try another way: I the string SQL expression that feeds the form recordset can be a public variable, and I use it to feed the report.RecordSource.

 

[Gasman]

I have only ever used Access tables, so have no related knowledge of SQL server.
I have heard passthrough queries mentioned, but that is about it.?

Good luck anyway.

 

[Etxezarreta]

I have a very poor expertise in SQL server too, thanks for your help anyway.
I hope that other people will give me advice.

 

[Uncle Gizmo]

I use many "forms-tabular sub forms", mainly to generate lists of people that match with queries:

If you've got a main form with many subforms on it, subforms which are all practically identical, then you can cut down the number of forms you create by using one single form, but modifying its properties. See my "Time Management Matrix" example for further information.

Time Management Matrix

Subform – Time Management Matrix Example - Nifty Access

Time Management Matrix This example demonstrates how you can can use "Instances" of a Form multiple times. You place the copies on the main form as "Sub-Forms" It's an interesting way of programming, and with this technique you can quickly develop Forms with exceptional properties and uses with...

www.niftyaccess.com

All of my sample code and sample databases are available for Free, contact me for details of how to get it for free.

 

[Uncle Gizmo]

Then I need to generate reports that correspond to the subforms:

In my multiple reports example I demonstrate how you can create SQL statements in a form and transfer them into a report.

Generate Multiple Reports

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

All of my sample code and sample databases are available for Free, contact me for details of how to get it for free.

 

[Minty]

Querydefs are definitely a good way to go if you are connecting to SQL Server, you can create and destroy them on the fly and/or use a generic one for single-use and simply re-use it.

It's worth learning how to handle them.

 

[Etxezarreta]

If you've got a main form with many subforms on it, subforms which are all practically identical, then you can cut down the number of forms you create by using one single form, but modifying its properties. See my "Time Management Matrix" example for further information.

Time Management Matrix

Subform – Time Management Matrix Example - Nifty Access

Time Management Matrix This example demonstrates how you can can use "Instances" of a Form multiple times. You place the copies on the main form as "Sub-Forms" It's an interesting way of programming, and with this technique you can quickly develop Forms with exceptional properties and uses with...

www.niftyaccess.com

Your approach is very interesting indeed, thanks.
Etxe.

 

[Etxezarreta]

Querydefs are definitely a good way to go if you are connecting to SQL Server, you can create and destroy them on the fly and/or use a generic one for single-use and simply re-use it.

It's worth learning how to handle them.

Very useful information thanks a lot!

 

[Etxezarreta]

In my multiple reports example I demonstrate how you can create SQL statements in a form and transfer them into a report.

Generate Multiple Reports

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

All of my sample code and sample databases are available for Free, contact me for details of how to get it for free.

Yes, I have seen it. I will use this method.
Here is how I did this time:

'Code in the form:
str_SQL_finale = "SELECT DISTINCT t_AffectationProjetA_Contacts_expend.ID_contact, t_AffectationProjetA_Contacts_expend.Nom, t_AffectationProjetA_Contacts_expend.Prenom, t_AffectationProjetA_Contacts_expend.Structure, t_ListeContacts.Fonction, t_AffectationProjetA_Contacts_expend.Statut, t_ListeContacts.Courriel, t_ListeContacts.Tel1, t_AffectationProjetA_Contacts_expend.Profession, t_AffectationProjetA_Contacts_expend.Specialite, t_AffectationProjetA_Contacts_expend.Ville, t_AffectationProjetA_Contacts_expend.Departement, t_AffectationProjetA_Contacts_expend.Projet, t_AffectationProjetA_Contacts_expend.Role_dans_projet " & _
                "FROM t_ListeContacts INNER JOIN t_AffectationProjetA_Contacts_expend ON t_ListeContacts.ID_Contact = t_AffectationProjetA_Contacts_expend.ID_contact" & _
                " WHERE " & _
                "(((t_AffectationProjetA_Contacts_expend.Departement In (" & str_CritereDepartement & "))" & _
                    " AND (t_AffectationProjetA_Contacts_expend.Profession IN (" & str_CritereProfession & "))" & _
                    " AND (t_AffectationProjetA_Contacts_expend.Statut IN (" & str_CritereStatut & "))" & _
                    " AND t_AffectationProjetA_Contacts_expend.Projet IN (" & str_CritereProjet & ")))"
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'The public variable that populates the report is assigned with the SQl code value:
str_pub_SqlAlimentationReport = str_SQL_finale
'
'Used this way in the report:
'Private Sub Report_Open(Cancel As Integer)
''    Me.RecordSource = str_pub_SqlAlimentationReport
'    Me.Requery
''End Sub
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Set oBjRcrdst_RecordSetSource = New ADODB.Recordset
    On Error GoTo GestionnaireErreur
    With oBjRcrdst_RecordSetSource
                .Source = str_SQL_finale
                .ActiveConnection = objConnection
      'doesnt work with 'CurrentProject.connection
                .CursorType = adOpenDynamic
                .LockType = adLockOptimistic
                .Open
    End With

'Me.sf_CreationListingsPourEnvoisInformation.Enabled = True
Set Me.sf_CreationListingsPourEnvoisInformation.Form.Recordset = oBjRcrdst_RecordSetSource