Value determined by VBA coding does not show in table

[lburleigh]

Hello,
So I have a worksheet on access with my VBA code along with a form and a table on access. All three of these are connected and work off of each other. My problem is that I have a value (T-Score) that is calculated by VBA with an If Then statement but it does not show the value on the table with the rest of the data. Anyone have any idea how to connect the two?

[zmbd]

lburleigh:
You need to show your code.[*]> Before Posting (VBA or SQL) Code

[lburleigh]

Sub Boys7to12()
   If [Total Score] >= 39 Then
      [T-Score] = 90
      ElseIf [Total Score] = 38 Then
         [T-Score] = 89
         ElseIf [Total Score] = 37 Then
            [T-Score] = 88
   End If
End Sub

(in the actual coding, it continues the same until 0.

[zmbd]

1) This is a sub not a function; therefor, it's not supposed to return a value.

2) There are neither recordsets opened nor SQL executed to update a field

3) Where are you attempting to use this code from: a form, a calculated control, a table field?

I take it that you are fairly new to database design, Access, and VBA?

[lburleigh]

1) so if I instead use a code like...

Public Function getAgePhrase(ByVal age As Integer) As String 
    If age > 60 Then Return "Senior" 
    If age > 40 Then Return "Middle-aged" 
    If age > 20 Then Return "Adult" 
    If age > 12 Then Return "Teen-aged" 
    If age > 4 Then Return "School-aged" 
    If age > 1 Then Return "Toddler" 
    Return "Infant" 
End Function

would that allow it to return the corresponding value?

2) I don't quite understand when to properly use SQL. Is it necessary here?

3) all of the above. the form is interactive for others to input their necessary data and then it connects to a table field where the data is simplified and displayed in an easy to read format.

I am pretty new to all of it and only know basics but I am trying to learn. Thank you so much for your help and patience.

[zmbd]

1) So in a query or the control source for a form control:

Query:
FieldName: getAgePhrase([AgeField])

In a control's property sheet, data tab, property [Control Source], provided the [AgeField] was in the form's record set or available in an unbound text box then:

=getAgePhrase([AgeField])

You would not normally use this in a table.

>>> I would also not hard code the example you've given for the age range - it's hard to maintain. Instead I would have a table with these text results and a query that returns the value based on the criteria see (2). You might have to redesign the table structure as well as often when I see things like this hardcoded there is an underlying weakness in the database design.

2) The answer is both yes and no, see (1). If you use the function in a query, via the query editor (QE), you are using SQL, just that Access has hidden it behind a pretty dialog box. You can see the SQL script by right clicking in the table area of the QE and selecting SQL-View. (Select design view to go back to the happy place (^_^) )

3)That was the impression I had of what was happening. Even though everything is related, it's not always so direct which can be confusing and frustrating (even to us old-hands)! So in your case using the vba in order for the VBA (or Macro which is a 2nd language avaiable in Access) to have an effect on a record set there must be an explict value change. This can happen in a form if the control is bound to table/query and the VBA alters the value of bound control, the VBA opens a recordset and updates the values (can even be the form's recordset), the VBA uses the execute method on an action SQL (such as INSERT or UPDATE).

4) I'm going to PM you my standard list of what I hope are helpfull links. There are some tutorials in this that I highly recommend you take a careful look at (and hopefully work thru the hands-on one). There is a sublink in the hands-on one that works thru database design (in ACC2003; however, the concepts still hold true) that you might find of great help.

[lburleigh]

Thank you so much!! You have been such a great help! I will definitely look through the links you send.
I will play around with all the information you have given me and hopefully work it out. I really appreciate all your time. :)