Solved

Passing a Recordset Field to a Sub

Posted on 2001-06-12
19
302 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
  • 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Share codes 68 115
Extract Data and filename Macro 20 99
Copy a row 12 53
Prevent checkbox click event occur while editing it in vb6 8 27
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now