static variables in visual basic 6

[aggeliki23]

i'm gonna use an sql variable which type is string to determine in my code my sql query. i' m trying to declare it static in general declaration:
static sql as string
but when i push the start button to run it causes me a compile error invalid outside procedure. How can i declare it? I want it to be static not loose its value.

Thank you
very much

 

[TheAceMan1]

How are ya aggeliki23 . . .

In the declaration section of a module try a [blue]public constant[/blue] instead:

[blue]Public Const [purple]Test[/purple] As String = "Now is the time for all good men " & _
                              "to come to the aid of their countrymen."[/blue]

See Ya! . . . . . .

 

[aggeliki23]

fine thank u!! and u?

i tried it but my sqlquery is like this

sql = "select Products.ProdName," & _
"OrderDetails.ProdPrice,OrderDetails.OrderQuantity," & _
"OrderDetails.ProdPriceWithFPA,OrderDetails.ProdSumPrice " & _
"from OrderDetails,products " & _
"where ((orderDetails.OrderID='" & rsOrd!OrderID & "')" & _
" and (OrderDetails.ProdID=Products.ProdID));"

because firstly i display values of fields from table orders and under of it i have a grid to display the sql query. The grid displays the orderDetails from orders for example the products of the concrete order something like invoice if you understand me.
So I broke the query like this:
Public Const sql As String = "select Products.ProdName," & _
"OrderDetails.ProdPrice,OrderDetails.OrderQuantity," & _
"OrderDetails.ProdPriceWithFPA,OrderDetails.ProdSumPrice " & _
"from OrderDetails,products " & _
"where ((orderDetails.OrderID='"

in my module and in the form load i said this:

sqlOrdDet = sqlOrdDet & rsOrd!OrderID & "')" & _
" and (OrderDetails.ProdID=Products.ProdID));"

but when i run it, occurs me compile error
Assignment to constant not permited
Do you have any idea?

 

[TheAceMan1]

liki23 . . .

I forgot about referencing form controls . . .

In that case the constant has to go in the [blue]Declaration Section[/blue] of the forms code module along with changing [blue]Public[/blue] to [blue]Private[/blue]:

[blue]Private Const qryStatic As String = "SELECT Products.ProdName, " & _
                                           "OrderDetails.ProdPrice, " & _
                                           "OrderDetails.OrderQuantity, " & _
                                           "OrderDetails.ProdPriceWithFPA, " & _
                                           "OrderDetails.ProdSumPrice " & _
                                    "FROM OrderDetails, Products " & _
                                    "WHERE ((orderDetails.OrderID='" & rsOrd!OrderID & "') and " & _
                                           "(OrderDetails.ProdID=Products.ProdID));"[/blue]

See Ya! . . . . . .

 

[aggeliki23]

I can't do it because in my sql query in criteria the ordetDetails.OrderID gets value from rsOrd!OrderID
rsOrd is a recordset from table orders
Well, i must firstly open the recordset rsOrd and then to set the value of sql an finally to open the sql query with rsOrdDet recordset which represents the OrderDetails table

 

[Golom]

You can't set it as a constant because the expression involves program variables (e.g. rsOrd!OrderID) which must be resolved at run time. Constants are resolved at compile time and cannot include variables or expressions to be evaluated in their definition.

You will need to do something like

[COLOR=black cyan]' General declarations[/color]
Public qryStatic As String

[COLOR=black cyan]' In Form_Load[/color]
qryStatic = "SELECT Products.ProdName, " & _
            "OrderDetails.ProdPrice, " & _
            "OrderDetails.OrderQuantity, " & _
            "OrderDetails.ProdPriceWithFPA, " & _
            "OrderDetails.ProdSumPrice " & _
            "FROM OrderDetails, Products " & _
            "WHERE ((orderDetails.OrderID='" & rsOrd!OrderID & "') and " & _
            "(OrderDetails.ProdID=Products.ProdID));"

 

[aggeliki23]

All right, so with this code, does the qryStatic keep its value in every point of code? For example in command button click event if I open this query with a newone recordset?
for example
Private Sub NextCmd_Click()
rs.open qryStatic
rs.MoveNext
end sub

Will it display the next record or not?

 

[Golom]

Not sure what you mean. You are using qryStatic to define the contents of the recordset "rs" ... not a particular record in the recordset.

The real issue (for me at least) is that defining qryStatic only once will pick up the value of rsOrd!OrderID that existed at the time that the variable qryStatic had the string assigned to it. Given that (I assume) rsOrd!OrderID is a field in a recordset, the string will always incorporate that initial value if you don't redefine the string when the current record in rsOrd changes.

 

[aggeliki23]

All right, thank you very much. I just wanted to have more less code but i tried it and i can see that is better to redefine the string.

 

[TheAceMan1]

aggeliki23 . . .

Apparently I'm misinterpreting something here. It appears the recordset is dependent on itself. In any case what I never understood you point of using static . . .

See Ya! . . . . . .

 

[aggeliki23]

Firstly, thank all of you help me.
Every record of orders has many details as the products that a customer orders.
You can understand that when i go to the next order record
the rsOrd!OrderID value changes. So and the orderDetails.
I use this sql query when the form loads and then in a command button going to next record so i have to display the next record from orders and its details.
The what I wanted to do, was not to write 2 times the sql query to keep its value, to write more less code. But i realized that i can't do this. So i let it as it is. I don't know how to explain it to you better. I hope you understood me.

 

[formerTexan]

I'm not exactly sure I clearly understand your problem, but I will try:

When you mention "the SQL query to keep its value", are you refering to the records it returns so that you can reference the same recordset with the command button?

If this is your issue, then create a recordset in the Declaration Section of the forms code module and load the result of the SQL statement into this recordset. Then you can reference it from the command button.

Hope this helps,
Cheers, Bill