Looping SendKeys with a variable Max

[chaz_nbs]

I am trying to loop sendkeys based on the number of lines in a transaction. I connect to an sql database, count the lines in a specific ticket and loop that many times. Only problem is I cannot get the darn thing to loop to move through the ticket. it tries to add a new item. If I specify the number of time to move it works fine but I need it to run with a varying amount of lines.

Any help will be much appreciated. Thank you



QUOTE = inputbox("Please enter the Quote Number:", "Quote Number")

SQL_STD_ORDERS = "SELECT TKT_NO FROM [DEMOLAWN].[dbo].[VI_PS_DOC_HDR] where TKT_NO = '"&QUOTE"'"

LINES = "SELECT COUNT(*) FROM DB.dbo.PS_DOC_LIN where TKT_NO=QUOTE"

WshShell.AppActivate "Touchscreen Ticket Entry"
WshShell.SendKeys "%u"
WshShell.SendKeys QUOTE
WshShell.SendKeys "%r"
WshShell.SendKeys "%M"

sqlrs.open SQL_STD_ORDERS, dbconnection

for X = 1 to LINES
WshShell.SendKeys "{UP X}"
WSCRIPT.SLEEP 2000
WshShell.SendKeys "{F10 2}"
next

[the_cat]

I don't know about the SendKeys part, but there are a couple of typos in the script. This line should be:

SQL_STD_ORDERS = "SELECT TKT_NO FROM [DEMOLAWN].[dbo].[VI_PS_DOC_HDR] where TKT_NO = '" & QUOTE & "'"

And this line I think should be:

LINES = "SELECT COUNT(*) FROM DB.dbo.PS_DOC_LIN where TKT_NO='" & QUOTE & "'"

However your main problem is that LINES is a string - a SQL statement, not a number. You need to run the SQL statement against your SQL table and ensure that it's returning an integer.
So something like:

LINES = "SELECT COUNT(*) FROM DB.dbo.PS_DOC_LIN where TKT_NO='" & QUOTE & "'"
sqlrs.open LINES, dbconnection
intLINES = CInt(sqlrs.Fields(0).Value)
sqlrs.Close
sqlrs.open SQL_STD_ORDERS, dbconnection
For X=1 To intLINES
REM SendKeys etc. etc.
Next