Speeding up the loading of multiple cascading combo boxes

[Pat Hartman]

Is there another way to select an item in a listbox? I am filling a form with selected information.

Normally we would expect a form to be filled by a human so what you are doing makes no sense. If the data already exists somewhere, why are you copying it to another table? And why do it with code rather than an append query?

 

[strager]

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!

Thanks I will try that. Looks much easier.

 

[strager]

Normally we would expect a form to be filled by a human so what you are doing makes no sense. If the data already exists somewhere, why are you copying it to another table? And why do it with code rather than an append query?

People do save information they have entered into a form, and at times they want to view what they have entered into the form. I am not copying to another table, I am filling the form with their saved record. Also, these primary forms do re-occurring scheduling. They can select any day, any week, week #, # of days, day of week etc and schedule out as long as they want... they can also adjust schedules by what ever they want. To do this you have to code. I do have a copy button on the forms. Users like that. Sometimes there are similar entries so instead of manually typing everything in, they can click a button and just make a couple of edits. I don't like binding anything and I don't like the limitations of Access, but since I gave up my Microsoft developer license about 10 years ago, I decided to just use Access from my 365 subscription.

 

[Pat Hartman]

I am not copying to another table, I am filling the form with their saved record.

But that is exactly what you are doing. The form's record will be saved to a table. Copying from a combo???? That is a very unnatural design.

I don't like binding anything and I don't like the limitations of Access, but since I gave up my Microsoft developer license about 10 years ago, I decided to just use Access from my 365 subscription.

Bound forms are what make Access a RAD tool. To eschew them is to ignore the absolute best feature of Access. You would do far better to choose a different platform if you think Access is a poor tool. Otherwise, you might want to learn how to use it. If you are going to copy a record, the most efficient method is an insert query,

 

[strager]

But that is exactly what you are doing. The form's record will be saved to a table. Copying from a combo???? That is a very unnatural design.


Bound forms are what make Access a RAD tool. To eschew them is to ignore the absolute best feature of Access. You would do far better to choose a different platform if you think Access is a poor tool. Otherwise, you might want to learn how to use it. If you are going to copy a record, the most efficient method is an insert query,

What combo am I copying from? I am setting the value of combo boxes from the table. As far as Access goes, it is good for what it does. I just prefer more flexibility. I retired and Quit programming in 2010. I'm not sure what version of asp.net I was using. I used VBA with vb4- vb6 for a lot of years so I know VBA pretty well, in fact I used to teach it at the community college of southern Nevada. Before commenting on a post, you should read the code... if you know how. BTW I am writing this program for myself and some friends.

 

[Pat Hartman]

What combo am I copying from?

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.

 

[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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

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

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.

 

[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!

 

[Pat Hartman]

Helping people isn't a bother. I'm glad you took my advice to use a platform you like better. I've never heard of Xojo before but do let us know what you think of it. I did a quick look at their website and couldn't get a feel for what it could do at all. I'm probably not going to download it so it would be interesting to hear your opinion of how it compares to Access. Access' bound forms make Access a very quick development tool once you understand how it works. Technically you need no code to produce a working application. But, in reality, no tool is useful if code can't be used to control certain things such as data validation. I still use the wizard to build a form for me. And then I fix it up. Creating a query first allows me to impose a little on what gets created by the wizard. It allows me to pick and choose fields and decide ahead of time on their order on a form. The wizard builds lousy forms but it saves enough work to make it useful.

I came to Access from COBOL in the early 90's. My first task out of the box was to build an application for Readers' Digest to help their sales staff upsell customers. My client suggested Access as an option to use to build the app rather than COBOL. The data was in DB2 tables on the IBM mainframe and I linked to them with no problem and I was off and running. Took me about a week to figure out some basic stuff and the app wasn't particularly difficult since no updating was involved. It was basically a tool to search the DB2 database for related items. Customers who bought a also bought b. Long before youtube was feeding us other videos we might like. So, by the end of the second week, the app was complete and I was hooked. It took soooooooo much less effort to create an interactive transaction/form using Access than it did with COBOL/CICS that it was at least a ratio of 1:5 and lazy bum that I am, I moved out of the mainframe world and into the Access world. I personally think that Access gets it about 90% right. There are things I would like it to do differently or to do that it doesn't do but I only complain a little. And 30 years later, Access still amazes me because it is essentially the same app I was using in 1994 and the foresight the original developers had is mind blowing. It's a little prettier. Some old bugs are fixed. Some new bugs have been added. It does some things it didn't used to do. The one thing it still doesn't do is work quickly over the internet. But Access was multi-user out of the box in the early 90's and it still works amazingly well today on a LAN, especially if you are using SQL Server or some other RDBMS rather than Jet or ACE. All the bad press Access gets is generally directed at perceived or actual flaws of Jet/ACE rather than at Access the RAD tool.