Have a Query use a Form?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I've a simple question.

How can I get a query to display the output in the same form that is used
for data entry to the queried table?

Many TIA,

-J-
 
Jay,

Maybe an example would help clarify what you mean :-).
Click to expand...

Sorry if I wasn't clear. I meant Form (as In Access Object), not form.

I have a simple table detailing tasks assigned to staff members, with a Form
for data entry. I have a parameter query which returns records for the
specified member of staff. The query fields are the same as the main table
& the Form.

So what I'm after is for the query to display the records using the Form,
rather than datasheet view.(thereby enabling the user to be able to scroll
through the records with the navigation controls).

This would be useful as some of the fields are long text fields which do not
fit on the screen in datasheet view.

I hope this clarifies things :-)

Regards

-Jay-
 
Jay,

I often do stuff like this by putting an unbound combobox in the Header
of the form, to produce a list of all Staff. Then, on the After Update
event ;-) of the combobox, you can either:
- Requery the form, assuming it is based on your query.
- Use code to change the form's Record Source property.
 
Jay,

I often do stuff like this by putting an unbound combobox in the Header
of the form, to produce a list of all Staff. Then, on the After Update
event ;-) of the combobox, you can either:
- Requery the form, assuming it is based on your query.
- Use code to change the form's Record Source property.
Click to expand...

So if my Form is based on the table there's no way, is that correct?

Am I right in thinking I'd have to set up an all fields & records query,
base the form on that, & then add the combo box with an AfterUpdate event
which opens a query which is the same as the query on which the form is
based , but with the Combo Box specified in the criteria of the StaffName
field?

Have I got that right? Your 2nd option is a bit of a no-go for me at the
moment Steve as I'm pretty new to Access so am bit code-phobic :-)

-Jay-
 
The Wizards can perform some magic to take the agony of getting your
problem solved. There is no way you can get much done in Access
without learning some VBA as you go and learning how to use some of
the tools Access provides to help you.

First, the reason your form should be based on a query and how to get
Access to help you with it. Records are stored in tables in
unpredictable ways. By using queries on your tables for your forms,
you determine the order in which your data will be displayed.


A very good way to get a form going is to select your properly
designed table and then click the Autoform wizard on the toolbar. You
fiddle with the form designer wizard until you get something you want
to work with (you can iterate the process as many times as you like).
Then with the form in design view, tweak it more to your liking.
Then, in the form's properties, click the ellipsis (three dots) to the
right of the Record Source window. You'll can then use the QBE Wizard
to graphically design your query. Test your results by clicking the
red Exclamation mark on the toolbar. Take your time and learn a bunch
about using the QBE wizard. When satisfied with your query, X out of
it. Answer Yes to saving the query. Answer No to saving it as a
named query in the Query window. That will leave your query embedded
in your form and won't clutter your Query window.

Now your form can be stepped through your recordset in the order you
determined with your query.

With the form in design view click View and click Form Header &
Footer. That will cause a header and footer to appear on your form.

Making sure that the Wizards are enabled (Icon with Stars and Wand),
click the combobox icon on the toolbox and then draw it in the Header
area of your form. You will be asked a series of questions. You want
to go to a particular record. When the wizard is done you'll have
what you want. Name the combobox something meaningful. Save your
work.

Return to Form view and play with it a bit. Go back to Design view
and select the combobox you just created..Turn the Properties window
on. Click Events. Click the ellipsis to the right of the AfterUpdate
event. You'll see the code that the combobox wizard wrote for you.

HTH
 
Jay said:
So if my Form is based on the table there's no way, is that correct?
Click to expand...

No, that's not correct. But it would involve using code or macro to
change the form's Record Source when you enter something in the combobox.
Am I right in thinking I'd have to set up an all fields & records query,
base the form on that, & then add the combo box with an AfterUpdate event
which opens a query which is the same as the query on which the form is
based , but with the Combo Box specified in the criteria of the StaffName
field?
Click to expand...

Pretty much. But you wouldn't open the query. The form will be based
on the query, so the form will show the records that the query returns,
so opening the query is not applicable. But you would need to put code
on the Combobox's After Update event, like this...
Me.Requery

As for "the Combo Box specified in the criteria of the StaffName field",
well this would normally be in the criteria of a unique identifier
field, such as StaffID. StaffName sounds like the name of the staff
member, which is something you can't guarantee you won't have two of in
the database. Also, you would specify the criteria like this...
[Forms]![NameOfForm]![NameOfCombobox] Or
[Forms]![NameOfForm]![NameOfCombobox] Is Null
This is so you can clear the combobox, and see all records again.

There are all sorts of other ways to do this, for example using the
Filter property of the form, and this is not too hard, but the approach
I have suggested is probably the simplest and least code.\
 
Back
Top