Switching between forms

[recanem]

Hello all - I have what I hope is a simple question. I'm looking for advice on switching between forms, specifically in access 2007 (though I suspect that the answer may be similar in 2003).


Overview:
I have summaryFormA and UpdatingFormB. On summaryFormA there is an Updatingbutton that when clicked calls for UpdatingFormB to open. During the opening of UpdatingFormB the Load and Current events trigger code to run, which updates the data in several tables and ultimately reopens summaryFormA to allow viewing of the new details.
Problem(i think):
The tables will generate a locking error asUpdatingFormB still thinks that summaryFormA is still open. I believe the reason for this is that the btnClickEvent on summaryFormA never reaches the End Sub, and is actually waiting for UpdatingFormB to process all of the vba code before continuing.

Question:
Is there a graceful way to change between forms, making sure that formA is completely closed with no code running, before anything on formB starts? Any pointers on what to read in regards to this would also be appreciated.

Thanks,

Mike

 

[Mr. B]

If you have code that is closing the summaryFormA then you can try using a DoEvents statement just after the close statement for your summaryFormA

Something like:
docmd.Close acForm ,"summaryFormA"
DoEvents

HTH

 

[recanem]

I think I need a bit more explanation on DoEvents, it sounds like its for VB only and not VBA (access). Here's the snippet of code I'm refering to and how I've attempted to use DoEvents. DoEvents returns a value of 0 when i use it in the immeadiate debug window, when I've been testing what it returns (sounds like it should return the count of the number of forms open). What i think needs to happen is the summaryFormA sub needs to hit the End Sub before the code reaches changeDistrict2().


summaryFormA button Click code:

Private Sub button_DistrictUpdate_Click()
'Dim MsgBox Stuff
Dim Msg, Style, Response
If IsNull(Me.txtChosenDistrict.Value) Or IsNull(Me.txtReportMonth.Value) Then
    Msg = "Please Enter a District and Report Month"
    Style = vbOKCancel
    Response = MsgBox(Msg, Style)
    If Response = vbOK Then
        Me.txtChosenDistrict.SetFocus
 
    Else
        MsgBox "Update of District Aborted", vbInformation + vbOKOnly
 
    End If
 
Else
 
   UpdatingDistrictFunctions.DistrictNumber = Me.txtChosenDistrict.Value
   UpdatingDistrictFunctions.ReportDate = Me.txtReportMonth.Value
   'Calling the function UpdatingDistrictFunctions.ChangeDistrict
   ChangeDistrict  [B]' <===This is the point that will start the other form [/B]
[B]                   'opening. 'The code will not advance past this line until   [/B]
[B]                   'the function completes, but the function will  [/B]
[B]                   'error because the "End Sub" has not been reached[/B]
[B]                   'and vba thinks the form still has a table lock in place[/B]
 
End If
 
[COLOR=red][B]End Sub[/B][/COLOR]

updatingDistrictFunctions:

Function ChangeDistrict()
'Debug Point: Check if variables were passed.
'Debug.Print DistrictNumber
'Debug.Print ReportDate
'We've stored the relevant data we need lets close the summary form
DoCmd.Close acForm, "CRU Tails Report", acSaveNo
'Note had to seperate out the form open to make certain the CRU Tails Report form is truly closed
'Explictly closing all sub-subforms in attempt to release locks
DoCmd.Close acForm, "CRUTails" 'subForm off CRU Tails Report
DoCmd.Close acForm, "OrdersTabPivotForSingleCustomerNameData" 'subForm off CRU Tails
DoCmd.Close acForm, "ShipmentsTabDatasheetForSingleCustomerNameData" 'subForm off CRU Tails
DoCmd.Close acForm, "VolumesTabPivotForSingleCustomerNameData" 'subForm off CRU Tails
DoCmd.Close acForm, "Summary", acSaveNo  'subForm off CRU Tails Report
DoEvents [B]'Added do events Here and it returns a value of 0 forms open[/B]
 
DoCmd.DeleteObject acTable, "OrdersTab" [B]'This is a quick debug point to test if summaryFormA is closed, once it is closed I should be able to delete and recreate this table, but i cannot[/B]
ChangeDistrict2
End Function
Function ChangeDistrict2()
'Lets open the form to capture the new district information
DoCmd.OpenForm "UpdatingDistrict", acNormal [B]'opening UpdatingDistrictFormB, once it opens running vba via Load Event and CurrentEvents[/B]
End Function

Thanks for any help,
Mike

 

[Mr. B]

The DoEvents is definately used in VBA. For info on what it does and how it works, go to the VBA window and in any module, just type it and then place your cursor in it and press F1.

The concept is that it will cause the previous statements to be completely executed before moving on to the next statement. My thought was that if you placed this statement just after the line that closes the form that already has the table open, it would just be a way to insure that the form was closed before moving on.

HTH

 

[recanem]

So DoEvents is doing exactly what it is supposed to then, and waiting for the form to close. The catch, I think, however is that it seems as if the ButtonClick event doesnt need to be completely finished in order for the form to close (i.e. the form close event can finish before all code is executed).

 

[recanem]

As an update, I've figured out a means around this by forcing the user to click another button on UpdatingFormB. This allows the click event on form A to complete, allowing for a clean close and unlock. Still thought there ought to be a means to allow one click changing of forms to update data. Mr B. thanks for the tip on DoEvent, I put it to good use!