Results 1 to 7 of 7
  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

    Working code stopped working

    Hey Y'all,

    I have a large FE/BE database that's been working for years however I now get an error with this code:

    Code:
    '                           +-------------------+                 +----------+
    '---------------------------| SetDateForBills() |-----------------| 02/17/12 |
    '                           +-------------------+                 +----------+
    'Called by: AllBillsReport()
    '           PrintedBillsReport()
    '           EmailBills()
    'Calls    : N/A
    'Returns  : True if rates exist or are added.
    '           False if rates do not exist and are not added or Cancel button
    '                 is selected.
    
    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, , , , acDialog
         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, , , , acDialog
         End If   '** if iAns     **
         
       End If     '** rst.NoMatch **
       
       Set qryName = dbName.QueryDefs("qryDocksForBills")
       
      zSQL = "SELECT Owners.OwnerID, Docks.OwnerID, Docks.Dock, IIf(([MaintThru]<[BillingDate])" & _
            " And (InStr('DT-GL-RL-WL-',Left$([dock],3))>0),[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 (((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] or IsNull([MowingThru])," & _
              "[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 line highlighted in RED produces this error:
    Error 3251 Message Access.PNG

    I've tried modifying the Dims by prefixing DAO. to the Database and RecordSet and adding , dbOpenTable to the Set rst = argument which produces this error:

    Error Message Access.PNG

    Here's the Table structure (with the exception of the last item which was what I was trying to add when I noticed the new error but I restored backups to make sure that wasn't causing the problem but didn't take a new screen capture.
    tblFeesStructure.PNG

    I've done quite a bit of googling with out any results that work.

    Sign me stumped!
    Last edited by RetiredGeek; 2016-01-13 at 20:00.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The error is occurring in a string concatenation operation, right? I don't see why you would change the Dim statements.

    Have you tried applying CStr to the operands? My guess is that something is going wrong in terms of string conversions. Does zBillDate have an actual value before Trim is applied to it?

    Just defy any assumptions about the values of your operands and see if that helps.
    Rui
    -------
    R4

  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
    Rui,

    OOPS! My bad I highlighted the wrong line. Fixed in OP.

    I had the entire post all finished and my cable modem hiccuped and I lost the post and I recreated it in a bit of a hurry sorry.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I didn't think you could use dbOpenTable and .Seek on a linked table? Try using dbOpenDynaset and .FindFirst instead.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by RetiredGeek View Post
    Rui,

    OOPS! My bad I highlighted the wrong line. Fixed in OP.

    I had the entire post all finished and my cable modem hiccuped and I lost the post and I recreated it in a bit of a hurry sorry.
    . I fear you will have to rely on more knowledgeable posters, then. It's been a few years since I last coded for Access and I mostly used ADO (as it could be used in multiple contexts, not just Access).
    Rui
    -------
    R4

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If this has been working for some time, then something in the environment must have changed. What version of Access, and did that change? Any recent updates to Access? What is the back-end - Access, SQL Server or something else? You might also try Mark's suggestion, but if it worked before and now doesn't then something must have happened - I suppose corruption is a possibility as well....
    Wendell

  7. #7
    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
    Hey Y'all,

    Just reporting back, I took Marks suggestion and it fixed the problem and all is working as it should now.

    FYI Here's the revised code:
    Code:
    '                           +-------------------+                 +----------+
    '---------------------------| SetDateForBills() |-----------------| 01/14/16 |
    '                           +-------------------+                 +----------+
    'Called by: AllBillsReport()
    '           PrintedBillsReport()
    '           EmailBills()
    'Calls    : N/A
    'Returns  : True if rates exist or are added.
    '           False if rates do not exist and are not added or Cancel button
    '                 is selected.
    
    Function SetDateForBills() As Boolean
    
       Dim iAns        As Integer
       Dim qryName     As QueryDef
       Dim dbName      As DAO.Database
       Dim rst         As DAO.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)
    '*** Although dtBillDt is not used within this function         ***
    '*** it is a Module level var and used elsewhere in this module ***
       dtBillDt = CDate(zBillDate)
       
       '***** Check to see if Rates for that year are present in tblFees *****
       
       Set rst = dbName.OpenRecordset("tblFees", dbOpenDynaset)
       rst.FindFirst "BillingDate = #" & zBillDate & "#"
       
       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, , , , acDialog
         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, , , , acDialog
         End If   '** if iAns     **
         
       End If     '** rst.NoMatch **
       
       Set qryName = dbName.QueryDefs("qryDocksForBills")
       
      zSQL = "SELECT Owners.OwnerID, Docks.OwnerID, Docks.Dock, IIf(([MaintThru]<[BillingDate])" & _
            " And (InStr('DT-GL-RL-WL-',Left$([dock],3))>0),[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 (((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] or IsNull([MowingThru])," & _
              "[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()***
    Note: I couldn't use the datetime value in the FindFirst so I reverted to using the #date STRING# format.

    If there is a way to use a datetime value I'd be interested in someone pointing out the syntax.

    Thanks again to all who responded.
    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
  •