VB6 Data Report Issue using the Data Environment and SQL

[h4fod]

Hi
I want to base a VB6 Data Report on an SQL Query which will retreive specific data from an Access 97 table. The context is 'Stock Control' and I have reduced the problem to the code below which will simply produce a stock list based upon stock search criteria (clearly at present all stock is retreived - no serach criteria given) using SQL literal. The error message 'Wrong Number of Arguments' appears in the 'DataEnvironment1.Pupils strSQL' line of the program when run.

'Stock' is a named command set up in the data environment which returns field data (Stock_ID, Qty_In_Stock' etc against the stock table. adCmdtable is the command type property assigned to this command.

Is my use of the Data Environment correct when attempting to populate DR fields this way. the Data report's data source is Data Environment 1?

Hope you can help. Suspect this could be a trivial issues but FAQ' s fail to clarify.

[code/]
Private Sub cmdPrint_Click()

Dim strSQL As String

strSQL = "SELECT * FROM tblStock"

DataEnvironment1.Stock strSQL


drStockSlip.Show

End Sub

[/code]

 

[Andrzejek]

Do you have a Sub called Pupils in DataEnvironment1 that accepts a String as the only argument? Something like:

Public Sub Pupils(strMySQL As String)
...
End Sub

Have fun.

---- Andy

 

[h4fod]

Hi
The answer is no to the question. What code would I need to put in the data Environment1 subroutine suggested?

Many thanks for your resply

 

[Andrzejek]

You stated: "The error message 'Wrong Number of Arguments' appears in the 'DataEnvironment1.Pupils strSQL' line of the program when run.?

And in you example you have:
[tt]
...
DataEnvironment1.Stock strSQL
...
[/tt]
So you either crash on [tt]DataEnvironment1[blue].Pupils[/blue] strSQL[/tt] or on [tt]DataEnvironment1[blue].Stock[/blue] strSQL[/tt], or both. Which one is it?
And do you have procedures called Pupils and/or .Stock in your DataEnvironment1 because it looks like that's the subs you call.


Have fun.

---- Andy

 

[h4fod]

Hi
See your confusion - sorry!. 'DataEnvironment1.Pupils strSQL' should read 'DataEnvironment1.Stock strSQL'. Trying to resolve problem with another dB!

There is a data Environment command called Stock' in DataEnvironment1 and against this 'tree' in the design Environment all the fields associated with the stock table are correctly listed as per the Db. I have checked the command type: adCmdTable and the Lock type is 'optimistic' and a 'Client-Side' cursor is used.

So, if I created a Stock Sub, passing the SQL literal as suggested, what code would I need to put into the sub? Will I need to invoke the sub from the 'Click Event' sub of the Print report button my form? Little confused here sorry.

many thanks again

 

[Andrzejek]

You would have to look into your code, because if you get an error 'Wrong Number of Arguments' and it points to a procedure called Stock, look in there and see how many / what kind (type) of arguments it expects.

If you have something like:

Public Sub Stock(strSQL As String, datDate As Date)
....
End Sub

and you call it this:

....
DataEnvironment1.Stock strSQL
....

You DO provide first arument (a string) but you DO NOT provide the second one(a Date) and you should get 'Wrong Number of Arguments' error.

Have fun.

---- Andy

 

[h4fod]

Hi
Now checked / re checked code, created global variable as well. Created a new command 'AllStock' as part of Data Environment.

Private Sub cmdPrint_Click()

Dim strSQL As String
g_strAllStock = "SELECT * FROM tblStock"
DataEnvironment1.AllStock g_strAllStock
End Sub

g_strAllStock contains correct SQL.


Sub called:

Public Sub AllStock(g_strAllStock As String)

MsgBox g_strAllStock 'test only

drRewardsSlip.Show

End Sub

At the code line where sub is called 'AllStock' is highlighted by the interpreter and 'Wrong number of arguments / invalid property assignment' dialogue displayed.

So frustrating!

http://www.tipmaster.com/images/sad.gif

Thanks again

 

[Andrzejek]

Did you try:

Private Sub cmdPrint_Click()
[green]
'Dim strSQL As String[/green]
g_strAllStock = "SELECT * FROM tblStock"[blue]
Call [/blue]DataEnvironment1.AllStock[blue]([/blue]g_strAllStock[blue])[/blue]
End Sub

BTW - "created global variable as well" are you talking about [tt]g_strAllStock[/tt] as global variable? If so, bad idea to name global and (very) local variable with the same name, like in:

Public Sub AllStock([red]g_strAllStock[/red] As String)

Have fun.

---- Andy

 

[h4fod]

Hi Andy
Perhaps my confusion stems from the fact that I am a 'beginner' in relation to the use of Data Reports and the Data Environment. Given the command below, the Data Environment1 object is a command in the Data Environment and strSQL acts as a 'filter' which returns records from the Stock Table in Access which is set as a property in the Data Environment. Then the Data Report bound to Data Environment 1 will contain field data in text boxes for these records only.

So, need for a sub is unecessary / misleading. It would only instigate the drReport.show code anyway.

Is my approach / understanding correct Andy. Or, I am I way offbeat here.

DataEnvironment1.Stock strSQL