Add Multiple Records To One Table Simultaneously With One Form

[Tlo]

Hello,



I'm working on a database to track job flow and profitability. I need to be able to store actual hours that are entered daily. I have a table, tlbActualHours, that contains the following fields: strDept, strManager, strForeman, strProperty, dteStartTime, dteStopTime. I need to have a form to input records into tblActualHours; currently I have a continuous form with all the fields listed above. The problem I have is that strDept, strManager, and strForeman are being repeated multiple times on one form. For example, if a foreman goes to four jobs on a single day, then the continuous form will have strDept, strManager, and strForeman repeated four different times, but strProperty, dteStartTime and dteStopTime are different for each of the four records. The repetition from a data entry standpoint is what I'm trying to avoid.

Is there a way, on one form, to set strDept, strManager, and strForeman one time, and then enter the strProperty, dteStartTime and dteStopTime for each individual stop?

I don't have knowledge of code so I'd like to stay away from that if possible.

Any help would be greatly appreciated.

[orange]

You need a plan.
What tables do you need?
What fields are involved?
How will you identify relationships?
How about some test data?
.....

[Tlo]

My apologies, I'm not sure I explained this well.
I have multiple tables and relationships already set up, but am currently storing the "Actual Hours" in one table, tblActualHours.
tblActualHours has the following fields, and these fields are the only fields involved to this issue: strDept, strManager, strForeman, strProperty, dteStartTime, dteStopTime.
Each manger has a relationship to a property (one to many).
Each manager has a relationship to a foreman (one to many).
Each dept has a relationship to a manager (one to many).

So the relationships, fields and tables are there. I've set up queries and reports that work and are in line with what I need. The continuous form works just fine in terms of adding data to tblActualHours. My issue is how to get around entering three of the fields multiple times on one form.

[orange]

What, if any, is the relationship from foreman to Property and/or department?

If you have to enter the same values multiple times, you may need to consider
a) entering data once to a control(s) on an open, but hidden form and then updating your other fields with the value(s) currently on that hidden form; or
b)use a TempVar and put the first entry there, then use the tempvar's value into those fields that need to be updated.

And there are probably other ways to accomplish that.

[Tlo]

No relationship (intentionally) from foreman to department, because any foreman can do work associated to any department. The relationship from foreman to property could work, but that changes often so I'd like to avoid creating another task to keep up with if possible.

I'm going to work with using TempVar on a continuous form. I've never used them before so it'll be some trial and error on how to set them up.

Thanks for the help!

[Tlo]

OK. I've got a form (frmTimeSheetHeader) with combo boxes for each of the following fields - department, manager, foreman. I added a button on the form with an "on click" macro that assigns each value in the combo box to a tempvar, and then opens another form (frmTimeSheet) where department, manager, foreman, property, start time and stop time fields are listed. In frmTimeSheet the fields department, manager, and foreman are set to TempVar previously assigned on frmTimeSheetHeader. So manager on the form frmTimeSheet =[TempVar]![Manager]. Good news is that they are populated with the correct values on the form based on what the TempVar value currently is saved as.

But I now have two problems:
- the values assigned in frmTimeSheet for the fields department, manager and foreman are not being stored in the table
- two new records are created when I go through this process. One when I click the button to open frmTimeSheet and then another when I close frmTimeSheet.
- each of the records created only have half the fields populated. The first record has department, manager, and foreman populated. The second record has property, start time, and stop time populated.

What I think is happening is that the variables that are shown on the form are not values that can be stored in a table. Is this correct?

I may have to just put up with the redundant data entry...

[orange]

My guess is that you are not updating the record in the table with the value on the form.
If the form control gets valued correctly; then I'd use some sql to append the record with the field values as appropriate.

(untested but that's the approach)