Call Function not working...

[anusha]

I have the following code and the call function below is not working..

The following global vars were setup in module4
[VBA]
Public strPW As String
Public strLogin As String
Public varDate1 As String
Public varSeries As String
Public varItem As String
Public varTable As String[/VBA]

And the following is the code behind a OK buton on a form, The globals work well inside the cmdOK_click() sub. I need to run macro1 which also requires the globals. No error... but no information is returned to the sheet!!

What am I doing wrong?
[VBA]
Private Sub cmdOK_Click()

Dim rng As Range
Dim MyVar As String

If x = true then

Code...

Elseif y = true then

code ...

Elseif z = true then

code ...

End If

Call Macro1

End sub[/VBA]

[BlueCactus]

Any chance we can see Macro1?

[anusha]

[VBA]Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/5/2005 by M1AFD00
'

'

strSQL = "select" & Chr(13) & _
"A.dt, B.dt, E.dt" & Chr(13) & _
"from " & Chr(13) & _
"fdrP.cuv_" & varTable & " a," & Chr(13) & _
"fdrP.cuv_" & varTable & " b," & Chr(13) & _
"fdrP.cuv_" & varTable & " E" & Chr(13) & _
"where (A.DT = (select max(dt) from fdrp.cuv_" & varTable & " where dt = " & varDate1 & ") " & Chr(13) & _
"AND B.DT = (select max(dt) from fdrp.cuv_" & varTable & " where dt < (select max(dt) from fdrp.cuv_" & varTable & " where dt <= " & varDate1 & "))" & _
"AND E.DT = (select max(dt) from fdrp.cuv_" & varTable & " where dt < (select max(dt) from fdrp.cuv_" & varTable & " where dt <" & Chr(13) & _
"(select max(dt) from fdrp.cuv_" & varTable & " where dt <= " & varDate1 & "))) " & _
"AND A.ID_RSSD = B.ID_RSSD " & Chr(13) & _
"AND B.ID_RSSD = E.ID_RSSD "

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=" & strLogin & ";pwd=" & strPW & ";MODE=SHARE;DBALIAS=M1DB2P;", _
Destination:=Range("D10"))
.CommandType = xlCmdSql
.CommandText = strSQL
.Name = "Query from M1DB2P"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
' .Refresh BackgroundQuery:=False
End With


End Sub[/VBA]

[malik641]

Hi Anusha
Just so you know, I placed your code in VB tags

Not too sure about Macro1, that looks a little intense for me (haven't used Query Tables before)

But...
If you need any information from the OK button for the Procedure, you could send it to the procedure using arguements through the call command.

Just place your variables that you want to send to the procedure like so:

[VBA]Call Macro1(Arg1, Arg2, etc)[/VBA] Then in Macro1 you would use these arguements in the Sub Procedure:
[vba]Sub Macro1(ThisArg1 as Long, ThisArg2 as Range)[/vba]But I'm not too sure if this is what you need...Could you post the workbook please???

[BlueCactus]

Got to admit, you've lost me with that QueryTable too.

But in principle, those Public variables ought to stay the course. Maybe try putting a couple of MsgBoxes in the start of Macro1 to verify their contents? Could be that their values are not getting set to what you expect.

[anusha]

Thanks for the suggestion ... but it is not working...

These are the global variables it needs to run the macro. I get wrong number of arguments or invalid property assignment.

Call Macro1(varDate1, varSeries, varItem, varTable)

[mvidas]

Something you may want to try is what was suggested earlier, a msgbox to test the variables.. After the "strSQL = ..." line, put[vba]MsgBox strSQL[/vba] for testing purposes to verify the variables are being transferred. If it looks correct, then the problem isn't with Call but with the macro. If you don't see the variable data in the sql statement then you know its a problem with the variables.

Also, you don't really need the call statement, you can just use[vba]Macro1[/vba], or if the variables dont work in the msgbox above and you use the argument method, you would use[vba]Macro1 varDate1, varSeries, varItem, varTable[/vba]If you do end up using the variable argument way, you'll need to change the sub declaration line to[vba]Sub Macro1(varDate1 As String, varSeries As String, varItem As String, varTable As String)[/vba]Matt

[anusha]

Matt, I tried your suggestion ... passing the variables and as always the query is running correctly. What I feel is that it does not know how to get back to sheet Analysis D10..

And se per the view.jpg ... you will see that E10 thru F10 needs info from the query which are two dates and it is not comming back...

Any other ideas?

[mvidas]

How about changing[vba]Destination:=Range("D10"))[/vba]to[vba]Destination:=Sheets("Analysis").Range("D10"))[/vba]Matt

[anusha]

Did that and also added this
Application.ScreenUpdating = True and still not result.... View the file and delete please...

Also... when you first open it... the drop down combobox does not default...
I have to click on another sheet and come back to Analysis to have it filled up.

Any fix?

[Norie]

Where have you declared the public variables?

Are they declared in the userform's module?

If they are, they shouldn't be, they should be declared in their own module.

[anusha]

They have been declared in module4 and not in the form section...

[mvidas]

Not sure why I didn't think of it before!

Change "With ActiveSheet.QueryTables.Add..." to "With Sheets("Analysis").QueryTables.Add, and make sure D10 still has sheets("analysis") before it.

Also, why are your public variables in 4, and not in 1? You're not using 4 for anything, you can just copy the variables to the top of module 1 and remove 4.

Matt

[anusha]

Nooop ....