Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Recalc (Access 2003)

    In <post#=396706>post 396706</post#> I asked about waiting for a subform to fill with data, and Hans suggested the Recalc method and that solved my problem.

    I have a similar situation, and tried that again, and again it worked, but it had a side effect that I don't understand.

    I have a form listing all items listed for an Auction (frmAuctionDetails). Each item has a VendorID (text) and some items also have a Stockcode .

    When I double click a stockcode, I want to open another form/subform which lists lists all items for a particular vendor, then find the item with matching stockcode in the subform.

    So I open the form, passing it the stockcode using OpenArgs. I use this to determine who is the vendor, set this value in the main form, wait for the subform to fill, then find the right item in the subform.

    Private Sub Form_Load()
    Dim rs As DAO.Recordset
    Dim strVendorID As String
    Dim f As Form
    On Error GoTo Form_Load_Error

    If Not IsNull(Me.OpenArgs) Then
    ' need to find a particular stockcode item
    strVendorID = fnGetVendorID(Me.OpenArgs)
    Me!ComboVendor = strVendorID

    Set f = Me!frmItemsbyVendorSub.Form
    ' wait for the subform to fill
    Me!frmItemsbyVendorSub.Form.Recalc
    Set rs = f.RecordsetClone
    rs.FindFirst "[stockcode]=" & Chr(34) & Me.OpenArgs & Chr(34)
    f.Bookmark = rs.Bookmark
    Set rs = Nothing
    End If

    Exit_Form_Load:
    Exit Sub


    Form_Load_Error:
    If Err.Number = 0 Then
    Resume Next
    ElseIf Err.Number = 3021 Then
    Resume Next
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of VBA Document Form_frmViewItemsbyVendor", , "Error in procedure Form_Load"
    Resume Exit_Form_Load
    End If
    End Sub

    Now if don't put in code to Wait for the subform to fill I often get Error 3021 - when I Set rs = f.RecordsetClone.

    However when I put in the Recalc line this happens. Before frmAuctionDetails disappears, the current record indicator moves back to the first record.
    So when I close the form showing details of the item, I don't return to the item I started at. When I take out the recalc line, this does not happen.


    If I replace the Recalc line with this code, it all works OK. It just holds things up while the subform fills, but the choice of 1000 for loop was just trial and error.
    Is there some other test I could use?

    Dim intCount As Integer
    intCount = 1
    Do While (intCount < 1000)
    DoEvents
    intCount = intCount + 1
    Loop
    Regards
    John



  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Recalc (Access 2003)

    Have you tried the Repaint method instead of the Recalc. This is just a WAG.
    I found the following in the help:
    The Repaint method completes any pending screen updates for a specified form. When performed on a form, the Repaint method also completes any pending recalculations of the form's controls.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recalc (Access 2003)

    Thanks for the suggestion Pat, but it does not work.

    I go back to getting error 3021.
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Recalc (Access 2003)

    Hi John,

    Is there a particular reason why you want to use the Recordset.Clone method instead of simply applying a filter, or even more directly by setting the link properties of the subform? We've found that in 2002 and 2003 you get some rather odd behaviors from the Clone process - I've personally seen a situation where using the combo wizard to do record lookups would fail in 2002 but work just fine in 2000 - it was never clear what caused the behavior. We switched to using filters and it worked flawlessly. OTH, you may have a compelling reason for not using filters - but race conditions such as this appears to be are a bear to try to debug.
    Wendell

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recalc (Access 2003)

    Thanks Wendell

    I am using the clone method because I want to find the record, rather than filter to it.

    My client wants to see the item within a list of all other items listed by that vendor, This list includes items from other auctions, and ones that may be waiting to put into an auction.

    It is the Recalc that is causing the problem. If I leave that in, but remove all code that tries to find the record in the subform, the record selector in the first form still jumps back to the first record.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recalc (Access 2003)

    Could you post a stripped down copy of the database?

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recalc (Access 2003)

    Thanks Hans

    I wll try to build a stripped down version, but it will be a couple of days till I can do it.

    John
    Regards
    John



  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Recalc (Access 2003)

    Well that certainly makes sense. Pondering this a bit more, I wonder if perhaps putting your code on one of the later events in the sequence would eliminate the need for the delay loop. Of course that might not work either if you have 100s or 1000s of records being displayed. Will be watching for your post with the cut down version.
    Wendell

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recalc (Access 2003)

    I am not getting anywhere with this.

    The problem does not occur when I make a stripped down version. To make it small enough I have to remove lots of stuff.
    Each time I do this the problem disappears at some stage, and I can't work out exactly which bit fixed it. Clearly I need to remove things one at a time, but that will take a very long time.

    I have tried moving the find record code to other events, but I always need the recalc line, and that alwayscauses me to lose the original record position.

    I am going to try to think of another solution, rather than fix this one.
    Regards
    John



  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Recalc (Access 2003)

    Have you tried to goto sleep for 1/2 second in the error routine when you test for 3021 then resume next to try the OpenRecordset command again. I would check how many times you have been thru the error routine and bomb out if it exceeds a certain value so as to stop an endless loop. Then you would not need the Recalc command.

    The sleep works like:
    'Declare Sleep API
    Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)
    ' this at top of module:

    'this above the Set rs command
    NoofRetries = 0
    Set rs = f.RecordsetClone

    This in your error routine:
    If Err = 3021 then
    Sleep 500 'wait 1/2 sec before trying again
    NoofRetries = NoofRetries +1
    If NoofRetries >= 5 then ' allow a maximum time of 2.5 seconds
    Resume
    Else
    Resume Next

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recalc (Access 2003)

    Thanks Pat

    I will try this out. I could also put something similar into my existing code that puts DoEvents within a loop as well.

    I have got something else to work, but I am not completely happy with that either.

    My original solution involved setting a value (VendorID) on a Main form, then waiting for a subform to fill with that vendors items(This utilised an existing form)

    I have made a new continuous form that lists every item. Now I open this form filtered to the right vendor, then find the right item. This works without problem, without needing the recalc line.

    My reservations are that it does not take the user to the form they expect to go to, and I have to make sure that I duplicate all the functionality in the original form in this new one.
    Regards
    John



  12. #12
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recalc (Access 2003)

    I may be way off beam here, but have you got SP1 installed?
    Access 2003 'vanilla' seemed to have major problems with continuous sub-forms containing calculations or conditional formatting in my experience. SP1 seems to fix this. When I was fighting with broken sub-forms it seemed to be a timing issue (when it didn't crash and burn!) Often the sub-form never populated past the first record.

    Just a thought <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recalc (Access 2003)

    Thanks for the suggestion.

    Yes I do have SP1 installed.

    In this case the subform does have quite a bit of code, but no calculated fields and no conditional formatting.
    Regards
    John



  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recalc (Access 2003)

    I thought this would work Patt, but it seems that if the waiting code (either via sleep or a DoEvents loop) is put in the error handler, the waiting occurs, but the form does not keep filling while the waiting takes place. No matter how long I wait, it does not find the right record.

    So I have had to move the waiting code back to the body of the procedure, but kept the NoofRetries stuff as follows.

    By putting aa: at the start of the line, I label the line, so I can resume at that line.

    Me!ComboVendor = strVendorID
    NoofRetries = 0
    Set f = Me!frmItemsbyVendorSub.Form

    aa: intCount = 1
    Do While (intCount < 500)
    DoEvents
    intCount = intCount + 1
    Loop
    Set rs = f.RecordsetClone
    rs.FindFirst "[stockcode]=" & Chr(34) & Me.OpenArgs & Chr(34)
    f.Bookmark = rs.Bookmark
    Set rs = Nothing

    End If



    Form_Load_Error:
    If Err.Number = 0 Then
    Resume Next
    ElseIf Err.Number = 3021 Then
    Debug.Print NoofRetries
    NoofRetries = NoofRetries + 1
    If NoofRetries <= 5 Then ' allow a maximum time of 2500 loops
    Resume aa
    Else
    Resume Next
    End If
    Regards
    John



  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recalc (Access 2003)

    Try this:

    Dim rs As DAO.Recordset
    Dim strVendorID As String
    Dim f As Form
    On Error GoTo Form_Load_Error

    If Not IsNull(Me.OpenArgs) Then
    ' need to find a particular stockcode item
    strVendorID = fnGetVendorID(Me.OpenArgs)
    Me!ComboVendor = strVendorID

    Set f = Me!frmItemsbyVendorSub.Form
    ' wait for the subform to fill
    f.Recalc
    DoEvents

    If f.RecordsetClone.RecordCount <> 0 then
    Set rs = f.RecordsetClone
    rs.FindFirst "[stockcode]=" & Chr(34) & strVendorID & Chr(34)
    If Not rs.NoMatch Then
    f.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
    End If
    End If
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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