Setfocus on subform

[sophannaly]

Hi,

In my access application, there are four main forms and each form has tab control with other two sub forms.

There is data transmission between access and other database, which subform will call batch file to run to update data on access table. During running batch file, user can click on any form. After batch file finish running, I have place me.requery.

Could anybody tell me what is me refer to? is it refer to the form in vba code or the form the get focus? I have this problem because after calling me.requery from that sub form, the new data didn't show up, I have to set focus on other form then go back to it again, then it will shows new data.

Best regards,
Sophanna

[NeoPa]

Me always refers to the object (which can be a form or a report.) associated with the module the reference is in.

IE. If your main form is called frmMain and you have an associated module with that (Form_frmMain) then any reference within that module will refer to that specific form.

[sophannaly]

Hi Neo,

Would you mind to explain in an easy way cuz i'm not really understand this? On my subform, I have button which will call module code to run. Does this module is what you mean?

Best regards,
Sophanna

[NeoPa]

I think so. It's hard for me to understand you too.

Event procedures (EG. Control_AfterUpdate() or Button_Click()) are always created for you automatically in the object's associated module.

[sophannaly]

Hi Neo,

I'm really sorry for that. I have this problem cuz in my form, on click button event, I have code which will call batch file to run, and this batch file will erase all data from existing ms access table, then import data from Postgresql DB to this access table. And i'm sure that after this batch file finish running, it means that data is already wrote to access table.

        runBAT ("Download_LastYear_Request_Expenditure\Download_LastYear_Request_Expenditure_run.bat")
        .Caption = lblDownloadLastYearBudget()
        .Enabled = True
    End With
 
    Me.Requery

But it just so strange, for me.requery it will shows as #Deleted for all record in continuous form. I have to go to other form then come back to this form again, then it will show data again.

So I think about solution as first set focus to other form (its mainform), then set focus back to this form(subform). But there is nothing change.

And when I use msgbox me.name, it shows this subform's name correctly.

So I think that during this VBA code run, we can use mouse to click on any other form, but for VBA code, it still remaining running on the form that we have call it to run.

The above is the reason why I came up with this question.

Best regards,
Sophanna

[NeoPa]

Sophanna:
"But it just so strange, for me.requery it will shows as #Deleted for all record in continuous form."

I think that's not quite right. I would expect that if you were using Me.Refresh. You should never see #Deleted after a Me.Requery. Please check this before we proceed.

[sophannaly]

Hi NeoPa,

Please a look at my screen shot.This my continuous form.
Below is the code that I use to call on button to run batch file

Private Sub btnFillWithNewRevision_Click()
    On Error GoTo err_handler
    If Me.Dirty Then
        Me.Dirty = False
    End If
 
    With Me.btnFillWithNewRevision
        .Caption = lblPleaseWait()
        .Enabled = False
        runBAT ("Download_LastYear_Request_Expenditure\Download_LastYear_Request_Expenditure_run.bat")
        .Caption = lblDownloadLastYearBudget()
        .Enabled = True
    End With
 
    'Sleep (5000)
    Me.Requery
 
err_handler:
    Exit Sub
End Sub

Before I use code that you provide on calling batch file and wait, but it doesn't help, so I tried with code that I got from Microsoft website, but it also doesn't help.
Below is the module code that I use to call batch file

Option Explicit
 
Private Type STARTUPINFO
   cb As Long
   lpReserved As String
   lpDesktop As String
   lpTitle As String
   dwX As Long
   dwY As Long
   dwXSize As Long
   dwYSize As Long
   dwXCountChars As Long
   dwYCountChars As Long
   dwFillAttribute As Long
   dwFlags As Long
   wShowWindow As Integer
   cbReserved2 As Integer
   lpReserved2 As Long
   hStdInput As Long
   hStdOutput As Long
   hStdError As Long
End Type
 
Private Type PROCESS_INFORMATION
   hProcess As Long
   hThread As Long
   dwProcessID As Long
   dwThreadID As Long
End Type
 
Private Declare PtrSafe Function WaitForSingleObject Lib "kernel32" (ByVal _
   hHandle As Long, ByVal dwMilliseconds As Long) As Long
 
Private Declare PtrSafe Function CreateProcessA Lib "kernel32" (ByVal _
   lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
   lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
   ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
   ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
   lpStartupInfo As STARTUPINFO, lpProcessInformation As _
   PROCESS_INFORMATION) As Long
 
Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal _
   hObject As Long) As Long
 
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&
 
Public Sub ShellWait(cmdline$)
   Dim proc As PROCESS_INFORMATION
   Dim start As STARTUPINFO
   Dim ReturnValue As Integer
 
   ' Initialize the STARTUPINFO structure:
   With start
        .cb = Len(start)
        .wShowWindow = vbHide
        .dwFlags = &H1&
    End With
   ' Start the shelled application:
   ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
      NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
 
   ' Wait for the shelled application to finish:
   Do
      ReturnValue = WaitForSingleObject(proc.hProcess, 0)
      DoEvents
      Loop Until ReturnValue <> 258
 
   ReturnValue = CloseHandle(proc.hProcess)
End Sub
 

I really don't understand what is the problem, do you think could it be the problem between access and batch file?

Best regards,
Sophanna

[twinnyfo]

Sophanna,

Is your button "btnFillWithNewRevision" by any chance on the Main Form and the data that shows "#Deleted" on a subform? As NeoPa said, with a Requery this is odd to see "#Deleted". However, if your data is on a subform, you may need to requery that subform:

Me.SubFormName.Form.Requery

That's the only thing I can think of that might help with this. Let us know if this hepps.

[NeoPa]

Hi Sophanna.

Sorry for the long delay. I've been very busy with work commitments to the extent that I haven't visited Bytes for a week or more. Very unusual for me.

Anyway, to get to your problem, your last post (#7) states that the code used to run the batch file is in the second block of code. It seems that this is not exactly true. The code to run the batch file uses a procedure called runBat(). This is not included in the second block of code. What is included is a version of the ShellWait() Function that is different from the one I posted.

Please test the code with the posted version of the ShellWait() function to see if this is where the problem lies. I would certainly only expect to see the results you've described if the batch file had not completed by the time you run the Me.Requery code.