Solved

Reading in records from Access

Posted on 2000-03-16
19
267 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:jjjjjjj
  • 10
  • 5
  • 2
  • +1
19 Comments
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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
0
 
LVL 1

Author Comment

by:jjjjjjj
Comment Utility
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
0
 
LVL 4

Expert Comment

by:wileecoy
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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






0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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.


0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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)






0
 

Expert Comment

by:rjjr
Comment Utility
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.
0
 

Expert Comment

by:rjjr
Comment Utility
rjjr changed the proposed answer to a comment
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:jjjjjjj
Comment Utility
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
0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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



0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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.
0
 
LVL 1

Author Comment

by:jjjjjjj
Comment Utility
Just got back from some time off.  I will review and respond.  Thanks for the help,

jjjjjjj
0
 
LVL 1

Author Comment

by:jjjjjjj
Comment Utility
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
0
 
LVL 2

Accepted Solution

by:
Glen Richmond earned 50 total points
Comment Utility
ive found a few things that could need changing, the outer brakets on the sql can go because this will stop you appending the where clause on cos youd end up with Where ")blah.blah='blah'

also the clause added onto the sql had the single inverted commas in the wrong place.

yours: sql = sql + "'tblstudent.stno=" & Text1.Text & "'"

mine: sql = sql + "tblstudent.stno='" & Text1.Text & "'"

the error message is caused by a field name not beeing found in the table. i think in this case its when referencing a field in the rst object that doesnt excist.

also changed the addmethod.

yours: List1.AddItem rst!TblStudent.name

mine: List1.AddItem rst!name

Also added the fornext in incase you want it

give this a go and let me know how it goes, you could send my the project and DB if you like and ill have a go at fixing it if you still have trouble

mailto:glenrichmond@hotmail.com

<---------------------->


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

for a=1 to reccount 'only if you want to put all recordse found into list

     List1.AddItem rst!name

rs.movenext
next a
dbs.Close
End Sub

0
 
LVL 4

Expert Comment

by:wileecoy
Comment Utility
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

0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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...
0
 
LVL 1

Author Comment

by:jjjjjjj
Comment Utility
Glenrichmond,

I have accepted your answer although I have not finalized my project.

Thanks for your help,

jjjjjjj
0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
cheers jjjjjjj but ive posted my mail address so anymore help just mail me..
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now