Display Resultset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. I have code that uses DoCmd.RunSQL (strSQL) when button is pressed. Code works find, but do not know how to take result sets and display as a messagebox. I have tried MsgBox(strSQL), but that just displays the SQL statement. Any simple solution?

Code:

rivate Sub DisplayScore_Click()
Dim strSQL, strName As String
strName = Me!cboStudent
strSQL = ("SELECT [Student], [Score] FROM table2 WHERE [Student] = " & strName & "'")
MsgBox (strSQL)

Toco
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Create a Query like this (SQL view). Name it qryStudentScores:

PARAMETERS Forms!FormName!cboStudent Text;
SELECT [Student], [Score]
FROM table2
WHERE [Student] = Forms!FormName!cboStudent

Change the FormName to whatever your form's name is.

If you just want to display the query in a Datasheet view on the
screen (code):

DoCmd.OpenQuery "qryStudentScores"

The query's result set will be displayed on the screen.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBCOxIechKqOuFEgEQKaDACg6LUy26QbGVdbeBwOE4zFfCPzq+wAnRPw
qLiX7f//URWrSwb9lf6ce+yF
=C/yf
-----END PGP SIGNATURE-----
 
Hello. I have code that uses DoCmd.RunSQL (strSQL) when button is pressed.
Click to expand...
Code works find, but do not know how to take result sets and display
as a message box. I have tried MsgBox(strSQL), but that just displays
the SQL statement. Any simple solution?
Code:

rivate Sub DisplayScore_Click()
Dim strSQL, strName As String
strName = Me!cboStudent
strSQL = ("SELECT [Student], [Score] FROM table2 WHERE [Student] = " & strName & "'")
MsgBox (strSQL)

Toco
Click to expand...

I don't see how the code can work fine, as RunSQL can not be used with
a Select query, only with an action query (Delete, Update, etc.)

If all you wish to do is display a student's score in a message box,
assuming the combo box has a string as it's bound column (your use of
strName As String), try:
MsgBox Me!cboStudent & " score is " &
DLookUp("[Score]","Table2","[Student] = '" & Me!cboStudent & "'")

According to your code, cboStudent carries the Student Name as Text in
it's bound column (Dim strName As String, strName = Me!cboStudent),
but you left off a single quote in the Where clause.
Note: you are also missing the "P" in Private Sub ...

There is then no need to look up the Student name. You can refer to
the combo box directly. The DLookUp will then match the Student to his
score.

However, you do have some potential difficulty if the Student Name is
the bound column of the combo box. What if there are 2 or more
students with the same name in that school/class? How do you select
the correct one.

Use at least 2 columns (more if needed to differentiate between
students with the same name).
StudentID (bound column) and StudentName (column1)

If the combo box bound column is a Number datatype and the text
Student Name is the 2nd column, you would use:
MsgBox Me!cboStudent.Column(1) & " score is " &
DLookUp("[Score]","Table2","[StudentID] = " & Me!cboStudent)
 
Back
Top