Results 1 to 6 of 6
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    I've having a problem with a form in Access that is set to MODAL but it doesn't stop the processing of the VBA. The program only stops when it gets to a MSGBOX for changing the default printer. Below are the VBA code and the properties box of the form showing the MODAL selection. Any Ideas?
    Code:
    '                           +-------------------+                 +----------+
    '---------------------------| SetDateForBills() |-----------------| 10/28/10 |
    '                           +-------------------+                 +----------+
    'Called by: Macro - AutoExec
    'Calls    : N/A
    'Returns  : True if rates exist or are added.
    '           False if rates do not exist and are not added.
    
    Function SetDateForBills() As Boolean
    
       Dim iAns        As Integer
       Dim qryName     As QueryDef
       Dim dbName      As Database
       Dim rst         As Recordset
    
       Dim zBillDate   As String
       Dim zSQL        As String
       
       Set dbName = CurrentDb
       zBillDate = InputBox("Enter Billing Year as 4 digits", "Billing Year", Trim(Str(Year(Now()))))
       zBillDate = "4/1/" & Trim(zBillDate)
       dtBillDt = CDate(zBillDate)
       
       '***** Check to see if Rates for that year are present in tblFees *****
       
       Set rst = dbName.OpenRecordset("tblFees")
       rst.Index = "PrimaryKey"
       rst.Seek "=", dtBillDt
       If rst.NoMatch Then
         rst.Close    '**** Close the Recordset no longer needed ****
         iAns = MsgBox("There are no Fees in the tblFees table for " & zBillDate & vbCrLf & _
                "Would you like to enter them now?", _
                vbYesNo + vbInformation, "Warning: No Fees!")
         If iAns = vbYes Then
           bFormMode = True      '** Data Entry Mode ***
           DoCmd.OpenForm "frmFees", acNormal
         Else
           MsgBox "There are no rates for Bill Year " & zBillDate & vbCrLf & _
                  "No bills will be produced at this time." & vbCrLf & _
                  "You will be returned to the Billing Options Menu.", _
                  vbOKOnly + vbInformation, "Can NOT Produce Bills:"
           SetDateForBills = False
           GoTo Exit_Function
         End If   '** if iAns     **
       Else                     '**Code for existing rates! **
         rst.Close    '**** Close the Recordset no longer needed ****
         iAns = MsgBox("Would you like to Edit the Fees for " & zBillDate & "?", _
                vbYesNo + vbInformation, "Information: View/Edit Fees?")
         If iAns = vbYes Then
           bFormMode = False   '** Data Edit Mode ***
           DoCmd.OpenForm "frmFees", acNormal, , , , acWindowNormal '*** Should STOP HERE! ***
         End If   '** if iAns     **
         
       End If     '** rst.NoMatch **
       
       Set qryName = dbName.QueryDefs("qryDocksForBills")
       
      zSQL = "SELECT Owners.OwnerID, Docks.OwnerID, Docks.Dock, IIf([MaintThru]<[BillingDate]," & _
             " [DockMaintFee],0) AS DockFee, IIf([LiftThru]<[BillingDate],[LiftMaintFee],0)" & _
             " AS LiftFee, Docks.MaintThru, Docks.LiftThru, tblFees.BillingDate" & _
             " FROM tblFees, Owners INNER JOIN Docks ON Owners.OwnerID = Docks.OwnerID" & _
             " WHERE (((Docks.Dock) < 'WB*') And ((tblFees.BillingDate) = #" & zBillDate & "#))" & _
             " ORDER BY Docks.OwnerID, Docks.Dock;"
            
       qryName.SQL = zSQL  'Assign SQL statement to Query Name
       qryName.Close       'Close and Save the Query
       
       Set qryName = dbName.QueryDefs("qryLotsForBills")
       zSQL = "SELECT Lots.OwnerID, Lots.Lot, Lots.PropertyAddr, Lots.DuesThru, " & _
              "IIf([DuesThru]<[BillingDate],[AsociationFee],0) AS AnnFee, " & _
              "IIf([DuesThru]<[BillingDate],[CapitalImpFee],0) AS CImpFee, " & _
              "IIf([DuesThru]<[BillingDate],[CapitalRepFee],0) AS CRepFee, " & _
              "Lots.Mowing, Lots.MowingThru, IIf([Mowing]='Yes',IIf([MowingThru]<" & _
              "[BillingDate],[MowingFee],0),0) AS MowFee FROM Lots, tblFees " & _
              "WHERE ((tblFees.BillingDate) = #" & zBillDate & _
              "#) ORDER BY Lots.OwnerID, Lots.Lot;"
            
       qryName.SQL = zSQL  'Assign SQL statement to Query Name
       qryName.Close       'Close and Save the Query
    
       SetDateForBills = True    '*** Successfully Set ***
       
    Exit_Function:
    
    End Function                '*** SetDateForBills()***
    The Form is displayed properly but execution blows right through to the MsgBox mentioned and of course it is modal so I can't even move it out of the way to get to this form which has to be processed first.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    My understanding is that code only stops when you open the form using acDialog

    Code:
     DoCmd.OpenForm "frmFees", acNormal, , , , acDialog '*** Should STOP HERE! ***
    Regards
    John



  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Thanks that works perfectly. What I don't understand is according to the Help File what I had should have worked?
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Your code should indeed have opened the form as Modal, but that is not enough to stop the code. You need the acDialog to do that.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Setting a forms modal property in design view is a waste of time, it just makes it hard to Switch from from view to design view.

    And only opens Modally if you open it from the database window / nav pane.

    If you open it with code then it opens the way you spec it in the code.

    Just open it as modal as per John's code.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John & Kent,

    Thanks for the explanations. My education continues!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •