On Enter event in subform to requery another subform

Question

I currently have a parent form ...Form1...with two subforms... subName (with a text box), and subNames (table with all Names)

I would like to use the On Enter event to >type a name

                                                           >hit enter

                                                            >subNames requeries with new name

ive tried drilling down from the parent form like...

         me.[subNames].Form.Requery        and

         [Form1].From.[subNames].Form.Requery

And ive tried this...

         [subNames].Form.Requery

Just a simple On Enter event. Does anyone have any advice why its not working?

Answer 1

Hi,

Something like this usually doesn't need code. How is subform #2 related to the main form or subform #1? How is subform #1 related to the main form?

You can take advantage of the Linked Fields to update the displayed records in each subform. For example, you could place a hidden textbox on the main form referencing the textbox in subform #1. You can then link subform #2 to this hidden textbox. Each time you change the value in subform #1, Access will automatically display the matching records in subform #2.

Hope it helps...

Answer 2

So I created the hidden box and references it with the field from tblsubNames. And I Linked both subName and subNames the same way. Its not working, I know Im missing something. This message popped up...

"You tried to assign the Null value to a variable that is not a Variant data type."

Answer 3

Hi,

That sounds like an error message coming from some code running. Did you remove any code you might previously had trying to filter the subform? As I was saying, using the hidden textbox approach eliminates the need to use any code.

Also, for now, try removing the link to subform #1 because it looks like your main form is unbound anyway.

Hope it helps...

Answer 4

first of all thank you so much for your assistance again.

secondly...no dice.

I started over... both main and sub1 are linked to the table... sub 2 is just the table... the hidden box is referenced as shown.

I have this incorrect as well...

Answer 5

In the after update of the text box in the first sub form, you can try:

Me.parent.[subNames].form.Requery

Because this is two separate sub forms, then they will not “update” automatic, and you are on the correct path that some code is required.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 6

Hi jshot,

I mentioned it above but maybe you missed it. Try removing the links from subform #1 and just have links set up for subform #2.

Hope it helps...

Answer 7

Hello jshot,

You could try to filter the SubNames subform in the enter event.

Private Sub Text20_Enter()
Me.Parent.[SubNames].Form.Filter = "[FName]='" & Me.Text20.Value & "'"
Me.Parent.[SubNames].Form.FilterOn = True
End Sub

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

I'm puzzled as to why are you using subforms for this?  You apparently want to filter a set of rows on the basis of a value entered in a text box.  This can be more simply achieved by means of one form, in continuous forms view, with an unbound text box in the form header.  Even better would be an unbound combo box in the header, in which the user can either select a name from the list, or progressively go to the first match in the list as each character is type into the control.

If you want to navigate to a specific record, then code in the unbound control's AfterUpdate event procedure would navigate to the record, leaving all others still visible.  If you wish to filter the form to one or more matching records then the code would set the form's Filter and FilterOn properties appropriately.

You'll find examples of both of these approaches in FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes, amongst others, forms to 'go to a record' or to 'filter by a non-key value'.  In my case the forms are in single form view, but that is immaterial.  The methodologies would be the same with a continuous form with the unbound control in the header.  In each case the relevant code is in the unbound control's AfterUpdate event procedure.

Note also how the RowSource property of the first adds a <New Contact> item to the top of the list, and that of the second adds an All Names item to the top of the list.

---

Ken Sheridan, Stafford, England

Answer 9

Ken,

I will take a gander at this wealth of info. Thank you Mr Sheridan.

This is a simplified version of a much larger project that Im doing at the moment. Think of it as a test and check for the bigger one that is schemed the same way... input boxes in one subform and the list it populates to in the one below it in real time. once you hit enter you can see it update and magic your face off. This isn't entirely my design its just my part.

In other news... I have taken a play from Terry Xus playbook... IT WORKS HOWEVER, it throws an error when opening.

Private Sub F_Name_Enter()
Me.Parent.[subfrmsubnames].Form.Requery      <<<<<<<<<  ERROR ON THIS LINE
End Sub

Answer 10

My apologies guys it was ALBERT KALLALs idea.

Answer 11

It was/is assumed that "after" you enter some value in the text box in the first sub form, then you want wanted the second subform to "update". (that was my assuming - I may well have been incorrect in my assume)

In your example, you using the "on enter" event of the first sub form "control". That code and control is STILL on the main form. So no parent form exists at this point in time - the code you are running is STILL in the main form.

The basic issue is what you want to occur in the first sub form AFTER you enter something into the text box on that first sub sub form.

However, it seems you JUST want the 2nd form to requery WHEN you enter the first sub form. This will only occur when  you move or "enter into" the first sub form.

As noted, in above, you using the on enter event of the sub form control of the FIRST sub form. However, at that point in time, we not really done anything in that first sub form, have we?

So it not clear why (or what) you want to occur when the user "jumps" into or sets focus to the first sub form.

However, if your basic question "still" is the simple issue of how to force the 2nd sub form to requery by the mere act of entering into the first one?

Then you above code should be:

me.[subfrmsubnames].From.Requery

