Subform showing same record x times (1 Viewer)

AndrewB

Registered User
Joined
Sep 13, 2004
Messages
21
Hello

I have a form which has a listbox containing my tables. From that, I can successfully select a table and click on a button. There is a sub-form that has a recordsource that is set to the selected table. I am able to dynamically change the captions to the correct field names for the table and can successfully display data for the fields.

However, where a table has x records, I get x instances of the SAME record (the first on the table) rather than seeing x separate records. x happily changes according to the selected table.

What I am doing wrong? I am displaying the data in a datasheet as the sub-form;am using Access 2007; it is an Access Project with a SQL Server 2008 back-end.

Here is the code fired by the button when the table has been chosen from the list box.



Code:
Private Sub BUTTON_Go_Click()
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strSQL As String
   Dim i As Integer
   Dim j As Integer
         
   If Me.List0 = "Please select...." Then
        MsgBox "Please select one of the tables from the drop-down list"
        Me.List0.SetFocus
        Exit Sub
   End If
   
   
   'Use the ADO connection that Access uses
   Set cn = CurrentProject.AccessConnection

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   strSQL = "SELECT * FROM " & Me.List0.Value & ";"
   With rs
      Set .ActiveConnection = cn
      .Source = strSQL
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
 
 Me.Displayed_data.Form.RecordSource = Me.List0.Value
 Me.Displayed_data.Form.Requery
 
 rs.MoveFirst
 For j = 1 To rs.RecordCount
 With rs
         i = 0

            Me.Displayed_data.Controls("KEY_FIELD").Value = rs.Fields(0)
            For i = 1 To 4
                Me.Displayed_data.Controls("F" & i).Value = rs.Fields(i)
                Me.Displayed_data.Controls("F" & i & "_Label").Caption = rs.Fields(i).Name
            Next i
    .MoveNext
    End With
    Next j
   rs.Close
   
 
   Set rs = Nothing
   Set cn = Nothing

End Sub
Thanks
Andrew
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
What is being selected in the listbox? Is it the name of a query? If so, does that query have multiple tables in it and happen to not have any joins (so you are ending up with a Cartesian Product - number of records in one table multiplied by the number of records in the other)?
 

AndrewB

Registered User
Joined
Sep 13, 2004
Messages
21
Bob
Thanks for the reply.
The List box is being populated by a function and displays tables in the database that are being read from a collection.

I don't think it is a cartesian join issue. The number of records in the subform is always correct for the number of records in that table and the data is correct for the first record in that table - only problem is that the data is repeated every time.

Record counts shown in the navigation buttons correctly show 1 of 1 for the main form and 1 of <number of records in the relevant table> for the subform.
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
Bob
Thanks for the reply.
The List box is being populated by a function and displays tables in the database that are being read from a collection.

I don't think it is a cartesian join issue. The number of records in the subform is always correct for the number of records in that table and the data is correct for the first record in that table - only problem is that the data is repeated every time.

Record counts shown in the navigation buttons correctly show 1 of 1 for the main form and 1 of <number of records in the relevant table> for the subform.
I guess I would have to take a look. If you can post a copy of the database (with bogus data, not real), I can take a look and see if I can spot it. I can usually find the problem fairly quickly if I can see and touch it.

You would need to first run COMPACT AND REPAIR and then after that is done, right click on the file and select SEND TO > COMPRESSED FOLDER to zip it up and then post the zip file. The final zip file has to be at, or under, 2Mb (which should be possible if you remember to compact and repair first and then zip).
 

AndrewB

Registered User
Joined
Sep 13, 2004
Messages
21
Bob
Thanks for the offer of help. Here is the DB zipped and attached. It is still being developed so all the test data is bogus.

The form in question is called
FORM TO RUN SQL 2012-07-17.

As I said, the database is still being developed and I promise that won't be the final name for the form!
 

Attachments

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
Okay, this is more difficult to diagnose given that it is an ADP and I don't have the backend to see what is happening. But in looking at the code, I am wondering why you are assigning the form's recordsource to be the ADO Recordset and doing the Requery and then trying to go through and assign values to the controls. And the reason why you are getting the same data for each record is you are setting the control values but using a DATASHEET view. In that view, and the Continuous Form View, the controls look like there are more than one but in reality there is only one control and setting a value makes it look like that for all of the others.

So what you need to do is to set the control binding to the fields of the recordset:

Code:
Private Sub BUTTON_Go_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim i As Integer
    
    If Me.List0 = "Please select...." Then
        MsgBox "Please select one of the tables from the drop-down list"
        Me.List0.SetFocus
        Exit Sub
    End If

    'Use the ADO connection that Access uses
    Set cn = CurrentProject.AccessConnection
 
    'Create an instance of the ADO Recordset class, and
    'set its properties
    Set rs = New ADODB.Recordset
    strSQL = "SELECT * FROM " & Me.List0.Value & ";"

    With rs
        Set .ActiveConnection = cn
        .Source = strSQL
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
    End With
 
    'Set the form's Recordset property to the ADO recordset
    With Me.Displayed_data.Form
        .RecordSource = Me.List0.Value
        .Requery
[B][COLOR=red]        .KeyField.ControlSource = "=" & rs.Fields(0).Name[/COLOR][/B]
        For i = 1 To 4
[B][COLOR=red]            .Controls("F" & i).ControlSource = "=" & rs.Fields(i).Name
[/COLOR][/B]        Next i
        .MoveNext
    End With
    rs.Close

    Set rs = Nothing
    Set cn = Nothing
End Sub
Now, I can't remember off hand if you need quotes around the field names when setting the control source via code. So you may need to use like this:

Code:
.Controls("F" & i).ControlSource = "=" & [B][COLOR=red]Chr(34) & [/COLOR][/B]rs.Fields(i).Name [B][COLOR=red]& Chr(34)[/COLOR][/B]
 

AndrewB

Registered User
Joined
Sep 13, 2004
Messages
21
Bob

Many thanks. This has sorted it for me.
I didn't need to use the quotes around the field names but did add one line to get my caption names showing the correct names in the datasheet column headers.

Code:
     With Me.Displayed_data.Form
        .RecordSource = Me.List0.Value
        .Requery
        .Controls("KEY_FIELD").ControlSource = "=" & rs.Fields(0).Name

                   For i = 1 To 4
[COLOR="Red"][B]                       .Controls("F" & i & "_Label").Caption = rs.Fields(i).Name[/B][/COLOR]
                       .Controls("F" & i).ControlSource = "=" & rs.Fields(i).Name
                   Next i
                   If rs.EOF Then Exit Sub Else rs.MoveNext

       End With
I really appreciate you taking the time to look at the code for me.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom