Can't figure out why this errors (1 Viewer)

[BoBaxter]

I hope this is the right place to put this... Please let me know since I have never really done the forum thing before.

This is to go through multiple subforms and calculate a total estimated and actual for each material listed along with carryover for the pricing. It worked great until we went from Access 2003 to Access 2010. For the longest it has been here and there that it errors but as we get busier it seems to happen more and more.

I can't figure out for the life of me where I am going wrong on this one. I know things should be better laid out on this, but I am more of a break/ fix kind of guy I guess. I get multiple types of errors on this, 3420 - where it says I am not specifying an object on the .movefirst line. It sometimes says the field does not exist on the !priceEst line. It has given me another that I can not think of off the top of my head.

Well here goes.

Private Sub Command113_Click()
On Error GoTo Err_Command113_Click
  
  Const MaxNumIte = 200
  Dim TotA(MaxNumIte) As Double
  Dim TotE(MaxNumIte) As Double
  Dim TotR(MaxNumIte) As Double
  Dim TotP(MaxNumIte) As Double
  Dim IDMatR(MaxNumIte)
  
  
  For I = 0 To MaxNumIte
    TotA(I) = 0
    TotE(I) = 0
    TotR(I) = 0
    TotP(I) = 0
    IDMatR(I) = 0
  Next I
  NumIte = -1
  
    IDArea = 100 * Int(Form_MATFORM.Areas_subform16.Form.Recordset!IDAre / 100)
    
    Form_MATFORM.Areas_subform16.Form.Filter = "dbo_Areas.IDare<" & (IDArea + 99) & " And dbo_Areas.IDare>" & (IDArea - 1)
    Form_MATFORM.Areas_subform16.Form.FilterOn = True
    
    Me.Areas_subform16.Form.Recordset.MoveFirst
    While Not Me.Areas_subform16.Form.Recordset.EOF
      If (Me.Areas_subform16.Form.Recordset!IDAre > IDArea - 1) And (Me.Areas_subform16.Form.Recordset!IDAre < IDArea + 99) Then
        Form_MATFORM.[Areas subform16].Controls("RAreMat subform").Form.Recordset.MoveFirst
        With Form_MATFORM.[Areas subform16].Controls("RAreMat subform").Form.Recordset
          While Not .EOF
            If IsNull(!PriceEst) Then
                .Edit
                !PriceEst = 0
              .Update
            End If
            IDMatE = !IDMat
            InsNewIte = True
            For I = 0 To NumIte
              If IDMatR(I) = IDMatE Then
                InsNewIte = False
                Exit For
              End If
            Next I
            If InsNewIte Then
              NumIte = NumIte + 1
              IDMatR(NumIte) = IDMatE
              TotA(NumIte) = Nz(!Actual, 0)
              TotE(NumIte) = Nz(!Estimate, 0)
              TotP(NumIte) = !PriceEst
            Else
              TotA(I) = TotA(I) + !Actual
              TotE(I) = TotE(I) + !Estimate
                If Nz(!PriceEst, 0) > 0 Then
                    TotP(I) = !PriceEst
                End If
            End If
            .MoveNext
          Wend
        End With
      End If
      Me.Areas_subform16.Form.Recordset.MoveNext
    Wend
    
    Me.Areas_subform16.Form.Recordset.MoveFirst
    While Not Me.Areas_subform16.Form.Recordset.EOF
      If (Me.Areas_subform16.Form.Recordset!IDAre > IDArea - 1) And (Me.Areas_subform16.Form.Recordset!IDAre < IDArea + 99) Then
        With Me.Areas_subform16.Controls("RAreMat subform").Form.Recordset
          .MoveFirst
          While Not .EOF
            IDMatE = !IDMat
            For I = 0 To NumIte
              If IDMatE = IDMatR(I) Then Exit For
            Next I
            If TotA(I) > 0 Then
              EstimateE = Int(TotE(I) * !Actual / TotA(I))
              .Edit
              TotR(I) = TotR(I) + EstimateE
              !Estimate = EstimateE
              !PriceEst = TotP(I)
              .Update
            End If
            .MoveNext
          Wend
        End With
      End If
      Me.Areas_subform16.Form.Recordset.MoveNext
    Wend
  
    Me.Areas_subform16.Form.Recordset.MoveFirst
    While Not Me.Areas_subform16.Form.Recordset.EOF
      If (Me.Areas_subform16.Form.Recordset!IDAre > IDArea - 1) And (Me.Areas_subform16.Form.Recordset!IDAre < IDArea + 99) Then
        With Me.Areas_subform16.Controls("RAreMat subform").Form.Recordset
          .MoveFirst
          While Not .EOF
            IDMatE = !IDMat
            For I = 0 To NumIte
              If IDMatE = IDMatR(I) Then Exit For
            Next I
            If TotA(I) > 0 Then
              .Edit
              !Estimate = !Estimate + TotE(I) - TotR(I)
              !PriceEst = TotP(I)
              TotR(I) = TotE(I)
              .Update
            End If
            .MoveNext
          Wend
        End With
      End If
      Me.Areas_subform16.Form.Recordset.MoveNext
    Wend
  
    Form_MATFORM.Areas_subform16.Form.Filter = ""
    Form_MATFORM.Areas_subform16.Form.FilterOn = False
    
