Solved Percentages combobox (1 Viewer)

[Minty]

The use of currency data type removes any rounding error you'll get if you use double or single.
Currency is fixed at 4 decimal places and is actually stored as an integer internally and shifted to display the decimals.

This removes the inherent inaccuracy of floating binary point numbers.

 

[gemma-the-husky]

Thank you for the code provided. I see what you mean. Still though, I can't see the purpose of using currency as Format Type of the field. I think that adding a new value leads to error because of the Field size property (single, double, long integer etc) and not the format. On the other hand I feel I can't ask from the ignorant user to imagine % and furthermore can't force him to type it. What if he mistypes or forgets it.

As @Minty just mentioned, using currency with values that don't have unlimited dps ensures rigour. A computer cannot store 0.1 precisely for instance, so if you enter 10.1% as a single or double, the value stored won't be exactly 10.1%, although it may not matter with most calculations. If you use a currency data type, the value will be exactly 10.1%, as it's precise to 4dps.

When you use a form, there's lots you can do with a small amount of defensive code to improve the user experience.

You can use validation code to check that a percentage value is in the ranges you expect. So if the value has to be between say 0 and 50, you can enforce that, or manipulate the result to comply. You can add code to manipulate the percentage value and ask the user to confirm the entry. You could store the user name and date of the entry to review subsequently.

So if he enters just 12 as the value you can ask him to confirm he means 12.00% and store 12 as the value
if he enters 6.5% you can ask him to confirm he means 6.50% and store 6.5 as the value


this sort of thing using a field newvalue, just defined as general number (aircode)

if newvalue<0 then
  msgbox "Sorry: you cannot use negative values"
  exit sub
else
  if newvalue>1 then
      checkvalue = newvalue/100
  else
     checkvalue = newvalue
  end if
end if

if msgbox "Please confirm you want to add " & format(checkvalue,"0.00%"), vbyesno+vbdefaultbutton2, "Confirm") = vbno then
  exit sub
else
  'save the new value
end if

 

[AlexN]

@Minty , @gemma-the-husky , @ebs17
you guys are gurus

Still I get a type mismatch error when I'm trying to add a new value via the combo's NotInList event, wtf am I doing wrong

 

[gemma-the-husky]

@Minty , @gemma-the-husky , @ebs17
you guys are gurus

Still I get a type mismatch error when I'm trying to add a new value via the combo's NotInList event, wtf am I doing wrong

Can you post the code you use to manage the new value.
You probably need to do a conversion of the entered value, using ccurr, csgl, or cdbl according to the data type in the percentage table.

you can probably error trap the insert action and display a meaningful message.
It might need on error, but it might need you to use the form error event to trap and recover from the action failure.

 

[ebs17]

Approximately:

Private Sub cboField_NotInList(NewData As String, Response As Integer)
    Response = acDataErrAdded

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT FieldName FROM TableX WHERE False", dbOpenDynaset)
    rs.AddNew
    rs!FieldName = NewData
    rs.Update
    rs.Close: Set rs = Nothing
End Sub

type mismatch

When entering a number in a number column?

 

[gemma-the-husky]

rs!FieldName = NewData

Yes, that will error, because new data is string, and fieldname is numeric.

 

[AlexN]

Can you post the code you use to manage the new value.
You probably need to do a conversion of the entered value, using ccurr, csgl, or cdbl according to the data type in the percentage table.

you can probably error trap the insert action and display a meaningful message.
It might need on error, but it might need you to use the form error event to trap and recover from the action failure.

Here's a sample. Feel free to punch me at the face

 

[arnelgp]

maybe this new code can give you what you want.

 

[AlexN]

maybe this new code can give you what you want.

Million thanks for taking the time to offer a solution.
Still, when I input 30, table holds a value of 30 but combo selection becomes 3000%.
And, there's always the unwanted extra button (that leads to an unwanted pop up form).
Please consider that this thing is a part of a much larger project with multiple forms of hundreds of controls, so, I want this small part to use as much smaller user interaction (and fewer lines of code) as possible.

 

[AlexN]

Note to everyone :
I'm not a developer (how could I be with this skill level). I'm a simple user and developing access databases is my hobby, a need to learn something.
So, I think as a user, and I'm terrified by the idea that a user should make assumptions, divisions by 100, or search where the % symbol is on the keyboard just to type a vat factor for a single product. We shouldn't take for granted all users are familiar with this stuff.

 

