Question about form operations


S

stacie.2410

I've got some experience with Access, but it's not extensive, and I really
can't figure out how to make something work the way I need to, or if it's
even possible, so I thought I'd ask here.

I've set up a form to use to enter data about a department, there's about 8
fields of data that will be filled out that pertain to the department. (Each
department will have it's own form.) Now, on that same form, under the other
rows of data, I'd like to set a button to push that will let me add a row of
data (to kinda create a little table), that will let me enter the employees
that are in that department, one row per employee, and the row will have
other data on it too, such as employee number, hire date, office number, etc.
There isn't a set number of employees, so I could just enter one employee,
or I could push the button and insert multiple rows for however many
employees I need.

The data doesn't HAVE to be entered directly into the little table, as long
as it does show up there. For instance, if you push the button and a small
subform pops up where I enter the data and then save and exit it, and it
takes me back to the main form where the data was entered into the table for
me, that'll work too.

Is this possible? Do you have any suggestions?
 
Ad

Advertisements

J

Jeanette Cunningham

Hi stacie,
the most important thing is the way the tables and relationships have been
setup.
If they are not set up correctly for what your database needs, keeping track
of which employee is in which department will be very difficult. From your
description of the problem, it is not clear how you have the tables set up.

Would you post back with names, primary key, foreign key and relationships
for those tables involved.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
J

John W. Vinson

I've got some experience with Access, but it's not extensive, and I really
can't figure out how to make something work the way I need to, or if it's
even possible, so I thought I'd ask here.

I've set up a form to use to enter data about a department, there's about 8
fields of data that will be filled out that pertain to the department. (Each
department will have it's own form.)

Well... that's probably NOT a good idea. You would only have a different form
if you were collecting different KINDS of information (different numbers and
kinds of fields) about each department.
Now, on that same form, under the other
rows of data, I'd like to set a button to push that will let me add a row of
data (to kinda create a little table), that will let me enter the employees
that are in that department, one row per employee, and the row will have
other data on it too, such as employee number, hire date, office number, etc.
There isn't a set number of employees, so I could just enter one employee,
or I could push the button and insert multiple rows for however many
employees I need.

The data doesn't HAVE to be entered directly into the little table, as long
as it does show up there. For instance, if you push the button and a small
subform pops up where I enter the data and then save and exit it, and it
takes me back to the main form where the data was entered into the table for
me, that'll work too.

Is this possible? Do you have any suggestions?

Stop. Step back. Check out some of these resources, particularly the
tutorials:

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

It sounds like you're making the very common mistake of starting your database
design with Forms. Forms are *very much secondary* - the Tables are the basis
of your application, not the forms. I'm guessing (in the dark, not knowing
anything other than what you posted) that you need two tables: a table of
Departments with a unique DepartmentID, name of the department, various other
information about the department as a whole; related one to many to a table of
Employees, with an EmployeeID, DepartmentID (which department is this employee
working for), LastName, FirstName, and other biographical data.

A Form based on Departments with a subform based on Employees would let you do
what you need... without any need for "a different form for each department".
 
S

stacie.2410

My apologies, I accidentally said each department would have it's own form,
that's not what I really meant. Each department will have it's own RECORD.

I also must admit that I used "Department" and "Employee" to simplify my
question, it might help me explain better though if I use what my actual
fields are.

What I have is a database that needs to house property names and the
individuals who own a percentage of that property. The property names will
be unique, the individuals will not be, they will own a percentage of several
properties more than likely.

My reason for creating a form, is because I need a place where I can enter
data easily about a property, then below that information, I need to add all
the owners of the property (sometimes there could be 10, sometimes there
could be just 1), their percentage they own and other fields specific to
their ownership in that one particular property.

Basically, I'm trying to convert my data from a spreadsheet to a database,
and here's why. I have a spreadsheet that lists all of the properties and
all of their owners. When there are multiple owners in a property, the
property will be listed for each owner, so there may be 10 rows for one
property, each with a different owner. Rather than doing this, I'd rather be
able to just open a database, put in a property name, and it would pull up
the property information and list all of their owners, their percentage, etc.
Likewise, I'd like to be able to enter an owner, and all the properties they
own a percentage in would pull up. There will be reports tied to this
information because several documents have to be generated based on this data.

Do you have a suggestion for me? I can post whatever information you need.
 
J

John W. Vinson

My apologies, I accidentally said each department would have it's own form,
that's not what I really meant. Each department will have it's own RECORD.

I also must admit that I used "Department" and "Employee" to simplify my
question, it might help me explain better though if I use what my actual
fields are.

That can help resolve confusion.
What I have is a database that needs to house property names and the
individuals who own a percentage of that property. The property names will
be unique, the individuals will not be, they will own a percentage of several
properties more than likely.

So you really have a many to many relationship, right? Each Property may have
many Owners, and each Owner may own (parts of) several Properties, right?
My reason for creating a form, is because I need a place where I can enter
data easily about a property, then below that information, I need to add all
the owners of the property (sometimes there could be 10, sometimes there
could be just 1), their percentage they own and other fields specific to
their ownership in that one particular property.

Then you need three tables: a table of Properties; a table of Owners; a table
of Ownership. This last table would have fields for the PropertyID, the
OwnerID, the percentage owned and these "other fields".

You could use a Form based on the Properties table, with a subform based on
Ownership. This subform would show all the owners; I'd have only the OwnerID
in the Ownership table and use a Combo Box to display the owner's name. You
could have code in the combo's Not In List event to pop up a form to enter a
new Owner when you encounter an owner not yet in the database.
Basically, I'm trying to convert my data from a spreadsheet to a database,
and here's why. I have a spreadsheet that lists all of the properties and
all of their owners. When there are multiple owners in a property, the
property will be listed for each owner, so there may be 10 rows for one
property, each with a different owner. Rather than doing this, I'd rather be
able to just open a database, put in a property name, and it would pull up
the property information and list all of their owners, their percentage, etc.
Likewise, I'd like to be able to enter an owner, and all the properties they
own a percentage in would pull up. There will be reports tied to this
information because several documents have to be generated based on this data.

The above will do just this.
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top