• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

Passing a Recordset Field to a Sub

    I call my PutDate function this way:

      PutDate rstThisPatient("DateFirstConsulted"), _ txtMedicalDateFirstConsulted

     The PutDate Sub looks like this (it's called from many different forms):

Public Sub PutDate(dateField As String, dateToOutput As String)

   If dateToOutput = "" Then
      dateField = "12/31/1899"
   Else
      dateField = dateToOutput
   End If

End Sub

     If I set the date in the TextBox to 1/1/2000 and trace it through PutDate, I see dateField being changed from 12/31/99 to 1/1/2000, just fine. But when I get to the next instruction after the call to PutDate, I see the rstThisPatient("DateFirstConsulted") field still sitting at the original 12/31/99.

     Obviously, "dateField" in PutDate is not rstThisPatient("DateFirstConsulted"), but why not?

---Bob Gebhart
0
GebhartBob
Asked:
GebhartBob
  • 7
  • 5
  • 4
  • +1
1 Solution
 
beckinghCommented:
Try explicitly setting the parameter as ByRef

i.e.

Public Sub PutDate(ByRef dateField As String, ByVal dateToOutput As String)

I _think_ that's the default, but its worth a shot.
0
 
GebhartBobAuthor Commented:
   If somebody can help me with this ... really a very general question concerning passing parameters to Subs and Functions, and therefore important for me to fully understand ... then we can talk about why the stupid PutDate function exists in the first place. But first things first ...
0
 
beckinghCommented:
If you pass a parameter ByRef then any changes you make in the sub are reflected in the passed in variable.  If you pass it in ByVal then you are making a copy of the variable and passing it to the Sub.  Any changes you make to that will be lost when you exit the sub.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
GebhartBobAuthor Commented:
    I tried declaring the function as this:

Public Sub PutDate(ByRef dateField As String, ByVal dateToOutput As String)

     Good idea, gallant try, but no joy. ByRef is the default. The Sub still behaves in exactly the same way; i.e., "dateField" gets changed, but the original field specified in the call remains unchanged.
0
 
beckinghCommented:
Try this:  In your code, before the Sub call, try setting the recordset value to a date.  If this works, then it has something to do with passing the parameter to the procedure.  If not, then the problem has to do with the configuration of your recordset.
0
 
amebaCommented:
To pass variable ByRef, you must pass variable, not calculated value of variable.
'-------------------------------
Sub somesub(a as Integer)
    a = 1
End Sub
'-------------------------------

' case 1 - passing byref
Dim x As string
x = 5
somesub x
MsgBox x  '  will show

' case 2 - passing byval
Dim x As string
x = 5
somesub ByVal x
MsgBox x  '  will show 5

' case 3 - passing byval - this is equivalent to case 2
Dim x As string
x = 5
somesub (x)
MsgBox x  '  will show 5

' case 4 - passing byval
Dim x As string
x = 5
somesub x * 1
MsgBox x  '  will show 5

----------------------------------------
Your case, first parameter is string:
(ByRef dateField As String,

To pass ByRef you should pass ONLY String variable, but you are passing calculated value
>  rstThisPatient("DateFirstConsulted")
which is the same as:
  rstThisPatient("DateFirstConsulted").Value
so what you get is passing ByVal

Instead of:
>     PutDate rstThisPatient("DateFirstConsulted"),  txtMedicalDateFirstConsulted
use:
     Dim x As String
     x = rstThisPatient("DateFirstConsulted")
     PutDate x, txtMedicalDateFirstConsulted
     rstThisPatient("DateFirstConsulted") = x
0
 
amebaCommented:
Oops,
>Dim x As string
should be:
Dim x As Integer
(in all 4 cases)
0
 
amebaCommented:
Ignore my previous post, and test this code:

' Form1 code
Option Explicit

Private Sub Form_Click()
    Dim a  As Integer
    a = 5
    somesub a
    MsgBox a, , "case no 1"
   
    a = 5
    Call somesub(a)
    MsgBox a, , "case no 2"
   
    a = 5
    somesub Val(a)
    MsgBox a, , "case no 3"
   
    a = 5
    somesub (a)   ' parens will evaluate (calculate) expression
    MsgBox a, , "case no 4"
   
    a = 5
    Call somesub((a))
    MsgBox a, , "case no 5"
   
End Sub

Private Sub somesub(a As Integer)
    a = 1
End Sub
0
 
TravisHallCommented:
Basically, to simplify what Ameba is saying a little, to get a modified value back out of your Sub (or function), you need to pass a variable of the same type as the corresponding parameter (as well as that parameter being ByRef, which is the default). If your parameter is a string, you pass it a string. If you don't have a string, you copy your variable's value to a string, pass it to the function, and copy the value back - just as Ameba said.

However, there's another way to do it. You could change your parameter to match your variable instead. rstThisPatient("DateFirstConsulted") is actually a Field object, I believe, so if you change your sub declaration to:

Public Sub PutDate(dateField As Field, dateToOutput As String)

...your existing code might work. Try it out and see what happens. I could be wrong on that one, of course, but I think you can do that.

If it doesn't work, you could call it with this code:

Dim fld As Field
Set fld = rstThisPatient("DateFirstConsulted")
PutDate fld, _txtMedicalDateFirstConsulted

I'm certain that would work.
0
 
amebaCommented:
>I'm certain that would work.
Yes, TravisHall, that second case should work

I would suggest using a function:

'NZ (not zero) functions
Public Function NZDate(strDate As String) As String
   If Len(strDate) Then
      NZDate = strDate
   Else
      NZDate = "12/31/1899"
   End If
End Function

' usage:
rstThisPatient.Fields("DateFirstConsulted").Value = NZDate(txtMedicalDateFirstConsulted.Text)

(if field "DateFirstConsulted" is text field)
0
 
TravisHallCommented:
>I would suggest using a function

Well, yeah, Ameba, you've got a good point there. Understanding parameter passing is very important, of course, but this sort of thing is exactly what functions are for.
0
 
GebhartBobAuthor Commented:
    Thanks for the extremely lucid explanation!
0
 
TravisHallCommented:
No problem. I used to be a tutor, so I've had a bit of practice at explaining things.

You gave me an awful lot of points for a simple answer, though, and I thought Ameba deserves some of them. Any idea if there's a way to give some of them to him?

(Not that it would be more than a drop in the ocean for him.)
0
 
amebaCommented:
TravisHall,

Thanks. I have enough points  :-)
I know I'm not good in explaining things... writting manuals / help files... speeling... and few other things ;-)
0
 
GebhartBobAuthor Commented:
   Guys, I just plain screwed up. I meant to give both TravisHall and ameba 50 points each, because you were both so very helpful. I'm entirely new at this, and don't know how to split credit, or how to un-do what I've done so unfairly. If you do, let me know.

     Maybe the question didn't seem worth 100 points to you, but it was immensely enlightening for me. From where I sit, 100 points is a bargain. I learned a lot about passing parameters, a very important topic.

     Thank you both, very much, for your smarts and for your thoughtfulness. Please forgive me, ameba, for seeming to be so ungrateful. It's ignorance, not malice.
0
 
amebaCommented:
GebhartBob, thanks!
Everything is OK, don't worry for the points.
0
 
TravisHallCommented:
Well, I ended up with 400 points, not 100, which is why I mentioned it. And I've got no idea how to give a refund. Sorry, Bob.

(Hey, maybe if you like my answer to your other question, you can accept it for 0 points. I wouldn't begrudge it.)
0
 
amebaCommented:
>I ended up with 400 points

This is how point system works - for A grade  multiplier is 4, for B it is 3 ...

Your code/explanation in second question also looks like A. Good job!


>...your existing code might work.
It seems it worked, without using separate field object.

--
ByVal/ByRef explanation is valid for 'normal' variables.
For *object* variables (e.g. Field) ByVal has different meaning.
When you pass object variable, you are *always* passing pointer to object.
(In a way, you can say, object variables are always passed ByRef.)

So this will also work:
Public Sub PutDate(ByVal dateField As Field, dateToOutput As String)
0
 
TravisHallCommented:
Oh. Fair enough, then. I'm new here, and I haven't been paying too much attention to the points I have earned. Thanks for the explanation, Ameba.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now