I need help from an expert

[Guest]

I have almost finished a database but I think I only need a bit more help.

I have a combo box for employee names and I have used cascading combo boxes
to populate four other list boxes with unique employee information.

I kept wondering why this information wasn't being stored in the underlying
table and it occurred to me (after work as it happens) that I wasn't clicking
on the four fields to confirm the option. Since there was only one option I
was naively wondering what was going wrong (silly me!).

Anyway, is there any kind of event procedure or SQL that would allow me to
automatically choose an employee name and permanently store the information
from the other fields in the table without having to confirm the option.

I anticipate your response with excitement...

 

[Duane Hookom]

Is there only one possible related value per employee for each of the combo
boxes?
Could you share your method for cascading?

 

[Guest]

Hi,

Well, the four fields are:

1) whether they are temporary or permanent
2) whether they are currently working for the company
3) hourly rate (for temporary staff)
4) total fee paid to the agency (for temporary staff)

So (1) and (2) are definitely not unique!

It just that my supervisor thought team leaders may complain if they have to
put in more information than they have to when it is possible just to select
the information automatically.

I wouldn't want people having to fill in all five fields manually if there
is a better method

cheers

 

[strive4peace]

Hi ScubaDiver,

(one of my sons just got his scuba certification...)

Have you assigned a ControlSource for each ListBox?

If you are collecting en EmployeeID, would not information
like this be stored in the Employee table? Since you are
picking from a list of Employee names, I assuming that you
are storing the EmployeeID but just showing names.the
records in this table are already created,,,

Is this a form to modify Employee records?

What is the purpose of this form?

Lets say you have the table something like the following:

*Employees*
EmployeeID, autonumber
Lastname, text
Firstname, text
Middlename, text
Status, text
IsActive, yes/no
HourlyRate, currency
etc

Now lets say you have another table with fee paid information

*FeesPaid*
FeePaidID, autonumber
CustomerID, long integer -- FK to Customers table
EmployeeID, long integer -- FK to Employees table
DatePaid, date
Amount, currency

Now lets say that you want to design a form to enable you to
add records when fees are paid, and total what has been paid
so far. You would create a form using the FeesPaid table as
a RecordSource of the form

On the form:

FeePaidID will be a textbox
ControlSource --> CustomerID
Locked --> Yes
TabStop --> No

CustomerID could be a combobox or listbox
ControlSource --> CustomerID
RowSource -->
SELECT CustomerID, Customer FROM Customers ORDER BY
Customer;
ColumnCount --> 2
ColumnWidths --> 0;2
ListWidth --> 2

EmployeeID could be a combobox or listbox

Name --> EmployeeID
ControlSource --> EmployeeID
RowSource -->
SELECT
EmployeeID,
Lastname & ", ' & Firstname & ' ' & Middlename as Employee,
Status,
IIF(IsActive,'Y','') as Active,
HourlyRate
FROM Employees
ORDER BY Lastname, Firstname, Middlename;

ColumnCount --> 5
ColumnWidths --> 0;1.5; .75; .75; .75
ListWidth --> 3.75

then, once a choice is made from the combo or listbox, you
can use calculated fields to show other columns. Column
indexing starts at 0, the Column 1 contains the employee
name (this is already showing since column 0, the ID field,
is hidden by using width=0 in ListWidths)

make textboxes -->

Name --> Status
ControlSource --> EmployeeID.column(2)
TabStop --> No
Locked --> Yes

Name --> Active
ControlSource --> EmployeeID.column(3)
TabStop --> No
Locked --> Yes

Name --> HourlyRate
ControlSource --> EmployeeID.column(4)
TabStop --> No
Locked --> Yes

then, you would have textboxes to collect additional
information such as

Name --> DatePaid
ControlSource --> DatePaid

Name --> Amount
ControlSource --> Amount

In the form footer, you can put a textbox with this calculation:

ControlSource --> =Sum(Amount)

If you want to FILTER the form for a particular employee,
then only fee paid for that employee will be displayed and
the total in the form footer would reflect the sum of what
is displayed.



I believe that, when Duane asked you, "Is there only one
possible related value per employee for each of the combo
boxes?", he was trying to determine if this information was
already stored somewhere. If not, then how would you know
what to pre-select? If so, then why store it again?

He also asked you about the method you were using for
cascading -- in other words, is code being used to determine
the choices and make a selection? are you using queries?
how are you limiting the choices and the selection to a
particular employee?


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Hi,

The purpose of the form is to collect working and salary information for
department staff. So the total number of hours worked by each member of staff
is recorded (the department has a high turnover of temporary staff). It isn't
so much to add records when fees have already been paid, but to produce
reports so the managers know how much to pay at the end of the week!

There is a section for permanent members of staff and there is a section for
temp staff.

As well as the main table for all the information, I have a look up table
for employees. This is a link table in Excel so it makes it easier for
employee information to be updated (the information for each employee isn't
necessarily unique to but it does make it easier). The columns are

