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.
Thanks
Andrew
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