Multiple Issues

[steffent]

Hi,

I'm having a lot of issues with macro scheduler.

#1) When I run the following code



Let>mymsg=Hello World!
MessageModal>mymsg
Let>strtest = "Test String Test String...."
MessageModal>strtest
Let>querystring = "select ItemGroup_C1 as Part, ItemGroup_C2 as Name from ItemGroupTbl where ItemGroupID = 16;"
MessageModal>querystring



I get a dialog that says "Hello World!", "strtest", and "querystring. Why doesn't it say "Test String Test String" and the query? I can't figure it out.

#2) I have the following VB code defined



VBSTART

Dim MyDB
Dim rsRTN

Sub OpenRecordSet
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "DSN=SQLServerDSN;Uid=sa;Pwd=****"
End Sub

Sub CloseDB
MyDB.Close
End Sub

Sub MoveNext
rsRTN.MoveNext
End Sub

Function ExecuteQuery(queryStr)
MsgBox queryStr
set rsRTN = MyDB.Execute(queryStr)
rsRTN.MoveFirst
End Function

Function GetNextRecord(recName)
If Not rsRTN.EOF then
GetNextRecord = rsRTN.Fields(recName)
else
MsgBox recName
end if
End Function

VBEND



but for some reason I'm getting an error saying rsRTN is not defined in GetNextRecord, which I think is because the ExecuteQuery method fails. But I don't know why or what I'm doing wrong. I just want one method that I can pass the query string to and have it execute and return. Then I have the GetNextRecord method that iterates over the results.

Any help would be appreciated.

Here's the full code.



VBSTART

Dim MyDB
Dim rsRTN

Sub OpenRecordSet
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "DSN=SQLServerDSN;Uid=sa;Pwd=****"
End Sub

Sub CloseDB
MyDB.Close
End Sub

Sub MoveNext
rsRTN.MoveNext
End Sub

Function ExecuteQuery(queryStr)
MsgBox queryStr
set rsRTN = MyDB.Execute(queryStr)
rsRTN.MoveFirst
End Function

Function GetNextRecord(recName)
If Not rsRTN.EOF then
GetNextRecord = rsRTN.Fields(recName)
else
MsgBox recName
end if
End Function

VBEND

VBRun>OpenRecordSet
Let>mymsg=Hello World!
MessageModal>mymsg
Let>strtest = "Test String Test String...."
MessageModal>strtest
Let>querystring = "select ItemGroup_C1 as Part, ItemGroup_C2 as Name from ItemGroupTbl where ItemGroupID = 16;"
MessageModal>querystring
VBEval>ExecuteQuery(queryString)
VBEval>GetNextRecord("Part"),part
Send>%part%
Press TAB
VBEval>GetNextRecord("Name"),name
Send>%name%
Press TAB

[Marcus Tettmar]

1. Your code should be:

Let>mymsg=Hello World!
MessageModal>mymsg
Let>strtest=Test String Test String....
MessageModal>strtest
Let>querystring="select ItemGroup_C1 as Part, ItemGroup_C2 as Name from ItemGroupTbl where ItemGroupID = 16;"
MessageModal>querystring

(Either remove the spaces or use the IGNORESPACES directive.

2.

ExecuteQuery is failing because you are calling it wrongly. Therefore the recordset is never created. queryStr is a variable, so you need to pass it as one. You also need a return var.

VBEval>ExecuteQuery(%queryString%),result

With your code the recordset is never created because you are passing an invalid value to ExecuteQuery.