Not able to pass Value from FORM to Multiple Query by using MACRO

  • Thread starter Thread starter vikash.verma
  • Start date Start date
V

vikash.verma

Hi All,
I have a created a form and after that i want to pass the VALUE of Combo box and "Date Picker" to a MACRO.This macro has to perform 3 Action.Eg "OpenQuery" ,"OpenQuery" and "OpenReport".I call the query which has Select statement along with the following query.
DelMast.DriverID=Driver.DriverID AND DelMast.CustomerID=DelDetl.CustomerID AND DelMast.StartDate=DelDetl.StartDate And Driver.Description=Forms!Form_DriverActivityReport!Driver_Control.ValueAnd DelMast.StartDate=Forms!Form_DriverActivityReport!StartDate_Control.Value

But i am not able to pass the value into the query.What can be the reason behind it.
In fact the 2nd Query needs the data from the FORM
I am not understanding.If i run the MACRO seperately i get the Output by passing the Values by the Parameter Box, but it does not take the value present inside the Form.

Any help i am stuck...........


Thanks and Regard
Vikash
 
Hello, Vikash.

Unless something changed from Access 97, I don't believe
you can pass values to a macro. This is easily done,
however, in VBA:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim strCrit As String

strCrit = "[OrderID] = '" & Me!txtOrderID & "'"
' Change names above to the names of your field and
' control

strDocName = "rptSales"
' Change to your report name

DoCmd.OpenReport strDocName, acViewPreview,, strCrit

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

I'm a little unclear, though, on why you need to execute
two queries before running the report. If the report's
Record Source property is set to a query name, the query
will provide the source records automatically.

HTH. If you need any more information, let me know.
Kevin Sprinkel
-----Original Message-----
Hi All,
I have a created a form and after that i want to pass the
Click to expand...
VALUE of Combo box and "Date Picker" to a MACRO.This
macro has to perform 3 Action.Eg "OpenQuery" ,"OpenQuery"
and "OpenReport".I call the query which has Select
statement along with the following query.
DelMast.DriverID=Driver.DriverID AND
Click to expand...
DelMast.CustomerID=DelDetl.CustomerID AND
DelMast.StartDate=DelDetl.StartDate And
Driver.Description=Forms!Form_DriverActivityReport!
Driver_Control.Value And DelMast.StartDate=Forms!
Form_DriverActivityReport!StartDate_Control.Value
But i am not able to pass the value into the query.What can be the reason behind it.
In fact the 2nd Query needs the data from the FORM
I am not understanding.If i run the MACRO seperately i
Click to expand...
get the Output by passing the Values by the Parameter Box,
but it does not take the value present inside the Form.
 
I would recommend using a VBA sub or function and NOT a macro. Macros are
hard do debug and difficult to read. In the example you are using the form
controls in the query instaed of the values in those controls

In the code you can create the query by concatenatiing the criteria VALUES
into the string..For example, the last criteria in your example can be added
to the query string as follows:
strSQL = " SELECT .........DelMast.StartDate = #" &
Forms!Form_DriverActivityReport!StartDate_Control.Value & " #......;"
This assumes that the form control contains a date value, literal date
values in a query criteria need to be enclosed in #s.
For character criteria you enclose them in single quotes while numeric
criteria need no enclosing characters.
To run queries you can use the DoCmd object, it has a RunSQL method.

Ragnar
 
Hi Kelvin,
Yes the code does work nd i have some Hands on on this code
But i am not able to pass multiple VALUES like

strCrit = "[Driver.Description] = '" &
Forms!Form_DriverActivityReport!Driver_Control & "'"
' This works fine but the code below does not when i add then together.The
Error says that "Run Time error 3126: Invalid bracketing of name
'[DelMast.StartDate]'"

& " And " & "[DelMast.StartDate] = '" &
Forms!Form_DriverActivityReport!StartDate_Control & "'"

MsgBox " Driver Name: " & strCrit
DoCmd.OpenReport strDocName, acViewPreview, , strCrit

Can i add multiple Filter by using AND or & sthing similar.Just quering this
question....

Hope to hear from you soon

Thanks for your help,
Vikash




Kevin Sprinkel said:
Hello, Vikash.

Unless something changed from Access 97, I don't believe
you can pass values to a macro. This is easily done,
however, in VBA:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim strCrit As String

strCrit = "[OrderID] = '" & Me!txtOrderID & "'"
' Change names above to the names of your field and
' control

strDocName = "rptSales"
' Change to your report name

DoCmd.OpenReport strDocName, acViewPreview,, strCrit

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

I'm a little unclear, though, on why you need to execute
two queries before running the report. If the report's
Record Source property is set to a query name, the query
will provide the source records automatically.

HTH. If you need any more information, let me know.
Kevin Sprinkel
-----Original Message-----
Hi All,
I have a created a form and after that i want to pass the
Click to expand...
VALUE of Combo box and "Date Picker" to a MACRO.This
macro has to perform 3 Action.Eg "OpenQuery" ,"OpenQuery"
and "OpenReport".I call the query which has Select
statement along with the following query.
DelMast.DriverID=Driver.DriverID AND
Click to expand...
DelMast.CustomerID=DelDetl.CustomerID AND
DelMast.StartDate=DelDetl.StartDate And
Driver.Description=Forms!Form_DriverActivityReport!
Driver_Control.Value And DelMast.StartDate=Forms!
Form_DriverActivityReport!StartDate_Control.Value
But i am not able to pass the value into the query.What can be the reason behind it.
In fact the 2nd Query needs the data from the FORM
I am not understanding.If i run the MACRO seperately i
Click to expand...
get the Output by passing the Values by the Parameter Box,
but it does not take the value present inside the Form.
Any help i am stuck...........


Thanks and Regard
Vikash
Click to expand...
Click to expand...
 
Hello Vikash,
& " And " & "[DelMast.StartDate] = '" &
Forms!Form_DriverActivityReport!StartDate_Control & "'"
Click to expand...

You need to bracket the table name and the field separately. like this
[DelMast].[StartDate].
Actually, bracketing is used when a name has a space in it so in this case
you could drop the brackets.

You also do not need to concatenate strings like you are doing, just
concatenate in the criteria values. The code on top could be simplified to:
& " And DelMast.StartDate = '" &
Forms!Form_DriverActivityReport!StartDate_Control & "'"

Ragnar
 
Back
Top