How can record data from a one option list automatically

[Guest]

....without clicking on the fields to confirm the selection.

I am populating four fields with employee information by selecting the name
from a combo list. How can I record the information in the table without
selecting the option. The four fields use a list box but it was suggested to
me that I use a text-box and use =employee.column(1) or similar but it hasn't
quite worked.

thanks in advance.

 

[Guest]

Scubadiver,

By trying to save fields that are stored in another table, you're missing
the importance of a fundamental of relational database design, that is,
Normalization. The only field that should be stored from one table to
another (with rare exceptions) is the primary key from the 2nd table, called
a foreign key in the other table.

For example, an Orders table would store the CustomerID field to associate
the order with the customer, but NOT the customer address, phone, etc. These
other fields are properly stored ONCE in the Customer table. To *display*
these other fields in controls on your form, you can include the fields as
columns in the RowSource of a combo or list box, and then set a textbox'
ControlSource to =YourComboBox.Column(x), where x is the column number,
starting with zero. Alternatively, you can base your form on a query that
joins the two table by the Primary Key-Foreign Key, and selects these other
fields.

Database Normalization is IMO the most important fundamental to understand
before you start designing applications. You can get further information at
the following links:

Getting Started:

http://www.mvps.org/access/tencommandments.htm

Glossary of database terms:
http://www.dhdursoassociates.com/database-glossary-3.html

Where to find information about designing a database in Microsoft Access:
http://support.microsoft.com/?id=289533

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Support WebCast: Database Normalization Basic
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp

Hope that helps.
Sprinks

 

[Guest]

I have decided to start my Database again. I know I have to many fields
within one table but these are all fields that I need.

I am not an expert at this so thanks for all the references. Using a query
as a control source rather than a table was going to be my next question so
presumably it can be done.

 

[Guest]

Scubadiver,

Although relational databases are much more efficient & powerful, designing
one is not intuitive, coming from a spreadsheet, or flat file approach. If
you post a generic description of what your application is trying to do, I'll
be happy to assist you.

I would guess that you truly do need all of the fields that you've listed,
but it's likely that they can be split into different tables.

Sprinks

 

[Guest]

My database is simply to record the wage and working hours information for
the department staff. The catch is that it needs to be done on a weekly basis
so each staff member will have four records.

If I use the employee table as a separate table in a query, I had the
imaginative idea of first duplicating the data four times (using week number
as an indicator variable as it were) and appending each query into a master
query which can then be compiled with the other tables (if that makes sense).

However, I have got the general idea of treating a query as a method for
compiling data (via a form) and I have succeeded using the controlsource code
to populate the fields as you have suggested. The problem is that it still
won't record the data to the query (if it can do that).

 

[Guest]

Scubadiver,

I'm not following you. Specifically,

- Why are you proposing four records for each employee? One for each week
in a month? Or do you have a four-day workweek?
- Data isn't "recorded to a query", it is stored in fields in a table. If a
form is based on a query, data entered into a form control (a textbox, combo
box, listbox, etc.) is saved to the field specified in the control's
ControlSource property. The ControlSource can *either* be the name of a
field of the underlying record source OR an expression, but not both.

- You have a situation that actually IS one of the exceptions to duplicating
a field between tables, because presumably you would store the Employee's
current labor rate in the Employees table and use it for the default value
for newly entered work records, but since the rate must be specific to the
week worked, the current value must also be stored in the work record table.
You can do this by using code in the AfterUpdate event procedure of the
employee combo box to assign the rate column to the field textbox control
bound to the Rate field in the work records table.

- I think it would be helpful for you to post how you envision reports
coming from the database, and some sample records. I can then advise you
more directly re: the design, although I sense it will be something like:

Employees
-------------
EmpID AutoNumber (Primary Key)
FName Text
LName Text
LaborRate Currency
HireDate Date/Time
SSN Text
....other Employee-specific fields

WorkRecords
-----------------------
WorkRecordID AutoNumber (Primary Key)
EmpID Long Integer (Foreign Key to Employees)
LaborRate Currency
WorkDate Date/Time
HoursWorked Long Integer
....any other work record-specific fields

Hope that helps.
Sprinks

 

[Sierras]

Sorry to jump into this post, but this is exactly what I am trying to do.
Would you please post the code on the after update combo on how to do this
with the tables and fields you have provided below. I changed the Labor
Rate field names to reflect the current rate and the one used for the Work
Records.

Thanks

 

[Guest]

Sierras,

