Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    requery issue (XP, 2000)

    I have a form that works perfectly until I insert it as a subform. My form displays search results based on a textbox entry. I created a command button with an on click event to clear the searchbox and requery the form to display all records. Again, this works perfectly as a stand alone form. Below is the code for the command button. I have fully qualified the objects as I think I should when using a subform. You will notice the me.requery - I have tried it as shown and as forms![master input form]![qry].requery in both cases, it does not clear the search box or requery.

    Private Sub Command11_Click()
    Forms![master input form]![Qry]!Searchbox = Null
    Me.Requery
    End Sub

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

    Re: requery issue (XP, 2000)

    If the command button and the search box are on the same (sub)form, there is no need to specify the form explicitly.

    Me!SearchBox = Null

    should work fine, whether the form is opened by itself, or as a subform on a main form.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery issue (XP, 2000)

    That makes sense but it does not work. Any other ideas? One additional piece of info that may be important - I just realized that, as a subform, the searchbox seems to retain the previous value input - despite the fact that it is an unbound control and I have an on current event for the form that clears the searchbox and does a requery (just like the command button). The searchbox does not display the previous value but the data displayed clearly indicates that the previous search criteria is still being used.

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

    Re: requery issue (XP, 2000)

    Could you post a stripped down version of your database?
    <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]

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery issue (XP, 2000)

    <P ID="edit" class=small>(Edited by Don_Sadler on 27-Dec-03 06:42. )</P>Hans, the smallest I can ZIP it is 141K. However, I can make a 103K RAR file. Is this acceptable?

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

    Re: requery issue (XP, 2000)

    I don't think I can open a .RAR file, moreover, 103 KB is still too big.

    Try the following to get a smaller size zip file:

    - Create a new, blank Access database, and import only the database objects needed to illustrate the problem into it. Then compact it, and zip it.
    or
    - Convert the database to Access 97 format (Tools | Database Utilities | Convert Database | To Prior Access Database Version (in Access 2000) or To Access 97 Format (in Access 2002), then zip the converted version.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery issue (XP, 2000)

    Converting it to Access 97 made the difference. I have attached the file. I noticed that my subform does not display all the records when there is a null in the searchbox but it seems to display the current record..

    The problem exists on the prospect lookup tab. As you change the prospect record using the nav buttons on top, the display below changes although it should not.
    Attached Files Attached Files

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

    Re: requery issue (XP, 2000)

    Your subform Qry_sub is linked to the main form Master Input form by Prospect/Area Name (in the Link Child Fields and Link Master Fields properties of the subform as a control on the main form. From your description, I would guess that you don't want this subform linked to the main form. Try clearing the Link Child Fields and Link Master Fields properties.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery issue (XP, 2000)

    Sorry, Hans, can you explain that again in simpler language? In looking at the properties of both the mainform and the subform, I don't see any link child or link master field properties. Where would I find them?

    You're correct in that I did not want that subform linked to the mainform. It is a subform only bacause that is the only way I know to get that form into the tab control.

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

    Re: requery issue (XP, 2000)

    Open the Master Input Form in design view.
    Activate the Prospect Look Up tab page of the tab control.
    Click once on the subform in that tab page to select the subform. Don't click a second time on the subform, because then you will select something on the subform. If you clicked a second time by mistake, click outside the subform, then try again.
    Activate the Data tab of the Properties window. You will see the Link Child Fields and Link Master Fields properties of the subform there; they are both set to Prospect/Area Name now. Clear both.

  11. #11
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery issue (XP, 2000)

    Perfect! Thank you again Hans. Now I have learned that when looking at a subform it has to be done in subform mode. Previously, I was looking at subforms in a stand alone mode thinking that whatever properties they had would be present when they were in the mainform. I think now I understand that when in the mainform, they inherit other properties associated with their subform status.

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

    Re: requery issue (XP, 2000)

    Yes, you have to distinguish
    1. <LI>A subform as an object in the database window.
      <LI>A subform as a control on a main form.
    In (1), you will see the properties of the subform as a form in its own right. Some of these properties will also be valid if you open it as a part of the main form (for example, whether scroll bars are displayed), but others aren't (for example, the Caption property.)
    In (2), you will see the properties of the subform as a part of the main form. These properties are specific to this particular situation, for example, the Link Child Fields property, which makes no sense if you open the subform by itself, directly from the database window.

Posting Permissions

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