Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Access 2003/2007 -requery problems

    I have a database that was developed in Access 2003 and is now running mostly under Access 2007. I have made some changes to one of the form/subforms. These changes include implementing bookmarks so the user can now sort the subform by any column. The form works fine in Access 2003 however when I move it into 2007 the requery method does not seem to work. I have attached a screen shot and the problem is immediately evident if I try to change the year selected - nothing happens. The code to execute the query is as follows:

    Private Sub ComboJobYear_AfterUpdate()
    Forms!frmDatasheets_List!frmDatasheets_List_Subfor m.Requery
    End Sub

    Any ideas?
    Attached Images Attached Images

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    You may have to set the focus to the subform before doing a requery.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    It may be just a typo in the post but this line
    Code:
    Forms!frmDatasheets_List!frmDatasheets_List_Subfor  m.Requery
    appears to have an extra space before the last m.

    Is this any better?
    Code:
    Me.frmDatasheets_List_Subform.Requery
    You could add in msgbox "test" just to be sure that the code actually executes.

    PS If you post graphics as Gif or Jpg images they will be displayed directly in the post. BMP images are much larger and are not displayed directly in the post.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Many thanks for your replies. The code does not have a space in it, that must have been me in cutting and pasting it in. I have tried your suggestion John and it made no difference. Patt, I did not think I needed to set the focus to the subform given the code specifies the subform.

    As I said in my first post, the strange thing is that the code works perfectly fine in Office 2003. It is only when I move it to Office 2007 that it does not seem to execute and I have stepped through the code to make sure that the statement is actually executed.

    I don't know if it has any bearing on the issue but I did have compress on close turned on and I noticed that when I started with an uncompressed database and then turned it on in 2007 the size was almost 2meg smaller

    I have now reverted to an earlier version of the application, added a couple of other small changes and not implemented the sort capability and it is functioning for the client. Meantime I still have to make other changes including this requirement.

    To describe what I mean by turning on Sort. I change the forms from being popup forms to no popup; add a menu for sorting to their custom menu bar. After the sort the user selects a record for editing. A function is then run to create a new version of the record for editing(they track drawing version changes). The function runs a requery on the subform to display the new version. This is why I then need to use bookmarks to ensure that I go back to the correct record for editing.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    A couple of questions that may help point us in the right direction: Is the database split into a front-end and a back-end, and if so what is the back-end database engine (Access, SQL Server, something else)? Also is the front-end (or complete) database in .mdb format both in 2003 and 2007? Finally, is the subform bound to the recordset, or are you working with an unbound form when they edit a record?
    Wendell

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Worst case, try closing then re-opening the subform, like this:

    me.frmDatasheets_list_Subform.sourceobject = ""
    me.frmDatasheets_list_Subform.sourceobject="frmDat asheets_list_Subform"

    (I'm assuming the subform control name is the same as the name of form it contains.)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Many thanks for your reply. I have been out of the country hence the delay in getting back to you. The backend is in .mdb format. The front end is developed in 2003 but they have now shifted almost all their computers to 2007 so before I take any new version down there I test it in 2007 format.

    The main form is unbound as it sets up all the selection criteria. The subform is bound to the appropriate query depending upon the selection criteria (there are 4 possible queries depending on whether they want to look at all versions or just the latest and whether they are using date selection or not. The screen shot I attached in my first post shows you what it looks like.

Posting Permissions

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