Include the current rate in the RowSource of the Employee combo box. For
sake of example, let's say it's the third column and that the name of the
combo box is cboEmployee. Since the index of the first column is 0, its
index is 2.

Place a textbox on the form whose ControlSource is the CurrentRate field in
your work records table. Let's say the textbox' name is txtCurrentRate.

In the AfterUpdate event procedure, the code is:

Me![txtCurrentRate] = Me![cboEmployee].Column(2)

If you don't want to allow the user to override this value, set the textbox'
Locked property to Yes, and Tab Stop property to No.

Hope that helps.
Sprinks

In the AfterUpdate event

 

[Sierras]

Thanks - that did it. Just couldn't figure out an easy way to do this.
Also, I think you meant to say

Place a textbox on the form whose ControlSource is the PaidRate field...

and not the CurrentRate Field. But I figured it was just a slip of the
keyboard...

Thanks again.

Sierras,

Include the current rate in the RowSource of the Employee combo box. For
sake of example, let's say it's the third column and that the name of the
combo box is cboEmployee. Since the index of the first column is 0, its
index is 2.

Place a textbox on the form whose ControlSource is the CurrentRate field
in
your work records table. Let's say the textbox' name is txtCurrentRate.

In the AfterUpdate event procedure, the code is:

Me![txtCurrentRate] = Me![cboEmployee].Column(2)

If you don't want to allow the user to override this value, set the
textbox'
Locked property to Yes, and Tab Stop property to No.

Hope that helps.
Sprinks

In the AfterUpdate event

Sorry to jump into this post, but this is exactly what I am trying to
do.
Would you please post the code on the after update combo on how to do
this
with the tables and fields you have provided below. I changed the Labor
Rate field names to reflect the current rate and the one used for the
Work
Records.

Thanks

 

[Guest]

Team leaders will be entering total hours for each week in a month. I have
so many fields, I have three tables. So how do I put them all together to
produce a form?
This is beginning to confuse me!

I think I may need to understand one-to-many relationships.

Your response to Sierras has also confused me a bit.

As far as reports go, for the permanent staff, It would be something like
the following.

Rows: department and subdepartment

Columns: contracted hours, over time (time and a half and double time),
Total hours.

 

[Guest]

I really have four tables:

Employee information
Info for permanent staff
Info for temp staff (part 1)
info for temp staff (part 2)

In total I have about 80 fields.

The question is: how do I combine these four tables into one table?

 

[Guest]

The reason why I am confused is because a form can only have one source of
information. Is that right?

 

[Guest]

Hi,

so what have you done then? I can't still work it out. I have a combo box
for employee name and using a text box to populate five other fields.

Thanks - that did it. Just couldn't figure out an easy way to do this.
Also, I think you meant to say

Place a textbox on the form whose ControlSource is the PaidRate field...

and not the CurrentRate Field. But I figured it was just a slip of the
keyboard...

Thanks again.

Sierras,

Include the current rate in the RowSource of the Employee combo box. For
sake of example, let's say it's the third column and that the name of the
combo box is cboEmployee. Since the index of the first column is 0, its
index is 2.

Place a textbox on the form whose ControlSource is the CurrentRate field
in
your work records table. Let's say the textbox' name is txtCurrentRate.

In the AfterUpdate event procedure, the code is:

Me![txtCurrentRate] = Me![cboEmployee].Column(2)

If you don't want to allow the user to override this value, set the
textbox'
Locked property to Yes, and Tab Stop property to No.

Hope that helps.
Sprinks

In the AfterUpdate event

Sorry to jump into this post, but this is exactly what I am trying to
do.
Would you please post the code on the after update combo on how to do
this
with the tables and fields you have provided below. I changed the Labor
Rate field names to reflect the current rate and the one used for the
Work
Records.

Thanks


On Tue, 16 May 2006 12:56:02 -0400, Sprinks

You can do this by using code in the AfterUpdate event procedure of
the
employee combo box to assign the rate column to the field textbox
control
bound to the Rate field in the work records table.

Employees
-------------
EmpID AutoNumber (Primary Key)
FName Text
LName Text
LaborRateCurrent Currency
HireDate Date/Time
SSN Text
...other Employee-specific fields

WorkRecords
-----------------------
WorkRecordID AutoNumber (Primary Key)
EmpID Long Integer (Foreign Key to Employees)
LaborRatePaid Currency
WorkDate Date/Time
HoursWorked Long Integer
...any other work record-specific fields

Hope that helps.
Sprinks

 

[Guest]

Go to "fancy a challenge?" in the "database design" forum.