[gemma-the-husky]

This is the way I would do probably do it., using your database.

The VAT table now stores an autonumber ID field, and the rate. Just the rate value, not the percentage.
The product table stores the ID field, not the VAT rate. You would join the two tables to derive the VAT Rate.
If you change the selected value (ie to pick a different tax rate), the stored record ID changes in the product table.

I also changed the code in the not in list event.

Note that in the combo box, the values appear as left justified, as strings not numbers.

I would probably add a description field to indicate what the VAT rate represents, eg Standard Rate, Special Rate, Zero Rate, Exempt etc, or even a non UK rate.

Note that by storing the VAT Record ID, you might need to consider what happens if the rate changes. So you might need to include a mechanism to derive the old rate at the relevant date. eg, VAT in the UK changes from 20% to 22%. When you make a sale you might store both the VAT code AND the rate applicable at the time, to avoid having to look up old values. As far as the product goes, all you probably need is the type of VAT, and not the actual rate.

But you always have this problem. If the rate of VAT in the UK went up from 20% to 22%, everywhere you stored 20% in old data is OK. Everywhere you store it for new data needs to change. So you would have to change every VAT value in the product table. If you store a reference code, then you only have to change the VAT value once, in the VAT table, but you have to manage the rate of VAT according to the tax date applicable. (and according to sometimes arcane HMCR rules regarding the actual tax point date)

if you store 20 and 22 in the database then you have to divide the value by 100 to work out VAT
if you store .20 and .22 then you would not need to divide the value. It's a matter of taste really. Whatever suits your business.

I hope this helps.

 

[AlexN]

This is the way I would do probably do it., using your database.

The VAT table now stores an autonumber ID field, and the rate. Just the rate value, not the percentage.
The product table stores the ID field, not the VAT rate. You would join the two tables to derive the VAT Rate.
If you change the selected value (ie to pick a different tax rate), the stored record ID changes in the product table.

I also changed the code in the not in list event.

Note that in the combo box, the values appear as left justified, as strings not numbers.

I would probably add a description field to indicate what the VAT rate represents, eg Standard Rate, Special Rate, Zero Rate, Exempt etc, or even a non UK rate.

Note that by storing the VAT Record ID, you might need to consider what happens if the rate changes. So you might need to include a mechanism to derive the old rate at the relevant date. eg, VAT in the UK changes from 20% to 22%. When you make a sale you might store both the VAT code AND the rate applicable at the time, to avoid having to look up old values. As far as the product goes, all you probably need is the type of VAT, and not the actual rate.

But you always have this problem. If the rate of VAT in the UK went up from 20% to 22%, everywhere you stored 20% in old data is OK. Everywhere you store it for new data needs to change. So you would have to change every VAT value in the product table. If you store a reference code, then you only have to change the VAT value once, in the VAT table, but you have to manage the rate of VAT according to the tax date applicable. (and according to sometimes arcane HMCR rules regarding the actual tax point date)

if you store 20 and 22 in the database then you have to divide the value by 100 to work out VAT
if you store .20 and .22 then you would not need to divide the value. It's a matter of taste really. Whatever suits your business.

I hope this helps.

Thank you so much! Seems you came up with a solution!

Note that by storing the VAT Record ID, you might need to consider what happens if the rate changes. So you might need to include a mechanism to derive the old rate at the relevant date. eg, VAT in the UK changes from 20% to 22%. When you make a sale you might store both the VAT code AND the rate applicable at the time, to avoid having to look up old values. As far as the product goes, all you probably need is the type of VAT, and not the actual rate.

Actual products table has a DateFrom and a DateTo fields that keep track of the time period VAt has that value. So if VAT for that product changes, a new record comes along with new value for a new time period. Meanwhile, when it comes to invoicing, VAT factor should be a DLook Up with product and Date criteria. (at least that's how I' m having it considered)


Thanks a lot again.

 

[AlexN]

@gemma-the-husky , @arnelgp , @ebs17 , @Minty and all the guys that have helped me so many times before:
you guys are gurus. Thank you so much.
When I first came here my level of knowledge was zero. You and your answers made it 10. I know it's a long way to 1M but I feel I'm on the right track.

Millions of thanks