' Open Connection object using supplied information in
' the connect string.
Set conOIG = wrkODBC.OpenConnection("Connection1", dbDriverComplete, False, _
This connection is successful, but I can not use the database variable dbsOIG in other modules. When I received the code ( I didn't write it), the DIM statement for dbsOIG was in this function and I concluded that the scope of the variable was stopping me from using dbsOIG outside of the current function.
Well, I moved the DIM statement to the top of the module and changed it to a GLOBAL definition as so.
Global dbsOIG As Database
I thought I would be able to use the dbsOIG database definition in another module but can not. I receive the error.
Runtime error 91
Object variable or With Block variable not set.
so it still doesn't recognize my GLOBAL dbsOIG database defintion.
You may need to create a Global_Moduel, which is nothing more than a moduel that has the varialbes set in them. I also beleve that you will need to use the key word public instead of Global IE
Public odbc_connection as database. This may be why its not working if you are coding in a moduel now so try that first and if that dosent work then but the variable in its own moduel
I declare variable A out of any procedure..... then I have Public Procedure and stick somethign into A and then Private Procedure which runs public procedure (can be stored anywhere in MDB file) and puts my variable into A. The difference between Public and Private Procedure is only in the score from where you can call it. Public Sub/Function can be called from anywhere, Private sub/Function only from it's Module/Form/Report - it saves some memory.... and sometimes lot of pain! The same is for variables.... if you put it INTO function/sub then it works only in that sub. If you put it in the OUT of sub as DIM or CONST then it works for all funvtions in the module, if you use word PUBLIC, then it works for all modules...
two small recommendations:
use OPTION EXPLICIT and declare all variables...
keep all PULBIC varibles and constants in one module (it's easier to find it)
Public Const consB = "This is test too."
Public strA As String
Public Sub PutItIntoA()
strA = " This is a test."
Private Sub ReadItFromA()
'this SUB can be in different module
MsgBox strA & " " & consB