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

 

[Pat Hartman]

Lol - did you actually read the thread you think tells you that you can do this?

Why would you not simply bind the subform to the query?

 

[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.

 

[Pat Hartman]

@bastanu, I was commenting on a link posted by Lol999. Not your link. 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.

Now that we know that Lol999 was only using an unbound form because he thought that was how Access worked, we can dispense with all the code and do this the Access way by using bound forms.

 

[Pat Hartman]

@lol999,
To bind a form, choose a table or query as the RecordSource. That connects you to all the columns in the RecordSource and you can use the add new fields option to drag fields from the RecordSource and place them on the form. OR, you can start with a wizard and let it build the form from the RecordSource you specify. Then when it is done, you can pretty up the layout.

Technically, you don't need a single line of code on a bound form to make anything happen. When you press a navigation button, Access scrolls the recordset. If you modify a value in a control, Access recognizes that you have dirtied the form and will save it whenever you leave the current record - no matter how you leave it. Moving the insertion point from the main form to the subform results in the main form record being saved because Access knows that it makes no sense to add subform records without a foreign key.

In practice, the majority of code that you will need to write will end up in the Form's BeforeUpdate event and that code will be validation code to make sure that required fields are present and valid. Amateurs don't ever even consider that validation code is needed but it is. Experienced programmers who have used other platforms always think that they have to do everything themselves because in their previous world they did but Access is a Rapid Application Development tool and it does an enormous amount of the tedious work for you. Your best tactic is to allow Access to do it's thing and jump in here and there to add customization and of course, validation. Just remember that the Form's BeforeUpdate event is like the flapper at the end of a funnel. ALL saves of bound forms MUST pass through this event regardless of what caused the save. Therefore, this is the event you use to validate data and either allow it to be saved or prevent it from being saved. If you attempt to use other events, you will be constantly chasing holes that need patching. To cancel a save due to a validation error:

If Me.MyDate < Date() Then
    Msgbox "Date must be >= Today.",vbOKOnly
    [B][COLOR="Red"]Cancel = True[/COLOR][/B]
    Me.MyDate.SetFocus
    Exit Sub
End If

 

[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.

 

[Pat Hartman]

My apps rarely use Jet/ACE as the BE. Most use SQL Server but others use the preferred RDBMS of the client such as Oracle, DB2, Sybase, etc. And I NEVER have to resort to the method you are asking how to do by choice.

Access is a RAD (Rapid Application Development) tool. A huge part of the benefit of using Access is using bound forms. If you are not going to use bound forms, you shouldn't be using Access. You are accepting ALL the limitations of Access but choosing to eschew the biggest benefit.

However, simply migrating the tables to SQL Server and doing nothing to the app (if the app was not designed using good client/server techniques) will make the app slower than with the Jet/ACE BE.

So, what do you have to do? Start by making sure that all forms are bound to queries that include selection criteria. The idea is to reduce the number of rows that Access requests from the server for each form. Typical Access methodology is to bind to a raw table or query with no WHERE clause and then use filtering locally. This is the worst possible scenario. All of my forms are either restricted on load by a prior form or have selection criteria on the form header that can be used later and the forms load "empty". So, if I want to filter by ClientID, there will be either an unbound textbox or combo where the desired ClientID is entered. Then a "button" to requery the form. The form's query includes the Where clause

Where ClientID = Forms!myform!cboClientID

So, when the form opens, the combo is empty and nothing is selected. Once the combo is populated and you requery the form, the query runs again and this time a single record is selected out of potentially millions. This technique alone may be all you need to do to get acceptable performance. Other things you might need to do.
1. Create views for complex joins and use the views in your queries.
2. Create stored procedures to create recordsets for complex reports.

 

[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?