Solved Am I going crazy? Combobox refreshing query in subform when updated (1 Viewer)

ChrisC

Registered User.
Local time
Today, 00:28
Joined
Aug 13, 2019
Messages
90
Hi all,

Im going crazy!

I have a sub form showing query results on my main form where the user can directly select a field which is then displayed in a text box back on the main form.
I also have a combo box, which is completely unrelated to either the text box OR the combo box. this is populated with a simple Value List.

Yet, when i select a value in the combobox list, the subform refreshes, and "defaults" the users selection to record 1.

I have stared at the code for the combobox, form and detail in VBA to make sure that I haven't inadvertently linked things but they are completely seperate.

Does anyone have any ideas what could be happening? I have another combo box that does similar job to the first, but DOESNT affect the subform.

Many thanks!!!

Chris
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:28
Joined
Feb 28, 2001
Messages
19,384
When you have the sub and parent, you say they are not linked. But how are you "selecting" what is being displayed in the text box? For instance, do you just copy to an unbound box? Or did you change the .ControlSource of the box? How did you implement that selection? Is there a chance that you implemented this with a change to the parent's .RecordSource in some way?

Certain changes to .xxxxSource elements on a form will force a .Requery, and I'm wondering if one of those cases apply.

The other possibility is, since this is a parent/sub case, do you have anything in either form's .Enter or .Exit event routines?
 

ChrisC

Registered User.
Local time
Today, 00:28
Joined
Aug 13, 2019
Messages
90
Hi Doc,

I select whats going in the text box with the following code: whereby the sub form is InventoryQuerysubfromReceipt, the parent is frmReceipt and WHLocatID is the field that is returned in the text box.

=[Forms]![frmReceipt]![InventoryQuerysubformRECEIPT].[Form]![WHLocatID]

Recordsource for the form remains blank through the "operation".

the VBA assigned to the comb box in question is:

Private Sub cboDestination_AfterUpdate()
If Me.cboDestination = "Return from Production" Then
Me.cboOther1.RowSourceType = "Table/Query"
Me.cboOther1.RowSource = "qryUsers"
Else
Me.cboOther1.RowSourceType = "Table/Query"
Me.cboOther1.RowSource = "qrySuppliers"
End If
End Sub

cboOther1 (forgive the horrendous name) is the other combo box i have, which does not affect the record that is selected in the subform.

Quite literally everything else works on the form. works like a dream. it is just this one "little" thing that is bugging me!

The only events attributed to the form itself are those that hide (visible = false) a series of text boxes (until they made visible again later with other actions).

many thanks
chris
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:28
Joined
Feb 28, 2001
Messages
19,384
When you chance the .RowSource, I'm thinking that is where you force a requery. Could be wrong, understand, but that might be your culprit. You see, you are not merely loading a value to a combo - you are changing its internal linkage because of the fact that it was bound to one query and suddenly it is bound to another. I could go all philosophical on you about why it can't be expected to stay put, but ... the bottom line is that changing any kind of recordset source pretty much forces some things to occur behind the scenes. That .Requery is one of those things.
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:28
Joined
Sep 21, 2011
Messages
7,898
My understanding was changing a rowsource negates the need for a requery, which I see so often after a setting the rowsource.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:28
Joined
May 21, 2018
Messages
4,763
I do not think the code is doing that, unless there is more. Show us the control source of the combo that causes the requery, and show the subforms recordsource. My only guess is what you think is a requery is actually a changed foreign key value in a joined table.
 

ChrisC

Registered User.
Local time
Today, 00:28
Joined
Aug 13, 2019
Messages
90
Hi guys, sorry only just replying.

Thanks for your messages.

Here is the combo box control source property - as you can see, it is blank:
1598427622858.png


and here is the info for the subform:
1598427691354.png
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:28
Joined
May 21, 2018
Messages
4,763
If I understand this is the combobox that changes the rowsource of cboOther but does not change anything to do with the recordsource of the subform. However when you change the combo rowsource the underlying recordsource appears to requery. Are you sure there is not linking of the subform to the main form in the link masther and link child fields? What is the recordsource of the subform and does that have any field references in it? Can you post the DB or a portion of it? Or can you post all of the code on the form and the sql for the forms query?
 

ChrisC

Registered User.
Local time
Today, 00:28
Joined
Aug 13, 2019
Messages
90
Sorry that I haven't been able to look at this until today. thank you MajP - i will get back to you ASAP.
 

ChrisC

Registered User.
Local time
Today, 00:28
Joined
Aug 13, 2019
Messages
90
Hi MajP,

If I understand this is the combobox that changes the rowsource of cboOther but does not change anything to do with the recordsource of the subform. However when you change the combo rowsource the underlying recordsource appears to requery.

Yes, that is basically correct, even though the combobox isn't (knowingly) linked to the subform at all. It is a "standalone" part of the form.

But before I write any more - i have sorted it!! :) :)
I can not pretend to understand why, but a TOTALLY different combobox on the form was the culprit. This had code for a requery of the subform in it and although this combo isn't used at all in conjunction with the "problem" box, i move the code elsewhere and everythign works exactly as it should.


Thank you to all for your help. Im gradually learning more and my troubleshooting skills are beginning to get better!

Chris
 

Users who are viewing this thread

Top Bottom