A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Change Form/Report Query Based on User Selection



 
 
Thread Tools Display Modes
  #1  
Old December 7th, 2009, 07:25 PM posted to microsoft.public.access.queries
Wayne[_13_]
external usenet poster
 
Posts: 19
Default Change Form/Report Query Based on User Selection

I have a form and report based on a query. I need to change the query
based on what the user selects on a form. Using VBA in MS Access
2002, can I change a single field in an existing query from GROUP BY
to LAST? This process would be initiated by a click event on a form.
Thanks in advance for any help.
  #2  
Old December 7th, 2009, 09:04 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Change Form/Report Query Based on User Selection

I would say only if you are willing to construct the entire query string in
VBA and then apply that string as the source of the report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Wayne wrote:
I have a form and report based on a query. I need to change the query
based on what the user selects on a form. Using VBA in MS Access
2002, can I change a single field in an existing query from GROUP BY
to LAST? This process would be initiated by a click event on a form.
Thanks in advance for any help.

  #3  
Old December 8th, 2009, 07:58 PM posted to microsoft.public.access.queries
Wayne[_13_]
external usenet poster
 
Posts: 19
Default Change Form/Report Query Based on User Selection

On Dec 7, 12:04*pm, John Spencer wrote:
I would say only if you are willing to construct the entire query string in
VBA and then apply that string as the source of the report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County



Wayne wrote:
I have a form and report based on a query. *I need to change the query
based on what the user selects on a form. *Using VBA in MS Access
2002, can I change a single field in an existing query from GROUP BY
to LAST? This process would be initiated by a click event on a form.
Thanks in advance for any help.- Hide quoted text -


- Show quoted text -


Thanks. Putting the SQL into a string seems to be the best way to do
it but now I have a new problem.

When I change the query GROUP BY field to LAST in VBA the Control
Source name changes from ServiceFrom to LastOfServiceFrom. On the
form I have tried many ways to display both fields like:

=IIf(IsError([LastOfServiceFrom]), [ServiceFrom],[LastOfServiceFrom])
This displays the [LastOfServiceFrom] correctly but gives an #ERROR
when the query is changed to the [ServiceFrom] field.

=IIf(IsError([ServiceFrom]),[LastOfServiceFrom],[ServiceFrom])
This displays the same as the above???? I thought it would be the
opposite but it’s not. Only the [LastOfServiceFrom] displays
correctly. Weird.

How can I get both field names to display correctly in my form when
the Record Source query is changed?
  #4  
Old December 8th, 2009, 08:26 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Change Form/Report Query Based on User Selection

You could alias the name in the query you are building so the name is consistent.

SELECT Last(ServiceFrom) as ServiceStart
FROM SomeTable

SELECT ServiceFrom as ServiceStart
FROM SomeTable
GROUP BY ServiceFrom

There are other ways, but they tend to cause parameter prompts to occur.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Wayne wrote:
On Dec 7, 12:04 pm, John Spencer wrote:
I would say only if you are willing to construct the entire query string in
VBA and then apply that string as the source of the report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County



Wayne wrote:
I have a form and report based on a query. I need to change the query
based on what the user selects on a form. Using VBA in MS Access
2002, can I change a single field in an existing query from GROUP BY
to LAST? This process would be initiated by a click event on a form.
Thanks in advance for any help.- Hide quoted text -

- Show quoted text -


Thanks. Putting the SQL into a string seems to be the best way to do
it but now I have a new problem.

When I change the query GROUP BY field to LAST in VBA the Control
Source name changes from ServiceFrom to LastOfServiceFrom. On the
form I have tried many ways to display both fields like:

=IIf(IsError([LastOfServiceFrom]), [ServiceFrom],[LastOfServiceFrom])
This displays the [LastOfServiceFrom] correctly but gives an #ERROR
when the query is changed to the [ServiceFrom] field.

=IIf(IsError([ServiceFrom]),[LastOfServiceFrom],[ServiceFrom])
This displays the same as the above???? I thought it would be the
opposite but it’s not. Only the [LastOfServiceFrom] displays
correctly. Weird.

How can I get both field names to display correctly in my form when
the Record Source query is changed?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.