Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Getting data in sql queries using visual basic in access
- Thread starter mk2lee1
- Start date
chirpyform
Programmer
if i understood you do something like
dim db as database
dim rcs as recordset
set db = currentdb
set rcs = db.openrecordset("queryname"
to have the data you do either
rcs.Fields("fieldname".value
or
rcs!fieldname
to got through the records i do:
if rcs.recordCount <> 0 then
rcs.movelast
rcs.movefirst
for i = 0 to rcs.recordCount
variable = rcs!fieldname
rcs.movenext
next i
end if
dim db as database
dim rcs as recordset
set db = currentdb
set rcs = db.openrecordset("queryname"
to have the data you do either
rcs.Fields("fieldname"
.valueor
rcs!fieldname
to got through the records i do:
if rcs.recordCount <> 0 then
rcs.movelast
rcs.movefirst
for i = 0 to rcs.recordCount
variable = rcs!fieldname
rcs.movenext
next i
end if
- Thread starter
- #3
- Thread starter
- #5
This is what I wrote.
And in my querysection, I have query called "testDate1MonthNotice" And within this query, there is 3 field retrieving. SchoolName, P-ID, and TestDate.
I get erroe in here saying that "Item not found in this collection"
With rst.Fields(SchoolName).Value
==============================================
Dim db As DAO.Database, rst As DAO.Recordset
Dim i As Integer
Dim variable As String
Dim msg As String
Set db = CurrentDb
Set rst = db.OpenRecordset("testDate1MonthNotice"
With rst.Fields(SchoolName).Value
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
For i = 0 To rst.RecordCount
variable = rst!SchoolName
rst.MoveNext
Next i
End If
If variable <> "" Then
MsgBox "hi", vbOKOnly, "Message Title"
End If
End With
And in my querysection, I have query called "testDate1MonthNotice" And within this query, there is 3 field retrieving. SchoolName, P-ID, and TestDate.
I get erroe in here saying that "Item not found in this collection"
With rst.Fields(SchoolName).Value
==============================================
Dim db As DAO.Database, rst As DAO.Recordset
Dim i As Integer
Dim variable As String
Dim msg As String
Set db = CurrentDb
Set rst = db.OpenRecordset("testDate1MonthNotice"
With rst.Fields(SchoolName).Value
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
For i = 0 To rst.RecordCount
variable = rst!SchoolName
rst.MoveNext
Next i
End If
If variable <> "" Then
MsgBox "hi", vbOKOnly, "Message Title"
End If
End With
chirpyform
Programmer
in my version i don't need to put DAO.Database, have you tried looking at the help for CurrentDb?
- Thread starter
- #7
I now have changed my code this this
but I get type mismatch error at
Set rst = db.OpenRecordset(SQL)
Any idea?
==========================================
Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim i As Integer
Dim SQL As String
SQL = "SELECT [school].[schoolName], [Participation].[ParticipationID], [Participation].[TestDAte]" & _
"FROM school RIGHT JOIN Participation ON [Participation].[SchoolID]=[School].[SchooliD]" & _
"WHERE ([Participation].[testDate]-date()<=30 And [Participation].[testDate]-date()>0)"
Set db = CurrentDb
Set rst = db.OpenRecordset(SQL)
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
For i = 0 To rst.RecordCount
variable = rst.Fields(i)
rst.MoveNext
Next i
End If
but I get type mismatch error at
Set rst = db.OpenRecordset(SQL)
Any idea?
==========================================
Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim i As Integer
Dim SQL As String
SQL = "SELECT [school].[schoolName], [Participation].[ParticipationID], [Participation].[TestDAte]" & _
"FROM school RIGHT JOIN Participation ON [Participation].[SchoolID]=[School].[SchooliD]" & _
"WHERE ([Participation].[testDate]-date()<=30 And [Participation].[testDate]-date()>0)"
Set db = CurrentDb
Set rst = db.OpenRecordset(SQL)
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
For i = 0 To rst.RecordCount
variable = rst.Fields(i)
rst.MoveNext
Next i
End If
chirpyform
Programmer
more than likely in the SQL statement. Create it with access to test if it works.
- Thread starter
- #9
- Status
Similar threads