Getting a Form recordset to a Report

[Ryan Diver]

I've created a form based on a parameter query that allows a comma
separated list of values for searching the records. I have all of the
records based on that query in my form. I now want to produce a report
based only on the records that are in the form using a command
button.
I can create a report based on the same query, but it would require my
users to reenter their search parameters. How can I get the records I
already have in my form into my report?

I've tried using the OpenReport method but my syntax is incorrect. I
either get all possible records, or a request to enter the parameters
again which I want to avoid. Even if I get it to work, I think it will
only give me the one specific record being viewed. Can someone check
my syntax here? The variable rsnumber is text. Currently I'm getting
all records in the report.

Dim stDocName As String
stDocName = "rs report by number"
DoCmd.OpenReport stDocName, acPreview, , [rsnumber] = Me.
[rsnumber]

Or maybe I'm going about this all wrong?
FYI. I've only been working in Access for 2 weeks, and I don't know
VBA yet.
Please advise and thanks,
- Ryan

 

[Jeff Boyce]

Ryan

Are you saying that your query prompts your users for the parameter string?
If so, consider an alternate approach:

Create a form & control into which your users key their parameter string.
Modify your query to point to that form/control to find its parameter. Add
a command button that runs your report in this new form.

As long as the form is open and the control is filled, the query finds the
parameter and returns the appropriate records, whether for a form display or
a report display.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ryan Diver]

Thanks for the advice. I thought I might be going about this the wrong
way.

Yes. They are entering a string of IDs separated by commas. I use the
IN() function to parse the IDs to identify the records. It is not
actually an ideal solution, the users would likely have a lot of IDs
and not want to type them all. With your form suggestion I'm
envisioning some sort of sub-datasheet that they could paste a list of
IDs into from a text or Excel file. Will let you know how it goes, and
I of course am open to alternative solutions.

Thanks again,

- Ryan

Ryan

Are you saying that your query prompts your users for the parameter string?
If so, consider an alternate approach:

Create a form & control into which your users key their parameter string.
Modify your query to point to that form/control to find its parameter.  Add
a command button that runs your report in this new form.

As long as the form is open and the control is filled, the query finds the
parameter and returns the appropriate records, whether for a form displayor
a report display.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a form based on a parameter query that allows a comma
separated list of values for searching the records. I have all of the
records based on that query in my form. I now want to produce a report
based only on the records that are in the form using a command
button.
I can create a report based on the same query, but it would require my
users to reenter their search parameters. How can I get the records I
already have in my form into my report?

I've tried using the OpenReport method but my syntax is incorrect. I
either get all possible records, or a request to enter the parameters
again which I want to avoid. Even if I get it to work, I think it will
only give me the one specific record being viewed. Can someone check
my syntax here? The variable rsnumber is text. Currently I'm getting
all records in the report.

   Dim stDocName As String
   stDocName = "rs report by number"
   DoCmd.OpenReport stDocName, acPreview, , [rsnumber] = Me.
[rsnumber]

Or maybe I'm going about this all wrong?
FYI. I've only been working in Access for 2 weeks, and I don't know
VBA yet.
Please advise and thanks,
- Ryan

 

[Jeff Boyce]

Ryan

If you are considering that much work, here's another thought...

If you use 'paired listboxes' (the one on the left has 'available' items,
the one on the right has 'chosen' items), your users won't have to
remember/type in codes, they can just pick.

For an example, try running the query wizard to see this paired listbox
approach in action.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks for the advice. I thought I might be going about this the wrong
way.

Yes. They are entering a string of IDs separated by commas. I use the
IN() function to parse the IDs to identify the records. It is not
actually an ideal solution, the users would likely have a lot of IDs
and not want to type them all. With your form suggestion I'm
envisioning some sort of sub-datasheet that they could paste a list of
IDs into from a text or Excel file. Will let you know how it goes, and
I of course am open to alternative solutions.

Thanks again,

- Ryan

Ryan

Are you saying that your query prompts your users for the parameter
string?
If so, consider an alternate approach:

Create a form & control into which your users key their parameter string.
Modify your query to point to that form/control to find its parameter. Add
a command button that runs your report in this new form.

As long as the form is open and the control is filled, the query finds the
parameter and returns the appropriate records, whether for a form display
or
a report display.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a form based on a parameter query that allows a comma
separated list of values for searching the records. I have all of the
records based on that query in my form. I now want to produce a report
based only on the records that are in the form using a command
button.
I can create a report based on the same query, but it would require my
users to reenter their search parameters. How can I get the records I
already have in my form into my report?

I've tried using the OpenReport method but my syntax is incorrect. I
either get all possible records, or a request to enter the parameters
again which I want to avoid. Even if I get it to work, I think it will
only give me the one specific record being viewed. Can someone check
my syntax here? The variable rsnumber is text. Currently I'm getting
all records in the report.

Dim stDocName As String
stDocName = "rs report by number"
DoCmd.OpenReport stDocName, acPreview, , [rsnumber] = Me.
[rsnumber]

Or maybe I'm going about this all wrong?
FYI. I've only been working in Access for 2 weeks, and I don't know
VBA yet.
Please advise and thanks,
- Ryan

 

[Piet Linden]

Ryan

If you are considering that much work, here's another thought...

If you use 'paired listboxes' (the one on the left has 'available' items,
the one on the right has 'chosen' items), your users won't have to
remember/type in codes, they can just pick.

For an example, try running the query wizard to see this paired listbox
approach in action.

Regards

Jeff Boyce
Microsoft Office/Access MVP

You could use the one from Access Developer's Handbook, but then it's
not a beginner book...