Copying null values from Access

[papote]

I am trying to copy values from an Access table to a program.

With the following code, I notice that if a field is empty or null the variable is set with the previous field’s value.

 

expandcollapse popup

Opt("MouseCoordMode", 2) ;1=absolute, 0=relative, 2=client
Opt("SendKeyDelay", 100) ; milliseconds

HotKeySet("{ESC}", "Terminate")

$it = InputBox("Loop","")

$AccessWindowTitle = "Microsoft Access - Accounts 2017 : Database (Access 2007 - 2010)"
$MyWindowTitle = "MyWindow Title"


if $it <= 0 then
    Exit
EndIf

For $i = 1 to $it Step 1

    if WinExists($AccessWindowTitle) Then
        $hWnd = WinWait($AccessWindowTitle, "" ,5)
        Sleep(100)

        ControlSend ($hWnd, "", "OGrid3", "^c")
        Global $oacc = clipget()
        ControlSend ($hWnd, "", "OGrid3", "{TAB}")

        ControlSend ($hWnd, "", "OGrid3", "^c")
        Global $oname = clipget()
        ControlSend ($hWnd, "", "OGrid3", "{TAB}")

        ControlSend ($hWnd, "", "OGrid3", "^c")
        Global $odir1 = clipget()
        ControlSend ($hWnd, "", "OGrid3", "{TAB}")

        ControlSend ($hWnd, "", "OGrid3", "^c")
        Global $ofac = clipget()
        ControlSend ($hWnd, "", "OGrid3", "{TAB}")
        

        if WinExists($MyWindowTitle) Then
            $hWnd = WinWait($MyWindowTitle, "" ,5)
            WinActivate($hWnd)
            
            Send ("{F8}")
            Send ("N")
            Send($oacc)
            Send ("{Enter}")
            Sleep (200)
            Send ("{Enter}")
            Send($oname)
            Send ("{Right}")
            Send ("{Space}")
            Send ("{Enter}")
            Send ("{Enter}")
            Send($odir1)
            Send ("{Enter}")
            Send ("{Enter}")
            Send($ofac)
            Send ("{Enter}")
            Send ("{F12}")

        EndIf

    endif

Next


Func Terminate()
    Exit
EndFunc   ;==>Terminate

How can avoid this?

[mLipok]

Did you try ADO? 

[papote]

ADO as in  ActiveX Data Objects? No. The program has a non accessible proprietary database. I have to do the data entry through a macro.

Edited June 7, 2016 by papote

[MichaelHB]

papote,

There is better ways to automate that task, just research and you will find it. The last window ($MyWindowTitle) you are using "send" commands, as i cant know what software is, also make sure that this is the best way (UI Automation is always a option). Meanwhile put a ClipPut("") before each copy command (^c).

[papote]

I put ClipPut("") before each ControlSend ($hWnd, "", "OGrid3", "^c"), but it does not save the text to clipboard at all.

[MichaelHB]

Check if the ControlSend returns 1. Have you tried something like this?

expandcollapse popup

#include <Array.au3>

Opt("MouseCoordMode", 2) ;1=absolute, 0=relative, 2=client
Opt("SendKeyDelay", 100) ; milliseconds

HotKeySet("{ESC}", "Terminate")

Global $aArray[4]

$it = InputBox("Loop","")

$AccessWindowTitle = "Microsoft Access - Accounts 2017 : Database (Access 2007 - 2010)"
$MyWindowTitle = "MyWindow Title"


if $it <= 0 then
    Exit
EndIf

For $i = 1 to $it Step 1

    if WinExists($AccessWindowTitle) Then
        $hWnd = WinWait($AccessWindowTitle, "" , 5)
        Sleep(100)

        For $i = 0 To 3
            ClipPut("")
            If Not ControlSend($hWnd, "", "OGrid3", "^c") Then Exit ConsoleWrite("Cant copy, check $hWnd and controlID" & @CRLF)
            $aArray[$i] = ClipGet()
            ControlSend($hWnd, "", "OGrid3", "{TAB}")
        Next
        
        _ArrayDisplay($aArray)


        if WinExists($MyWindowTitle) Then
            $hWnd = WinWait($MyWindowTitle, "" ,5)
            WinActivate($hWnd)

            Send ("{F8}")
            Send ("N")
            Send($aArray[0])
            Send ("{Enter}")
            Sleep (200)
            Send ("{Enter}")
            Send($aArray[1])
            Send ("{Right}")
            Send ("{Space}")
            Send ("{Enter}")
            Send ("{Enter}")
            Send($aArray[2])
            Send ("{Enter}")
            Send ("{Enter}")
            Send($aArray[3])
            Send ("{Enter}")
            Send ("{F12}")

        EndIf

    endif

Next


Func Terminate()
    Exit
EndFunc   ;==>Terminate

Exit

 

[papote]

I don't understand, but putting clipput('') BEFORE copying returns empty.

[MichaelHB]

papote,

Did you try to put some delay in the copy part? Like:

For $i = 0 To 3
    ClipPut("")
    Sleep(500)
    If Not ControlSend($hWnd, "", "OGrid3", "^c") Then Exit ConsoleWrite("Cant copy, check $hWnd and controlID" & @CRLF)
    $aArray[$i] = ClipGet()
    Sleep(500)
    ControlSend($hWnd, "", "OGrid3", "{TAB}")
Next

Play with the delay between the ClipPut and ClipGet to see if this fix. And as i said before there is other ways to automate this task, search for Access and you will see many topics about it.