Populating a subform and saving to the underlying tables

[new2access123 via AccessMonster.com]

Populating a subform and saving to the underlying tables

I have created a database of three tables Client, Project, Participant,
TestName and Scores. I have enabled referential integrity in the
relationships. Client may have many Projects and a project may have many
Participants.

I have three combo box controls the value selected in the Client cbo is used
in the row source query to populate the Projects cbo and that value is used
in the row source query to populate the Participants cbo.

When I select a participant I want to populate a "grid" with the
participant's scores. Since there is no grid control in Access I was looking
at using a subform. But it is not populated like a cbo or list box. And I
am not sure if that is the correct control to use in this case.

What I need is a control that is:
• Populated from a join (I would guess a record set) from all of the tables
but only shows the columns I need.
• Allows me to edit existing rows in the grid and save the changes to the
source tables
• Allows me to append new records and save to the source tables.

What control should I use and how do I do it? Can some one provide some
sample code or direct me to a sample?

Thanks

 

[John Spencer]

First you say you have three tables and then list at least 4 (perhaps 5) names.

I would use a sub-form. You can set the relationship up between the main form
(where you have your choice comboboxes) and the sub-form to use the main form
combobox (link Master fields) and the sub-form ParticipantID (Link Child
Fields). You will have to do this yourself since it can't be set up by the
sub-form wizard. And I know it says Field, but you can actually use a control.


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

 

[new2access123 via AccessMonster.com]

As i add more information to my post I did not go back and change the table
count. Sorry bout that. Thank you for your reply but being a brand new
user of ACCESS and teaching myself i have no idea how to accomplish what you
suggested. Is there sample cod some where I can look at to understand the
technique and syntax?

First you say you have three tables and then list at least 4 (perhaps 5) names.

I would use a sub-form. You can set the relationship up between the main form
(where you have your choice comboboxes) and the sub-form to use the main form
combobox (link Master fields) and the sub-form ParticipantID (Link Child
Fields). You will have to do this yourself since it can't be set up by the
sub-form wizard. And I know it says Field, but you can actually use a control.

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

Populating a subform and saving to the underlying tables

[quoted text clipped - 23 lines]

Thanks

 

[John W. Vinson]

As i add more information to my post I did not go back and change the table
count. Sorry bout that. Thank you for your reply but being a brand new
user of ACCESS and teaching myself i have no idea how to accomplish what you
suggested. Is there sample cod some where I can look at to understand the
technique and syntax?

Take a look at some of these resources, especially Crystal's and Allen's
tutorials or Crystal's video.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[new2access123 via AccessMonster.com]

Thanks for the site list I found some useful stuff that I will incorporate
into my app.

I think the approach I have to take is this… I have created a record set
(dbOpenDynaset) that brings together the client, project, test name and
scores for a selected participant. I will need to edit existing data and
append and delete rows.

But unfortunately I have found nothing that addresses the question of how can
I populate a subform from the record set so I can add edit, add and delete
records?

 

[John W. Vinson]

Thanks for the site list I found some useful stuff that I will incorporate
into my app.

I think the approach I have to take is this… I have created a record set
(dbOpenDynaset) that brings together the client, project, test name and
scores for a selected participant. I will need to edit existing data and
append and delete rows.

But unfortunately I have found nothing that addresses the question of how can
I populate a subform from the record set so I can add edit, add and delete
records?

You're making it harder than it is.

You don't need *any code at all*.
You don't need to create *any recordsets*.
Access *does it for you*.

Simply create a Form based on the "one" side table. Use the Forms Wizard, or
do it manually in form design view.

Create a Subform based on the "many" side table. Same drill.

Both the Form and Subform may have "combo box" controls for your lookup tables
(e.g. tests, projects). They may also have textboxes, bound to (say) the score
field in your table.

Open the form.

Edit your data.

Let Access work for you; that's what it's designed to do! Rewatch Crystal's
video (putting your own application to the side briefly) and see how she sets
up the form.

 

[new2access123 via AccessMonster.com]

I went back and did some more studying on how Access maintains referential
integrity when using subforms. I am coming from the FoxPro world and Access
does this in a very different way. Access does do all the work. Thanks for
pointing me in the right direction.

I am left with a couple of final question about how to do this right. There
are three combo boxes on the form that the user must set. Each provides a
value to the next cbo to properly set the next list. This means that each
control after any given cbo needs to be refreshed (requery) to display the
correct information. Is there a form level method that will refresh all
controls o the form. This was a FoxPro feature where a container could
refresh all of it's contained controls.

Last, in my subform is displayed as a datasheet. The link to the form is a
primary key which I do not want to appear in the subform. How do I control
the columns and column titles? O at least make the PK not show.

Thanks for the site list I found some useful stuff that I will incorporate
into my app.

[quoted text clipped - 7 lines]

I populate a subform from the record set so I can add edit, add and delete
records?

You're making it harder than it is.

You don't need *any code at all*.
You don't need to create *any recordsets*.
Access *does it for you*.

Simply create a Form based on the "one" side table. Use the Forms Wizard, or
do it manually in form design view.

Create a Subform based on the "many" side table. Same drill.

