Unable to link to my data

[bookworm72]

I am just learning, and am having troubles getting my macro to link to data. Can someone see where I went wrong? Plus I get stuck on line 75 using the RS.EOF

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
Dim Sys as Object, Sess as Object
Dim Conn as object, RS as Object, DB as String, Sql as string, DLR as string

Set Sys = CreateObject("Extra.system")
Set Sess = Sys.activesession


Set Conn = Createobject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")

DB = "C:\Junk\AIMSIN.MDB"
Sql= "Select * from AIMS Close by DLR_NBR;"

conn.open "Provider=Microsoft.jet.oledb.4.0;data source=" & DB & ";User Id=admin;password=;"



' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 1250 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session Object
Dim Sess0 As Object

Dim MyScreen As Object
Dim MyArea As Object
Dim TotCL as Integer
Dim RunCL as integer
Dim LnCL as integer
Dim ROWX as Integer
Dim Status as string
Dim OneOne as string


Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Do Until RS.EOF
DLR = Rs.Fields("Dealer")
RunCL = 0
ROWX = 13
OneOne = "N"
' This section of code contains the recorded events
Sess0.Screen.moveto 04,13
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.screen.sendkeys("<EraseEOF><Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Putstring DLR
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.moveto 05,14
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.screen.sendkeys("P<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' Check the status in AIMS in order to be able to clear the credit lines
' If unable to change because of current status, program will go to 1 - c
' to change the status to open

Set MyScreen = Sess0.Screen

Set MyArea = MyScreen.area(04,65,04,68)
MyArea.Select
Status = MyArea

' This needs to be modified to check for all invalid status codes.

If Status like "HOLD" then
OneOne = "Y"

Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("1")
Sess0.Screen.moveto 23,66
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("C")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Putstring DLR
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


Sess0.Screen.moveto 03,67
Sess0.Screen.sendkeys("open")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.moveto 04,79
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.sendkeys("3")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Enter>")

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("5")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.moveto 23,66
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("C")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Putstring DLR
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.moveto 05,14
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.screen.sendkeys("P<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


Else
end if



'Get the total current credit line

Set MyScreen = Sess0.Screen

Set MyArea = MyScreen.area(07,17,07,24)
MyArea.Select
TotCL = MyArea

If TotCL > 0 then


Do until TotCL = RunCL

Sess0.Screen.moveto ROWX,16
Set MyArea = Myscreen.area(ROWX,16,ROWX,23)
MyArea.Select
LnCL = MyArea

If LnCL > 0 Then
Sess0.Screen.moveto ROWX,09
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.sendkeys("C")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.sendkeys("0.0<EraseEOF>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Else
End if
ROWX = ROWX + 1
RunCL = RunCL + LnCL
Loop

Sess0.Screen.moveto 07,17
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.sendkeys("0.0<EraseEOF>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.screen.sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)



'Invalid Distributor number check
CurrentCol = MyScreen.Col

Do until CurrentCol <> 28

CurrentRow = Myscreen.row

Sess0.Screen.moveto CurrentRow,9
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.sendkeys("D")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.screen.sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
CurrentCol = MyScreen.Col
Loop


Sess0.Screen.Sendkeys("<Pf12>")


System.TimeoutValue = OldSystemTimeout


Else
End if

'checks to see if we had to change the status code in order to modify the credit lines
'if so go back and close the status
'You could have this section change all to a close status here, but then you are doing
'additioanl screan flips. It would be better to just run a seperate macro that changes
'the account status to what ever you want

If OneOne = "Y" then



Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("1")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.moveto 23,66
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("C")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Putstring DLR
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


Sess0.Screen.moveto 03,67
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.sendkeys("CLOS")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("5")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.moveto 23,66
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("C")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Else
End if


RS.Movenext
Loop
End Sub

 

[SkipVought]

Hi,

You never OPEN the recordset.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bookworm72]

Thanks