Need help creating Access Report in VBA

[Nzaaev]

I am generating a student transcipt report initiated from a VBA Menu screen. The user clicks a menue option to generate a student transcript. The vba code asks the user to enter the student ID. The vba gets the user ID, verifies the student is in the database. I have preset Report (form) already created with Report header information, labels and textbox for student ID, Name, Courses taken, etc. This portion I have already done.

Here is where I am struggling: I want the vba code to pass the student ID to the Access Report form or it's query and it would use the ID to search a "Courses-Taken" table and if the student has completed at least one course, it would generate the student's transcript report. How to pass the student ID to the Report form or its underlying query is where my problem is.

Any help would be appreciated.

Thanks,
Nzaae

[twinnyfo]

Nzaaev,

Create a SQL string that uses the Query for the Report's record source and use the Student ID as the criteria for the query. Then set the report's record Source to that Query. That should do the trick.

[NeoPa]

It's even easier than that.

1. Design the report (or more precisely the underlying query) to get all the information required from any relevant tables for all students.
2. When opening the report specify a filter string for the selected student.

[Nzaaev]

Thanks for the response. However, here is where my hangup is:
SELECT StudID
FROM StudTable
WHERE StudID = SID;

(1) How do I pass the variable SID to this query from my VBA code?
(2) StudTable has more than one column. Iam getting a prompt to enter values for the other columns which I do not need and did not specify in my Select statement. How do I specify to select only one column so I do not get the prompt?

Thanks, again. I am new to VBA

[NeoPa]

It's not clear from your post which post you're referring to. It doesn't make good sense for either from what I can see.

If you're taking Twinny's approach then the SQL created has a literal value set where you refer to SID.

If you take my approach then it's even easier. You start with a working query that exposes [StudID] and apply the filter with the DoCmd.OpenReport() call.

[twinnyfo]

Nzaaev,

Using NeoPa's (better) advice,

DoCmd.OpenReport "YourReportName", , "[StudID] = " & [SID]

However, I am concerned about the actual language you use in your questions, and the term "Report" seems to be freely interchanged with the term "Form" (see post #1).

Is this a "Report" or a "Form" that is reporting data?

[Nzaaev]

Ok. Let me try it again.
The menu selection the user clicked was to generate a transcript for a student. The VBA code prompts the user to enter the ID (SID) of the user to which a transcript is to be generated. The user enters the student ID (SID variable) in the VBA code. Then my VBA code is to pass this SID to the SQL query to be used as the search criteria in my WHERE clause. I used Access Report Wizard to design the Report. The SQL query is already defined as the Record Source for the Report.

Therefore, the user selects the request to generate student transcript, the vba code prompts the user for the student ID; the user enters the student ID in a text box window; the vba code verifies the ID and then passes the ID (SID variable) to the query; the query executes and generates the transcript reportand displays it Report View.

So how do I pass the SID from the VBA Code to the Query?

Hope this is clearer.

Thanks,

[twinnyfo]

Post #6 describes exactly what to do. You do not send the SID to the Query, you filter the report based on the SID.

[Nzaaev]

twinnyfo,
I implemented post #6 as suggested. Below is the Docmd statement:

DoCmd.OpenReport "Transcript-Report", acViewPreview, , "[Stud_ID] = " & [SID]

But I am getting the following error code with message:

Run time error "3071":

"The expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric
expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of
the expression to variables".

The variable SID is type String in my VBA code, and Stud_ID is the field in my table of type Text.

What amy I doing wrong?

Thanks,

[NeoPa]

Firstly, it's important that you read and follow the instructions in Debugging in VBA. So much time and effort is wasted dealing with stupid errors that should never come up, but do because people don't get even the basics right.

I would guess that this is the result of a run-time error due to referring to your variable (SID) within brackets ([]).

If not, then it's likely that it's because you don't have a working query for the report in the first place.

First ensure your code compiles properly with :

Option Explicit

When that's done test out the report with NO filtering. This is the point you should be at before you even start thinking about the filter. If that all works but the filtering doesn't then it's a good time to look at the filtering. At that point it should be very simple and there will be nothing else to confuse matters unnecessarily.

[NeoPa]

It may also help to get your head around what you're doing by reading this amended version of your statement of intent from post #7 :

The menu selection the user clicked was to generate a transcript for a student. The VBA code prompts the user to enter the ID (SID) of the user to which a transcript is to be generated. The user enters the student ID (SID variable) in the VBA code. Then my VBA code is to pass this SID to the SQL query to be used as the search criteria in my WHERE clause. the report is opened to show just the transcript for that selected student. I used Access Report Wizard to design the Report. The SQL query is already defined as the Record Source for the Report.

The underlined text should be replaced by the italicised text.