Help with Access Report

[DanielConserv]

Hi,

I have a problem with selecting records that i want and sending them to the report for preview.

I have a table called student. In the table, i have student_ID, std_name, std_addr, std_coursetaken and so-on.

And in the form, i have a listbox that lists out all the students. Also, i have a button called make report that takes all student_ID enlisted on the listbox and print-preview them on the report.

My problem is that before it opens the report, I just want students with all courses but not with "Computer science" to be on the report.

Here is my code.

nList = Me.LB_PrintSelection.ListCount

For ncount = 0 To nList - 1
stLinkCriteria = "," & Me.LB_PrintSelection.ItemData(ncount) & stLinkCriteria
Next ncount

If Len(stLinkCriteria) > 0 Then
stLinkCriteria = "[student_ID] In (" & Mid(stLinkCriteria, 2) & ")"
End If

stDocName = "Student_report"

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

And i think i have tried the hard way that is to open the student table and do the filtering from there. But i've got error message says run-time '91' something like that.

Here is the code that i tried and received the error mesg.

dim rsNew as ADODB.recordset

nList = Me.LB_PrintSelection.ListCount

rsNew.Open "Select * from student_table", CurrentProject.Connection, adOpenForwardOnly
For ncount = 0 To nList - 1
Do Until rsNew.EOF
If rsNew!student_ID = Me.LB_PrintSelection.ItemData(ncount) Then
MsgBox "you're in"
If Not rsNew![Course taken] = "Computer science" Then
stLinkCriteria = "," & Me.LB_PrintSelection.ItemData(ncount) & stLinkCriteria
End If
'Exit Do
End If

rsNew.MoveNext
Loop

Next ncount

rsNew.Close

So, could you please tell me what i did wrong or you might have other solution to this?

Thank you

[dannymking]

In your reports Record Source try the following SQL..

SELECT * FROM Student WHERE std_coursetaken <> "Computer Science"

[DanielConserv]

Thank you Danny

It works now. Well Done...

[dannymking]

There's no need for code on this as setting the SQL of a table should suffice..

Don't forget to mark this thread resolved using the thread tools at the top