jjjjjjj
asked on
Reading in records from Access
Here is my code:
Dim dbs As Database
Dim rst As Recordset
Set dbs = OpenDatabase("a\studinfo.m db")
Set rst = dbs.openrecordset("SELECT TblStudent.StNo, TblStudent.Name, TblTranscript.Desc,TblTran script.Gra de, TblCourses.Credits " _
& "FROM TblCourses INNER JOIN (TblStudent INNER JOIN TblTranscript ON TblStudent.StNo = TblTranscript.StNo) " _
& "ON TblCourses.Desc = TblTranscript.Desc;")
This SQL, as I understand it establishes a frame in which records can be retrieved, once the query is executed. Since this is a select query, I want to do the following in VB:
1. execute the select SQL above so that records are retrieved into the rst based on entered data (in a separate textbox)which could reside in one of the fields, and then return the records which represent the entire recordset.
An example is a user enters a student ID number in a textbox control and this data is used as the parameter by which the query returns data.
3. Populate a listview object with the returned records.
Is what you see here the best way to do this? If it is then please help me to complete this project. If you have a better way then please advise.
Regards,
jjjjjjj
Dim dbs As Database
Dim rst As Recordset
Set dbs = OpenDatabase("a\studinfo.m
Set rst = dbs.openrecordset("SELECT TblStudent.StNo, TblStudent.Name, TblTranscript.Desc,TblTran
& "FROM TblCourses INNER JOIN (TblStudent INNER JOIN TblTranscript ON TblStudent.StNo = TblTranscript.StNo) " _
& "ON TblCourses.Desc = TblTranscript.Desc;")
This SQL, as I understand it establishes a frame in which records can be retrieved, once the query is executed. Since this is a select query, I want to do the following in VB:
1. execute the select SQL above so that records are retrieved into the rst based on entered data (in a separate textbox)which could reside in one of the fields, and then return the records which represent the entire recordset.
An example is a user enters a student ID number in a textbox control and this data is used as the parameter by which the query returns data.
3. Populate a listview object with the returned records.
Is what you see here the best way to do this? If it is then please help me to complete this project. If you have a better way then please advise.
Regards,
jjjjjjj
ASKER
Thanks for the help, but that is not a dynamic solution. If I were to do that then I would have no way of allowing the user to select any other values other than the one that is hard coded in the SQL system.
Therefore I need to reject that answer.
jjjjjjj
Therefore I need to reject that answer.
jjjjjjj
Actually, if you combine your code above with the rejected answer, you do have a dynamic process.
The SQL query that you coded will bring up all matching records from the joined tables (all students).
However, you don't want all matching records, you want specific records - one student matching the user input StudentID (as you indicated, you want the user to set parameters for the SQL statement).
Using your code:
....FROM TblCourses INNER JOIN (TblStudent ...
If you want the user to specify a Student ID in a textbox to bring up a specific record, ....
....From TblCourses Where StudentID = '" & Trim(txStudentID.Text) & "' INNER JOIN (TblStudent...
(Where "StudentID" is the field name in the database that stores the Student ID#)
This code will run the SQL statement with the users input from the textbox.
Let me know what you think, if this is going in the right direction or not.
Thanks.
The SQL query that you coded will bring up all matching records from the joined tables (all students).
However, you don't want all matching records, you want specific records - one student matching the user input StudentID (as you indicated, you want the user to set parameters for the SQL statement).
Using your code:
....FROM TblCourses INNER JOIN (TblStudent ...
If you want the user to specify a Student ID in a textbox to bring up a specific record, ....
....From TblCourses Where StudentID = '" & Trim(txStudentID.Text) & "' INNER JOIN (TblStudent...
(Where "StudentID" is the field name in the database that stores the Student ID#)
This code will run the SQL statement with the users input from the textbox.
Let me know what you think, if this is going in the right direction or not.
Thanks.
yes my origonal solution can be dynamic as the value of the where clause can be input at run time. the 'me.EnterdName.text' value does come from a control and may have any value inserted to act as a clause in your sql.
ill send you some apps ive written using a similar method if you like.
good luck.
ill send you some apps ive written using a similar method if you like.
good luck.
i think i know what you are after though you could use the method ive previously shown and use this to build a sql string determined by the textboxes on the form that have data in: a search engine if you like...
what you will have to do is determin which boxes have a search paramiter in and and a string to the sql....
dim sql as string
sql = "select * from MyTable Where "
if text1.text<>"" then
sql=sql+ "Name='" & me.EnterdName.text & "'"
end if
if text2.text<>"" then
sql=sql+ ID=" & me.EnterdID.text & ""
end if
if text3.text<>"" then
sql=sql+Age='" & me.EnterdAge.text & "'"
end if
rs=db.openrecordset(sql)
sorry its a bit sloppy but im a work and in a hurry, so you mite need to clean it up
gool luck
Glen Richmond
what you will have to do is determin which boxes have a search paramiter in and and a string to the sql....
dim sql as string
sql = "select * from MyTable Where "
if text1.text<>"" then
sql=sql+ "Name='" & me.EnterdName.text & "'"
end if
if text2.text<>"" then
sql=sql+ ID=" & me.EnterdID.text & ""
end if
if text3.text<>"" then
sql=sql+Age='" & me.EnterdAge.text & "'"
end if
rs=db.openrecordset(sql)
sorry its a bit sloppy but im a work and in a hurry, so you mite need to clean it up
gool luck
Glen Richmond
sql = "select * from MyTable Where "
if me.EnterdName.text<>"" then
sql=sql+ "Name='" & me.EnterdName.text & "'"
end if
if me.EnterdID.text<>"" then
sql=sql+ ID=" & me.EnterdID.text & ""
end if
if me.EnterdAge.text<>"" then
sql=sql+Age='" & me.EnterdAge.text & "'"
end if
rs=db.openrecordset(sql)
just making it more readable.
if me.EnterdName.text<>"" then
sql=sql+ "Name='" & me.EnterdName.text & "'"
end if
if me.EnterdID.text<>"" then
sql=sql+ ID=" & me.EnterdID.text & ""
end if
if me.EnterdAge.text<>"" then
sql=sql+Age='" & me.EnterdAge.text & "'"
end if
rs=db.openrecordset(sql)
just making it more readable.
sorry about this you will also need to put the AND clause if there is more than one criteria added to the sql string so youll have to handle this in code.
sql = "select * from MyTable Where "
if me.EnterdName.text<>"" then
sql=sql+ "Name='" & me.EnterdName.text & "'"
morethatone=true
end if
if me.EnterdID.text<>"" then
if morethanone=true then
sql=sql+ " AND ID=" & me.EnterdID.text & ""
else
sql=sql+ " ID=" & me.EnterdID.text & ""
end if
end if
rs=db.openrecordset(sql)
sql = "select * from MyTable Where "
if me.EnterdName.text<>"" then
sql=sql+ "Name='" & me.EnterdName.text & "'"
morethatone=true
end if
if me.EnterdID.text<>"" then
if morethanone=true then
sql=sql+ " AND ID=" & me.EnterdID.text & ""
else
sql=sql+ " ID=" & me.EnterdID.text & ""
end if
end if
rs=db.openrecordset(sql)
Try setting up the query in the access database and call the query in VB supplying the parameter.
I can generate a sample if you are interested.
I can generate a sample if you are interested.
rjjr changed the proposed answer to a comment
ASKER
Glenrichmond,
You are definitely on the trail!!!
I have taken your follow on suggestions and have applied them to the code structure, but now I cannot populate a listview object with the results of the sql (which now should be populating the Recordset, correct?)
Is the recordset text delimited at this point? If so, do I need to parce it out and populate the listview with the items that make up the RST?
If I need to increase the points, I will. Please advise,
jjjjjjj
You are definitely on the trail!!!
I have taken your follow on suggestions and have applied them to the code structure, but now I cannot populate a listview object with the results of the sql (which now should be populating the Recordset, correct?)
Is the recordset text delimited at this point? If so, do I need to parce it out and populate the listview with the items that make up the RST?
If I need to increase the points, I will. Please advise,
jjjjjjj
once you have applied the sql to a recordset you can add it to you list view by 2 methods in vb use the add method ie..
set rs=db.openrecordset(sql)
if rs.eof then :msgbox("Nothing found"): exit sub
rs.movelast
reccount=rs.recordcount
rs.movefirst
for a=1 to reccount
list1..AddItem rs!FieldName
rs.movenext
next a
this will add a field value to the list box you can add more than one to the the line by...
list1..AddItem rs!FieldName + ", " + rs!NextField
if your using access set the list box record source to the sql ie..
MyList.recordsource=sql
this will populate the list box you may have to adjust the column count and size etc..
good luck
let me know how it goes
set rs=db.openrecordset(sql)
if rs.eof then :msgbox("Nothing found"): exit sub
rs.movelast
reccount=rs.recordcount
rs.movefirst
for a=1 to reccount
list1..AddItem rs!FieldName
rs.movenext
next a
this will add a field value to the list box you can add more than one to the the line by...
list1..AddItem rs!FieldName + ", " + rs!NextField
if your using access set the list box record source to the sql ie..
MyList.recordsource=sql
this will populate the list box you may have to adjust the column count and size etc..
good luck
let me know how it goes
PS a recordset object is a genuine Object Class with properties and methods if you review the object browser you will get a better handle on the recordset object.
when you apply a query to it each field asked for by the query becomes a propertie of the recodset object where you can set values or reteive by field name.
when you apply a query to it each field asked for by the query becomes a propertie of the recodset object where you can set values or reteive by field name.
ASKER
Just got back from some time off. I will review and respond. Thanks for the help,
jjjjjjj
jjjjjjj
ASKER
GlenRichmond,
You have been very patient with me. Thanks.
I have taken your suggestions and have tried to implement the code (below) but get an error runtime error 3265 "item not found in this collection."
All I want to do is to have the user enter a value in the text1.text and then return a recordset based on the value they have selected. There could be multiple records so I would like to see them all.
Again, if I need to increase the points I will. Please advise.
Private Sub CmdFindStudent_Click()
Dim dbs As Database
Dim rst As Recordset
Set dbs = OpenDatabase("a:\studinfo. mdb")
Dim reccount As Integer
Dim a As Integer
Dim sql As String
sql = ("SELECT TblStudent.StNo, TblStudent.Name, TblTranscript.Desc,TblTran script.Gra de, TblCourses.Credits " _
& "FROM TblCourses INNER JOIN (TblStudent INNER JOIN TblTranscript ON TblStudent.StNo = TblTranscript.StNo) " _
& "ON TblCourses.Desc = TblTranscript.Desc WHERE ")
If Text1.Text <> "" Then
sql = sql + "'tblstudent.stno=" & Text1.Text & "'"
End If
Set rst = dbs.OpenRecordset(sql)
If rst.EOF Then: MsgBox ("nothing found"): Exit Sub
rst.MoveLast
reccount = rst.RecordCount
rst.MoveFirst
List1.AddItem rst!TblStudent.name
dbs.Close
End Sub
You have been very patient with me. Thanks.
I have taken your suggestions and have tried to implement the code (below) but get an error runtime error 3265 "item not found in this collection."
All I want to do is to have the user enter a value in the text1.text and then return a recordset based on the value they have selected. There could be multiple records so I would like to see them all.
Again, if I need to increase the points I will. Please advise.
Private Sub CmdFindStudent_Click()
Dim dbs As Database
Dim rst As Recordset
Set dbs = OpenDatabase("a:\studinfo.
Dim reccount As Integer
Dim a As Integer
Dim sql As String
sql = ("SELECT TblStudent.StNo, TblStudent.Name, TblTranscript.Desc,TblTran
& "FROM TblCourses INNER JOIN (TblStudent INNER JOIN TblTranscript ON TblStudent.StNo = TblTranscript.StNo) " _
& "ON TblCourses.Desc = TblTranscript.Desc WHERE ")
If Text1.Text <> "" Then
sql = sql + "'tblstudent.stno=" & Text1.Text & "'"
End If
Set rst = dbs.OpenRecordset(sql)
If rst.EOF Then: MsgBox ("nothing found"): Exit Sub
rst.MoveLast
reccount = rst.RecordCount
rst.MoveFirst
List1.AddItem rst!TblStudent.name
dbs.Close
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As I read through, glenrichmond addressed most of the things that I noticed in the code you included in your last message.
The only thing that I would add is that you should move the positioning of the for..loop that includes the list1.additem - if I understood your objective.
If you want to list only those records found in the recordset selected, you need to move the loop to the following:
sql = "SELECT TblStudent.StNo, TblStudent.Name, TblTranscript.Desc,TblTran script.Gra de, TblCourses.Credits " _
& "FROM TblCourses INNER JOIN (TblStudent INNER JOIN TblTranscript ON TblStudent.StNo = TblTranscript.StNo) " _
& "ON TblCourses.Desc = TblTranscript.Desc WHERE "
If Text1.Text <> "" Then
sql = sql + "tblstudent.stno='" & Text1.Text & "'"
End If
Set rst = dbs.OpenRecordset(sql)
If rst.EOF Then
MsgBox ("nothing found")
Exit Sub
Else
List1.Clear
rst.MoveFirst
For a=1 to rst.RecordCount
List1.AddItem rst!name
rst.MoveNext
Next a
RecCount = rst.RecordCount
End If
dbs.Close
End Sub
The only thing that I would add is that you should move the positioning of the for..loop that includes the list1.additem - if I understood your objective.
If you want to list only those records found in the recordset selected, you need to move the loop to the following:
sql = "SELECT TblStudent.StNo, TblStudent.Name, TblTranscript.Desc,TblTran
& "FROM TblCourses INNER JOIN (TblStudent INNER JOIN TblTranscript ON TblStudent.StNo = TblTranscript.StNo) " _
& "ON TblCourses.Desc = TblTranscript.Desc WHERE "
If Text1.Text <> "" Then
sql = sql + "tblstudent.stno='" & Text1.Text & "'"
End If
Set rst = dbs.OpenRecordset(sql)
If rst.EOF Then
MsgBox ("nothing found")
Exit Sub
Else
List1.Clear
rst.MoveFirst
For a=1 to rst.RecordCount
List1.AddItem rst!name
rst.MoveNext
Next a
RecCount = rst.RecordCount
End If
dbs.Close
End Sub
wileecoy yours appears no different from mine except the structure of the If statment and use of rs.recordcount
It certanly will do nothing different.
but it is useful to include the List.clear to refresh the list box every time the query is run...
It certanly will do nothing different.
but it is useful to include the List.clear to refresh the list box every time the query is run...
ASKER
Glenrichmond,
I have accepted your answer although I have not finalized my project.
Thanks for your help,
jjjjjjj
I have accepted your answer although I have not finalized my project.
Thanks for your help,
jjjjjjj
cheers jjjjjjj but ive posted my mail address so anymore help just mail me..
"select * from MyTable Where Name='" & me.EnterdName.text & "'"
for a number it would look like this
"select * from MyTable Where ID=" & me.EnterdNumber.text & ""
hope this helps
good luck
contact for more help
glenrichmond@hotmail.com