473,572 Members | 3,184 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

Dec 26 '05 #1
2 5044

<fi********@gma il.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.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
Dec 26 '05 #2
fi********@gmai l.com wrote in
news:11******** **************@ g44g2000cwa.goo glegroups.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/
Dec 26 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3156
by: Petri Savolainen | last post by:
After reading the manuals and googling around a bit, I thought I'd use the 'compile' built-in to create a code object. Then, using either new.function() or types.FunctionType(), create a function object out of the code object. The function object can then be turned into a method for example using types.MethodType(). Right? Well, on Windows 98,...
1
3103
by: Alex Elbert | last post by:
Hi I have built dynamic HTMLTable. Now I want to attach it directly to the Email Body - it is already built, so why not to use a ready table. However, I cannot find the way of getting plain HTML text out of dynamically built control. I tried to put my table between div and read div.innerHTML then - HTTP exception has been thrown. Any thoughts,...
1
1591
by: ezmeralda | last post by:
Hello, I have to solve the following task: - create an editor-application in c# to read, modify and create xml-files - .xsd-schema-file is available and can be used Currently, I am thinking of the following way to do the job: - use xsd.exe to create basic c#-classes from xsd.file (modify these classes according to my particular needs)
0
1182
by: Billy | last post by:
I have a web page with a control (Control#A) on it. The control (#A) has a control (Control#B) on it which displays an exception message when set and displayed. I am creating a simple datagrid dynamically with delegates and assigning it to a placeholder on control #A. If, within a delegate of the dynamically built datagrid, I set a property...
7
6764
by: pmclinn | last post by:
I was wondering if it is possible to dynamically create a structure. Something like this: public sub main sql = "Select Col1, Col2 from Table a" dim al as new arraylist al = LoadOracleData(sql) '____Do amazing things
2
2385
by: John Oliver | last post by:
Fedora Core 2, httpd-2.0.54-10.2, php-4.3.10-2.4, postgresql-7.4.7-3.FC2.1 Using a test.php that includes: <?php pg_connect("host=localhost dbname=db_name user=user password=password$"); phpinfo(); ?>
2
1621
by: mike | last post by:
i'm building an desktop windows app in C#. the main menu for the application will be different depending on the admin rights of the user. i've built the entire app. in delphi and i simply activated or deactivated the menu items in that case, but all the menu items are visble. i'm hoping / thinking that with .NET, i can dynamically generate...
4
8268
by: sirjohnofthewest | last post by:
If I possessed the power to sway the mind of every user in the world to delete all forms of Internet Explorer I would die a happy man. Hi guys, I frequently visit this site to get answers to my problems and this one is really getting to me... I have a page that allows you to Browse Authors. There are three drop down boxes that auto-populate...
18
2280
by: Angus | last post by:
Hello We have a lot of C++ code. And we need to now create a library which can be used from C and C++. Given that we have a lot of C++ code using classes how can we 'hide' the fact that it is C++ from C compilers? Can we have a C header file which uses the functionality of the C++ files and compile this into a lib file?
11
2929
by: =?Utf-8?B?UGV0ZXIgSw==?= | last post by:
I am working with Visual Studio or alternately with Expression Web. I need to create about 50 aspx pages with about 1200 thumbnali images, typically arranged in three to four groups per page, having hyperlinks to the corresponding full size images. Can anybody point me to locations in MSDN or elsewhere giving the references to attach, the...
0
7733
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7957
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8000
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6337
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5248
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3673
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2138
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1240
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
975
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.