Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Requery a form when inside the form??

    Hi all,

    I have a subform called Main_Sub1 and a text box outside the subform called txtbox_Selection

    The subform is set to Continous form and displays a row of records.
    When i type in the textbox "txtbox_Selection" I have a requery action on the subform (Main_sub1) set on 'Key up'

    Main_Sub1 control source query contains the following code in the critieria under the field a would like to filter.

    Like [forms]![main]![Main_Sub1]![txtbox_Selection].[Text] & "*"

    So when I type in the text box it filters the results of the subform.
    This works fine.


    I would like to move the text box inside the Subform (Main_Sub1). However, when i start to type in the text box it says I must save the current field before running the requery action. I've tried to use the if dirty = true set it to false but it didn't work.

    Anyone have any ideas how I can fix the problem?
    thankss
    marcus

  2. #2
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Does anyone have any idea how I could fix the problem?
    Kind regards
    marcus

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Do you need more information or have I been black-listed for some unknown reason

    help me please!!! I still very new to access and to be honest i find it a struggle

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Providing some more information would be helpful. Try to provide the relevant VBA code and SQL statements, not just the fragment of a query.

    Meanwhile, I don't understand why you want to move the control to the subform, as this will cause it to be replicated for each row.
    Have a nice day!

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho,

    Ok here is more detail.
    unbound form called Main.
    In that form a page, with numerous tabs.
    On the first tab called Overveiw I have a subform called Main_Sub1 which is a continuous form displaying a list of records. The user clicks on a record and I update the information in the other tab/pages based on the selection.

    Above each column, outside Main_sub1 i have a text box for the user to type into. Physically each text box is the same length as the fields in the Main_Sub1, so the user knows if they start typing in the box it starts to filter the results based on that column. This works fine.

    The problem I have is the subform has numerous fields which cannot all be displayed at once. The user has to navigate with the horizontal scroll bar to view all the fields. When the user does this the text boxes do not align up, because they are static and outside the subform. I need the text boxes to be placed inside the subform so they move when the user scrolls horizontally.

    This is when the problems start.

    Run-time error 2118
    You must save the current field before you run the Requery action

    Here is all the code relating to the problem.

    SQL for the subform

    SELECT Orders.Ref_ID, Orders.Contract, Orders.Date_Created, Orders.Customer_Name, FROM Orders
    WHERE (((Orders.Ref_ID) Like [forms]![main]![Main_Sub1]![cbo_RefIDSelection].[Text] & "*") AND ((Orders.Contract) Like [forms]![main]![cbo_ContractSelection].[Text] & "*") AND ((Orders.Date_Created) Like [forms]![main]![cbo_DatedSelection].[Text] & "*") AND ((Orders.Customer_Name) Like [forms]![main]![cbo_CustomerSelection].[Text] & "*"));



    So Orders.Ref_ID text box is inside the subform called Main_Sub1 in the form header, which is where I am having the trouble.

    The other fields are outside Main_Sub1 and they work fine. So as I start typing the results get filters based on the text I enter for that particular field, but if I use the scroll bar they no longer marry up.


    VBA code:
    Event triggered "on Key up"
    Forms.main.[Main_sub1].Requery

    Hope that's enough information
    thanks
    Marcus

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thanks for the precisions.

    Two things to begin with:

    1. My remark still remains. If you place an unbound control (a combo: [cbo_RefIDSelection] according to the SQL statement you posted, a textbox: [RefIDSelection] according to your explanation) on a form in Continuous view or in Datasheet view, this control will be repeated for each row of data displayed in the form.

    2. If you trap the Key Up event of the control and force a Requery of the subform there, this means that the Requery will occur for every key stroke in the control.

    Now, if we examine the SQL statement used as the DataSource of the form Main_Sub1, we see that it contains a reference to the form Main_Sub1 itself (a kind of self-reference or circular reference) with:
    Code:
    ... Like [forms]![main]![Main_Sub1]![cbo_RefIDSelection].[Text] &...
    And a key stroke in [cbo_RefIDSelection] causes the form to be requeried.

    However, the Dirty event for the form occurs before the KeyUp event for the control (meaning that the form "knows" that the current record must be saved or the modification must be cancelled) while the Value property of the control has not changed yet. Here's the chronology of events for a TextBox with a Date/Time format:
    Code:
    Order | Object  | Event       | Text property | Value property | Notes
    ------+---------+-------------+---------------+----------------+------------------------ 
        1 | Form    | Current     | 2/01/2011     | 2/01/2011      | 
        2 | Control | Click       | 2/01/2011     | 2/01/2011      |  
        3 | Control | Enter       | 2/01/2011     | 2/01/2011      |  
        4 | Control | KeyDown     | 2/01/2011     | 2/01/2011      |  
        5 | Control | KeyPress    | 2/01/2011     | 2/01/2011      |  
        6 | Form    | Dirty       | 2/01/2011     | 2/01/2011      | *** Current row has changed
        7 | Control | Dirty       | 2/01/2011     | 2/01/2011      |  
        8 | Control | Change      | 1             | 2/01/2011      |  
        9 | Control | KeyUp       | 1             | 2/01/2011      | *** A Requery here will cause 
                                                                   |     a Form Current Event
       10 | Control | KeyDown     | 1             | 2/01/2011      |  
       11 | Control | KeyPress    | 1             | 2/01/2011      |  
       12 | Control | Change      | 1/            | 2/01/2011      |  
       13 | Control | KeyUp       | 1/            | 2/01/2011      |  
       14 | Control | KeyDown     | 1/            | 2/01/2011      |  
       15 | Control | KeyPress    | 1/            | 2/01/2011      |  
       16 | Control | Change      | 1/1           | 2/01/2011      |  
       17 | Control | KeyUp       | 1/1           | 2/01/2011      |  
       18 | Control | KeyDown     | 1/1           | 2/01/2011      |  
       19 | Control | BeforUpdate | 1/1           | 1/01/2011      | *** The Value property of 
                                                                   |     the Control only changes here 
       20 | Control | AfterUpdate | 1/1           | 1/01/2011      |  
       21 | Control | KeyPress    | 1/01/2011     | 1/01/2011      |  
       22 | Control | KeyUp       | 1/01/2011     | 1/01/2011      |
    A Requery event causes a Current event to occur. Both cannot be performed before the BeforeUpdate event (see BeforeUpdate and AfterUpdate events in Access Help) of a control in the form. If you trapped the BeforeUpdate or AfterUpdate of the control this could possibly work, ot at least you would be able to control the Dirty property of the form.
    Have a nice day!

  7. #7
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho,

    Thanks for the information. In the end I lengthened the subform so there were no scroll bars and positioned the text boxes outside the subform. I couldn't get it to work any other way.
    thanks again
    Marcus

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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