Public Variables - not populating blank data

[timber910]

OK, I've searched all over and used the help on the VBA editor. Helped a little but I'm not getting close enough to see where I'm going wrong.

I need a public variable that I will be able to use on different forms as part of the join property in a SQL statement. I think it would easier to make it a public so I don't have to create the variable in every button it might be used in. Here is what I've come up with so far. While I get no errors when I complie or run the functions its not giving any data in the variable either so while it runs its not really doing anything helpful.

Saved Module

Public Function GetCS_NO(gCs_No) As String
' Create global variable for cs_no join from the Ref_data table join_cs_no
    Dim rs1 As New ADODB.Recordset
    Dim strConn As String ' stores the connection string
    'Dim gCs_No As String
 
    strConn = CurrentProject.Connection
    ' set the srcZoneTbl variable to name of the table given
    Set rs1 = New ADODB.Recordset
        rs1.Open "SELECT join_cs_no FROM Ref_Data", strConn, adOpenDynamic
 
        If rs1.RecordCount <> 0 Then
            rs1.MoveFirst
                gCs_No = rs1("join_cs_no").Value
            Else
                Debug.Print rs1("join_cs_no").Value
        End If
 
End Function
 

Just to test if my variable is being pulled correctly I added this to my form that opens with the DB.

Private Sub Form_Open(Cancel As Integer)
    Dim strCs_No As String
 
    strCs_No = GetCS_NO(gCs_No)
    MsgBox strCs_No
End Sub
 

I get the message box, but it is empty.

I want to be able to use the variable strCs_No later in SQL statements, so I'm just trying at this poing to get the public variable to actually pass the data.

Anyone have any ideas? I'm not having any luck so far.

Thanks,
Will

[ChipR]

If you want the variable to be global, declare it at the top of a Module, rather than in a Function.

If you want to call a public function that returns a value, use

strCs_No = Get_CS_No()
 
Public Function Get_CS_No() As String
  Get_CS_No = "return this string"
End Function

[timber910]

I used the code listed about and add the

strCs_No = GetCS_NO()
 

like this

strCs_No = GetCS_NO()
 
Public Function GetCS_NO() As String
' Create global variable for cs_no join from the Ref_data table join_cs_no
    Dim rs1 As New ADODB.Recordset
    Dim strConn As String ' stores the connection string
    'Dim gCs_No As String
 
    strConn = CurrentProject.Connection
    ' set the srcCS_NO variable to name of the join property given
    Set rs1 = New ADODB.Recordset
        rs1.Open "SELECT join_cs_no FROM Ref_Data", strConn, adOpenDynamic
 
        If rs1.RecordCount <> 0 Then
            rs1.MoveFirst
                GetCS_NO = rs1("join_cs_no").Value
            Else
                Debug.Print rs1("join_cs_no").Value
        End If
 
End Function
 

And I get an error stating: Compile error: Invalid outside procedure

[timber910]

Ok, i got it to work. But can someone look over this and see if it actually done correctly. Or is there a better way of doing this?

Module code

Public Function GetCS_NO() As String
' Create global variable for cs_no join from the Ref_data table join_cs_no
    Dim rs1 As New ADODB.Recordset
    Dim strConn As String ' stores the connection string
    'Dim gCs_No As String
 
    strConn = CurrentProject.Connection
    ' set the srcCS_NO variable to name of the join property given
    Set rs1 = New ADODB.Recordset
        rs1.Open "SELECT join_cs_no FROM Ref_Data", strConn, adOpenDynamic
 
        If rs1.RecordCount <> 0 Then
            rs1.MoveFirst
                GetCS_NO = rs1("join_cs_no").Value
            Else
                Debug.Print rs1("join_cs_no").Value
        End If
 
End Function
 

first form

Public gCs_No As String
Private Sub Form_Open(Cancel As Integer)
    Dim strCs_No As String
 
    strCs_No = GetCS_NO()
    MsgBox strCs_No
End Sub
 

[ChipR]

If you want a single value from a table, it's much easier and clearer to use DLookup rather than a recordset.

[timber910]

So if I were to use DLookup in this case it would look like this?

 
DLookup(join_cs_no, Ref_Data)
 
 

In this case?

[ChipR]

Close. The arguments should be string expressions, or the compiler will try to resolve them as variables.

DLookup("join_cs_no", "Ref_Data")

[timber910]

then just set the dlookup to some variable i need I would assume.

That does seem a little easier... LOL!

Thanks for the help ChipR!

[NeoPa]

Do you have this data stored in your [Ref_Data] table Will?

If so, this is probably a better approach than a global variable, which would be declared as :

Public {name} As {Type} = {Value}

in a standard (not class) module, and at the start before any executable code.

Is there only the one record in your [Ref_Data] table?

[timber910]

Yes, by Ref_Data table has just 1 record and several fields. I just wanted the 1 record so I didn't have to put the results in an array and split all that out and what not. The only question I would have about that suggestion is how I would make it pull the {Value}

I have tried this

Public strCs_No As String = GetCS_NO()
 

But I get an error when I push enter to go to the next line. The error I get is "Compile error: Expected: end of statement"

[NeoPa]

Sorry. Being a muppet again. That should have been :

Public {Name} As {Type}

I was confusing with the Const declaration :

Public Const {Name} As {Type} = {Expression}

NB. I was actually suggesting that Chip's idea of referring to it using DLookup() seems sound. Your single record setup is spot on.

PS. Note that {Expression} cannot include a function reference. Function calls within a declaration cannot work. This is not an executable stage of the code.