Solved

Passing a Recordset Field to a Sub

Posted on 2001-06-12
19
306 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

856 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