Can the form be created dynamically?? 1

[rushitshah]

Hello...

I am working on a applcation that asks the user to enter the data in to related tables from forms.

Consider, a company that works on testing of a product and has some parameters of that product. They are doing forecase for those parameters.

In April 2004, they have data for the forecast of March 2005. But it is monthly basis data.

In Dec 2004, they have forecast of March 2005, but now at a more accurate level, at weekly basis.

In Jan 2005, they have data for the forecast of March 2005, but at a very precise level, like daily basis.

So the form should display different fields like, mothly basis if the forecast time is too long.
As we come close it should display the fields for weekly entry...and as we are very close it should disply fields of daily entry...

I need help on this kind of application...

Rushit..

 

[GingerR]

First you'd have to define what "forecast time is too long", "as we come close", and "as we are very close".
Also, how are your tables structured? when you say that they have data at a daily level, do you really mean that they have data for each day of the year? Think about what is the "standard plan" for an item? Would it be something like


T-12 months
T-6 months
T-5 months
T-4 months
T-3 months
T-2 months
T-7 weeks
T-6 weeks
T-5 weeks
T-4 weeks
T-29 days
T-28 days
.
.
.
T-2 days
T-1 days

Maybe you have to set up your table like that, so it will work no matter what your Rollout Date is, know what I mean?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Thanks a lot for replying Ginger.

It is like for 6 months of forecasting I need to ask the user to enter the weekly data and roll up that data to monthly data and quarterly data automatically by updating the table fields.

If the time for forecasting in more than 6 months to 2 years, I need to ask the user to enter the monthly data and to roll up that data in quarterly basis and at the same time drill down that data on weekly basis automatically.

If the time for forecasting is more than 2 years then I need to ask the user to enter the quarterly data and drill down that data automatically to monthly and weekly data.

As you can see the time will go in the application all the time window will shift. Does that make sense?

Please help if you have any idea.
Thanks,
Rushit..

 

[GingerR]

So you have now pretty much defined what you need. How far have you gotten so far? What are you envisioning for a user interface? Someone "initiates" a project (title, customer, etc) and puts in a "DUE DATE", and hits a button which then fills a table with generic backoffs (sort of like what I listed before) and blank fields to input the data into? Then depending on the current date vs. the DUE DATE, different screens are visible for the user to enter data for the appropriate date chunks?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Ginger,

Yes, It is like the whole application is dynamic. I will say it as a FORECAST DATE. Compare current date with forecast date. Decide which timeframe are you in?

I mean by time frame is..

if 6 months then -- I need to ask the user to enter the weekly data and roll up that data to monthly data and quarterly data automatically by updating the table fields. So one form should pop up for data entry.

If the time for forecasting in more than 6 months to 2 years, I need to ask the user to enter the monthly data and to roll up that data in quarterly basis and at the same time drill down that data on weekly basis automatically. So I think other form should pop up.

If the time for forecasting is more than 2 years then I need to ask the user to enter the quarterly data and drill down that data automatically to monthly and weekly data. And here also I think different form should pop up.

And how to automatically update other fields of DB with some fields in DB?

Rush..

 

[GingerR]

Yes I understand. How far have you gotten? Do you have a specific question? I've given you some ideas, maybe you can goof around with table structure and a sample db to see how you want to format the interface. The best thing is to design a table like how you think it will be, put in some data, and build a form and start playing with it to see if it's how a user would want it.

As for opening different forms based on different values, check out the DateDiff function.

As for "updating other fields of the DB with some fields of the DB": not advised. No sense in storing the same data in multiple places as that could create problems. If you are talking about storing sums in various places, I wouldn't do it. You'd just display the sums instead, on reports and such. You wouldn't actually store the sums in a table.

Does every PRODUCT have a certain plan life? like does ProductA need estimates for a year prior to it's FINISH DATE (or whatever) until 6 months after it's FINISH DATE? Any kind of standard plan like that?

what do you mean by having a quarterly forecast "drill down data on a weekly forecast automatically"? how? peanut-butter spread? how would the computer know how to divvy up a quarterly forecast into weeks?

Try messing around with what you think you'd want the user to see, and see how far you get. How will you structure the table? Just start doing it and see what problems you run into.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Thanks Ginger..

By updating other fields of DB by some fields of DB I mean is...

Consider..I have a product, television. I want to forecast its price monthwise for some years...

so is it possible to create the fields dynamically when the user enters values from the form.

The form will have Month, Year and Price.

The user will enter January, 2005 and 200

So I want to create a field in table Jan'05 that will store the price of that month.

If the user enters February, 2007 and 150 I need the field in the table named Feb'07 that stores 150 as price.

Is this possible? to give the name of the table not from the beginning but while the user enters data from the form to make them dynamically??

For drilling down and rolling up the data there are rules.

like if for one parameter my monthly values are 1.2,1.3,1.4
then the quarter value will be either average or worst of them depending on the parameter.

if quarterly value is 1 then monthly values will be 1 too.

Is this possible?

Thanks again,
Rushit..

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GingerR]

It's doable but I would say pretty complicated since I've been doing this for several years and think it'd take me some time to figure it out. You wouldn't name the fields with each date. You'd add the data as rows, some how creating the date/year/month/quarter blank rows for each "project" based on the date span a user inputs when initiating a project. It's a complex/advanced project in my opinion.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Will this kind of logic work??

If IsNull(Me.Text134) = False Or Me.Text134 <> "" Then
rs!January2005 = Me.Text134
rs!February2005 = Me.Text134
rs!March2005 = Me.Text134
Else
rs!January2005 = Me.Text10
rs!February2005 = Me.Text24
rs!March2005 = Me.Text26
End If

Text134 is my quarterly data, so if that field has something in it, copy it simply to monthly data fields or if that quarterly fields is empty then copy the individual monthly data from text boxes...

Rush..

 

[GingerR]

It'll work if that's what you want. Again, this is very complex and would take a lot of thought. Also, as we've mentioned before, it's best to rename your control to sensible names other than "Text134", etc.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[rushitshah]

Ginger..
Thanks..

For Text134..I tried a lot to change it...

Here's what I am doing..

On the form I put textbox from tools menu.

It puts two boxes on the form, one has unbound written in it. and the other has text134. Then I click on text134 box and rename it to Quarter1. and then save it. But still it shows text134 only in the properties.

Rush..

 

[GingerR]

The LABEL and the TEXT BOX are two different things. You have to change the name of the TEXT BOX in addition to changing the caption of the LABEL. View the properties of the TEXT BOX, and change the name to something else.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[rushitshah]

yes.. I just got it and did that..change the names of text boxes and now seems ok.

But is there any way to change these names of text boxes in VBA code? Or I have to manually look for each text box and change its name??

Rush..

 

[GingerR]

That's a diff question so you might want to ask it in a diff post. If you re-name in the VBA code, you'd still have to tell it what to name them. The habit to get into is to just rename them as you make them.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[MichaelRed]

There are threads in these fora (Tek-Tips) with working examples of creating forms based on recordset structures. The examples are somewhat simplistic, as they only generate the continuious form view of the recordset with the associated lables in the form header. Doing more elaborate ones might take some thought, but the ones posted work, using the basic functions necessary to create the form, place the controls on the form and place labels in a reasonable location with respect to the controls.

To find some of the examples, use the search capability with teh keyword(s) CreateForm or Create Report or do your own homework from the ubiquitous ]b]{F1}[/b].



MichaelRed