Select Case on Subform

[Applefritter]

I'm new to Access and know very little about VBA so please bear with me. I have a form (formProfile) which contains a field for (Temperature). Now associated with this form is a subform (formNPF). In this subform is a textbox (TCF) which will automatically populate with a certain value based on the Temperature value in the main form. If Temperature is Between 23.6 and 24.15 my TCF value should be 1.03. If Temperature is Between 24.16 and 24.7, my TCF value should be 1.02, etc...

I started to write some code for this but I'm not sure if I'm doing this correctly or where the code should go. Should I associate the code with an event such as "On Open"? Should the code be in the module associated with my mainform or my subform? Here's what I have so far:

Option Compare Database
Select Case ???

Case 23.6 To 24.15
Me.txtTCF = 1.03

Case 24.16 To 24.7
Me.txtTCF = 1.02

Case 24.71 To 25.30
Me.txtTCF = 1

Case 25.31 To 25.85
Me.txtTCF = 0.99

Case 25.86 To 26.40
Me.txtTCF = 0.98

Case 26.41 To 26.95
Me.txtTCF = 0.96

Case 26.96 To 27.50
Me.txtTCF = 0.95

Case 27.51 To 28.05
Me txtTCF = 0.94

End Select

 

[KenHigg]

I would probably write a function and and use it in the control soucre property.

Just wondering, are you storing the result in a table or just displaying them on the subform?

 

[Applefritter]

By function I assume you mean "If-Then-Else" statement? I was thinking that with as many alternatives as I have it may be too complicated. Unsure how to write this function. Could you provide me with a little lead to get my code started?

My temperature value in the Main form is stored in a table but I do not plan on storing the TCF value. It will be used for display in my subform and to calculate other fields.

 

[Mile-O]

In a standalone module:

Public Function TCF(ByVal sngValue As Single) As Single

    Select Case sngValue
        Case <= 23.6
            TCF = 0
        Case <= 24.15
            TCF = 1.03
        Case < = 24.7
            TCF = 1.02
        Case <= 25.30
            TCF = 1
        Case <= 25.85
            TCF = 0.99
        Case <= 26.40
            TCF = 0.98
        Case <= 26.95
            TCF = 0.96
        Case <= 27.50
            TCF = 0.95
        Case <= 28.05
            TCF = 0.94
        Case Else
            TCF = 0
    End Select

End Function

Then, in your subform:

Me.txtTCF = TCF([i]mySource[/i])

 

[KenHigg]

Hum...

Being new to vba, I assume you have never written a custom function?

ken

 

[Applefritter]

You are correct. I've been getting by with what I learn here and the Access Help menus.

 

[KenHigg]

Think you can make Mile-Phile's code work?

 

[Applefritter]

I'm going to give it a try and see what happens. Does the subform code go into the control source property or into the subform module (This is probably self-evident to most people here but I'm a novice.)

I'll get back and let everyone know if I was successful.

 

[Applefritter]

Ok, so I created the standalone module with code and I added the subform code to its Control Source. All code I transcribed verbatim. I am still seeing an error message ((#Name?) in my TCF field. Any idea of what I'm doing wrong now?

 

[Applefritter]

What does error #Name? mean?