Dynamic Query Based on Current Row

[Bonz099]

Hello, I have a query that dumps 4 column values to a form with a button at
the end of each row. One of the fields is called "datesubmitted". I would
like to execute a query based on the dynamic data of that current row’s
respective "datesubmitted" field.

For example when I click the button at the end of the row, it runs <sudo
code> SELECT * FROM TableName WHERE date = "currentrow.datesubmited" </sudo
code>

Sorry for the bad explanation im just learning to use access.

 

[Albert D. Kallal]

For example when I click the button at the end of the row, it runs <sudo

code> SELECT * FROM TableName WHERE date = "currentrow.datesubmited"
</sudo
code>

No problem, but what do you want with the results of that query? Do you want
to open a new form, open a new report?...You have to "do" something with
that query. When you run a query in code, you can only send the results to a
internal reocrdset for further processing. If you need the "user" to see the
results of that query for the most part you open up a report, or form based
on that table and "pass" the criteria.

for example when you click the button to launch a report that opens with the
records on the datesumiited, you could go:


dim strWhere as string

strWhere = "DateSubmited = #" & format(me!DatesSubmitted,"mm/dd/yyyy") & "#"

docmd.Openform "frmViewDate",,,strWhere

Note that it *is* possible to build the select query, but you options to
display it are limited, and using a form, or report with a filter as above
will do the trick.

You might expand a bit as to what you want to do with that select query, but
as above shows, it FAR easier to pass a "where" clause to a report or form
that can display the data, and as above shows it not a lot of code either...

 

[Bonz099]

Idealy I would want to build a report that would allow me to pass the
"datesubmitted" to the query for it. But it have no idea where to paste this
code or how to structure the dynamic query.

Would you mind explaining where to put the code and how my query that the
form references should look like?

 

[Albert D. Kallal]

Idealy I would want to build a report that would allow me to pass the
"datesubmitted" to the query for it. But it have no idea where to paste
this
code or how to structure the dynamic query.

Would you mind explaining where to put the code and how my query that the
form references should look like?

ok...

Lets assume you want to send the results to a report.

The user could then
print, or close the report when done.

The 1st thing is to build a report based on that table. Set all of the
sorting and grouping stuff in the report. Get it exactly the way you want.

Note that the report will have no parameters and there is little advantage
to
base the report on a query. Once that reports looks the way you want, then
close it and save it.

Ok...now that our report is good, we go back to your continues form, and the
code behind the button will be:

dim strWhere as string
strWhere = "DateSubmited = #" & format(me!DatesSubmitted,"mm/dd/yyyy") & "#"
docmd.Openform "MyReport",acViewPreview,,,strwhere

So, the code goes behind that button on the continues form you talked
about...

I have assumed that the date on that row when clicked is me!DatesSubmitted
(I you have to change that to the name of the actual date field you used
(can't read minds!!!).

If you allow editing of data in the continues form, then you need to force a
disk write BEFORE you launch the report. Change above to:

dim strWhere as string
strWhere = "DateSubmited = #" & format(me!DatesSubmitted,"mm/dd/yyyy") & "#"
me.refresh
docmd.Openform "MyReport",acViewPreview,,,strwhere

So, it is assumed that the above code goes behind the button your pressing.
We thus do not have to build a new query for the report..but only pass it
the "where" clause to restrict it to whatever we want -- in this case the
one date.....

 

[Bonz099]

Ok so let me see if I have this right, for the on click for the button I use
this:

dim strWhere as string
strWhere = "DateSubmited = #" & format(me!DatesSubmitted,"mm/dd/yyyy") & "#"
docmd.Openform "MyReport",acViewPreview,,,strwhere

So that particular code opens a form called "MyReport" and that form is
linked to query. And my Query should looks like "SELCT * FROM Table WHERE
DateSubmitted = strwhere"

Is that right? Or do i not even need to create a query? Im sorry im really
new to writing applications in Access. I really appreciate the help you’ve
given me thus far.

 

[Piet Linden]

Ok so let me see if I have this right, for the on click for the button I use
this:

dim strWhere       as string
strWhere = "DateSubmited = #" & format(me!DatesSubmitted,"mm/dd/yyyy") & "#"
docmd.Openform "MyReport",acViewPreview,,,strwhere

So that particular code opens a form called "MyReport" and that form is
linked to query. And my Query should looks like "SELCT * FROM Table WHERE
DateSubmitted = strwhere"

Is that right? Or do i not even need to create a query? Im sorry im really
new to writing applications in Access. I really appreciate the help you’ve
given me thus far.

No. That's not right. The SQL for your report is just
"SELECT * FROM Table;"

then you pass the filter as a separate argument. That way, you can
pass any filter you want to at runtime/when you open the form/report.

 

[Albert D. Kallal]

Ok so let me see if I have this right, for the on click for the button I
use
this:

dim strWhere as string
strWhere = "DateSubmited = #" & format(me!DatesSubmitted,"mm/dd/yyyy") &
"#"
docmd.Openform "MyReport",acViewPreview,,,strwhere

So that particular code opens a form called "MyReport" and that form is
linked to query. And my Query should looks like "SELCT * FROM Table WHERE
DateSubmitted = strwhere"

No, you don't need any link or anything speicak in the report. The report
will be based on the table, and that is all.

The OpenReprot command (above for some reason we have flipped into talking
about a form..but I assumed it was a report). The process is the same for
both cases.


So, the above command is not going to open a form (but, it could), it is
going to open a report...

eg:
dim strWhere as string
strWhere = "DateSubmited = #" & format(me!DatesSubmitted,"mm/dd/yyyy") &
"#"
docmd.Report "MyReport",acViewPreview,,strwhere

Note above how the number of "," been corrected....

Note however, often we have a continuous form, and we do open up a form to
view "more" details. The above code to open a form would be:

dim strWhere as string
strWhere = "DateSubmited = #" & format(me!DatesSubmitted,"mm/dd/yyyy") &
"#"
docmd.OpenForm "MyFormName",,,strwhere

So you can open a form, or report using this approach. There is not a
special query or connection..the "where" clause simply restricts the records
that the report will show.