So while the above is the correct syntax, with your given problem, the above code correct is now.

So if your goal really is to re query the 2nd subform when the focus or user "enters" into the first  sub form, the above syntax should work.

The sub form control is still on the main form, and that event is part of the main form - so no "parent" exists at this point in time.

so me.Parent ONLY works for code + events inside of the sub form, not controls on the main form like this case. However at this point in time, we really not done anything in the 1st sub form, nor have we changed anything - all we doing is forcing the 2nd form to requery by the "mere" act of entering into the first sub form. If that "really" is your goal, then the above code should work just fine.

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 12

Think of it as a test and check for the bigger one that is schemed the same way... input boxes in one subform and the list it populates to in the one below it in real time.

It makes little sense to use a subform as a dialogue for entering criteria on which another subform's rows will be restricted or filtered.  Subforms are really intended to be used as bound forms, generally based on a table which is the referencing table in a one-to-many relationship type, where the parent form is based on the referenced table.

The simplest interface is that which I described, where unbound controls in the header of a bound form are used for restricting or filtering the form's recordset, either by means of code, or by basing the form on a query which references the unbound controls as parameters.

An alternative is to use an unbound parent from, within which is a bound subform.  The parent form contains the controls in which criteria are entered or selected.  Rather than restricting the subform by referencing the unbound controls as the LinkMasterFields property, performance is far better if the unbound controls are referenced as parameters.  This also makes it easy to make the parameters optional, so values can be entered in a subset of the unbound controls only.  This is done by testing not only for equality with the parameter, but also for the parameter being Null.  This is done in a parenthesized OR operation in the query's WHERE clause:

WHERE (SomeColumn = [SomeParameter] OR [SomeParameter] IS NULL)
AND (SomeOtherColumn = [SomeOtherParameter] OR [SomeOtherParameter] IS NULL)
AND.......etc

The parameters referenced  in this way can be of any number, simply by adding further AND operations.  This applies equally to the use of a bound form with unbound controls in its header, or to a subform where the unbound controls are in the unbound parent form.  The logic is extremely simple, and performance is good, provided of course that the columns are appropriately indexed.

---

Ken Sheridan, Stafford, England

Answer 13

Albert,

Thank you for responding. Yeah the code you provided does works near perfectly and does what I need it to do...... enter a value in subform 1.....press enter...... subform 2 automatically reflects changes.

The issue is when I open the form from the navigation pane, the above screen shot error pops up. Maybe my assumptions are wrong. When I type code into the "on Enter" event, its saying that when I hit the enter key the code runs.

Answer 14

Hi jshot,

Do you still have the linked fields set for subform #1?

Earlier, I suggested you try removing it.

Just curious...

Answer 15

DBGuy,

Yessir I did. I made the correction earlier I apologize for not commenting to you//leaving you hanging.

Subform #1 has no master or child links. But it does have the table as a record source with the above code as the only VBA (in the project) in the FName field.

Subform #2 (which contains the table) has no linked fields as well. NO VBA.

Main form has no record source nor VBA.

Answer 16

DBGuy,

Yessir I did. I made the correction earlier I apologize for not commenting to you//leaving you hanging.

Subform #1 has no master or child links. But it does have the table as a record source with the above code as the only VBA (in the project) in the FName field.

Subform #2 (which contains the table) has no linked fields as well. NO VBA.

Main form has no record source nor VBA.

Okay, to recap what I suggested:

1. Main form is unbound

2. Subform 1 has no linked fields set up

3. Hidden textbox on main form refers to textbox in subform 1

4. Subform 2 has linked fields set up using the hidden textbox

5. No VBA whatsoever, anywhere

I was just curious if you tried this setup and it didn't work for you.

Cheers!

Answer 17

DBGuy,

Yessir, it did not work. Let me try this again real quick..... be back in a sec.

Answer 18

DBGuy,

Yessir, it did not work. Let me try this again real quick..... be back in a sec.

Okay, I wanted to know. Thanks.

If it still doesn't work, without VBA, then I guess Albert was correct that the main form has to be refreshed after subform 1 is changed.

In this case, I would try using the following code in the AfterUpdate of the textbox in subform 1:

Me.Parent.Recalc

Hope it helps...

Answer 19

Bind the hidden text box in main form,, to the box I enter a value into in subform 1,, as the control source?

Answer 20

Bind the hidden text box in main form,, to the box I enter a value into in subform 1,, as the control source?

Yes, by using something like:

=[SubformName].[TextboxName]

Hope it helps...

Answer 21

Answer 22

No luck

Answer 23

its showing #Name? in the hidden txt box

Answer 24

Looks correct...

You might show the hidden textbox for now for troubleshooting purposes.

Again, if you don't see it change after entering the name in the subform, try using the Recalc method.

Cheers!

Answer 25

SO CLOSE!!!!!! the recalc works.... hit enter.... subform 2 refreshes..... MUST hit enter twice to commit your input as new record... otherwise any added letters will be added.

Im in the process of trying a few different lines of code to cap it off.

Answer 26

