passing variable to other sub 2

[NewTexican]

what is the best way to assign a variable a value in one subroutine and then use that variable in another. I have a subroutine that adds a record to a table depending on a checkboxs' values.

Sub exarecordsetaddnew()
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Company-Mailout T")
With rs
.AddNew
![Company Numeric] = Me!Text18
![Address Type] = Me!Text20
!Mailout = "c mailout " & [highlight] mlout[/highlight]
![Send mailout] = [highlight] mloutyn [/highlight]
.Update
End With
rs.Close
End Sub

the highlighted variables depend on the checkbox being checked (I was thinking it would be best to reuse this procedure for each textbox on the form)

 

[HitechUser]

NewTexican,

This simplist way is to define the variable as public (not within a sub). Now the variable is available to all subroutines.

Take a quick peek in the help for public variables.

I hope this helps..

Good Luck...

 

[NewTexican]

I was playing with that and with byref. I can't get byref to work so I guess I'll try a public variable. I have been steering away from public because the public variable would take on different values depending on which checkboxes the user checked on one form. I am afraid that the value assigned to the public variable by one checkbox could get missmatched with another checkbox.

thanks.

 

[PHV]

Why not passing the 2 values as parameters when calling exarecordsetaddnew ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[NewTexican]

PHV, I was trying to figure out how to do that, but I was not able to get the code to work out. I haven't used by ref before. I'm not sure of the structure for assigning the variables. Public variables do seem to work, but I'd like to know more about passing variables

sub that has values to be passed:

assign variables and values.

sub that recieves passed variables:

do I re-assign variables in the code here?
what would the code look like that passes the value of the variable.

byref variablename as string ???

 

[PHV]

Don't worry with ByRef or ByVal.
Replace this:
Sub exarecordsetaddnew()
By this (with appropriate type):
Sub exarecordsetaddnew(mlout As String, mloutyn As Boolean)
And call the routine with the 2 needed values.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Axoliien]

You may find an easy fix above, but here is some more information if you would like to understand what and why there are different variable arguments.

You want to steer away from public variables if they are important to your data, such as data to be stored into the database. Public variables become visible to all and stay around as long as they are instantiated, so they breed mistakes.

That said, here is an example of a simple sub call:

Private Sub Main
    Dim CurrInt as Integer
    Dim CurrStr as String

    CurrInt = 0
    CurrString = ""

    CalledSubVal CurrInt, CurrString
    'CurrInt is still 0 since it is passed ByVal
    'CurrString is still "" since it is passed ByVal

    CalledSubRef CurrInt, CurrString
    'CurrInt is now 100 since it is passed ByRef
    'CurrString is now "This is sub CalledSubRef"
End Sub

Private Sub CalledSubVal(ByVal MyInt as Integer, ByVal MyString as String)
    MyInt = MyInt + 100
    MyString = "This is sub CalledSubVal"
End Sub

Private Sub CalledSubRef(ByRef MyInt as Integer, ByRef MyString as String)
    MyInt = MyInt + 100
    MyString = "This is sub CalledSubRef"
End Sub

Any time you pass variables to a sub or function, the variable is considered instantiated to whatever you named it in the sub or function. You can reinstantiate or do whatever you want to the variable, however how you pass the variable determines what occurs with the variable. When you pass variables by value, the variable is copied and anything you do to that variable only occurs during the lifetime of the function. When you pass by reference, the variable is referenced and no new copy is made, so anything you do is performed on the original variable. Using these two methods allows you to manage what each sub and function may do. If you are calculating something for insertion into the database, you should probably use ByVal to keep from changing the original vairable. If you are trying to build a query string using several procedures to put together data, you may want to use a function to return a partial string. If you are connecting and disconnecting from databases, you may want to use ByRef variables for your recordsets and connections so that you can keep them alive once the query function has completed.

 

[NewTexican]

thanks guys