SQL Array question 2

[dmaranan]

I run an SQL from a form that a user can enter 6 different value for 6 different groups. The problem is I have to code the SQL 6 different times, which seems like a waste. Is there a way to use an array that would allow me to cycle through the SQL using some type of counter for each different value?

So instead of (I omitted the where clause, but you get the idea):

SQL = "UPDATE bbtl_rec SET tltrb = " & tltrb.value1 & " "
SQL = "UPDATE bbtl_rec SET tltrb = " & tltrb.value2 & " "
SQL = "UPDATE bbtl_rec SET tltrb = " & tltrb.value3 & " "
SQL = "UPDATE bbtl_rec SET tltrb = " & tltrb.value4 & " "
SQL = "UPDATE bbtl_rec SET tltrb = " & tltrb.value5 & " "
SQL = "UPDATE bbtl_rec SET tltrb = " & tltrb.value6 & " "

I do
(Cycle through array until done)
SQL = "UPDATE bbtl_rec SET tltrb = " & array.value & " "

 

[GShen]

You an perform a DO WHILE..... LOOP adding 1 to a counter inside the loop and appending this counter to tltrb.value.

Respond back if you need more help.

Remember when... everything worked and there was a reason for it?

 

[dmaranan]

I tried the appending idea but I'm not sure how to append a number to the form name/value.

For example, I have objects tltrb1.value, tltrb2.value, tltrb3.value and so forth. Basically I'd have to append the somethign like tltrb(COUNTER).value which doesn't work.

Can you show me how to do this?

 

[RoyVidar]

Yeah, thought there was something mystic about the first post;-)

[tt]dim lngCount as long
for lngCount = 1 to 6
SQL = "UPDATE bbtl_rec SET tltrb = " & me("tltrb" & lngCount).value
' your favourite way of executing the sql
next lngCount[/tt]

Roy-Vidar

 

[GShen]

I think it is something like this
"tltrb" & counter & ".value"

Let me know how you make out

Remember when... everything worked and there was a reason for it?

 

[GShen]

roy,
The for next loop is better. Trying to solve a problem of my own and not thinking too clearly. Sorry.
trying to pass a value back from a stored procedure calling it from VB. I have done it before, I can't remember where.


Remember when... everything worked and there was a reason for it?