datagrid

[Peter Morris]

I want to show the results of an SQL select statement in a form.

I've used a DataGrid control in VB 6 but I can't find it in the
Access toolbox. Is there an equivalent control in Access?

I want to join two tables and show the results in a single grid.

Not a datasheet view based on the query. That gives me two
controlls, I have to select a value from table 1 to get the linked
rows from table 2. What I want is the join in a single grid.

Nor do I want the navigation bar at the bottom.

Nor do I want to edit records directly in the grid.

 

[Chris]

I don't quite get what is keeping you from using a datasheet based on a query
joining the two tables. But for the other things:

Nor do I want the navigation bar at the bottom.

In the form properties, set Navigation Buttons to "no"

Nor do I want to edit records directly in the grid.

In the form properties window i teh data tab you can disbale edits,
additions and deletions.

HTH

Chris

 

[Albert D.Kallal]

Not a datasheet view based on the query. That gives me two
controlls,

Don't understand what you mean by "two" controls....?? (what is, or why is
that a problem?).

I have to select a value from table 1 to get the linked
rows from table 2. What I want is the join in a single grid.

You can send the data if the results are small to a multi-column listbox.
But, better is a continues form.

Nor do I want the navigation bar at the bottom.

All forms have the setting to turn off the navigation stuff, record
selectors, and just about everything else you could want.

Nor do I want to edit records directly in the grid.

You can turn off allow edits also....

Here is some screen shots of using a continues form for that of a grid....

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

As you can see, some have navagation buttions...some don't, and they are
just settings in the form...

 

[Peter Morris]

Don't understand what you mean by "two" controls....?? (what is, or why is
that a problem?).

I have two tables.

Tab_1 with columns KEY, A, B, C
Tab_2 with columns FKey, D, E, F

My query is :

select A, D, E, F from Tab_1, Tab_2 where FKey = Key

I tried creating a form based on this query. What I mean by
two controls is that I get one box where I can select a value
for column A, and a seperate grid where it shows the values
of D, E, F joined to the value of A that I select. It has two
nested navigation bars.

This is a problem because I want one single grid showing A, D, E, F.

You can send the data if the results are small to a multi-column listbox.
But, better is a continues form.

That looks like what I want.

How do I create one?

 

[Albert D.Kallal]

As mentioned, if you look at those screen shots, some grids are listbox, and
some are continues forms.

I tend to prefer a continues form, but it really depends on what you need
the resulting data form.

A multi-column listbox can easily be built with the wizard (simply supply it
your query, and follow the prompts).

For better formatting (and allowing things like formatted text, formatting
dates, and controls like check boxes etc), then just use a continuous form.
You build a continuous form like any other form in ms-access. (just use the
mouse, and drop controls on the screen).
So, just drop your controls in to the detail section, size the detail to
"one" line high (or, two lines if you wish - this is what sets that
continues forms apart from grid controls...you can have each detail record
take two or even 3 lines if you want!!). You then in the form "other" tab
simply set the form to continues view. You are done. Note that all of those
screen examples are simply plan Jane ms-access forms with great
functionality, and very little code. Even the last example that "sums" up
and shows a total in the bottom is easy (that total takes no code, as a
sum([fieldname]) command in the forms footer actually generates a total of
the current records.

If the list is to be quite small (say, 0-100), then a list box is a great
choice. (and, it allows you to multi-select items with ease). If you got
larger datasets, and need better control of the layout, then using a
continuous form is a better choice. (but, then to multi-select, you need to
provide a check box column). Note that virtually all of those screen shots
are continues forms except for the first example. Of course, if you are
taking about a form that needs "several" grids, and plan to use a continues
form, then that form has to be a sub-form. You can read about sub-forms
here:

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

 

[Peter Morris]

As mentioned, if you look at those screen shots, some grids are listbox,
and some are continues forms.

I tend to prefer a continues form, but it really depends on what you need
the resulting data form.

A multi-column listbox can easily be built with the wizard (simply supply
it your query, and follow the prompts).

For better formatting (and allowing things like formatted text, formatting
dates, and controls like check boxes etc), then just use a continuous
form. You build a continuous form like any other form in ms-access. (just
use the mouse, and drop controls on the screen).

Sorry, I really don't understand.

WHAT controls do I drop on the form to make a datagrid?

So, just drop your controls in to the detail section, size the detail to
"one" line high (or, two lines if you wish - this is what sets that
continues forms apart from grid controls...you can have each detail record
take two or even 3 lines if you want!!). You then in the form "other" tab
simply set the form to continues view. You are done. Note that all of
those screen examples are simply plan Jane ms-access forms with great
functionality, and very little code. Even the last example that "sums" up
and shows a total in the bottom is easy (that total takes no code, as a
sum([fieldname]) command in the forms footer actually generates a total of
the current records.

If the list is to be quite small (say, 0-100), then a list box is a great
choice. (and, it allows you to multi-select items with ease). If you got
larger datasets, and need better control of the layout, then using a
continuous form is a better choice. (but, then to multi-select, you need
to provide a check box column). Note that virtually all of those screen
shots are continues forms except for the first example. Of course, if you
are taking about a form that needs "several" grids, and plan to use a
continues form, then that form has to be a sub-form. You can read about
sub-forms here:

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

Sorry, I really don't understand.

WHAT controls do I drop on the form to make a datagrid?

Any of the standard ms-access controls (text box, check box, combo etc.).
You simply drop those controls on to the form.

So, you drop the SAME controls that you would use to make a form. The
process of creating a regular form, or a continues form that displays as a
grid is exactly the same. (the only DIFFERENCE is that you set the form to
"continuous" mode in the other tab).

So, at this point, I have to assume that you have the know how to build a
form (some users have worked with ms-access for considerable amounts of time
and have NEVER built a standard form - they use the wizards). I am going to
assume that you have at least the basic skills to build ms-access forms. So,
when you build a regular form, you simply drop controls on the form. Either
you select a control from the tool box, and then simply click on the form.
Another common approach is to go view->field list. You then drag and drop a
field from that field list onto your form.

So, we are talking about a standard form, and the approach to build a
continues form (that displays as a grid), or a standard form is EXACTLY the
same. There is NO special grid control here. The only way to learn this is
to try building a form. Drop one text box onto the detail section of a form,
and set the form into continues mode (that is set in the forms property
sheet "other" tab). You of course need to re-size the "detail" section of
the form to only one line high...and in continues mode that detail section
repeats over and over.

If you have basic skills to create forms, then you got the skills to build
forms like my sample screen shots.

Building a continues form that displays as a grid requites NO new skills
here, and you use the SAME basic controls as you use on forms now.