Link to home
Start Free TrialLog in
Avatar of jjjjjjj
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.mdb")

Set rst = dbs.openrecordset("SELECT TblStudent.StNo, TblStudent.Name, TblTranscript.Desc,TblTranscript.Grade, 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
Avatar of Glen Richmond
Glen Richmond
Flag of United Kingdom of Great Britain and Northern Ireland image

use the where cause using the variable from the use inter face say its a name and is input into a textbox by the user to use this it would look like this

"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
Avatar of jjjjjjj
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
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.
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.
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






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.


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)






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.
rjjr changed the proposed answer to a comment
Avatar of jjjjjjj

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
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



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.
Avatar of jjjjjjj

ASKER

Just got back from some time off.  I will review and respond.  Thanks for the help,

jjjjjjj
Avatar of 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,TblTranscript.Grade, 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
ASKER CERTIFIED SOLUTION
Avatar of Glen Richmond
Glen Richmond
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,TblTranscript.Grade, 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

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...
Avatar of jjjjjjj

ASKER

Glenrichmond,

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