I'm getting an "#Size!" error when trying to set a value to a textbox on a report using VBA in Access

Private Function tbxspecValue() As String

If [jmCommentText] Like "*Part Mark*" Then
tbxSpec = "Part Mark"
Else
tbxSpec = "Unknown"
End If


End Function

Open in new window


Here is the code I'm using.. I placed a textbox on a report that I called tbxSpec. I then set the control source for that textbox to "=tbxspecValue()"


I was actually just using this code to figure out how to simply set the value of the textbox, but in the end I wanted to reference a table to set the value. The table will have a column called spec and then a column called verbage. Ideally the vba will look at the field jmCommentText and see if any of it equals the "Spec" items in the table and then return the corresponding "Verbage" from the table. (The vba will loop through the table and then return the first occurence it sees)

Here is approximately what I was going to start with for that vba:

Private Sub Spec()

Dim rs As DAO.Recordset
    Dim blSpecFound As Boolean
    Set rs = CurrentDb.OpenRecordset("SELECT Spec, Verbage FROM tblSpecs")
    Do Until rs.EOF
        If [jmDescription] Like "*" & rs!Spec & "*" Then
            tbxSpec = rs!Verbage
            Exit Do
        End If
        rs.MoveNext
    Loop

    Else
        tbxSpec = "Unknown"
    End If
    
End Sub

Open in new window


I don't know if this code is good or not as I have not yet accomplished simply assigning a value with the more basic vba above.. If someone could tell me first why I'm getting the #Size! error and perhaps help me fine tune the second vba code (if need be) so that I can make it work?

Thanks for the help in advance!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
I think you need to adjust your code to return thae value like this

Private Function tbxspecValue() As String

If [jmCommentText] Like "*Part Mark*" Then
tbxspecValue= "Part Mark"
Else
tbxspecValue= "Unknown"
End If


End Function
0
Jarred MeyerProduction ManagerAuthor Commented:
That doesn't seem to work right.. The name of the texbox is tbxSpec.. The name of the function is tbxspecValue.. I may have made it confusing by naming them close..

In any case I just tried to move onto the table vba to get the value to assign from a table and here is the code I'm currently at, which is returning an #Error!.. :(

Private Function Spec()

Dim rs As DAO.Recordset
    Dim blSpecFound As Boolean
    Set rs = CurrentDb.OpenRecordset("SELECT Spec, Verbage FROM tblSpecs")
    Do Until rs.EOF
        If [jmDescription] Like "*" & rs!Spec & "*" Then
            tbxSpec = rs!Verbage
            Exit Do
        End If
        rs.MoveNext
    Loop
    If tbxSpec = "" Then
    tbxSpec = "Unknown"
   End If
    
End Function

Open in new window

0
mbizupCommented:
The rationale for that is that your control source is calling the function like this: ""=tbxspecValue()", but your function was not returning anything.

To get a function to return a value to the calling procedure (or control source in this case), you need code like this to define that return value:

    YourFunctionName = "Something"

So highlighting the changes:

Private Function tbxspecValue() As String

   If [jmCommentText] Like "*Part Mark*" Then
           tbxspecValue= "Part Mark"    ' Changed from tbxspec
   Else
           tbxspecValue= "Unknown"    ' Changed from tbxspec

   End If
End Function




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jarred MeyerProduction ManagerAuthor Commented:
My apologies mbizup.. That did actually work.. I forgot to add the = in front of the textbox control source when I switched back to the other formula.. Really sorry about that..
0
mbizupCommented:
No trouble...

You have the same issue with your second function.  How are you calling it?

To return a value, you need a line in there somwhere to define that return:

      Spec = "something"

Unless you do that, the return value of your function will be Null.
0
Jarred MeyerProduction ManagerAuthor Commented:
Well I'm getting it to return Unknown but it seems the first part where it is looking through the table for the spec is not working.. I think I will accept your answer above and close this question out and start a new one on how to troubleshoot the table portion of my code..
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

As an aside, you procedure could be made faster by moving the like crtieria check into the open recordset.  Then any records returned will already be a match.  

  That's a better approach then you fetching every record and looping through until you find one that fits.

Jim.
0
Jarred MeyerProduction ManagerAuthor Commented:
Hey J.. I appreciate that but I'm rather new to VBA and I'm not exactly sure how I would set that up? I just posted another question on the recordset formula, perhaps you could go into more detail on that question regarding that so I can award point there if possible?

Thanks Sir!

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27491111.html
0
mbizupCommented:
Jim,

I'm not sure how the criteria would work... I was trying to figure that out yesterday in one of author's earlier questions.

If the arguments around the LIKE were reversed (ie: the wildcards on the form field instead of on the recordset field), I think this could be shortened to a DLookup statement (but I'm not sure that's doable in this case).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.