Both the Form and Subform may have "combo box" controls for your lookup tables
(e.g. tests, projects). They may also have textboxes, bound to (say) the score
field in your table.

Open the form.

Edit your data.

Let Access work for you; that's what it's designed to do! Rewatch Crystal's
video (putting your own application to the side briefly) and see how she sets
up the form.

 

[John W. Vinson]

I went back and did some more studying on how Access maintains referential
integrity when using subforms. I am coming from the FoxPro world and Access
does this in a very different way. Access does do all the work. Thanks for
pointing me in the right direction.

I have done a little work with FoxPro (some time ago) and I can assure you
that crossing the gulf between them is equally difficult in either direction!

Sometimes being expert in one area of sofware is an actual *disadvantage* in
learning a new one because in addition to learning the new, you must
sometimes "unlearn" the "way things are always done". But I think you'll find
that Access is (like FoxPro) a fully capable program, once you learn its
quirks.

I am left with a couple of final question about how to do this right. There
are three combo boxes on the form that the user must set. Each provides a
value to the next cbo to properly set the next list. This means that each
control after any given cbo needs to be refreshed (requery) to display the
correct information. Is there a form level method that will refresh all
controls o the form. This was a FoxPro feature where a container could
refresh all of it's contained controls.

You can put a Requery macro, or VBA code, a oneliner such as

Me!comboboxname.Requery

in some appropriate event. Conditional combo boxes like you describe are very
common; the usual way to manage them is to base the second combo box on a
Query referencing the first combo as a criterion, and requery it in the
AfterUpdate event of the first combo.

Last, in my subform is displayed as a datasheet. The link to the form is a
primary key which I do not want to appear in the subform. How do I control
the columns and column titles? O at least make the PK not show.

By far the simplest approach is to avoid the Datasheet view, and use
Continuous Form view instead. That will let you select which fields you see,
which you don't, and give a lot more GUI control over the appearance and
layout of the data.

 

[new2access123 via AccessMonster.com]

Yes the transition is proving to be more of a pain than I thought. I am
heavy into the “unlearn/relearn†process. Unfortunately the vast majority of
the help out there is geared to using wizards not explaining what is actually
going on in Access. I am not a fan of wizards. They don’t write the best
code and are limiting.

The .Requery has gotten my controls refreshing properly.

In my searching I am seeing a pattern of people suggesting the use of
Continuous Form view instead of Datasheet view. I can understand why
considering the problems I am having accomplishing what should be an
elementary task. But in my humble opinion in a lot of cases a Continuous
Form is not the best interface mode and a grid (Datasheet view since VBA does
not have a grid) would be much better solution. I am not quite ready to give
up on the Datasheet view just yet. There must be methods and properties to
control a Datasheet view just like there are for grids in C# and Fox. It is
hard to believe Microsoft would have left a gaping hole like that In Access.

Thank you for your feed back. It has been helpful.

I went back and did some more studying on how Access maintains referential
integrity when using subforms. I am coming from the FoxPro world and Access
does this in a very different way. Access does do all the work. Thanks for
pointing me in the right direction.

I have done a little work with FoxPro (some time ago) and I can assure you
that crossing the gulf between them is equally difficult in either direction!

Sometimes being expert in one area of sofware is an actual *disadvantage* in
learning a new one because in addition to learning the new, you must
sometimes "unlearn" the "way things are always done". But I think you'll find
that Access is (like FoxPro) a fully capable program, once you learn its
quirks.

I am left with a couple of final question about how to do this right. There
are three combo boxes on the form that the user must set. Each provides a

[quoted text clipped - 3 lines]

controls o the form. This was a FoxPro feature where a container could
refresh all of it's contained controls.

You can put a Requery macro, or VBA code, a oneliner such as

Me!comboboxname.Requery

in some appropriate event. Conditional combo boxes like you describe are very
common; the usual way to manage them is to base the second combo box on a
Query referencing the first combo as a criterion, and requery it in the
AfterUpdate event of the first combo.

Last, in my subform is displayed as a datasheet. The link to the form is a
primary key which I do not want to appear in the subform. How do I control
the columns and column titles? O at least make the PK not show.

By far the simplest approach is to avoid the Datasheet view, and use
Continuous Form view instead. That will let you select which fields you see,
which you don't, and give a lot more GUI control over the appearance and
layout of the data.

 

[John W. Vinson]

But in my humble opinion in a lot of cases a Continuous
Form is not the best interface mode and a grid (Datasheet view since VBA does
not have a grid) would be much better solution. I am not quite ready to give
up on the Datasheet view just yet. There must be methods and properties to
control a Datasheet view just like there are for grids in C# and Fox. It is
hard to believe Microsoft would have left a gaping hole like that In Access.

You make a good point - the Datasheet should be more controllable and flexible
than it is. You can set a field to zero width, or even use a Datasheet based
on a query selecting only a subset of the fields, if you want to conceal a
field; but it is in fact limited.

There are non-Access (and even non-Microsoft) Grid controls which you can
include in a database, which may serve your turn; I personally don't have any
experience with them though. You may want to go to http://groups.google.com
and search these groups for "grid control" to find folks who would know more
than I.