SO CLOSE!!!!!! the recalc works.... hit enter.... subform 2 refreshes..... MUST hit enter twice to commit your input as new record... otherwise any added letters will be added.

Im in the process of trying a few different lines of code to cap it off.

You lost me a little bit. Are you using the AfterUpdate event or the Change event?

Answer 27

When correlating two subforms performance is far better if you cut out the hidden control middleman and simply reference the relevant control in the first subform as a parameter in the second subform's RecordSource query.  The following is an example from the CorrelatedSubs demo in the same OneDrive folder to which I gave you a link earlier:

SELECT OrderDetails.*
FROM Products INNER JOIN OrderDetails
ON Products.ProductID = OrderDetails.ProductID
WHERE OrderDetails.OrderID = [Forms]![frmCompanies]![frmsubOrders]![OrderID]
ORDER BY Products.ProductName;

In this case the first subform is bound to the referenced  Orders table, so the second subform is requeried in the first subform's Current event procedure:

    Me.Parent!frmsubOrderDetails.Requery

With an unbound first subform you'd do so in the AfterUpdate event procedure of the control in the first subform.

---

Ken Sheridan, Stafford, England

Answer 28

Thanks again for assisting me.

def the After update.

Private Sub F_Name_AfterUpdate()
Me.Parent.Recalc
End Sub

Answer 29

Ok, so some action is to occur “after” we update that text box in sub form 1.

My original code should thus work. Because we using me.parent.[subform 2].Form.Requery, then it will NOT matter if we place this “whole” mess inside of a navigation system/form.

Also, the “on enter” event has zero to do with Enter key.

You need (and want) to use the after update event of the text box in sub form 1.

However, because there is ONLY one control on the form, then hitting “enter” can’t move to another control. You may well have to place a fake text box after the first text box. You also need to set the forms “record navigation” to current. (The form will try to move to a new record. Given the form is un-bound, then this may not be an issue. However, for testing, I would place a “tiny” 2^nd text box after the first in sub form 1 – since without any additional controls, then tab/enter key can’t move to another control.

So the event you are to use here is the after update event of the text box on sub form 1. You don’t need nor want to use the “on enter” event, since that fires when you move into the control. We want an event that fires after we update that text box. However for testing, place a 2^nd text box, since tab/enter key can’t move to another control – this will interfere with the after update event firing on that first form.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 30

SO CLOSE!!!!!! the recalc works.... hit enter.... subform 2 refreshes..... MUST hit enter twice to commit your input as new record... otherwise any added letters will be added.

Im in the process of trying a few different lines of code to cap it off.

Hi,

By the way, I just gave it a try and I don't have to use Recalc at all. When I enter something in the textbox in subform 1, the hidden textbox shows up right away, and subform 2 gets filtered right away too.

I am not using any VBA code at all.

Answer 31

ok so error on my part. that's fair... i knew it was a big possibility. im going to restart.

Answer 32

Albert,

so slap a txt box in subfrm 1 with that txt in it? or on the main form?

Answer 33

DBGuy,

To clarify, my master field is the hidden txtbox and the child is the column name in my table?

Answer 34

DBGuy,

To clarify, my master field is the hidden txtbox and the child is the column name in my table?

Yes, that's correct. However, I just realized, to make my hidden textbox show up the value from Subform 1, I had to use the following syntax:

=[SubformName].[Form]![TextboxName]

Hope it helps...

Answer 35

Heres what i got... the hidden box now populates with the value but the table doesn't. Note, ive circled the record selector in red showing that i entered random values in 10 times... also to show the FName field in the form view i had to use the ID field as the child and the hidden boxes name. Im using the names i see under the other tab in the properties menu.

I will be back on tomorrow AM... i want to sincerely thank all for their assistance and my apologies for my adept Access skills.

Answer 36

Hi,

When you set up the master and child linked fields, the values have to match. So, when you enter "eryh" in the textbox, are you expecting to see IDs with the value "eryh" too? Otherwise, link the textbox to the FName field and then enter a matching name in the subform textbox.

Hope it makes sense...

Answer 37

Ok I meticulously followed all previous advice according to the hidden txt box method. This is what I have...

Form 1 (main form) - Unbound

Subform 1 (subfrmName) - bound to the table

--- Text box in Subfrm1 - Unbound

Subform 2 (subfrmNames: which is just the table refrenced as the subform)

--- master and child links are both FName (this is also the name I gave the hidden text box and is the name of the field in Hidden text box references the text box in subfrm1.

NO VBA anywhere.

CURRENT EFFECT:

Hidden text box populates... table inside subfrm2 does not.

Answer 38

**CORRECTION**(this is also the name I gave the hidden text box and is the name of the field in the table. The hidden text box references the text box in subfrm1.

Answer 39

Hi,

Can you send me a sample copy of your db? I can't see where we're having a disconnect. Sorry.

Answer 40

sent it to your DBGuy email

Answer 41

sent it to your DBGuy email

Got it and sent it back.

I looks like you did everything right, and when I tried it, it works as expected for me.

My understanding is the first subform will be used to sort of "search" for matching names in subform 2, that's why the textbox is unbound (you did say it was unbound originally, correct?).