I have a simple form that collects information about an employee such worked hours, overtime, dept, employee ID, etc. Fields such "Dept" and "Week" I want to keep the same because when a supervisor is entering the required data the dept and week worked will not change. Is there a trick to make this work? And if it works, is it being transferred to a table?

Thank you

Hi and welcome to UA!

There are many many methods that will accomplish what you're after. The method that I use is to include a checkbox beside each of the controls that you would like to propegate from record to record.

The method I use is this :

in the _AfterUpdate() event of each textbox control (or combo, or whatever)

me.txtDept.tag = me.txtDept

and in the form_current event

if chkDept and me.newrecord then
me.txtDept = me.txtDept.tag
endif

repeated for each one you would like to propegate. The advantage of this method is that you can toggle which you'd like, and it will only go to a new record. The disadvantage is that you will need to do that for each control.

Hope this helps,

Adam

Hi Adam, I did the process above and received a message back (see attachment)

Hi,

Ok, first, create each checkbox and name it appropriatly. Ensure that they are unbound.

Next, right click the first textbox taht you wish do propegate. Select 'Build an event'

You will be presented with the VBA editor screen. From the dropdown menu on the right, select the event 'After Update'. Type in :

me.txtDept.tag = me.txtDept

Then from the left dropdown, select 'Form', then from the right dropdown, select 'Current'

type :

if nz(chkDept,0) and me.newrecord then
me.txtDept = nz(me.txtDept.tag,vbnullstring)
endif

Then try it out.

This will work if the checkbox is called chkDept, and the textbox is called txtDept.

Hope this helps,

Adam

Adam, thansk for helping me. I got another error. This time is says (see attachment). Though it would be easier.

Sergio

Hi,

you're not building an event yet. You must place it in the VBA editor, not the property list display. "Me." doesnt refer to a macro, but the form object itself. If you are still having problems, can you zip & strip your db, and I will have a look.

Adam

Thanks Adam, here is the zip file

Sergio

Well first your database is not normalized properly. You should have at least 2 tables:

tblEmployees
EmployeeID (PK)
Lastname
FirstName
Status
Dept

tblHours
HoursID (PK Autonumber)
EmployeeID (FK)
WeekOf
HoursTypeID (FK to tluHoursTypes see below))
Hours
Comment

tluHoursTypes
HoursTypeID (PK Autonumber)
HoursType (Regular, OT, etc.)

Now, you create a mainform/subform where the main form is bound to tblEmployees and the subform to tblHours linked on EmployeeID. You would then add an UNBOUND control for WeekOf and set the WeekOf control on the Subform to default to that value so you only have to enter it once. Since Dept is an attribute of the epmployee that would be stored as part of the employee record.

Some other pointers: You shouldn't use spaces in object names. This can haunt you later. Also its not recommend to use lookup fields on the table level. They should be done only on the form level. Finally, we don't store calculated values like a total, those can be calculated on the fly as needed.

Scott, sorry for the lack of knowledge on these short terms. But could you please tell me what you mean by:
- tlhHoursType: Is this a new table name that I need to set?
- FK (what this mean)
Do I have to create two fields in HourType (one for Regular and another for OT)

Thank you

Sergio

Sergio,

I had a look at your db - and codewise, you were close. And Scott is right, you need to look at normalization before continuing. You have one table - essentially a flat file. You need to add several more tables. For each of your drop-down lists that you have in your table, I would suggest another table containing those items, and then a FK (number), pointing to that table.

Once you have that completed, the rest of your application will become simple.

Work on properly structuring your db first, then functionality.

To get the code to work which you currently have, you just need to rename 'check36' to 'chkDept'. Ensure that you dont accidently name the label 'chkdept'.

Again, just to hammer down the point, normalize your db - it will save you time and effort in the future.

Hope this helps,

Adam

EDIT : just saw your post.



tluHoursType - This is the name of another table. I'm assuming that the prefix is either "tlu" for Table LookUp, or a typo ("tbl").

FK - is a foreign key. It is the numeric value of the index of the table it points to. This way, you can use joins to display the text value if required, and store only the bare essiential number in your main table.

You only create one field in HourType, but as many ENTRIES in that table as you need (one for reg, one for OT right now). Then, in your main table, in your HourTypeID field, you would place the number which represents the index in tluHoursType.

Adam

Edited by: adamsherring on Tue Jan 16 14:32:29 EST 2007.

I think I got lost. Can you take a look in the attachment and let me know where I am making the mistake. The form was not negerated yet. I did got an error and deleted

Sergio

Hi Sergio,
Adam has answered your questions to me (thanks Adam). Just to add one point. There is a concept called entity/attributes, that is important in database design. This concept means that you design a table to represent an entity and the records in that table display the attributes of the entity You don't use fields but records to display attributes. To use your app Hours worked is an entity. The attributes of that entity are when they were worked, who worked them, the type and the amount. So those become your fields. You then represent the attributes with a record that shows the date, the employee, the type and the amount.

Ok, I would start with the tables then.

I've gone in and redone your structure, and given you a little form. Have a look at the structure, form, and code, and ask questions if you dont understand some of it.

For the hours table - its a transactional table, ie, one that records data for as it occurs, to be used in calculations later on. I also chose "week of" and a date datatype. You could easily do this by day if you wanted, or simply a Week Number, or month name, or however it is to be structured logically to work for you.

Adam

Scott - always happy to give anwsers (no clowning aroud here )

Edited by: adamsherring on Tue Jan 16 15:50:02 EST 2007.