Invalid procedure call error

[aman1234]

Hi All

I'm declaring a global variable and getting the above error message

Dim sql1 As String
sql1 = "(TO>#" & Format(Date, "mm/dd/yyyy") & "# or TO IS null)"

Any help will be much appreciated.

Thanks

[LJMetzger]

Hi aman1234,

It seems like you did not declare a Sub or Function. Your code should probably look something like:

Option Explicit

Dim sql1 As String

Sub Macro1()
  sql1 = "(TO>#" & Format(Date, "mm/dd/yyyy") & "# or TO IS null)"
End Sub

The above code compiles fine with a value of '(TO>#12/01/2015# or TO IS null)'.

Option Explicit is very important
It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Option Explicit

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

[Kyle123]

The sql1 = ... line must be in a sub

[aman1234]

I need to use the value of sql1 many times in the code so that's why I want to define it globally so that I can use it as many times as I want . please see below:

Option Explicit
Dim sql1 As String
sql1 = "(TO>#" & Format(Date, "mm/dd/yyyy") & "# or TO IS null)"
Private Sub ComboBox1_Change()
If ComboBox2.Visible = False Then
ComboBox2.Visible = True
End If
If Label2.Visible = False Then
Label2.Visible = True
End If
ComboBox2.Clear
ComboBox3.Clear
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws1 As Worksheet
Dim r As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\WilliamsLea-AIMM\Unit Rate MI\Alice's Adventures in Wonderland\The Mad Hatter\WorkQueue.mdb;"
    Set rs = CreateObject("ADODB.Recordset")
 
 
    strsql = "SELECT DISTINCT Type1 FROM TestTType3Tbl where Type='" & ComboBox1.Value & "' and sql1"
    rs.Open strsql, cn
    Do While Not rs.EOF
    ComboBox2.AddItem (rs.Fields("Type1"))
    rs.MoveNext
    Loop
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

Im getting invalid outside procedure error. Any help will be much appreciated.

[Kyle123]

It must go in a sub, just make sure the sub runs before all your other code

[aman1234]

kyle123, so how can I use the value of sql1 in combobox1_change() event?

[LJMetzger]

Try something like:

Option Explicit

Dim sql1 As String

Public Sub Define_sql1()
  sql1 = "(TO>#" & Format(Date, "mm/dd/yyyy") & "# or TO IS null)"
End Sub

Private Sub ComboBox1_Change()

'Define the Value of 'sql1'
Call Define_sql1

If ComboBox2.Visible = False Then
ComboBox2.Visible = True
End If
If Label2.Visible = False Then
Label2.Visible = True
End If
ComboBox2.Clear
ComboBox3.Clear
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws1 As Worksheet
Dim r As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\WilliamsLea-AIMM\Unit Rate MI\Alice's Adventures in Wonderland\The Mad Hatter\WorkQueue.mdb;"
    Set rs = CreateObject("ADODB.Recordset")
 
 
    strsql = "SELECT DISTINCT Type1 FROM TestTType3Tbl where Type='" & ComboBox1.Value & "' and sql1"
    rs.Open strsql, cn
    Do While Not rs.EOF
    ComboBox2.AddItem (rs.Fields("Type1"))
    rs.MoveNext
    Loop
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub