Speeding up the loading of multiple cascading combo boxes

[strager]

Sorry, I said combo by mistake. You said you were filling from a listbox.

Access is a RAD tool. It therefore has its own ways of doing things. You will find that when you use a screwdriver as a hammer, the task is ever so much harder than what it needs to be. I suggest using a tool as it was designed to be used. You might find that you like it better than you used to. When you want to twiddle bits, use a low level programming tool. When you want development speed and productivity, use a high level programming tool. One tool is not better than another but they are each better suited to different tasks. When you want to do a lot of extra work, use a low level tool to do a high level job. When you want to frustrate yourself, use a high level tool as if it were a low level tool.

Your code seems to be doing much more work than it needs to do. If you are taking data from a table via a listbox, that data presumably has already been validated or it shouldn't be in the table so I'm not sure what the purpose of the UDF type conversion functions are. Why can't you just copy from one control to another?

And finally, VBA loops are significantly slower than append queries. If you want to generate a bunch of schedule records, a simple solution is an append query that takes an argument as the FK and joins to what some folks call a "tally" table. A "tally" table has a number of rows with just numeric values - 1,2,3,etc. Or dates 10/7/24, 10/8/24, etc. You use a range or quantity to select the number of rows you want. When using dates, you may want to include a week day field so that you select 10 "Mondays" if you want to schedule an appointment for the next 10 Mondays.

While writing this program I have discovered some new features. Back a long time ago if you wanted to programmatically select an item in a combo box you had to loop through it. Since I programmed in VB for a lot of years, and already had Access I decided to write this program for myself. I am older now, it's harder to learn new languages so I am sticking with a language that is kind of like riding a bike... Said of skill that, once learned, is never forgotten. As for binding, I never really bound anything, it would be like learning a new language.

 

[cheekybuddha]

You are confusing your code with code to make selections in a multi-select listbox

 

[strager]

You are confusing your code with code to make selections in a multi-select listbox

 

[cheekybuddha]

With a bound form you would just have RecordSource:

SELECT
  *
FROM Invoices
WHERE InvoiceID = [lstSelectInvoice]

Not one line of code required

 

[strager]

Do not work with VBA objects the way you work with VB objects. Also, FYI, the default property for a control with VB is .text but the default property for VBA is .value - don't ask;(

There are more differences. Just keep this at the front of your mind - Access is a RAD tool. It's objects have properties and methods that will help you. Before embarking on using a new VBA object for the first time, take some time to review all its properties and methods. Don't assume you know how they work. Objects such as combos and listboxes have TWO places of binding. The control can be bound to a column of a table/query and that is where the data will be saved. The RowSource is a table or query that provides the list of items displayed. YOU don't need to fill the list whenever the form opens, Access automagically does it for you

The listbox is not multiselect. I do have some that our, but this is not.

 

[strager]

With a bound form you would just have RecordSource:

SELECT
  *
FROM Invoices
WHERE InvoiceID = [lstSelectInvoice]

Not one line of code required

I really like coding and I'm pretty fast at it.

 

[cheekybuddha]

I really like coding and I'm pretty fast at it.

Me too

 

[strager]

The listbox is not multiselect. I do have some that our, but this is not. View attachment 116378

Yes:

Public Function SetComboBoxValueLong(cnt As Control, LID As Long)
  cnt.Value = IIf(LID > 0, LID, Null)
End Function

though a separate function to set the value of a combo seems overkill!

This code really helps. Didn't know you could do that. Thanks

 

[cheekybuddha]

As I mentioned, you don't really need the function.

Just use:

Me.cboWhatever = rs.Fields("Whatever")

If the recordset field is Null, then the combo will have Null as its value. Better than arbitrary -1

 

[strager]

As I mentioned, you don't really need the function.

Just use:

Me.cboWhatever = rs.Fields("Whatever")

If the recordset field is Null, then the combo will have Null as its value. Better than arbitrary -1

Wow, I will definitely try that. Thanks

 

[strager]

Then don't ever complain about Access, since you have no desire to use it well.

Personally, I've written my million lines of code and don't need the practice. I have better things to do than to waste my time recreating functionality that already exists and actually works efficiently with code that is inefficient and now you're asking us to make it efficient. Use the controls correctly and the code will be efficient

Because you suggested that I should use a different development environment, I decided to look into it. I found Xojo and MySQL. I used SQL Server for a lot of years and with work bench, I have the IDE. I went through the code for xojo and it is very much like vb, so I've set that up. I'm hoping I don't have to bother you again. Only millions of lines??? I wish. I programmed for 40 years!