Access Database Form - Multi-Option Input Section

Hi experts,

I'm looking for advice on how to handle a multi-option input scenario in an Invoice form.
The attached database shows a simplified version of what I'm after.
dbTestTab.accdb

The db has a table, T_Invoice:
T_Invoice - table for Invoice
And a form built off it, F_Invoice:
F_Invoice.  Form for Invoice
The sample form above is intended to provide a choice of fee payments for the Invoice.
For example:
If the user chooses Base, then the entered Base Rate is applied to the fee's base amount.
If the user chooses Multi-Fee, then the entered Base Rate and entered Additional Rate are applied to the fee's base amount and additional amount.

However, I'm using a tab control and have encountered some issues with that approach.
Hence, I'm wondering if anyone has any suggestions for best practice solutions to cover the follow issues.

Potentially Dodgy Practice #1

The multi-option inputs are built from a tab control.
I'm not sure its best practice, because:
(i)   The tab control seems to act like a subform.
      Eg. if you choose multi-fee, set the two rates, then hit tab, the form gets reset.
      This is potentially a deal breaker for this approach.
(ii)  I have to hide the tab buttons (ie. set them to 'None')
(iii) Its fiddly to try to align the input boxes and lables
(iv) It seems impossible to left-align the labels with the Input Type label above the tab form
(v)  I have to use VBA to show the correct input boxes

I initially wondered instead about using one or more subforms to handle these options.
But the tab control approach seemed more elegant.
However, due to these problems perhaps its not suitable after all.

Potentially Dodgy Practice #2

There are actually several different types of rates, but I have "shoehorned" them into three fields.
1) Rate - this could the Base Rate, or the Compensation Rate, or the Commission Rate.
2) RateAdditional  - this is a single field with only one purpose (ie. the additional rate if the invoice Type is a Multi-Fee)
3) Fixed Fee - this is a single field with only one purpose (ie. the fixed fee if the invoice Type is a Fixed Fee)

The resultant calculations would be built off the InvoiceType field, to determine how the Rate is applied in the calculations.
This was a design choice to keep it 'simple'.
If anyone has any advice about my approach here that would be welcome.


The first issue above is the main problem, but very happy to hear of improved solutions for either of those issues.
Many thanks in advance,

Chris
ChrisSelf EmployedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Eg. if you choose multi-fee, set the two rates, then hit tab, the form gets reset.
You've got that form set to DataEntry = Yes, which means you're always adding a new record to the table. This would "reset" the form. You should set DataEntry = No, which is the more standard way of doing this.

I personally don't like "magic" fields (i.e. fields that appear and disappear), and most users don't like them either (they tend to think they've done something wrong when the UI changes like that). If you have only 3 fields, just show all of those fields, and enable/disable the fields required based on the user selection. I don't quite follow the logic, but as an example, if the user picks "Fixed Fee", you might enable the first control and disable the other 2.
0
PatHartmanCommented:
Tab controls are not "like" subforms.  They have no RecordSource so every bound control on EVERY tab of the tab control is bound to the form's RecordSource.  Frequently though, people place subforms on the tab pages because that gives a condensed view of a large amount of data.  It is very rare in a properly normalized database to have too many controls to fit on a single form.

I also don't follow the logic of what you are trying to do.
0
ChrisSelf EmployedAuthor Commented:
Hi Scott and Pat,

Many thanks for looking at this issue.
I've digested your comments, and have atached a new demo db that hopefully provides a clearer picture.
dbTestTab.accdb

The three additional items are:

Table: T_Invoice_NoTab
This table breaks out the rates into the original fields (Base Rate, Additional Rate, Commission Rate, Compensation Rate, and Fixed Fee)
(The previous db model in my initial question had condensed those fields into three - Rate, RateAdditional, FixedFee)

T_Invoice_NoTab - the new table with all fee fields
Form: F_Invoice_NoTab
This shows all the invoice type fee fields.
It uses VBA to enable the correct fields as require (as per Scott's suggestion)
 
F_Invoice_NoTab - the new form with no tab and no magic controls
Query: Q_Invoice_NoTab
This shows how the invoice type fee fields would be used to calculate the Invoice Fee.
This is simply included for explanatory value to help understand my purpose.

Q_Invoice_NoTab - a query of the new table, showing the resultant calculations
Due to the Access scrunching of SQL, here's a tidy view of the SQL code behind that query.

SELECT ID
	,InvoiceType
	,BaseAmount
	,AdditionalAmount
	,Ccur(Switch(
                InvoiceType = "Base Rate", BaseAmount * BaseRate
                ,InvoiceType = "Multi Fee", (BaseAmount * BaseRate) + (AdditionalAmount * AdditionalRate)
                ,InvoiceType = "Total Compensation", (BaseAmount + AdditionalAmount)* CompensationRate
                ,InvoiceType = "Commission Trailer", (BaseAmount + AdditionalAmount*0.5)* CommissionRate
                ,InvoiceType = "Fixed Fee", FixedFee
                )) AS InvoiceFee
FROM T_Invoice_NoTab;

Open in new window


@Scott
1) Magic Controls.
A useful point of view, thanks.
I used those magic controls to (i) make it simpler for the user, and (ii) to reduce the 'real estate' required in my actual Invoice form.
Perhaps the alternative shown in the new demo db is the better way to go.

2) Data Entry = Yes
I set this property to yes, because each form is for a new Invoice to be raised (rather than looking back through old invoices).
What you seem to be suggesting is to set the property to No, but then have the form open up as a new entry.
In which case, I imagine I'd have to use some VBA in, say, the form open event.

All that said, must confess my surprise that tabbing out of the text boxes on the Tab control forces a new entry.

@Pat
Thanks for the explanation on Tab controls and subforms - I've a better understanding now.
Hopefuly the logic is more clearly discernible in the new attached demo db.


Many thanks again,
Chris
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
All that said, must confess my surprise that tabbing out of the text boxes on the Tab control forces a new entry.
The tab control has nothing to do with this.  If you want the form to stay on the current record when you tab out of the last control rather than moving to a new record, you need to set the Cycle property of the form on the Property Sheet's Other tab to Current Record.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChrisSelf EmployedAuthor Commented:
Thanks Pat, I wasn't aware of the Cycle property having that effect
(one of many things yet to pick up with Access)

On my real application, the tab control lies on a subform.
And yes, it works now when I set the subform Cycle property to Current Record.

Many thanks for pointing that out.
0
PatHartmanCommented:
You're welcome.  Don't forget to close the question.

In most cases, you want the Cycle property to be "Current Record" when the form is showing a single record and "All records" when the form is continuous or a DS.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.