Forms based a query

[Jose Perdigao]

I need to open many queries and if I open the access in run time I can't use
the menu popu, so I would like to do the following technic is is possible.

Open a form in datasheet view based in any query, is it possible?

Thanks for suggestions.
José Perdigão

 

[Arvin Meyer [MVP]]

Forms have recordsources that are either bound or unbound. The unbound ones
must be filled at runtime with a recordsource such as a query. If they are
unbound, you must also define the controlsource of each control at runtime.
Assuming different recordsources that can be difficult. Far easier is to use
numerous predefined subforms, bound to queries (or tables) then change the
sourceobject property of the subform control to whichever subform you'd like
to use. You could, for instance, use a list box bound to a table of subform
names, and use the clickor after update event of the listbox to fill the
subform control, something like:

Me.subMasterControl.SourceObject = Me.listboxcontrolname

Now you can choose which query/subform combination that you'd like to
display.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Marshall Barton]

I need to open many queries and if I open the access in run time I can't use
the menu popu, so I would like to do the following technic is is possible.

Open a form in datasheet view based in any query, is it possible?

If all the queries have the same field list, then use the
form's Open event to set its RecordSource property to the
name of the query. I might be more helpful if you explained
where/how you decide which query should be used.

 

[Jose Perdigao]

Thanks,

This is the problem that I have. The queries don't have the same field's
list names and different queries have different number of columns.



What I would like is, change programmatically the record source and then I
will open the form in datasheet view showing the records and using popup
menu in runtime version.

 

[Jose Perdigao]

Thanks,

In subform, if I change the record source it works?
I mean, I change record source from one query to another if I opne the form,
it will show the records in subform?

thanks

 

[Marshall Barton]

I can think of three ways to approach this issue.

One is to use Arvin's idea of creating a separate form for
each query.

Another is to use a fairly substantial chunk of VBA code in
the Form's Load event to make unneeded text boxes invisible
and assigning the needed text box's Control source and their
associated lab;e's Caption.

The third idea is to give up on the form and just open the
query.

 

[Arvin Meyer [MVP]]

In a subform, if you change the recordsource it will work if you use the
same fields to fill the controls. I suggested changing the SourceObject
property, not the Recordsource property. Changing the SourceObject changes
the entire subform.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Jose Perdigao]

Hi Arvin,

The queries don't have the same fields. They are completely different. Some
queries have 3 columns other have many columns. I don't know if is possible

What I would like is, create just one form or sub form and open it in
datasheet view and show the query selected for example from an list box in a
main form.



Thanks

jose

 

[Jose Perdigao]

Changing the sourceObject, I'm changing the subform, it means I must have a
sub form for each query. I want only one form or subform if is possible.

Is it possible to use the shortcut menu (popup) in access runtime? I can't
use, is not available.

Thanks
José Perdigão

 

[Jose Perdigao]

I use the third ideia, I open queries from a list box existing in a form. I
have a problem, I can't use the shortcut menu. When I do right click the
popup menu, doesn't show if I use access runtime.

I can think of three ways to approach this issue.

One is to use Arvin's idea of creating a separate form for
each query.

Another is to use a fairly substantial chunk of VBA code in
the Form's Load event to make unneeded text boxes invisible
and assigning the needed text box's Control source and their
associated lab;e's Caption.

The third idea is to give up on the form and just open the
query.
--
Marsh
MVP [MS Access]

This is the problem that I have. The queries don't have the same field's
list names and different queries have different number of columns.

What I would like is, change programmatically the record source and then I
will open the form in datasheet view showing the records and using popup
menu in runtime version.



"Marshall Barton" wrote

 

[Arvin Meyer [MVP]]

A form has controls (textboxes, etc.) and needs to have a control for each
field displayed. You will need separate subforms, or you will need to create
the subforms on the fly (not very efficient at all). You could create a
subform with the maximum number of controls that you will need, then using
them unbound, assign the fields at runtime. That is also not very efficient
and probably more time consuming to create than just building separate
subforms.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Marshall Barton]

Just grasping at straws now, but see if my answer in the
thread (this newsgroup):
Subject: Header footer in datasheet view...
Date: Mon, 3 Apr 2006 09:12:01 -0700
might be adaptable to your situation.

 

[Marshall Barton]

Be sure to read Duane's post in that thread. It turns out
that there is a feature I had not run across before where
you can set a subform's SourceObject to a query or table to
display it in sheet view. Sounds like just what you want
and very simple too.

 

[Jose Perdigao]

Hi Marshall and Arvin,



Thanks a lot,

This is what I wanted.

When you and Arvin talked about change SubForm SourceObject I didn't
understand because I thought I must have one SubForm for each query.

Now, after i saw the example from Duane, I understood and it works perfectly
and quickly.



Now I have another question.

When I open a query or a form in datasheet view, as you know, I can export
quickly the data to MS Excel, (from menu bar, tools and then Analyze it with
Ms Excel.).



I would like to do, the same procedure for SubForm. I tried and the MS Excel
open but off course is blank because is exporting the data from the form and
not from the SubForm.



Do you have any idea for this problem?



Thanks a lot,

José Perdigão.

Be sure to read Duane's post in that thread. It turns out
that there is a feature I had not run across before where
you can set a subform's SourceObject to a query or table to
display it in sheet view. Sounds like just what you want
and very simple too.
--
Marsh
MVP [MS Access]

I use the third ideia, I open queries from a list box existing in a form.
I
have a problem, I can't use the shortcut menu. When I do right click the
popup menu, doesn't show if I use access runtime.