Solved

Passing a Recordset Field to a Sub

Posted on 2001-06-12
19
309 Views
Last Modified: 2008-02-26
    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
Comment
Question by:GebhartBob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
19 Comments
 
LVL 4

Expert Comment

by:beckingh
ID: 6183691
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
 

Author Comment

by:GebhartBob
ID: 6183692
   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
 
LVL 4

Expert Comment

by:beckingh
ID: 6183705
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
Industry Leaders: 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!

 

Author Comment

by:GebhartBob
ID: 6183710
    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
 
LVL 4

Expert Comment

by:beckingh
ID: 6183720
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
 
LVL 15

Expert Comment

by:ameba
ID: 6183776
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
 
LVL 15

Expert Comment

by:ameba
ID: 6183782
Oops,
>Dim x As string
should be:
Dim x As Integer
(in all 4 cases)
0
 
LVL 15

Expert Comment

by:ameba
ID: 6183845
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
 
LVL 2

Accepted Solution

by:
TravisHall earned 100 total points
ID: 6183888
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
 
LVL 15

Expert Comment

by:ameba
ID: 6183966
>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
 
LVL 2

Expert Comment

by:TravisHall
ID: 6183983
>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
 

Author Comment

by:GebhartBob
ID: 6184152
    Thanks for the extremely lucid explanation!
0
 
LVL 2

Expert Comment

by:TravisHall
ID: 6184167
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
 
LVL 15

Expert Comment

by:ameba
ID: 6184204
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
 

Author Comment

by:GebhartBob
ID: 6184216
   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
 
LVL 15

Expert Comment

by:ameba
ID: 6184226
GebhartBob, thanks!
Everything is OK, don't worry for the points.
0
 
LVL 2

Expert Comment

by:TravisHall
ID: 6184538
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
 
LVL 15

Expert Comment

by:ameba
ID: 6185274
>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
 
LVL 2

Expert Comment

by:TravisHall
ID: 6188719
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

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question