Normalization/Recordsets on Continuous Sub-Form

[wrldruler]

Hi All,

I am building a Workload Forecasting system. It is similar to a
monthly Time Card except, instead of caring about what they did last
month, I care about what they think they'll be doing within the next
12 rolling months.

It is currently semi-working with a Bound form, but I need to
normalize it and save/read with recordsets. You guys helped me with
normalizing / Recordsets before, but that was on a sub-form that was a
Single Form. I just queried the Recordset, and iterated through and
said this piece of data goes into this box.

But now I am using a sub-form that is Continuous. I can't figure out
how I will get the data into the correct boxes when I am unable to
reference the box by a unique name -- each textbox name will be
repeated for each record in a Continuous form.

Here is what I have:

Main Single Form: Employee ID

Continuous Sub-Form: Billable Time
Continuous Sub-Form: Admin Time

Underneath each, I have a "Task Name" dropdown and 12 textboxes for
their hours.

So my form looks like:

Employee 123
Admin Time Jan Feb Mar ...
*Training 8 0 16
*Vacation 0 8 0

I want my data to look like

* Employee ID
* Task Name
* Task Type (Billable vs Admin)
* Task Start Month
* Task End Month
* Task Hours

I know my main Single form will be filtered by Employee ID. I figured
I will set the RecordSource of each Sub-Form. For ADMIN time I would
say give me all task names & hours for Employee 123 which is of type
"Admin".

But I can't figure out how to get 0 hours to go into the February
Training textbox, and 8 hours to go into the February Vacation
textbox. It's different records, but the textbox will have the same
name for both.

Can you point me in the right direction?

Thanks,

Chris

 

[Mark A. Sam]

I sounds like you need to add a Year field into your main table. So your
form would look like this:

Employee 123
Year 2007
Admin Time Jan Feb Mar ...
*Training 8 0 16
*Vacation 0 8 0

God Bless,

Mark A. Sam

 

[wrldruler]

I made some progress today, but still have problems.

First, I populated the Control Source of each textbox and said
something like "If Task Date falls in January, put the hours in the
January textbox". First I tried an IIF statement, which worked, and I
also tried this DLookup:

=DLookUp("Forecast_Hours","tbl_Forecasts_Weekly_Details_Normalized","#1/1/07#

= [Forecast_Start_Date] and #1/1/07# <= [Forecast_End_Date] and

[Forecast_Details_ID] =
Text_Forecast_Details_ID")

Worked great, except then the user could not edit the data because it
was bound to the expression.

So now I understand why I would need to bind to a temporary table.

So I decide to try and use VBA to write to each texbox. The problem is
that I can't seem to iterate through the records on the Continuous sub-
form -- my DLookup only works with the selected record.

I tried using the RecordSetClone same same result -- only populates
the selected record.

Any suggestion

Thanks

 

[wrldruler]

I tried using the RecordSetClone same same result -- only populates

the selected record.

Let me clarify -- I have been able to successfully iterate through the
RecordSetClone, proven by Msgbox() statements. I see it moving
through.

But it only writes to the currently selected record. So I see it
writing a bunch of values, but it is stuck on the same record.

Thanks