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?
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.
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:
... 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:
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.
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.