Exit_Command113_Click:
        Exit Sub
 Err_Command113_Click:
    MsgBox Err.Description
    Resume Exit_Command113_Click
  
End Sub

 

[CJ_London]

as access moves to higher versions, tolerance for things like poor naming conventions, misuse of . & ! etc has reduced resulting in errors which may have been ignored before.

Suggest try using recordsetclone rather than recordset and also test the recordsetclone (after filtering) is populated before using .movefirst

 

[Galaxiom]

Form_MATFORM.Areas_subform16.Form.Filter

This expression is referring to a form via its module and should never be used. A hidden instance of the form will be loaded if a form is referenced in code this way without already being open.

Always refer to other forms via the Forms Collection.

Forms!MATFORM.Areas_subform16.Form.Filter

BTW I also highly recommend against using underscores in object names. Firstly, that character is already used in VBA as the delimiter between an object and its event in event procedure names. Names are also easier to read without underscores which artificially split up the object name with what is mostly white space.

 

[BoBaxter]

I'll give those a shot, thank you. I know the records were populated throughout in the instances it was throwing an error. The recordset clone is a very good thought though.

 

[BoBaxter]

Agreed.. They named these very poorly. I just never had time to delve in to changing all of this stuff honestly.

 

[Galaxiom]

BTW This code is unnecessary. The value of a newly initialised numeric variable is already zero.

  For I = 0 To MaxNumIte
    TotA(I) = 0
    TotE(I) = 0
    TotR(I) = 0
    TotP(I) = 0
    IDMatR(I) = 0
  Next I

 

[BoBaxter]

If they ran the code prior would it possibly be populated from the last go around?
Like running it twice in one session?

 

[Galaxiom]

If they ran the code prior would it possibly be populated from the last go around?
Like running it twice in one session?

No. Procedure variables go out of scope and are cleared at the end of a procedure. It would be different if they were module scoped variables declared at the top of the module.

 

[BoBaxter]

That's great to know. Thank you. I've certainly wasted time with that in other procedures.

 

[Galaxiom]

as access moves to higher versions, tolerance for things like poor naming conventions, misuse of . & ! etc has reduced resulting in errors which may have been ignored before.

Can you give examples of how this has changed? In my experience (up to A2010), Access never gave a damn about naming conventions provided reserved words and special characters are avoided. Even then it will tolerate quite a lot, provided the name is enclosed in square brackets, (though best avoided).

How has the use of dots and bangs changed? Strictly speaking, the bang has always represented the default Collection and the dot for Properties though the dot will be tolerated in many places where their aught rightly be a bang. There would be a lot of gnashing of teeth if this tolerance was changed since most published code uses it.

 

[CJ_London]

Can you give examples of how this has changed?

nothing immediately springs to mind as an example, just an impression I've got over the years and I have no means of testing 2003 v 2007+

It's comments like this, that have me thinking this way

In general, VBA solutions developed against a certain version of Office will work with a newer version. The devil lies in the details though. Due to bug fixing and new features the applications might behave slighty different than the older version. The only way to find out if everything still works is exhaustive testing.