(1) Name
(2) Temporary or permanent
(3) Are they working for the company or not
(4) hourly rate paid to the employee
(5) total fee charged by the agency to the company

I have successfully created cascading combo boxes so that these four fields
are automatically chosen when the name is chosen. It works perfectly okay.

The "problem" is that the result in each of these four fields need to be
clicked on to store the data in the table.

So if I have got this right, are you suggesting that I include all the
columns in the combo-box and then use textboxes to calculate the information
(I am currently using list boxes)? Would this still work if only the names of
the employees were visible even if the width is zero for the other columns?

That sounds good!! I will work on it and see what happens.


Scuba

PS So where did your son do his certification then? I don't go diving myself
but have been doing it for four years now. I really love it.

 

[strive4peace]

Hi ScubaDiver,

Before discussing implementation using forms, lets discuss
your data structure and what is actually happening -- what
is being stored and where and how it gets in...

I assume that your tables look something like this:

*Departments*
DeptID, autonumber
DeptCode, text
DeptName, text

*Staff*
StaffID, autonumber
DeptID, long
Lastname, text
Firstname, text
Middlename, text
Title, text
etc

*StaffPositions*
StafPosID, autonumber
StaffID, long
Position, text, 1 (T/P -- Temporary, Permanent)
DateStart, date
DateEnd, date
Rate, currency
RateTypeID, long
(related to RateTypes table -- Hourly, Salary)
(could be redundant with Position field -- am just throwing
out ideas)
etc

*StaffHours*
StafHrID, autonumber
StaffID, long
StartDate, date (first date/time of work date)
EndDate, date
(daily hours can be calculated)

if you just track hours on a weekly or other basis, adjust
the structure accordingly

*StaffFees*
StafFeeID, autonumber
StaffID, long
AgencyID, long -- FK to Agencies table
FeeDate, date
Fee, currency

The total fee charged should add up to the sum of the fees
in the StaffFees table. Otherwise, if you choose not to
record the detail, the total fee paid could be tracked in
another table with the date that was reported.

If it is possible to include StaffID in your Excel table, I
would highly recommend it. Then, if there is no StaffID,
the system can lookup the record by name and add the record
and if there is one. StaffID is needed to accurately link.

the system should check to see if
1. the person is in the Staffs table -- if not, add them
2. the information in StaffPositions is the same as is what
is in Excel -- (2) (3) and (4) -- if not, update or add a
record according to your business procedures

When an employee is chosen on your form, the StaffID becomes
known and the other information is checked and/or updated --
so it can simply be echoed from its place of storage.

Your form would have controls only for fields that the team
leaders will actually change.

How are you getting the information from Excel? Linking?
Importing?

These ideas are best implemented using VBA ... but don't get
lost in that right now if you are not a programmer. The
forum is here to help you past your hurdles.

Right now, you need to look at your database using a
different perspective -- instead of thinking about how your
forms will work, think about the foundation of what you are
building -- your data structure and relationships.

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Hi,

The only fields I am concerned about at the moment are the following:

1) Name (which is a combo box)
2) Current (Is the employee currently working for the company)
3) status (is the employee permanent or temporary)
4) rate (hourly rate)
5) totfee (fee charged by agency to the company)

Believe me, it really is that simple.

The information for fields 2-5 (which are list boxes) work perfectly using
cascading combo's using all the information from the "Name" field (its just
that I have to click on the fields in order for the data to be stored in the
table which I don't want to do).

The only slightly different problem I have is that the information for
"totfee" can't be clicked on because the precision isn't great enough (its
decimal). When I change the precision in the table design it gives the
following error message:

"The settings for the fieldsize property of one or more fields has been
changed to a shorter size. If data is lost, validation rules maybe violated
as a result". When I click on "yes", it says "too many fields defined".

I don't understand why two different fields can have all the same
specifications (apart from a slight difference in the row source) and one
field can store information in the table and other can't.

David

 

[strive4peace]

Hi ScubaDiver,

Access is very powerful -- of course you CAN store the same
information in multiple places, use long text fields to
match data instead of short numbers, etc ... it is a
question of whether or not that is the best way to do it.

Just as you wouldn't build a skyscraper on a foundation that
has not set, you should solidify your data structures before
you build forms.

Many people do not want to discuss data structure, and part
of the reason for that is that they do not yet understand
the value in it -- just wait a few months when it takes you
weeks to do something that would take a day using a strong
and flexible structure.

Duane asked you to share your method for cascading. That
would be very useful information.

What is the RowSource for each of your combos? Are the
source tables stored in Access or linked to another format?

What is happening on the AfterUpdate event of the name
control to limit these combos?

What is the RecordSource for your form?

We need to know more about how your data is STORED, and we
need to understand what you are doing to populate the combos.

Please answer these questions so that we can help you. Thanks.

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day