Variable Check Boxes

[TitaniaTiO2]

background info - my table includes the following categories: Last name,
first name, Document number, document title, date trained, training status

I would like a form where I can have the user enter in a new document number
and document title and then be able to select the employees(last name, first
name) that need to be trained on that document.

Since the user needs to select multiple names I considered having the form
display all employees on a form as check boxes, allowing the user to look at
the list and check which names apply.

Then I want to add the same number of lines to my table as checked
employees, with the appropriate names listed and the new document number and
title listed in their respective columns.

1. Is it possible to display check boxes in this way? I cannot just set
them up from the beginning with the employee names since the list of
enmployees changes over time.
2. If it is not possible - are there any alternative suggestions?
3. How do I feed the needed information back into the table.

I want to use something relatively automated because I do not want the user
to have to type in each name and risk mistakes.

Any help is greatly appreciated.

Thanks!

Titania

 

[Jeff Boyce]

Possible, yes.

Desirable, NO! (if you have to have a checkbox for each employee, and you
have code/macros to handle that, won't you have to modify the form, the
reports, the code, the macros, the table, everything if you add (or remove)
an employee?!)

By the way, it all starts with the data. If your data has one column per
employee, you are using a spreadsheet, not a relational database! Consider
posting a description of the underlying data structure. "How" depends on
how your data is organized.

If you want to assign a collection of some things (say, employees) to
something else (say a specific training/class), you could do this using a
combobox to pick the class and a listbox ("multi-select" turned on) to pick
the employees.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TitaniaTiO2]

The table is set up with the following columns:

Department (there are about 8 options here)
Last Name
First Name
Title
Document # (There are several hundred documents)
Document Title
Document History
Date Trained
Training Status (i.e. New or Existing)





When a new document comes out, I want the user to be able to easily add it.
The new document would have anywhere from 1 - 100 people being trained to it
(use 5 as an example). The user needs to be able to select which users need
to be trained to that document. I then want to add the 5 lines to the table.
The same document number/title/history will be used for all 5 lines, but
each line is a different employee name. In addition, the training status for
all 5 would be "new"
This would allow me to generate a training document (report already created)
that can be printed out for just those 5 employees.

Does that make sense?

Thanks

 

[Jeff Boyce]

I must be missing something. Are you saying that you repeat Department,
LastName, FirstName, and Title for each of "several hundred documents)
record when it's the same person being trained on a different document? And
that you repeat DocumentTitle every time you add a record in which you've
already included Document#? That sounds like a spreadsheet, not a
relational database.

In the absence of any better understanding of your specific data situation,
I'll offer a first cut notion at a less-spreadsheetly design. See if it
accurately reflects your situation...

tblPerson
PersonID
FirstName
LastName

tblDepartment
DepartmentID
DepartmentName

tblEmployment
EmploymentID
PersonID
FromDate
ToDate
DepartmentID
Title

tblDocument
DocumentID
DocumentTitle

trelTraining
TrainingID
PersonID
DocumentID
DateOfTraining

I'm still not clear on what [DocumentHistory] and [TrainingStatus] refer to,
or what kind(s) of data they contain.

Note that this focuses FIRST on the underlying data. Once you have that in
a form that Access can (better) use, then it's time to proceed to "how"
questions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TitaniaTiO2]

Thanks.

I inherited the file from someone else. They had 1 class on Access and
undertook a decent sized project. I was enlisted to help because I have
experience with Excel and VBA for Excel. Which in no way makes me qualified
to deal with Access and I am just trying to muddle my way through. It was
handed to me with one table, a report that did not work, and no forms.

I have it to a functional point and I am debating just leaving it rather
then face starting over from scratch.

If I start over, let me see if I understand the tables.

For tblTraining
TrainingID - this is just an ID number (1, 2, 3...)
PersonID - This is the ID number from the Person table that corresponds
to the person I want (1, 2, 3...)
DocumentID - This is the ID number from the Document table that
corresponds to the Document I want (1, 2, 3...)
DateOfTraining

So my table looks like:
TrainingID 3
PersonID 2
DocumentID 57
DateofTraining 7/16/08

Right?

This seems like it is going to be painful to do the initial data entry to
use for testing until I get some forms in place.

Thanks for the help
Titania

 

[John W. Vinson/MVP]

This seems like it is going to be painful to do the initial data entry to
use for testing until I get some forms in place.

Painful? Not really. Miserable would be more like it.

Forms are *absolutely essential* - as integral to the use of Access
as, say, cell references in Excel spreadsheets. Are cell references
trivial to learn? Not really, but you learn to use them and now
they're natural to you. Forms will be the same way.

Build a data entry form. Use combo boxes on it for these fields.
Better, make it a Subform of the People form if you'll normally be
picking a person and assigning them to one or more classes, or a
subform of the Classes form if you'll normally pick a class and then
assign people to it.

 

[Jeff Boyce]

.... what John said!

Access tables store data, Access forms (and reports) display it.

Just as you need to understand "normalization" and "relational" to make good
use of Access, you also need to learn about the tools Access offers (e.g.,
forms, queries, ...).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP