Populate unbound subform with a recordset

[Lol999]

I'm trying to generate a recordset and use it to populate a subform.
My SQL is good because I have run it in the Access query builder and it returns the expected result I just can't get anything to appear in the subform!

I used a loop to fill the subform but nothing is showing. If anyone could point out the idiot mistake that would be great!
Code below:

Private Sub txtPart_Number_AfterUpdate()
Dim SQL1 As String
Dim PartNumber As String
PartNumber = Me!txtPart_Number.Text
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim rs1 As New ADODB.Recordset
rs1.ActiveConnection = cnn1
rs1.CursorType = adOpenDynamic
rs1.LockType = adLockOptimistic
SQL1 = " SELECT Tbl_Location.Location_Category, Tbl_Location.Contract_Number, Tbl_Location.Contract_Details, Tbl_Location.Van_Reg, Tbl_Product.Part_No, Tbl_Current_Location.Date_Loaned, Tbl_User.Details, Tbl_Current_Location.Comments " & _
" FROM Tbl_User INNER JOIN (Tbl_Location INNER JOIN (Tbl_Product INNER JOIN Tbl_Current_Location ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product) ON Tbl_Location.ID_Location = Tbl_Current_Location.ID_Location) ON Tbl_User.ID_User = Tbl_Current_Location.ID_User " & _
" WHERE (((Tbl_Current_Location.ID_Product)=[Tbl_Product].[ID_Product]) AND (Tbl_Product.Part_No)=""" & PartNumber & """);"
rs1.Open SQL1
With rs1

    'Ensure recordset is populated
    If Not .BOF And Not .EOF Then
        
        'not necessary but good practice
        .MoveLast
        .MoveFirst
        
        While (Not .EOF)
            Me!frmLocation.Form!txtLocation_Category.RecordSource = "Location_Category"
            Me!frmLocation.Form!txtContract_Number.RecordSource = "Contract_Number"
            Me!frmLocation.Form!txtContract_Details.RecordSource = "Contract_Details"
            Me!frmLocation.Form!txtVan_Reg.RecordSource = "Van_Reg"
            Me!frmLocation.Form!txtPart_No.RecordSource = "Part_No"
            Me!frmLocation.Form!txtDate_Loaned.RecordSource = "Date_Loaned"
            Me!frmLocation.Form!txtDetails.RecordSource = "Details"
            Me!frmLocation.Form!txtComments.RecordSource = "Comments"
            Me!frmLocation.Form!txtComments.Requery
            .MoveNext
        Wend
        
    End If
    
End With
' Debug.Print PartNumber
rs1.Close
cnn1.Close
Set rs1 = Nothing
Set cnn1 = Nothing
End Sub

 

[Ranman256]

you cant.
subform MUST be bound to a query , or a form to see the data.
and requires zero code.

 

[isladogs]

Start with the simplest question.
Why use ADO?
DAO recordsets are easier to work with and would do this task just as well if not better

Next remove Me!frmLocation.Form and .RecordSource from each line

I'm fairly sure the While ..Wend should also be removed.

There may be more issues but I'll stop there

 

[Lol999]

you cant.
subform MUST be bound to a query , or a form to see the data.
and requires zero code.

Apparently you can http://www.accessforums.net/showthread.php?t=32865

 

[Lol999]

Start with the simplest question.
Why use ADO?
DAO recordsets are easier to work with and would do this task just as well if not better

Next remove Me!frmLocation.Form and .RecordSource from each line

I'm fairly sure the While ..Wend should also be removed.

There may be more issues but I'll stop there

Evening Ridders, curious as to why DAO is better than ADO but I think I'm going to take a different approach

 

[bastanu]

Have you tried to set your subform's recordset equal to your ADO recordset?

https://msdn.microsoft.com/en-us/vba/access-vba/articles/bind-a-form-to-an-ado-recordset

Cheers,
Vlad

 

[Lol999]

Hi Vlad, I tried that but kept getting error messages.
If you have a suggestion as to how to do it I'm interested.

 

[bastanu]

What happens if you try this:

Private Sub txtPart_Number_AfterUpdate()
Dim SQL1 As String
Dim PartNumber As String
PartNumber = Me!txtPart_Number.Text
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim rs1 As New ADODB.Recordset
rs1.ActiveConnection = cnn1
rs1.CursorType = adOpenDynamic
rs1.LockType = adLockOptimistic
SQL1 = " SELECT Tbl_Location.Location_Category, Tbl_Location.Contract_Number, Tbl_Location.Contract_Details, Tbl_Location.Van_Reg, Tbl_Product.Part_No, Tbl_Current_Location.Date_Loaned, Tbl_User.Details, Tbl_Current_Location.Comments " & _
" FROM Tbl_User INNER JOIN (Tbl_Location INNER JOIN (Tbl_Product INNER JOIN Tbl_Current_Location ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product) ON Tbl_Location.ID_Location = Tbl_Current_Location.ID_Location) ON Tbl_User.ID_User = Tbl_Current_Location.ID_User " & _
" WHERE (((Tbl_Current_Location.ID_Product)=[Tbl_Product].[ID_Product]) AND (Tbl_Product.Part_No)=""" & PartNumber & """);"
rs1.Open SQL1
With rs1

    'Ensure recordset is populated
    If Not .BOF And Not .EOF Then
        
        'not necessary but good practice
        .MoveLast
        .MoveFirst
               
            Me.frmLocation.Form.recordset=rs1  'vlad I take it frmLocation is the name of the subform control on your form where this code is running

    End If
    
End With
' Debug.Print PartNumber
rs1.Close
cnn1.Close
Set rs1 = Nothing
Set cnn1 = Nothing
End Sub

 

[Mark_]

To deal with an unbound form, you need to work out your own code for the following;
1) Declare and load your record set.
2) Move to the first/last/next/previous record.
3) Load the values from the current record in your record set into your unbound controls
4) Move values from your unbound fields to your current record and update
5) Add a new record
6) Delete a record
7) Method (optional) to find a specific record
8) code to requery your record set after you do things so you are dealing with current data.

All of this is built in to the form itself if you are using a source query / table for your form. If you can request the same data through SQL, you can use a table definition or query to do the same. More important, if you have an existing query that you can run your form off of based on your SQL, why would you want to do this much work?

 

[bastanu]

Hi Pat,

I take your LOL was directed towards me so please have a look at the attached file. The link I provided was from MSDN not another forum thread. In my reply I did indeed have a mistake, I forgot to use Set to assign the ADO recordset to the subform's recordset. So that line should read:

Set Me.frmLocation.Form.recordset=rs1

You see that the code works fine, the trick is to leave the form recordsource property empty but to "bind" the controls (so the controlsource matches the underlying recordset fields).

Cheers,
Vlad

 

[Galaxiom]

While Lol999's efforts to do this don't really achieve anything over simply binding to the query, there are good reasons to bind forms to ADO Recordsets under some circumstances.

I saw an order of magnitude performance boost on a subform using a recordset from an ADO Command calling a parameterised Stored Procedure in SQL Server compared to using a conventional setup with a linked table as the subform RecordSource with Master and Child LinkFields.

Fabricated ADO Recordsets can be used on a form without requiring any query or table. I have used them to display information that does not need to be retained beyond the session.

Disconnected ADO Recordsets can be used to add fields to a form's recordset without requiring underlying records in a table. This can be used to add a selection checkbox to records on forms without using a temporary table.

 

[Lol999]

To be honest I have probably overthought or rather over complicated the matter by harking back to what I used to know.
I once did a full system in VB6 tied to an Access db using blank forms and using record sets and SQL to manipulate the display and the data.

Unfortunately perhaps Access forms don't behave like VB6 forms or I in my dotage have failed to recall exactly how I did what I did :banghead:

Nevertheless I will abandon this path and seek a different solution so you have been warned, I will be back

Thanks, Lol

 

[Mark_]

Unfortunately perhaps Access forms don't behave like VB6 forms or I in my dotage have failed to recall exactly how I did what I did :banghead:

Correct, Access forms do not behave the same as a "Form" in VB6. Access has built in code that handles most of the record manipulation for you and does not require you to manually write file I/O for each record in a record set.

 

[Galaxiom]

I know that you can bind a subform to a recordset but the other link implied that you could actually add records by populating form fields which of course is not possible when the record is unbound.

A form bound to an ADO recordset is a bound form. Records can be updated or added like any other bound form.

The trick to make this work with the underlying table is to disconnect the recordset before binding it to the form then reconnect it.

 

[Etxezarreta]

Hello, this is an old thread, but I am facing the same problem: the reason is I want to migrate the tables into SQL Server, and I read it was better to use the ADO way, and that working with an already "access made" query was a bit risky.
I am not sure it is worth the headache though.
Could you share your experience on this matter please?
Many thanks in advance.
Etxe.

 

[zeroaccess]

Pat,

Since we're on the topic, do you believe that opening a form with a WHERE condition (DoCmd.OpenForm, etc etc) is functionally equal in performance to adding a criteria to the form's query designer? When adding criteria and checking the SQL view, it adds a WHERE condition. What we want to avoid is having the server send back a set of records which are then filtered locally by Access. If your goal is to say, have the server only send back a single record, are both methods equal in your experience?

 

[Etxezarreta]

Hello, this is an old thread, but I am facing the same problem: the reason is I want to migrate the tables into SQL Server, and I read it was better to use the ADO way, and that working with an already "access made" query was a bit risky.
I am not sure it is worth the headache though.
Could you share your experience on this matter please?
Many thanks in advance.
Etxe.

Thanks a lot Pat,
This is the way I am trying to work.
Here is a short sample of code I use, does it fit with good practices?

str_SQL = "SELECT t_ListeContacts.[Nom_contact], t_ListeContacts.[Prenon_contact]," & _
"t_ListeContacts.[Nom_structure], t_ListeContacts.[Fonction], t_StatutJuridiqueSoignants.StatutJuridiqueSoignant, " & _
"t_IntitulesProfession.Profession, t_ListeContacts.[Departement], t_ListeContacts.[Ville] FROM t_StatutJuridiqueSoignants " & _
"INNER JOIN (t_IntitulesProfession INNER JOIN t_ListeContacts ON t_IntitulesProfession.ID_Profession = t_ListeContacts.FK_Profession) " & _
"ON t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant = t_ListeContacts.FK_Statut_juridique"


If (Len(str_CritereDepartement) <> 0) Then
str_SQL = str_SQL & " Where t_ListeContacts.Departement In (" & str_CritereDepartement & ")"
End If
Debug.Print str_SQL

oBjRcrdst_RecordSetSource.ActiveConnection = objConnection
oBjRcrdst_RecordSetSource.CursorType = adOpenDynamic
oBjRcrdst_RecordSetSource.LockType = adLockOptimistic

oBjRcrdst_RecordSetSource.Open str_SQL
With oBjRcrdst_RecordSetSource
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
Set Me.sf_Essai.Form.Recordset = Nothing
Set Me.sf_Essai.Form.Recordset = oBjRcrdst_RecordSetSource
End If
End With