Conditional VBA code

[PRPIETRO]

I have a column called [EmissionStd] with Options: A, B, C.
A = 0.65
B= 0.35
C = 0.30

Based on selection of A, B or C I would like Column [TwoDayStandard] to automatically output the numerical value based on the VBA code below.

How do I call out the function in my table?


My code below compiles without errors.




Function TwoDay(EmissionStd As String, TwoDayStandard As String)
If EmissionStd = "A" Then
TwoDayStandard = 0.65
ElseIf EmissionStd = "B" Then
TwoDayStandard = 0.35
ElseIf EmissionStd = "C" Then
TwoDayStandard = 0.3
End If
End Function

 

[GregRun]

Are you coding this in a form or trying to get it to calculate in table view? In general, calculated fields are best left to queries.

 

[mdlueck]

How do I call out the function in my table?

"tables" by their self can not trigger a VBA event to fire.

Do you have a Form in this application? Perhaps that you have entered said VBA code into?

When do you expect the VBA code to fire? When the form Opens? When a button is pushed? When data on the form changes? Those are types of events you can wire your code to.

 

[PRPIETRO]

Are you coding this in a form or trying to get it to calculate in table view? In general, calculated fields are best left to queries.

I want the code to execute once a user has entered the selection into the table column

 

[PRPIETRO]

"tables" by their self can not trigger a VBA event to fire.

Do you have a Form in this application? Perhaps that you have entered said VBA code into?

When do you expect the VBA code to fire? When the form Opens? When a button is pushed? When data on the form changes? Those are types of events you can wire your code to.

I currently have a table in which I want the VBA code to trigger based on a selection in another column.

In the Expression builder for the table I can't make my VBA function fire like a built in function?

 

[spikepl]

A number of issues here:

1. You do not type anything into a table directly. You interact with tables only via forms or code

2. Having two expressions for the same thing in a table is redundant - in one column you have some string and in another a numerical value, with a 1-to-1 relation between them. Such data can be constructed on the fly in a query, so only one of the values is required in a table. Actually, a more customary way would be to have a separate table where the numerical values corresponding to the index - or string value in your case - would be stored, and looked up as necessary in a query.

3. Apart from Acess 2010, there are no "triggers" in tables. A table is a passive storage container for data. If you want to change values in a table you do it via code, set off by some event on a form.

 

[GregRun]

Technically, you CAN get a function to fire if you are in access 2010 and you use a calculated field. HOWEVER, this is poor database design as you will essentially be storing this data TWICE. What you should do instead is create a query with all of your other fields in it and have an additional field set up like this:

TwoDayStandard: Switch(EmissionStd = "A", 0.65, EmissionStd = "B", 0.35, EmissionStd = "C", 0.30, EmissionStd Not Like "*[ABC]*", 0)

**Note: I added an extra 2 arguments (, EmissionStd Not Like "*[ABC]*", 0) to set the field to zero if EmissionStd is neither a, b, or c. You can take that bit out of there if you want the field to be blank in that case.

Now, open the query in Datasheet View and when you make a change to EmissionStd you will instantly see the change in TwoDayStandard.

The next logical step here is to use this query to make a form so that it is easier for you or your users to enter data. If you haven't ever designed a form before I would recommend using the Form Design Wizard.

For more info, see: http://allenbrowne.com/casu-14.html

 

[PRPIETRO]

Technically, you CAN get a function to fire if you are in access 2010 and you use a calculated field. HOWEVER, this is poor database design as you will essentially be storing this data TWICE. What you should do instead is create a query with all of your other fields in it and have an additional field set up like this:

TwoDayStandard: Switch(EmissionStd = "A", 0.65, EmissionStd = "B", 0.35, EmissionStd = "C", 0.30, EmissionStd Not Like "*[ABC]*", 0)


Now, open the query in Datasheet View and when you make a change to EmissionStd you will instantly see the change in TwoDayStandard.

The next logical step here is to use this query to make a form so that it is easier for you or your users to enter data. If you haven't ever designed a form before I would recommend using the Form Design Wizard.

Does it matter if I have EmissionStd as a separate table with the column EmissionStd and choices A,B,C as rows?

I get an error still when using the Switch Expression when trying to save. Could building EmissionStd as a separate table be the issue?

 

[spikepl]

Did you get the bit with

What you should do instead is create a query with all of your other fields in it and have an additional field set up like this:

TwoDayStandard: Switch(EmissionStd = "A", 0.65, EmissionStd = "B", 0.35, EmissionStd = "C", 0.30, EmissionStd Not Like "*[ABC]*", 0)

 

[GregRun]

What are you trying to save? The table?

Let me rephrase:
STOP TRYING TO USE CALCULATED FIELDS!!!!

Read the link I posted.

 

[PRPIETRO]

When entering the code provided into a Query. The data sheet view still returns a #Error. Any idea as to why?

 

[The John Rambo]

Please post the code you are entering into the query?

 

[PRPIETRO]

TwoDayStandard: Switch(EmissionStd = "A", 0.65, EmissionStd = "B", 0.35, EmissionStd = "C", 0.30, EmissionStd Not Like "*[ABC]*", 0)


Note: EmissionStd is a table linked by relationship to my main table with EmissionStd being a column in the linked table. The code above is what I am entering into the Query.

 

[GregRun]

Can you switch to SQL view and paste the entire SQL code?

EDIT: Please also list all fields and data types for all the tables used in this query. Thanks.