Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Refresh (Access 2K)

    Hi All,

    I'm having a problem after conversion of an Access 2.0 database into Access 2K
    After a selection is made by means of a combobox the underlying data is not refreshed.
    When i use the following command's, Remove Filter / Filter by Form / Apply Filter
    after a selection the data is refreshed.

    I assume the problem is the vba code. As i'm not very familiar with vba code i'm asking for your assistance.

    btw
    The data is refreshed when i open the database in Access 97. However that's not the intention.

    Attached is the form's vba code.

    TIA
    Joop
    Attached Files Attached Files

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

    Re: Refresh (Access 2K)

    Hi Joop,
    I think your problem is with the code that applies the filter - in 2.0 they worked rather differently in conjunction with forms. That code looks like:<font face="Georgia"><font color=blue>
    Private Sub cbShipName_AfterUpdate()
    On Error GoTo cbShipName_AfterUpdate_Err:

    DoCmd.ApplyFilter "SELECT DISTINCTROW tbl_VesselInformation.LRN, tbl_VesselInformation.LRNNumeric,tbl_VesselInforma tion.VesselName, tbl_VesselInformation.Registry, _ tbl_VesselInformation.ClassSociety, tbl_VesselInformation.VesselType, tbl_VesselInformation.YearBuilt, tbl_VesselInformation.DeadWeight, tbl_VesselInformation.LOA, _
    tbl_VesselInformation.Beam, tbl_VesselInformation.Draft, tbl_VesselInformation.InertGas, tbl_VesselInformation.NitrogenGenerator, tbl_VesselInformation.NitrogenManifold, _
    tbl_VesselInformation.NitrogenBottles,tbl_VesselIn formation.Customer, tbl_VesselInformation.Owner, tbl_VesselInformation.ShipPhoneNumber, _
    tbl_VesselInformation.ShipFaxNumber, tbl_VesselInformation.VesselOperator, tbl_VesselInformation.BowToManifoldDistance FROM tbl_VesselInformation; _
    ", "Forms![frm_VesselInformation].[cbShipName] = [tbl_VesselInformation].[LRN]"

    Exit Sub

    cbShipName_AfterUpdate_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub</font color=blue></font face=georgia>
    What you should have is something that lists just a Where condition, which seems to be completely missing from the line that begins docmd.ApplyFilter.... To find out more about this, open the form you are working with in design mode, click the button just to the left of the Properties button on the design toolbar, and that will open the module in design mode. Then start VBA Help in the VBA editor window, and search for Filter. You will note that among other things, the filter is often applied as Me.Filtter = "ShipID=12345" or may concatenate a string to create a value using a combo box or other text box, and then turned on with Me.FilterOn=True and off with Me.FilterOn=False. Hope this helps get you through the conversion.
    Wendell

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

    Re: Refresh (Access 2K)

    Hello Joop,

    In the After Update event of the combo box, you use DoCmd.ApplyFilter with both a filter and a where-condition argument. Moreover, you set the filter argument to an SQL string instead of the name of a query. You should not use this argument at all, so it can be shortened to:

    DoCmd.ApplyFilter , "[tbl_VesselInformation].[LRN] = Forms![frm_VesselInformation].[cbShipName]"

    I have attached a complete version of the module you posted, with old code translated to standard Access VBA. Of course, I can't test it, so post back if you still have problems.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Refresh (Access 2K)

    Hi Hans,

    I tested your code without changes in the result. The data is not refreshed when another slection is made.
    Any other suggestions are welcome.

    TIA
    Joop

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Refresh (Access 2K)

    Hi Wendell,

    Thanks for the reply.
    As your suggestion means changing/adding vba code i need time for this.

    Thanks
    Joop

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

    Re: Refresh (Access 2K)

    Joop,

    Sometimes, ApplyFilter fails to work; I don't know why. In such cases, you can set the Filter and FilterOn properties. Try replacing

    DoCmd.ApplyFilter , "[tbl_VesselInformation].[LRN] = Forms![frm_VesselInformation].[cbShipName]"

    by

    Me.Filter = "[tbl_VesselInformation].[LRN] = Forms![frm_VesselInformation].[cbShipName]"
    Me.FilterOn = True

    If that doesn't help either, we would need to know more about the form, its record source etc.

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Refresh (Access 2K)

    Hans,

    thanks for the quick reply.
    What you told me to do is probably the same thing as Wendell told me; however no result.
    The database consists of two mdb files.
    The form i'm having problems with is the so-called sys file whereas the actual data resides in the dat file.
    The dat file is weekly updated and distributed.
    Is it any good, helpful for you if i run the Access Documenter and post that on the forum?

    TIA
    Joop

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

    Re: Refresh (Access 2K)

    You could do that, but please post the results as an attachment, not in the post itself, for the output of the Documenter tends to be very long.

    You could also post a stripped down copy of your databases:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Refresh (Access 2K)

    i'll do what suggested (stripped down version) however at the moment not enough time.
    Should i contine this posting or create a new one when replying (probably next week)?

    Joop

  10. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Refresh (Access 2K)

    Hi Hans,

    It's solved.
    After some digging in the access newsgroups i've changed the code as follows
    Private Sub cbShipName_AfterUpdate()
    On Error GoTo cbShipName_AfterUpdate_Err

    ' DoCmd.ApplyFilter , "[tbl_VesselInformation].[LRN] = Forms![frm_VesselInformation].[cbShipName]"
    Me.Filter = False
    Me.Filter = "[tbl_VesselInformation].[LRN] = Forms![frm_VesselInformation].[cbShipName]"
    Me.FilterOn = True
    RunCommand acCmdFilterByForm
    RunCommand acCmdApplyFilterSort
    Exit Sub

    cbShipName_AfterUpdate_Err:
    MsgBox "The following error occurred: " & Err.Description
    Resume Next
    End Sub

    Is this an error free solution? I'm don't have an opportunity to test it on another pc yet.

    Thanks very much/hartstikke bedankt <img src=/S/smile.gif border=0 alt=smile width=15 height=15> for your assistance.

    Joop

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

    Re: Refresh (Access 2K)

    Hi Joop,

    This code seems to be overkill to me (the filter by form dialog is displayed and immediately closed again), but hey, if it works for you...

    I tested a version of it on my PC. It caused some screen flickering, which can be reduced by putting DoCmd.Echo False at the beginning of the subroutine, and DoCmd.Echo True before the Exit Sub. Otherwise is does what is intended.

Posting Permissions

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