setting combo.rowsource fires subform.current() event (1 Viewer)

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
Access-FE on SQL-db, multiuser.

I have a mainform (invoices) with some 15 combo's in which user selects data (f.e. invoicesender, invoicereceiver, paymentterms etc)

There is a subform (invoicelines) that is linked to the mainform (on InvoiceID, master/child). The recordsource of the subform is a SQL-view that is a little bit sluggish, (because of some function that checks the state of the invoiceline against stock. I don't see a way to speed that up. The subform being a bit slow is not a real problem, there is a slight delay - around 2sec - on opening the invoiceform which is sufficient

Except:

All 15 combo's have a Enter() and Exit() event that set the combo.rowsource to:
- On combo Enter() "select * from tblCombo WHERE inactive=false"
- On Combo Exit*() "select * from tblCombo"

This way users can only select active values when they open the combolist, but the combo is able to display historic values that are not active anymore and should not be chosen anymore.

The problem is: setting the rowsource of any of the combo's in the combo Enter()/Exit() through VBA fires the subform.Current() event twice, and after or before that (I don't know) the subform refreshes - at least once.
So on any of the combo's entering or exitting it the forms is busy refreshing the subform and I'm getting a 2sec delay.
The rowsources of some the combo's are very small (f.e. select * from tblPayTerm) = 6 records) so that's not the delay.

The subform has no link to the combo's whatsoever, only a master/child link to display the Invoicelines for the invoice. There is no code in the mainform that does anything to the subform. When I disabled the combo.rowsource="select..." in the Enter/Exit (and leave other code on) the subform does not refresh anymore.
I have one combo that does not have a need for the enter/exit rowsourceadjustment, that does not fire the subform.current()

Why should the subform be refreshed? Is there a way I can disabled it? workaround?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:28
Joined
May 7, 2009
Messages
19,169
what are the Items in the Combo?
can you make the source as local table (copying from the SQL table)?
 

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
tnx for your suggestion. I could do a workaround where I copy the values locally, but that would require a lot of effort to keep the data synced with SQL (multiuser), and I'm fairly sure that setting the combo.rowsource on Enter/Exit to a local table would still get the subform to refresh.

But I can test it by reverting one or 2 of the combo's to a local table (just for a test)

btw: When I set a break on the subform.current() and press CTRL-L to see what code fires before the current() there is nothing. So it's something MSaccess does.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:28
Joined
May 7, 2009
Messages
19,169
you can also try not to change the combo rowsource.
just add another column for showing Active/Inactive in the row.

the combo has code on BeforeUpdate to disallow (Cancel=True) selecting Inactive status.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:28
Joined
May 21, 2018
Messages
8,463
I am not sure why this happens, but I use this code to convert a rowsource to a value list. Maybe you could do something like that.

Code:
    Dim rs              As dao.Recordset
    Dim strSql          As String
    Dim fldField        As dao.Field
    Dim fldCount        As Byte
    Dim strLstValue     As String
    Dim intColCount     As Integer
    Dim intRowCounter   As Integer
    Dim i               As Byte
    
    strSql = Me.ListBox.RowSource
    Me.ListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    Me.ListBox.RowSourceType = "Value List"
    
    'IF LBOX COLUMN COUNT PROPERTY > ROWSOURCE COLUMNS - ERROR
    fldCount = rs.Fields.Count
    If ListBox.ColumnCount > fldCount Then
        Me.ListBox.ColumnCount = fldCount
    End If
    
    intColCount = Me.ListBox.ColumnCount
    'Since converting to value list column heads do not work
    If Me.ListBox.ColumnHeads Then
       MsgBox "Unfortunately, this will not work with column heads. You will have to make your own.", vbInformation
       Me.ListBox.ColumnHeads = False
    End If
    
    Do While Not rs.EOF
       For i = 0 To intColCount - 1
          strLstValue = strLstValue & """" & CStr(Nz(rs.Fields(i), " ")) & """;"
       Next i
      
       intRowCounter = intRowCounter + 1
       rs.MoveNext
       strLstValue = Left(strLstValue, Len(strLstValue) - 1)
       Me.ListBox.addItem (strLstValue)
       strLstValue = ""
    Loop
End Sub

