Access 2010 Event Firing/Ordering Issues

I have a form (frmTST_Maintenance) which adds, changes, deletes and browses records in a table CUS.  Upon opening, I have created a filter that points it to a record in CUS based upon a CUS_ID stored in a static local table.  That static table holds the CUS_ID until/unless a different record is pointed to and operated upon in CUS.

A "Browse" button click in frmTST_Maintenance opens another form -- a popup form -- (frmTST_Browse) using cmdBrowse_Click() event which contains " DoCmd.OpenForm 'fmTST_Browse' " to call the browse form with the maintenance form open/visible in the background.  That browse form allows the user to scan through records in CUS and "select" one of those records.  The selection of a record causes the CUS_ID of that new record to be stored in the static local table.  The browse form then closes leaving the maintenance form which is still visible.

What is supposed to happen is that the maintenance form should be re-reading the local static table to obtain the new CUS_ID, create a new filter, and display the newly browsed/selected record.  For the life of me I cannot get this process to properly occur without artificially/manually "refreshing" the maintenance form via a phony button that I created for that purpose but that I need to manually click in order to get the maintenance form to react to the new filter and display the new record.

The browse button code looks like:

Private Sub cmdBrowse_Click()
   ' call the browse form
   DoCmd.OpenForm "frmTST_Browse"
  ' after returning from the browse form, read the latest value from the local static table
  ' and establish the filter on CUS so as to show the latest selected record ID
   If g_GetSTAValue("STA_CUS_ID") <> 0 Then
      Me.Filter = "[CUS_ID] = " & g_GetSTAValue("STA_CUS_ID")
      Me.FilterOn = True
   End if
End Sub

I cannot get the filter lines to fire AFTER the opening of the browse form.  They want to file BEFORE the line that opens the browse form is executed.  I have confirmed this with msgbox bracketing the filter if/then lines.  If I msgbox before the browse form open line I get (let's say CUS_ID = 30).  If I place another msgbox after the filter lines, it still gives me the CUS_ID = 30 (even though in the browse selection form I selected CUS_ID = 33, and in addition, BOTH msgbox commands fire before the browse open line executes.

I have tried to bracket the browse open and if/then with me.refresh, me.requery, me.repaint, form_current(), form_activate(), form_open() -- everything that I can think of, but I cannot get the filter to take effect AFTER the browse selection without manually clicking other buttons on the maintenance form which then "magically" fires the filtering and the redisplay of the new record.

If anyone out there can tell me what is going on, I would REALLY appreciate it.
Thanks.
Bill
boordwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can try open the form in Modal mode, and then within that form, save the selection's ID into a Global variable, and then in your cmdBrowse_Click event, read that variable's value accordingly.

like:

Private Sub cmdBrowse_Click()
   ' call the browse form
DoCmd.OpenForm "frmTST_Browse", acNormal, , , , acDialog

  ' after returning from the browse form, read the latest value from the local static table
  ' and establish the filter on CUS so as to show the latest selected record ID
   If vSTA_CUS_ID <> 0 Then
      Me.Filter = "[CUS_ID] = " & vSTA_CUS_ID
      Me.FilterOn = True
   End if
End Sub

in a Module of your VBA editor, try add:

Public vSTA_CUS_ID As Long

then finally in form: frmTST_Browse , capture the selected ID value and save into vSTA_CUS_ID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
boordwAuthor Commented:
Before I changed the form to modal and without using a global variable, I simply appended your "acNormal, , , , acDialog" onto the open command and it worked like a champ!

I don't know what the appended parameters are doing, so if there is a quick/easy explanation, that would be great to hear, including why appending these parameters would cause the previously out-of-order commands to fire in the correct order.

Thanks.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
DoCmd.OpenForm method allows users to customize the behaviours of the opened form by passing relevant parameters. For example, it allows users to limit what to be shown, filtering, make it as a dialog form, whether to make it to enter new records, etc.

for a reading pleasure, you can refer to:

DoCmd.OpenForm Method (Access)
https://msdn.microsoft.com/en-us/library/office/ff820845.aspx

DoCmd.OpenForm and its options
http://www.datawright.com.au/access_resources/access_docmd.openform_arguments.htm

I found these 2 articles are quite useful.

and ... glad that I could make some helps here cheers
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

boordwAuthor Commented:
You solved my problem, but those two articles are pretty much stock information that doesn't really tell me why the firing of events occurred (or actually DIDN'T occur) in the order that I placed them in the subroutine and why the method of opening another form would impact the order of events in the calling form.  Maybe there isn't an easy answer to this question.  But thanks for solving my problem.  Sometimes it's just better to get a solution that works and accept it.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok, just for some explanation...

once a form is opened in a dialog mode, it will wait till the form is being closed before continue the process.

just imagine...

As-Is:

sub test()
  ...
  call the form() without dialog mode
  ..
  the scripts continue to run...
  ...
end sub

To-Be:

sub test()
  ...
  call the form() with dialog mode
  ..
  the scripts wait the form to be closed, before continue to run...
  ...
end sub

hope that's clear
0
boordwAuthor Commented:
That does answer it.  Thanks again.

Are you versed in porting an Access 2010 front end/back end over to a SQL Server back end?  I have a major application that I am VERY disappointed in with regard to the Access back end performance and would be interested in looking for someone to help me do that job.  If this is something you have done and are available to provide me with consulting services, please let me know and tell me your rates.

Email directly to boordw@aol.com.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
FYI, the other way to accomplish this if you can't open a form in dialog mode is to have the called form set the filter and/or requery the calling form when it goes to close.

You can do that in any number of ways, but most use the OpenArgs parameter when calling a form.   OpenArgs allows you to pass parameters to the form your calling.

Jim.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.