By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,036 Members | 960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,036 IT Pros & Developers. It's quick & easy.

Setfocus on subform

P: 67

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,
May 31 '14 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 15k+
P: 31,769
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.
Jun 1 '14 #2

P: 67
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,
Jun 2 '14 #3

Expert Mod 15k+
P: 31,769
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.
Jun 2 '14 #4

P: 67
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.
Expand|Select|Wrap|Line Numbers
  1.         runBAT ("Download_LastYear_Request_Expenditure\Download_LastYear_Request_Expenditure_run.bat")
  2.         .Caption = lblDownloadLastYearBudget()
  3.         .Enabled = True
  4.     End With
  6.     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, 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,
Jun 4 '14 #5

Expert Mod 15k+
P: 31,769
"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.
Jun 4 '14 #6

P: 67
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnFillWithNewRevision_Click()
  2.     On Error GoTo err_handler
  3.     If Me.Dirty Then
  4.         Me.Dirty = False
  5.     End If
  7.     With Me.btnFillWithNewRevision
  8.         .Caption = lblPleaseWait()
  9.         .Enabled = False
  10.         runBAT ("Download_LastYear_Request_Expenditure\Download_LastYear_Request_Expenditure_run.bat")
  11.         .Caption = lblDownloadLastYearBudget()
  12.         .Enabled = True
  13.     End With
  15.     'Sleep (5000)
  16.     Me.Requery
  18. err_handler:
  19.     Exit Sub
  20. 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
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  3. Private Type STARTUPINFO
  4.    cb As Long
  5.    lpReserved As String
  6.    lpDesktop As String
  7.    lpTitle As String
  8.    dwX As Long
  9.    dwY As Long
  10.    dwXSize As Long
  11.    dwYSize As Long
  12.    dwXCountChars As Long
  13.    dwYCountChars As Long
  14.    dwFillAttribute As Long
  15.    dwFlags As Long
  16.    wShowWindow As Integer
  17.    cbReserved2 As Integer
  18.    lpReserved2 As Long
  19.    hStdInput As Long
  20.    hStdOutput As Long
  21.    hStdError As Long
  22. End Type
  25.    hProcess As Long
  26.    hThread As Long
  27.    dwProcessID As Long
  28.    dwThreadID As Long
  29. End Type
  31. Private Declare PtrSafe Function WaitForSingleObject Lib "kernel32" (ByVal _
  32.    hHandle As Long, ByVal dwMilliseconds As Long) As Long
  34. Private Declare PtrSafe Function CreateProcessA Lib "kernel32" (ByVal _
  35.    lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
  36.    lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
  37.    ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
  38.    ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
  39.    lpStartupInfo As STARTUPINFO, lpProcessInformation As _
  42. Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal _
  43.    hObject As Long) As Long
  45. Private Const NORMAL_PRIORITY_CLASS = &H20&
  46. Private Const INFINITE = -1&
  48. Public Sub ShellWait(cmdline$)
  49.    Dim proc As PROCESS_INFORMATION
  50.    Dim start As STARTUPINFO
  51.    Dim ReturnValue As Integer
  53.    ' Initialize the STARTUPINFO structure:
  54.    With start
  55.         .cb = Len(start)
  56.         .wShowWindow = vbHide
  57.         .dwFlags = &H1&
  58.     End With
  59.    ' Start the shelled application:
  60.    ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
  61.       NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
  63.    ' Wait for the shelled application to finish:
  64.    Do
  65.       ReturnValue = WaitForSingleObject(proc.hProcess, 0)
  66.       DoEvents
  67.       Loop Until ReturnValue <> 258
  69.    ReturnValue = CloseHandle(proc.hProcess)
  70. 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,

Attached Images
File Type: jpg 1.jpg (38.5 KB, 267 views)
Jun 5 '14 #7

Expert Mod 2.5K+
P: 3,488

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:

Expand|Select|Wrap|Line Numbers
  1. Me.SubFormName.Form.Requery
That's the only thing I can think of that might help with this. Let us know if this hepps.
Jun 10 '14 #8

Expert Mod 15k+
P: 31,769
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.
Jun 22 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.