Running a query from a for w/Parameter

[merkava]

Hi All,

I have created a query that I want to run from a form (thus far no problem). Where I do have a problem is that I want to take the contents of a variable and use it in the query. I don't want the input box asking for the value.

I hope that I have made myself clear.

Cheers,
Aaron

 

[ansentry]

Where I do have a problem is that I want to take the contents of a variable and use it in the query

What is the variable using as its source, combo box, list box text box, what?

 

[merkava]

What is the variable using as its source, combo box, list box text box, what?

The variable is one that I have created in code it is a string variable.

What happens is that a button is pressed to display a report. The report is based on a query that receives one parameter that is unknown to the user. So... there is an inputbox that pops up, I would like to avoid this.

Cheers,
Aaron

 

[ansentry]

The variable is one that I have created in code it is a string variable.

Sorry, I don't follow you; post the code your are using so that I or someone else can understand what you are doing.

 

[merkava]

Hi,

The user preeses a button:

Private Sub cmdPreviewTodaysActions_Click()
On Error GoTo Err_cmdPreviewTodaysActions_Click

Dim stDocName As String

stDocName = "ShiftWorked Query"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewTodaysActions_Click:
Exit Sub

Err_cmdPreviewTodaysActions_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewTodaysActions_Click

End Sub

A report based on this query is opened:

SELECT ShiftWorked.ShiftWorkedID, ShiftWorked.StartDate, ShiftWorked.EndDate, Employees.FirstName, Employees.LastName, TeamLeader.FirstName, TeamLeader.LastName, TaskCounter.TaskDescription, TaskCounter.CountOfTaskID
FROM Employees INNER JOIN ((ShiftWorked INNER JOIN TaskCounter ON ShiftWorked.ShiftWorkedID = TaskCounter.ShiftWorkedId) INNER JOIN TeamLeader ON ShiftWorked.SupervisorId = TeamLeader.SupervisorID) ON Employees.EmployeeID = ShiftWorked.EmployeeId;


It requires the following parameter to be inputed: ShiftWorked.ShiftWorkedID

Once the user presses the button to view the report, access requests this parameter and opens an input box. This is a value the user will not know.

So I want to take this value from a variable in the code and "provide" it to the report.

Cheers,
Aaron

 

[ansentry]

Assuming that:-
ShiftWorkedID is a numeric
ShiftWorkedID appears as a control on the form that has the print button
ShiftWorkedID is a control on the report.
What I have added to your code is in Blue

Private Sub cmdPreviewTodaysActions_Click()
On Error GoTo Err_cmdPreviewTodaysActions_Click

Dim stDocName As String
[COLOR="Blue"]dim stWhere as string[/COLOR]

stDocName = "ShiftWorked Query"
[COLOR="Blue"]stWhere= "[ShiftWorkedID]=" & Me![ShiftWorkedID][/COLOR]

DoCmd.OpenReport stDocName, acPreview[COLOR="Blue"], , stWhere[/COLOR]

Exit_cmdPreviewTodaysActions_Click:
Exit Sub

Err_cmdPreviewTodaysActions_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewTodaysActions_Click

End Sub

Hope this helps.

 

[merkava]

Hi John,

It certainly helps, thank you very much

 

[merkava]

Hi,

After making the following changes:

Private Sub cmdPreviewTodaysActions_Click()
Dim stWhere As String
On Error GoTo Err_cmdPreviewTodaysActions_Click
Dim stDocName As String

stDocName = "ShiftWorked Query"
stWhere = "[ShiftAdminId]=" & intPresentShiftId

DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewTodaysActions_Click:
Exit Sub

Err_cmdPreviewTodaysActions_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewTodaysActions_Click

End Sub

The pop-up still appears requesting a value, I am trying to push the value of intPresentShiftId into the query the powers the form.

Below is the SQL for the query:

SELECT ShiftWorked.ShiftWorkedID, ShiftWorked.StartDate, ShiftWorked.EndDate, Employees.FirstName, Supervisor.FirstName, TaskCounter.TaskDescription, TaskCounter.CountOfTaskID
FROM TaskCounter, Supervisor INNER JOIN (Employees INNER JOIN ShiftWorked ON Employees.EmployeeID = ShiftWorked.EmployeeId) ON Supervisor.SupervisorID = ShiftWorked.SupervisorId
WHERE (((ShiftWorked.ShiftAdminId)=[?]));

Cheers,
Aaron

 

[ansentry]

intPresentShiftId is not in the query!

SELECT ShiftWorked.ShiftWorkedID, ShiftWorked.StartDate, ShiftWorked.EndDate, Employees.FirstName, Supervisor.FirstName, TaskCounter.TaskDescription, TaskCounter.CountOfTaskID
FROM TaskCounter, Supervisor INNER JOIN (Employees INNER JOIN ShiftWorked ON Employees.EmployeeID = ShiftWorked.EmployeeId) ON Supervisor.SupervisorID = ShiftWorked.SupervisorId
WHERE (((ShiftWorked.ShiftAdminId)=[?]));

If you are trying to print the details from the current form (the one that is on the screen) then you just need a report that is based on the same query as the form. The WHERE that you have in the query is not needed as you have it in the code.

Here is a sample that will show you how to print the current record.

Also just for neatness this;

Private Sub cmdPreviewTodaysActions_Click()
Dim stWhere As String
On Error GoTo Err_cmdPreviewTodaysActions_Click
Dim stDocName As String

stDocName = "ShiftWorked Query"
stWhere = "[ShiftAdminId]=" & intPresentShiftId

should be;

Private Sub cmdPreviewTodaysActions_Click()
On Error GoTo Err_cmdPreviewTodaysActions_Click

Dim stDocName As String
[COLOR="Blue"][B]Dim stWhere As String[/B][/COLOR]

stDocName = "ShiftWorked Query"
stWhere = "[ShiftAdminId]=" & intPresentShiftId

If you still can't get it to work then post a copy of your db and I or someone will have a look at it for you.

 

[merkava]

Hi,

I have been trying to figure this one out. I am attaching the DB with the report I want the button on form1 to display. I would like the value inthe label to be passed as a parameter to the query behind the report.


Cheers,
Aaron

 

[gemma-the-husky]

in a query you have to return the variable as a function result

eg

function returnmyvariable as string

returnmyvariable = myactualvariable

end function

in the query put brackets after the function

eg returnmyvariable()

 

[merkava]

in a query you have to return the variable as a function result

eg

function returnmyvariable as string

returnmyvariable = myactualvariable

end function

in the query put brackets after the function

eg returnmyvariable()

I am sorry but I have no Idea what you mean by this.

I have a form that has one label or textbox that has a numerical value, for the example I have provided it is a label with the numerical value of eight.

when the user preses the button on the form, the following code is run. Query the powers the report requires a numerical value. This value I wish to take from the label/textbox

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
stDocName = "ShiftWorked Query"


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Please help

Cheers,

Aaron

 

[ansentry]

Here is your db