Solved Pass Parameter from Form to Report (1 Viewer)

kstinnett

New member
Local time
Today, 17:39
Joined
Oct 22, 2024
Messages
7
I am having issue with a "type mismatch" when I click my button to run a report with a parameter in the form. I have searched for the exact syntax but I am having issues finding it.

Here is my code. I am using a combo box to pick my criteria for the query that the report is based on. ANy help would be greatly appreciated.

Private Sub Command13_Click()



Dim strParamValue As String



strParamValue = Me.txtCourseName.Value ' Get value from text box

DoCmd.OpenReport "Course Completed by Employees", acViewPreview, , , "[Enter Course Name] = '" & strParamValue & "'"


End Sub
 
You are passing your criteria to the parameter 'WindowMode' of 'OpenReport'. So check the commas.
 
Hi. Welcome to AWF!

I agree. You had the WhereCondition argument in the wrong spot.
 
Appreciate all the feedback. I am not a programmer. I have learned a lot on my own but I am not sure how I need to change this to get it to work from your responses. Could either one of you provide me more detail. I just took the code from a google search and I have modified it several times trying to get it to work but haven't had any success.
 
if your report is based on a Parameter Query, you need to remove the parameter ([Enter Course Name]) from the Query.
then you can open the report with WhereCondition"

DoCmd.OpenReport ReportName:="Course Completed by Employees", View:=acViewPreview, WhereCondition:="[Course Name] = '" & strParamValue & "'"
 
Appreciate all the feedback. I am not a programmer. I have learned a lot on my own but I am not sure how I need to change this to get it to work from your responses. Could either one of you provide me more detail. I just took the code from a google search and I have modified it several times trying to get it to work but haven't had any success.
Use Intellisense. That is what it is there for.
 
if your report is based on a Parameter Query, you need to remove the parameter ([Enter Course Name]) from the Query.
then you can open the report with WhereCondition"

DoCmd.OpenReport ReportName:="Course Completed by Employees", View:=acViewPreview, WhereCondition:="[Course Name] = '" & strParamValue & "'"
I took the parameter out of the query and put your code in but now its popping up the normal parameter box when I run it. Sorry if I missing something simple.

1729608516813.png
 
Can you copy/paste the form's SQL Code from the Control Source property box?
 
Can you copy/paste the form's SQL Code from the Control Source property box?
I am thinking this isn't what you are asking for but I am not sure if this isn't it where to find what you are really asking for.

Private Sub Command13_Click()



Dim strParamValue As String



strParamValue = Me.txtCourseName.Value ' Get value from text box


DoCmd.OpenReport ReportName:="Course Completed by Employees", View:=acViewPreview, WhereCondition:="[Enter Course Name] = '" & strParamValue & "'"




End Sub
 
I am thinking this isn't what you are asking for but I am not sure if this isn't it where to find what you are really asking for.

Private Sub Command13_Click()



Dim strParamValue As String



strParamValue = Me.txtCourseName.Value ' Get value from text box


DoCmd.OpenReport ReportName:="Course Completed by Employees", View:=acViewPreview, WhereCondition:="[Enter Course Name] = '" & strParamValue & "'"




End Sub
What is the name of the field or column that you're trying to filter?
 
You're right, that isn't what I was looking for.

Open your form in Design view. Then under the design tab click "Property Sheet":
1729610329690.png


Now in the properties tab, make sure the combo box on top is set to "Form". Click the Data tab and copy/paste the code from the "Record Source" box.
1729610447914.png
 
You're right, that isn't what I was looking for.

Open your form in Design view. Then under the design tab click "Property Sheet":
View attachment 116648

Now in the properties tab, make sure the combo box on top is set to "Form". Click the Data tab and copy/paste the code from the "Record Source" box.
View attachment 116649
SELECT [Course Catalog].[Course Name], [Course Completed].[Date Completed], Erp_EmpBasic.Name, [Course Completed].CourseID
FROM Erp_EmpBasic INNER JOIN ([Course Completed] INNER JOIN [Course Catalog] ON [Course Completed].CourseID = [Course Catalog].ID) ON Erp_EmpBasic.EmpID = [Course Completed].EmpID;
 
Okay the SQL code looks fine but it looks like you still have the "Enter Course Name" prompt in your OpenReport command. Replace your DoCmd.OpenReport line with this line:

Code:
DoCmd.OpenReport "Course Completed by Employees", acViewPreview, , "[Course Name] = '" & strParamValue & "'", acWindowNormal

In addition, a couple of tips on naming conventions. It's generally better practice to not use spaces in your field names or database object names. Some developers will_use_underscores and OthersPreferCamelCase. The reason for this is that it eliminates the need for bracketing your field names and makes the code easier to read.
Also, I see you have a field name in the erp_EmpBasic table called "Name". It's not generally a good idea to use "Name" as a field name because the term "name" has a specific function in VBA. Better to use "empName" or two fields like "FirstName" and "LastName".
 
Okay the SQL code looks fine but it looks like you still have the "Enter Course Name" prompt in your OpenReport command. Replace your DoCmd.OpenReport line with this line:

Code:
DoCmd.OpenReport "Course Completed by Employees", acViewPreview, , "[Course Name] = '" & strParamValue & "'", acWindowNormal

In addition, a couple of tips on naming conventions. It's generally better practice to not use spaces in your field names or database object names. Some developers will_use_underscores and OthersPreferCamelCase. The reason for this is that it eliminates the need for bracketing your field names and makes the code easier to read.
Also, I see you have a field name in the erp_EmpBasic table called "Name". It's not generally a good idea to use "Name" as a field name because the term "name" has a specific function in VBA. Better to use "empName" or two fields like "FirstName" and "LastName".
First of all thanks so much that took care of it.
Secondly, I am aware of the Underscore but I get in a hurry sometimes because this isn't my full time job but I greatly appreciate the reminder. I will go back and clean all of that up. lastly, the name field is not mine it is connected to my ERP and that is what they named it. :)

Once again, I greatly appreciate everyones time.
 
My pleasure! Make sure you mark the thread as solved, and welcome to the forum!
 

Users who are viewing this thread

Back
Top Bottom