G
Guest
I am Trying to create a recordset from an SQL query and cycle through the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why. This is
what I have so far:
Private Function Dateline()
On Error Resume Next
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit working'
strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host Data]![HostId]);
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn
If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF
Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop
End If
End Function
I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why. This is
what I have so far:
Private Function Dateline()
On Error Resume Next
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit working'
strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host Data]![HostId]);
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn
If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF
Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop
End If
End Function
I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.