If the source data is getting updated then you can run the code on the on current event, but you will have to save the original row source somewhere. Maybe in the tag. This will not likely work if parameterized.
 

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
Some combo's are depending on others, f.e. after you choose the invoicereceiver, you have to choose one of the (1 to many) adresses that belong to that specific receiver (ánd only active adresses, not old ones).
I have to limit the adresscombo to the adresses of the receiver, it would not be practical to show all adresses of all receivers there. It's even more complex because I need to show a filtered receiver adress-list ánd active adresses

It's just stupid: when I do not set a recordsource on the subform alle combo's work with Enter/Exit without any delay. So it's not the setting the rowsource that's slow (becuase of slow combolists), it's actually the subform getting refreshed because I set the rowsource when entering a combo.
But then of course the subform does not show anything... Setting recordsource on the subform in MainForm_Open() does not make a difference
Never seen this behaviour (but I should say I never had any issues with a slow form in a setup like this). I also see it on other forms in this FE (but not everywhere combo's are set with Enter/Exit

Thanks for the tips so far, I'll think about what I can do a bit more.

Btw, I didn't write the Access-FE, I'm just maintaining it. I already sped up the Mainform by making better selects in de combo's but this was a delay that I'm not able to fix so far
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:28
Joined
Feb 19, 2002
Messages
42,970
I don't change the RowSource on the fly. The query bound to the RowSource's includes criteria that references some field on the form.

Also, I leave inactive items in the RowSource, I just get them to sort to the bottom of the list by sorting on the ActiveYN flag first True values sort to the top of the list and false values sort to the bottom. This of course leaves me with using code in the BeforeUpdate event of the combos to prevent the user from choosing an inactive value. That is the only benefit to your current method. You can skimp on validation code by eliminating the inactive values from being chosen.

Are you sure there is not some other code that is forcing the subform to requery? Requering the main form will do that.

The contents of lookup's don't change frequently, so I sometimes use @arnelgp 's suggestion to keep the lookup table(one in my case because I use a consolidate method of defining lookup tables) local. When the app opens, it copies the table that populates all the RowSources down to a local table so they are refreshed each time the user opens the database. There is also a button on a maintenance form that lets the user reload without closing and reopening the app. And the form that does the maintenance for the lookup tables automatically reloads the lookup table after the user modifies a value on the server.
 

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
Ive been doing this for at least 15 years in all kinds of frontends, never had this occurring. It could be that this has always been the case, but I never had such a slow subform (as I said; I'm not the builder) so maybe it has never occured to me that the subform gets refreshed (and it's not a problem on itself)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:28
Joined
Feb 19, 2002
Messages
42,970
Did you search the code? Are there requeries or refreshes in the code? I can think of no reason why Access would requery the subform when you change the recordsource of a combo UNLESS the subform is bound to a query that is using the combos for criteria. If that is what is causing the problem, then the solution is to STOP replacing the RecordSource and use the technique I suggested to drop the inactive items to the bottom of the list and add the code in the BeforeUpdate event of the combo to prevent the choosing of inactive items..
 

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
Of course I did. The subform does NOT use any of the combo's

There is a lot of code in the FE, and I already deleted a lot of unneccesary "me.refreshes" (there are none left) and there is no reference to the subform ("me.subform.form.xxxx") anywhere in the mainform VBA. I also checked if f.e. maybe the mainform gets updated/saved which I think would also trigger a subformrefresh, but that's also not the case as far as I can see

The only link between the mainform and de subform is the master/child linked fields InvoiceID

I cannot think of a reason why Access would display this behaviour, and I have never seen in all my years of building.

What I can do is build a testform setup in a new access-db (simpel main/subform with one combo0 and see if this is indeed Access default behaviour when there is absolutly no other code that I could be overlooking. If that's not the case it's something lurking somewhere in this FE (which I did not write).

btw: it's not just "inactive" items. As described above I also need to filter the adresses of comboB to the selected receiver in comboA. I cannot display all thousands of adresses of all receivers in comboB when a user chooses a receiver in comboA.
Next to that, they should also be active adresses. I could stop filtering inactive adresses, but I cannot stop filtering for adresses of the chosen receiver.

It is absolutly acceptable if I just leave it working like this because all my optimizations have made the form at least twice as responsive as it was before, so the users will notice a considerable performance enhancement already.
It's just annoying that I can't find the cause of this.
 
Last edited:

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
I created a test; a mainform with
- no vba
- one combo with a valuelist (not even a recordset)
- one subform with the same (slow) view as recordsource (same view as the subform mentioned above)
- one button

When I set the rowsource of the combo (to a simple value list like "1;2;3;4;5") by pressing the button (not even OnEnter() ) the form shows a delay and the subform.Current() fires twice.

Conclusion: it is standard Access behaviour and I cannot find any reason why this should work at is does. There is no link whatsoever between whats in the subform and whats in the combo

However, there is a link between the data in the subform and the mainform. I can either use
- Parent/Child linked fields
- setting a subform.recordsource on the mainform.Current() "select from tbl WHERE ID="& mainform.ID
- use a query with a mainform.ID parameter as recordsource
- i could use a storproc or pt-query to get a filtered recordset without having to set any link between main- and subform, that might prevent the problem but I need an editable subform (not possible with sp or pt)

only when I have an unlinked - not linked in any of above 3 ways - subform the subform stops refreshing when I set a rowsource on the combo. That of course does not work

btw, when I browse the mainform to another record (with a linked subform) the subform.current() fires 3 times in a row
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:28
Joined
May 7, 2009
Messages
19,169
if it does not Refresh when Not Linked (master/child link fields). then
don't use master/child link, use dynamically change the rowsource of the subform.

when you add new record you add code to beforeInsert event to insert the Linking fields first
to your subform.
 

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
as I said, I can dynamically set a recordsource on the subform f.e. in the mainform.current() event or even with a button just for testing but the problem remains. As soon as I do, the subform will fire twice on setting a rowsource on the combo

I'm giving up; it has never been a problem for me. The mainform is already a lot faster than before and I will just accept this (stupid) delay. The users will also have to (in practise, they will be happy the form is a lot less sluggish...)

There are probably all kinds of workarounds for this issue, but I try to keep away from workarounds that try to circumvent default behaviour.

Tnx for your considerations anyway (y)
 

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
small positive update:

it seems that when I set a recordsource on the subform based on a Access query with a formparameter: "select * from sqlview where ID=forms!frmInv!InvID" and do not use master/child (no need for of course) and never set any recordsource through VBA, the subform.current() stops firing (not even "select * from sqlview" without filter)
Even when i set literally exactly the same SQL just once (even using subform.recordsource = subform.recordsource) on the form through VBA, either on mainform.Open(), Load(), Current() or on any other means (f.e. button) the problem comes back
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:28
Joined
May 21, 2018
Messages
8,463
Can you post just the form if the db is not possible to post? There is probably some line of code you are just not seeing after staring at this. That behavior does not seem possible.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:28
Joined
May 7, 2009
Messages
19,169
is your subform continuous?
 

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
is your subform continuous?
yes....it must be (meaning it has to show multiple rows)
Can you post just the form if the db is not possible to post? There is probably some line of code you are just not seeing after staring at this. That behavior does not seem possible.
That could be it (that I'm just missing something in all the code), but the behaviour is the same in below test, with local access tables and also with sql tables (you will have to connect the SQL tables)

When the subform has a recordsource like: "select * from tblSub WHERE ID=forms!main!ID" there is no subform.current() event when you set a combo.rowsource,
With any other type of recordsource (f.e. select * from tblSub combined with Master/Child fields, or any variation I can think of (using VBA) to set it) subform.current() fires on setting a combo.rowsource

Try the database below
 

Attachments

  • Database1.accdb
    1.7 MB · Views: 218
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:28
Joined
May 7, 2009
Messages
19,169
it doesn't occur to me on
asus i5, 11th gen, 6-core, 8g memory, x64 office.
 

Zakraket

Registered User.
Local time
Today, 11:28
Joined
Feb 19, 2013
Messages
88
not in my testdatabase with the frmInv?

Did you set the subform frmInvLines to "select * from tblInvLines" and use the Master/Child InvID on frmInv? (option 1)

If I do, I get a msgbox from the subform.current() event after pressing the button that sets the combobox.rowsource

When I use option 2 I don't get the msgbox

Both on my laptop (Office365, i5) or on a RPD server environment. Also office365 but not exactly the same Access version as on my laptop.
 

Attachments

  • Screenshot_8.jpg
    Screenshot_8.jpg
    87.8 KB · Views: 188

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:28
Joined
May 21, 2018
Messages
8,463
I see what you are saying, and I have to say I am confused. I need to build my own test form to see if this is actually default behavior. I do not think so, but I cannot figure what is going on. If that is default behavior it does not make sense.
 

Users who are viewing this thread

Top Bottom