Create Dynamically Built Data-Entry form to enter data into two tables

[filbennett]

Hi Everyone,

I'm generally unfamiliar with Access form design, but have programmed
Cold Fusion applications for a couple of years.

I'd like to build a data entry form in Access that allows the
following.

First, the data schema: Three tables are involved. The first is a
PERSONS table which has two fields, SSNUMBER (primary key), and NAME.
The second table is GROUPS, which has two fields, GROUPID (primary key)
and GROUPNAME. Each person can be a member of 0, 1, or many groups. I
created a linking table PERSONS_GROUPS which has two fields, SSNUMBER
and GROUPID, which together comprise the primary key.

In Cold Fusion, I was able to easily build a form to allow the user to
enter both a new PERSON and also specify each group they were a member
of in a single input form. I built the form by first having a data
entry field for SSNUMBER AND NAME. Then I would query the GROUPS table
to build the remainder of the form with checkboxes for each available
group. Thus, the user could type in the SSNUMBER and NAME, and then
CHECK each box for the groups that the person was a member of. Then
when they submitted the form, the person was entered into the PERSONS
table via an insert query, and an entry for each group that was checked
was entered in the PERSONS_GROUPS table via separate input queries.
The nice thing about this dynamicly built data entry form was that as
new groups were entered in the database, there was no need to change
the data entry form.

I'd like to do the same thing with MS Access forms, but I'm not sure
how to do it. Could someone point me in the right direction on this?

Thanks.

Phil

[Larry Linson]

<fi********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi Everyone,

I'm generally unfamiliar with Access form design, but have programmed
Cold Fusion applications for a couple of years.

I'd like to build a data entry form in Access that allows the
following.

First, the data schema: Three tables are involved. The first is a
PERSONS table which has two fields, SSNUMBER (primary key), and NAME.
The second table is GROUPS, which has two fields, GROUPID (primary key)
and GROUPNAME. Each person can be a member of 0, 1, or many groups. I
created a linking table PERSONS_GROUPS which has two fields, SSNUMBER
and GROUPID, which together comprise the primary key.

In Cold Fusion, I was able to easily build a form to allow the user to
enter both a new PERSON and also specify each group they were a member
of in a single input form. I built the form by first having a data
entry field for SSNUMBER AND NAME. Then I would query the GROUPS table
to build the remainder of the form with checkboxes for each available
group. Thus, the user could type in the SSNUMBER and NAME, and then
CHECK each box for the groups that the person was a member of. Then
when they submitted the form, the person was entered into the PERSONS
table via an insert query, and an entry for each group that was checked
was entered in the PERSONS_GROUPS table via separate input queries.
The nice thing about this dynamicly built data entry form was that as
new groups were entered in the database, there was no need to change
the data entry form.

I'd like to do the same thing with MS Access forms, but I'm not sure
how to do it. Could someone point me in the right direction on this?

Thanks.

Phil

If you are not compelled to use checkboxes and labels -- which, of course,
is only one way to accomplish your purpose, you may be able to do what you
want with a Form and a Subform Control, embedding a Form that allows
selection of a Group to create the junction table you describe. To select,
use a Combo Box, including but not showing the Group ID, but showing the
Group's description. With a little care, my guess is that you can do it
using static forms, and bound forms, so you won't even have to write code to
add the records to the junction table.

It isn't exactly the interface you had in mind, but doesn't seem a great
burden on the user to choose one item from a dropdown list, instead of
having check boxes or radio buttons with labels.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

fi********@gmail.com wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:

I'm generally unfamiliar with Access form design, but have
programmed Cold Fusion applications for a couple of years.

I'd like to build a data entry form in Access that allows the
following.

First, the data schema: Three tables are involved. The first is
a PERSONS table which has two fields, SSNUMBER (primary key), and
NAME. The second table is GROUPS, which has two fields, GROUPID
(primary key) and GROUPNAME. Each person can be a member of 0, 1,
or many groups. I created a linking table PERSONS_GROUPS which
has two fields, SSNUMBER and GROUPID, which together comprise the
primary key.

In Cold Fusion, I was able to easily build a form to allow the
user to enter both a new PERSON and also specify each group they
were a member of in a single input form. I built the form by
first having a data entry field for SSNUMBER AND NAME. Then I
would query the GROUPS table to build the remainder of the form
with checkboxes for each available group. Thus, the user could
type in the SSNUMBER and NAME, and then CHECK each box for the
groups that the person was a member of. Then when they submitted
the form, the person was entered into the PERSONS table via an
insert query, and an entry for each group that was checked was
entered in the PERSONS_GROUPS table via separate input queries.
The nice thing about this dynamicly built data entry form was that
as new groups were entered in the database, there was no need to
change the data entry form.

I'd like to do the same thing with MS Access forms, but I'm not
sure how to do it. Could someone point me in the right direction
on this?

Perhaps I'm misinterpreting your message, but it sounds very simple:
use a multiselect listbox that lists the categories. It would be
populated by SQL from the table that holds the list of groups.

How you initiate the insert of the data into the intermediary join
table is up to you. I'm not certain how I'd choose to do it. Using
the listbox's AfterUpdate event has its pitfalls, and using a
command button to require the user to initiate it has it's pitfalse,
as well.

Another option, which is very easy in Access, is to use a subform
built on the join table, linked to the parent table (which is the
recordsource of the parent form), and have a combo box that lists
the category choices. To add a category, you add a new record to the
subform and choose a category from the dropdown list. It's not the
most intuitive UI, but once people have done it a couple of times,
they understand it perfectly well. But it's very easy to build.

Keep in mind that rich-client UIs are by definition going to be very
different from a web interface. The checkboxes approach is probably
appropriate for a browser-based app, but it's completely
inappropriate for an Access application. Access has far more UI
widgets than a browser, so you can choose more efficient and more
appropriate controls for implementing your UI.

I have the exact opposite problem when I'm doing web pages -- I
can't figure out how to translate the type of UI I'd build in Access
into a proper web-based UI.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/