Form with Spreadsheetlike Performance (1 Viewer)

[AJR]

Hi

Thanks for taking your time with my problem, even if you just read it and move on.

I'm pretty new to Databases and Access and am just finishing off my first real Database.

Sorry for using the S word. There are many reasons that this project should be done by a database, I'm finding one instance, however, where I know it would be easy to do on a spreadsheet, so that's how I chose the Title.

The database will allow the user to develop an Investment Asset Allocation Target and then compare their actual allocation to that target.

So, a user could define Bond Asset Categories (e.g., AAA, Corporate, Junk) and then define what % of all their bonds would be allocated to each category (e.g., AAA -70%, Corporate 20%, Junk 10%).

When doing this it is advantageous to be able to see all of the Categories and what each allocation is, at one time (One page of a form). A datasheet form actually works fine (and I have all the tables and queries built to do that) but it's a little ugly to a user with no Access experience.

Is there some way I can produce a form that operates like a Datasheet form but looks like a Tabular Form?

Another aspect, which I will post on it's own thread when I get the form issue nailed down, I will include here as it may be pertinent to the above question, is:

It is essential that the fields finish up summing to 100%, yet inevitable that they will not, during manipulation (I reduce one category by 10% and increase another by 10% --The categories finish up totaling 100% but total 90% for the time between my first change and my second.) I need a way to alert the user when the fields do not total 100%. On a spreadsheet I could simply provide a total, or I could use conditional formatting to show all values in red text if the Sum of all values <>100%

Any Ideas on how to accomplish both?


Form (which the user would use to input Target % to a table field) would look something like this:

-----------------------------------------------------------------------------------
Bond Risk Allocation Targets

Category Target %

AAA---------------- 70%
Corporate ---------- 20%
Junk----------------10%
Total -------------% 100%

----------------------------------------------------------------------------------

Thanks again for your time, any help would be greatly appreciated

AJR

 

[Isskint]

Welcome AJR

Wow, thats a lot of info:banghead:, but the more the better i guess.

From what i can understand, you simply want a way to alert the user that a change has resulted in a not 100% balance? If so just do a check in the Before Update event of the field/control that stores this value. In the Before Update event you would use DSUM() to get the current total, then if not 100% you could;

• Option 1
  • Alert the user
  • Offer options to adjust another value to get the 100%
  • reject the change (set CANCEL = TRUE)
• Option 2
  • Have a label in the form header that can be made visible in BOLD RED to alert the user
• Option 3
  • update a textbox (hidden or displayed in form header) showing the balance. Set conditional formats on controls to set red if this textbox is not = 1

You could also run a check before closing the form and not allow the form to close if balance is not 100%

 

[Galaxiom]

Is there some way I can produce a form that operates like a Datasheet form but looks like a Tabular Form?

Maybe you want a Continuous Forms simulation of a Datasheet?

Cut the labels and Paste them into the header.

Put all the other controls into a row in the Details Section and move the bottom of Details right up against the bottom of the controls.

BTW If all the controls are moving together, select them and remove their Layout (right click).

 

[AJR]

Isskint -Sorry to be so verbose. Seems to be a personality trait -you should see some of my complicated questions.
In any case you have supplied a lot of great info. I just learned how to use After Update when creating Cascading Combo Boxes and After Change (Haven't caught the difference there yet) for automatically populating fields. So I understand what you are suggesting and will learn some more applying it --Thanks Again

 

[AJR]

Galaxiom -Funny you should mention Continuous Form. I had a sort of feeling that might be where the solution lay. The cut and paste idea is brilliant. Probably why I didn't think of it.

Thanks for the help

 

[Galaxiom]

AfterUpdate fires after the user leaves the control (or causes the record to save in the case of the Form's event.)

OnChange fires every time the content of the control is changed. So this is used for search as you type and similar functionality.

 

[AJR]

Duh.

Seems obvious when spelled out for you.

Thanks again.

AJR