Basic VBA & entering in a table

[Tomrobo]

Hi,

I'm a real beginner with VBA so please accept my apologies.

I'm having trouble with my code In a form.

I've used VBA to calculate a number stored in the variable "Discount".

I would like if possible, when the Discount is calculated to save the number in a table called "Customers" under the field "Discount"

I would really appreciate some guidance.

 

[John Big Booty]

It is generally accepted (with a few very limited exceptions) that calculated values are not stored in Access.

In excel this is quite an accepted practice, as if there is a change in one of the underlying values then the cell simply recalculates. This does not automatically happen in Access however, unless you force it to. But then why store a value that is really only need for display purposes? Given that all the underlying values are already stored, in your tables, it is a simple matter to calculate the result in a form or report as it is required. This also has the added advantage that it will save space (particularly if you have large amounts of data).

If you are doing the calculation now at the time of data entry you can also do that same calculation at any other time you wish to display the result.

 

[Tomrobo]

It is generally accepted (with a few very limited exceptions) that calculated values are not stored in Access.

In excel this is quite an accepted practice, as if there is a change in one of the underlying values then the cell simply recalculates. This does not automatically happen in Access however, unless you force it to. But then why store a value that is really only need for display purposes? Given that all the underlying values are already stored, in your tables, it is a simple matter to calculate the result in a form or report as it is required. This also has the added advantage that it will save space (particularly if you have large amounts of data).

If you are doing the calculation now at the time of data entry you can also do that same calculation at any other time you wish to display the result.

Hi, thanks for the very fast reply and information.

Just to clear things up, I should of given this info before hand. I have 3 option groups. Salary, EmploymentType and PurchaseValue. Each one delivers a value 1 or 2 depending on which of the two options is selected.

My code looks like this.......

Dim Total As String
Dim Discount As Single

Total = [EmploymentType] & [Salary] & [PurchaseValue]

Select Case Total

Case "122"
Discount = 0.1
Case "121"
Dicount = 0.05
Case "221"
Discount = 0.1
Case "211"
Discount = 0.05
Case "222"
Discount = 0.2
Case "221"
Discount = 0.15
End Select

MsgBox Discount

---Code here that puts the discount value in the table 'customers' under field name 'Discount' ----

When the user selects an option e.g. option 1 for salary the Option group records a 1 in the field of the table Customers.

I would also like the Discount that has been calculated above to be stored in the same record under the fieldname "Discount"

Hope that makes sense.

Thanks again in anticipation.

 

[John Big Booty]

Are your fields [EmploymentType], [Salary] and [PurchaseValue] being stored in your table?

If the answer to that question is yes then there is no need to store the discount value as you can calculate it whenever you need it by using the code you already have.

Now if you wish to plough ahead down your current path, then assuming the field you wish to store this data in appears on your form use;

Dim Total As String
Dim Discount As Single

Total = [EmploymentType] & [Salary] & [PurchaseValue]

Select Case Total

Case "122"
Discount = 0.1
Case "121"
Dicount = 0.05
Case "221"
Discount = 0.1
Case "211"
Discount = 0.05
Case "222"
Discount = 0.2
Case "221"
Discount = 0.15
End Select

MsgBox Discount 

[B][COLOR="Magenta"]Me.FieldName = Discount[/COLOR][/B]

 

[Tomrobo]

Thanks. I was going down the route of putting the data in the table.

That worked a treat using:

Me.[Discount] = Discount

Many thanks you really helped!

Are your fields [EmploymentType], [Salary] and [PurchaseValue] being stored in your table?

If the answer to that question is yes then there is no need to store the discount value as you can calculate it whenever you need it by using the code you already have.

Now if you wish to plough ahead down your current path, then assuming the field you wish to store this data in appears on your form use;

Dim Total As String
Dim Discount As Single

Total = [EmploymentType] & [Salary] & [PurchaseValue]

Select Case Total

Case "122"
Discount = 0.1
Case "121"
Dicount = 0.05
Case "221"
Discount = 0.1
Case "211"
Discount = 0.05
Case "222"
Discount = 0.2
Case "221"
Discount = 0.15
End Select

MsgBox Discount 

[B][COLOR="Magenta"]Me.FieldName = Discount[/COLOR][/B]