from this thread

http://stackoverflow.com/questions/...-in-vba-versions-between-office-2003-and-2007

I'm sure there are some references in Allen Browne's website to things that worked in 2003 but not in 2007 (I'm not referring to things like menubars and replication)

 

[CJ_London]

this is another example of something that apparently used to work in 2003, but doesn't in 2007+. My assumption is that the earlier VBA was more forgiving in terms of not generating an error

http://www.accessforums.net/showthread.php?t=63881

see post #11

 

[BoBaxter]

Just to weigh in since the conversation has gone this way, we have 8 front end databases (before the amount of users I believe was more limited in 2003 so they were broken up by department). These were all converted from 2003 to 2010. The main problems we have had were the calendar objects did not transfer over (I grabbed a copy and registered mscal.ocx on every computer to work around this) and this procedure were the only real casualties in the transition. For the most part it has gone much better than I expected honestly. This one procedure has been working on and off since we transitioned (I kept trying things to try and remedy it and ran out of ideas). That also puzzled me since usually things work or they don't. I will be trying to roll out the ideas you guys gave me. I will keep you posted. Thanks again.

 

[CJ_London]

can you clarify this

we have 8 front end databases (before the amount of users I believe was more limited in 2003 so they were broken up by department).

in a multi user environment, each user should have their own copy of the front end and share the backend - this sounds like you have multiple users using the same front end.

 

[BoBaxter]

I do have multiple users using the same front end. They all link to one .accdb on a server. We are constantly changing and updating things. My responsibilities are not primarily the database and I would not have time to update 50 users front ends on a normal basis. I could write it into a batch file when they turn their computers on, but there are certain people here that never turn off their computers. It is just what I have inherited and the set up that has been used for a while here.

I just tried with the recordsetclone and it seems to be going well. Much thanks on the tip.

 

[CJ_London]

I would not have time to update 50 users front ends on a normal basis

So long as you appreciate the risk of corruption is increased significantly by having users use the same front end. The corruption can be insidious - everything appears to work OK for a long time - perhaps years, then suddenly it doesn't. It's not that difficult to automate, plenty of examples on this and other forums.

 

[Frothingslosh]

The way we do it where I work:

First, there is a batch file written that ensures a block file (block.txt) isn't in the master folder, then ensures the master copy exists, then deletes the local copy, then downloads a new copy from the master.

The batch file is kept in the same place as the master copy of the front end.

A shortcut is created that is distributed to each user which runs the batch file.

Viola, every time they go to run the application, they download a fresh copy. It might take a second or so longer, but guarantees that the user is kept up-to-date.

Another option would be to use THIS.

 

[BoBaxter]

CJ - I appreciate the thought. I am mostly self taught in this, so if issues do not come up, I do not learn about certain things. The programming I was actually taught was mostly web based and I do not recall too much about corruption. I heard a little about Access should be on every user's c: but I read varying articles that covered multiple points. But I guess the back of my mind I always tried to just put it off.

Frothingslosh - The batch file seems like a great idea. But the code from your link seems like a wonderful idea as well. The way we have it set up is we have 9 databases separated out. The main form everyone gets has hyperlinks to all of the databases. 7 of the databases are by department (accounting, engineering, material inventory, etc). With the code posted there, I could potentially put it on the "on click" property of the link and it would update as they go.

I only have knowledge of running batch files from active directory, so I was a bit unsure if linking the batch file from that hyperlink would run it as an administrator (I just really do not know from lack of experience).

Any recommendations from you guys on the route to go? I am more speaking out loud I guess.

 

[Cronk]

Frothingslosh,

In some areas where users are very computer unsophisticated, rather than a batch file update, I completely automate the update process.

Firstly, I put code in that immediately closes the FE if it is opened from anywhere other than the C drive.

Keep a version number in both the FE and BE data which is compared when the FE is opened. If there has been a version upgrade, run code that causes another Installer db to open and close the FE. The installer replaces the users copy of the FE with a copy of the FE on the network, and then opens then updated FE.

 

[CJ_London]

@BoBaxter - FYI a current example of what can happen if the db is not split

https://www.access-programmers.co.uk/forums/showthread.php?p=1518271#post1518271