By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,964 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,964 IT Pros & Developers. It's quick & easy.

Public Variables - not populating blank data

P: 39
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
Expand|Select|Wrap|Line Numbers
  1. Public Function GetCS_NO(gCs_No) As String
  2. ' Create global variable for cs_no join from the Ref_data table join_cs_no
  3.     Dim rs1 As New ADODB.Recordset
  4.     Dim strConn As String ' stores the connection string
  5.     'Dim gCs_No As String
  6.  
  7.     strConn = CurrentProject.Connection
  8.     ' set the srcZoneTbl variable to name of the table given
  9.     Set rs1 = New ADODB.Recordset
  10.         rs1.Open "SELECT join_cs_no FROM Ref_Data", strConn, adOpenDynamic
  11.  
  12.         If rs1.RecordCount <> 0 Then
  13.             rs1.MoveFirst
  14.                 gCs_No = rs1("join_cs_no").Value
  15.             Else
  16.                 Debug.Print rs1("join_cs_no").Value
  17.         End If
  18.  
  19. End Function
  20.  
Just to test if my variable is being pulled correctly I added this to my form that opens with the DB.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Dim strCs_No As String
  3.  
  4.     strCs_No = GetCS_NO(gCs_No)
  5.     MsgBox strCs_No
  6. End Sub
  7.  
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
Jun 12 '09 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 1,287
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
Expand|Select|Wrap|Line Numbers
  1. strCs_No = Get_CS_No()
  2.  
  3. Public Function Get_CS_No() As String
  4.   Get_CS_No = "return this string"
  5. End Function
Jun 12 '09 #2

P: 39
I used the code listed about and add the

Expand|Select|Wrap|Line Numbers
  1. strCs_No = GetCS_NO()
  2.  
like this

Expand|Select|Wrap|Line Numbers
  1. strCs_No = GetCS_NO()
  2.  
  3. Public Function GetCS_NO() As String
  4. ' Create global variable for cs_no join from the Ref_data table join_cs_no
  5.     Dim rs1 As New ADODB.Recordset
  6.     Dim strConn As String ' stores the connection string
  7.     'Dim gCs_No As String
  8.  
  9.     strConn = CurrentProject.Connection
  10.     ' set the srcCS_NO variable to name of the join property given
  11.     Set rs1 = New ADODB.Recordset
  12.         rs1.Open "SELECT join_cs_no FROM Ref_Data", strConn, adOpenDynamic
  13.  
  14.         If rs1.RecordCount <> 0 Then
  15.             rs1.MoveFirst
  16.                 GetCS_NO = rs1("join_cs_no").Value
  17.             Else
  18.                 Debug.Print rs1("join_cs_no").Value
  19.         End If
  20.  
  21. End Function
  22.  
And I get an error stating: Compile error: Invalid outside procedure
Jun 12 '09 #3

P: 39
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
Expand|Select|Wrap|Line Numbers
  1. Public Function GetCS_NO() As String
  2. ' Create global variable for cs_no join from the Ref_data table join_cs_no
  3.     Dim rs1 As New ADODB.Recordset
  4.     Dim strConn As String ' stores the connection string
  5.     'Dim gCs_No As String
  6.  
  7.     strConn = CurrentProject.Connection
  8.     ' set the srcCS_NO variable to name of the join property given
  9.     Set rs1 = New ADODB.Recordset
  10.         rs1.Open "SELECT join_cs_no FROM Ref_Data", strConn, adOpenDynamic
  11.  
  12.         If rs1.RecordCount <> 0 Then
  13.             rs1.MoveFirst
  14.                 GetCS_NO = rs1("join_cs_no").Value
  15.             Else
  16.                 Debug.Print rs1("join_cs_no").Value
  17.         End If
  18.  
  19. End Function
  20.  
first form
Expand|Select|Wrap|Line Numbers
  1. Public gCs_No As String
  2. Private Sub Form_Open(Cancel As Integer)
  3.     Dim strCs_No As String
  4.  
  5.     strCs_No = GetCS_NO()
  6.     MsgBox strCs_No
  7. End Sub
  8.  
Jun 12 '09 #4

Expert 100+
P: 1,287
If you want a single value from a table, it's much easier and clearer to use DLookup rather than a recordset.
Jun 12 '09 #5

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. DLookup(join_cs_no, Ref_Data)
  3.  
  4.  
In this case?
Jun 12 '09 #6

Expert 100+
P: 1,287
Close. The arguments should be string expressions, or the compiler will try to resolve them as variables.
Expand|Select|Wrap|Line Numbers
  1. DLookup("join_cs_no", "Ref_Data")
Jun 12 '09 #7

P: 39
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!
Jun 12 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. 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?
Jun 12 '09 #9

P: 39
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

Expand|Select|Wrap|Line Numbers
  1. Public strCs_No As String = GetCS_NO()
  2.  
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"
Jun 12 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
Sorry. Being a muppet again. That should have been :
Expand|Select|Wrap|Line Numbers
  1. Public {Name} As {Type}
I was confusing with the Const declaration :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 12 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.