A
Alex
Hello everyone,
I trying to populate a subform (Datasheet view) from a recordset created on
the fly. The problem that I'm having is that its not showing the data on the
subform. I'm getting an #Name? error. The amount of records matches the
recordset row count return with the amount of rows in the subform. This
makes me believe that at least the form is recognizing the recordset. In the
subform, I'm using two unbound Text box controls, named File Name and
Status. Nor the sub Form or the parent form has a Record Source attached to
it.
Logic Explanation:
- The program connects to a table and loops thru each field to
get the last date stored in each row on fields where the type = date. The
table has about 150 fields.
- The user who created this db, created about 100 fields to
store the status date of when the data was modified individually.
The code serves it's purpose, but I can't seem to populate the data in a
subform. My knowledge in access is limited but I can't find any information
describing my problem.
Any help is appreciated.
Alex
Here is the code:
' --------------------------------------------------------------------------
-----------
Dim strSQL As String
Dim rsData As New ADODB.Recordset
Dim rsCloneDB As New ADODB.Recordset
Dim AdoCn As ADODB.Connection
Dim intFields As Integer
Dim datLatest As String ' - As string
On Error GoTo errHandler
'SQL
strSQL = "SELECT * FROM [Transactions Log]"
'Open data
Set AdoCn = CurrentProject.Connection
rsData.Open strSQL, AdoCn, adOpenForwardOnly
'Any Records?
If rsData.EOF And rsData.BOF Then
MsgBox "No records found.", vbInformation, "No record found"
GoTo CloseDB
End If
'Open / Clone rst
rsCloneDB.Fields.Append "sFileName", adVarChar, 100, adFldIsNullable
rsCloneDB.Fields.Append "dStatus", adVarChar, 10, adFldIsNullable
rsCloneDB.Open , , adOpenForwardOnly
'Analyze data...
Do Until rsData.EOF
For intFields = 0 To rsData.Fields.Count - 1
If rsData.Fields(intFields).Type = adDate Then
If datLatest < rsData(intFields) Then datLatest =
rsData(intFields)
End If
Next
'Clone data
rsCloneDB.AddNew Array("sFileName", "dStatus"),
Array(rsData("sFileName"), datLatest)
datLatest = ""
rsData.MoveNext
Loop
Set Me.StatusSub.Form.Recordset = rsCloneDB
Me.StatusSub.Form.Controls("File Name").ControlSource =
rsCloneDB("sFileName")
Me.StatusSub.Form.Controls("Status").ControlSource =
rsCloneDB("dStatus")
'Close Objects
CloseDB:
rsData.Close
Set rsData = Nothing
rsCloneDB.Close
Set rsCloneDB = Nothing
'Error Handler
Exit Function
errHandler:
MsgBox "An error has occurred on frmStatus.LoadStatusData." & vbCrLf & _
"Description: " & Err.Description, vbInformation, "Error"
End Function
I trying to populate a subform (Datasheet view) from a recordset created on
the fly. The problem that I'm having is that its not showing the data on the
subform. I'm getting an #Name? error. The amount of records matches the
recordset row count return with the amount of rows in the subform. This
makes me believe that at least the form is recognizing the recordset. In the
subform, I'm using two unbound Text box controls, named File Name and
Status. Nor the sub Form or the parent form has a Record Source attached to
it.
Logic Explanation:
- The program connects to a table and loops thru each field to
get the last date stored in each row on fields where the type = date. The
table has about 150 fields.
- The user who created this db, created about 100 fields to
store the status date of when the data was modified individually.
The code serves it's purpose, but I can't seem to populate the data in a
subform. My knowledge in access is limited but I can't find any information
describing my problem.
Any help is appreciated.
Alex
Here is the code:
' --------------------------------------------------------------------------
-----------
Dim strSQL As String
Dim rsData As New ADODB.Recordset
Dim rsCloneDB As New ADODB.Recordset
Dim AdoCn As ADODB.Connection
Dim intFields As Integer
Dim datLatest As String ' - As string
On Error GoTo errHandler
'SQL
strSQL = "SELECT * FROM [Transactions Log]"
'Open data
Set AdoCn = CurrentProject.Connection
rsData.Open strSQL, AdoCn, adOpenForwardOnly
'Any Records?
If rsData.EOF And rsData.BOF Then
MsgBox "No records found.", vbInformation, "No record found"
GoTo CloseDB
End If
'Open / Clone rst
rsCloneDB.Fields.Append "sFileName", adVarChar, 100, adFldIsNullable
rsCloneDB.Fields.Append "dStatus", adVarChar, 10, adFldIsNullable
rsCloneDB.Open , , adOpenForwardOnly
'Analyze data...
Do Until rsData.EOF
For intFields = 0 To rsData.Fields.Count - 1
If rsData.Fields(intFields).Type = adDate Then
If datLatest < rsData(intFields) Then datLatest =
rsData(intFields)
End If
Next
'Clone data
rsCloneDB.AddNew Array("sFileName", "dStatus"),
Array(rsData("sFileName"), datLatest)
datLatest = ""
rsData.MoveNext
Loop
Set Me.StatusSub.Form.Recordset = rsCloneDB
Me.StatusSub.Form.Controls("File Name").ControlSource =
rsCloneDB("sFileName")
Me.StatusSub.Form.Controls("Status").ControlSource =
rsCloneDB("dStatus")
'Close Objects
CloseDB:
rsData.Close
Set rsData = Nothing
rsCloneDB.Close
Set rsCloneDB = Nothing
'Error Handler
Exit Function
errHandler:
MsgBox "An error has occurred on frmStatus.LoadStatusData." & vbCrLf & _
"Description: " & Err.Description, vbInformation, "Error"
End Function