Add data to combobox recordsource

[jpl458]

Is there a way a user can add data to a combobox. I have a form with a combobox that allows the user to select values from that box. But each user might have some entries that are different from the others. Is there a way a combobox can be set up to allow the addition/deletion items from the box?

 

[mike60smart]

Is there a way a user can add data to a combobox. I have a form with a combobox that allows the user to select values from that box. But each user might have some entries that are different from the others. Is there a way a combobox can be set up to allow the addition/deletion items from the box?

Hi
Yes you can use the Not In List Event

 

[Pat Hartman]

It makes no sense for a combo to have different values for different users. The point of a combo is to streamline data entry as well as to standardize it.

Keep in mind that if you do this at the form level, your users changes will be lost when you distribute a new copy of the FE.

So, do NOT do this using the not in list event.

If you really must have different lists for different users, create a table with a form that maintains it. That will allow each user to create his own list because each row will include his login name. Then the query for the RowSource would use a where clause that selects only rows that that particular user has entered.

 

[jpl458]

Hi
Yes you can use the Not In List Event

Thanks

 

[The_Doc_Man]

Read Pat's response. She is correct in that if you had a list as your .RowSource (rather than a query or table) then you lose all added entries the next time you update. So... yes, you CAN use the Not In List event. But only if you are going to add a row to a table that will be caught by the query that is your .RowSource!

 

[jpl458]

It makes no sense for a combo to have different values for different users. The point of a combo is to streamline data entry as well as to standardize it.

Keep in mind that if you do this at the form level, your users changes will be lost when you distribute a new copy of the FE.

So, do NOT do this using the not in list event.

If you really must have different lists for different users, create a table with a form that maintains it. That will allow each user to create his own list because each row will include his login name. Then the query for the RowSource would use a where clause that selects only rows that that particular user has entered.

There is no back end to this app. Everything the user needs is in the app itself. And, there is not a lot of data.

Read Pat's response. She is correct in that if you had a list as your .RowSource (rather than a query or table) then you lose all added entries the next time you update. So... yes, you CAN use the Not In List event. But only if you are going to add a row to a table that will be caught by the query that is your .RowSource!

I read Pat's response, and it makes sense. But this app has no backend, all data is in the app or added by the user, and the data amount will be somewhat small.

 

[Pat Hartman]

I presume you are never going to make any change to the application, EVER. Having embedded data, even for a single user application makes maintenance a nightmare.

 

[KitaYama]

It makes no sense for a combo to have different values for different users.

Not correct.
We have users who register new orders and each of them is in charge of specific customers. So two different users, have different list of customers in OrderedFrom combo box in the same form. We filter and show them only those who are relevant to them.

In shop floor, we have different machines. Each operator does a different job. So when they open the same form, they see different lists in their combo boxes, a list of jobs that are assigned to them. It makes no sense to show a lathe operator a list of a NC jobs.

And no need to tell you when they want to print a report or a drawing, they have different devices in their "Select a Printer" combo box. Because it makes their life harder if we show them the same list, even the printers that is not connected to their machine.

There are a lot more cases that different users have different values in their combo boxes.
The need to change a combo's row source based on what the logged in user is allowed to do is obvious. It keeps the row count of the combo box smaller and makes it easier to select one. And for sure it's a way to prevent user to mis-select an item that is not relevant to his/her job.

 

[Pat Hartman]

@KitaYama What you are suggesting is not how I took the OP's statement. The combos you are talking about are not simple lists, they are data from tables that have multiple columns and other uses throughout the application. Simple lists are things like gender, language, status codes. A user would never have cause to add a new "machine" to his combo. Nor would he have a reason to add a state to the state combo or a customer or an employee or a product, etc. All of those things are entities in their own right and have validation rules and multiple supporting attributes. They belong to the whole application, not to just a single user. Just because they are frequently used in combos as foreign keys doesn't make them simple lists. We are only talking about things that would commonly be defined in a value list.

 

[KitaYama]

@KitaYama What you are suggesting is not how I took the OP's statement. The combos you are talking about are not simple lists, they are data from tables that have multiple columns and other uses throughout the application. Simple lists are things like gender, language, status codes. A user would never have cause to add a new "machine" to his combo. Nor would he have a reason to add a state to the state combo or a customer or an employee or a product, etc. All of those things are entities in their own right and have validation rules and multiple supporting attributes. They belong to the whole application, not to just a single user. Just because they are frequently used in combos as foreign keys doesn't make them simple lists. We are only talking about things that would commonly be defined in a value list.

@Pat Hartman I know and I was sure you know the situation I explained. And there's a good chance you do the same in your applications. My post wasn't meant to correct you. It was just for the sake of others who may be new to Access to see that there are cases that having different lists in the same combo is not wrong. My apologies if I didn't make my point clear.

 

[Pat Hartman]

No problem. I thought it was clear from the OP's question the type of data he was talking about which is why I didn't elaborate earlier. Who knows, maybe he really thinks that different companies belong to different users

 

[The_Doc_Man]

I read Pat's response, and it makes sense. But this app has no backend, all data is in the app or added by the user, and the data amount will be somewhat small.

Unless you never ever do maintenance on this DB, this is a dangerous situation. Further, if you are saving this new value as part of a text string rather than a table and you are not actually performing a "SAVE" on the form itself after the "Not In List" update, your next exit loses everything. What you are doing with a text-based list update on a combo box like that is a design-level change, not to the combo but to the whole form. If the form is not in design mode, that change is at risk.

 

[jpl458]

No problem. I thought it was clear from the OP's question the type of data he was talking about which is why I didn't elaborate earlier. Who knows, maybe he really thinks that different companies belong to different users

In My world, they do, as weird as that may sound.

 

[jpl458]

In My world, they do, as weird as that may sound. Just writing code to fit the situation, which is unusual.

 

[Pat Hartman]

I'm pretty sure that companies don't really "belong" to individual employees. What happens when an employee leaves the company? Does he take all his contacts with him?

There are far better and more flexible solutions than the one you want to implement. Start with a table and a form to maintain it. The query for the combo can select only rows from the table that are assigned to the person logged in or where the UserID is null in the case where items get added to the table but not immediately assigned to a user.

Then if you really want to use the not in list event to allow people to add new users on the fly, you can open the maintenance form for that table so the user can add the new row along with all the ancillary data. You can also add code in the BeforeUpdate event with the rest of the validation code that populates the AssignedToUserID with the logged in UserID if you want to.

I find that it is only very rarely that there is an actual need to add items to tables on the fly this way. When you allow this functionality, what happens is the users get sloppy and add typos which is what the combo is supposed to eliminate. I much prefer having the user go to the maintenance form and add the new record that way. You can give him better search tools to avoid adding typos.

 

[jpl458]

Trust me. This is one of those very rare occasions. What and who this app is for is not in any way traditional. I am new to ACCESS, but not new to the industry. I joined IBM in 1965 as an SE, and was headhunted out of there in short order. I have designed and implemented a general ledger system, a payroll system, physical inventory system that reduced a 2 week process to 12 hours for a major supplier to the automobile industry. ACCESS the 15th language I've used, starting with Autocoder, 360 assembler(my native tongue)........ and so on. The were other stops along the way, always with start-ups, and ending up in management. I really appreciate the help and advice that you have posted, and realize that it is not just for me alone, but for the community. I have a Pat Hartman folder where I keep your examples and a few notes that I can refer to.

 

[Pat Hartman]

What and who this app is for is not in any way traditional. I am new to ACCESS

And Access is not a traditional development tool. You might want to take the advice of the experts and do things the "Access" way. Even single user apps should be split if you are going to maintain them.

 

[jpl458]

ACCESS is the first event level language I have used, all the others were linear, mainline and subroutines. Plus when I started in the business the 5081 80 column Hollerith card was the preferred, and only, storage device. Then came tape, then disk and so on. I have an offspring that is a wizard python programmer, ( he has one program that has 250,000 lines of code, all written by him) and he keeps telling me that I think at too low a level. I marvel at all the stuff that ACCESS does for you, that you don't have to code, and I don't understand why people keep implying that ACCESS is going away. That offspring of mine is of that ilk. I don't see that happening.

I am just an old dog trying to learn a new trick. Having fun doing it.

Just went and read about split apps. The app I am developing, at the expreme outside, might have 1,000 rows in the master table. I would say the average may be about half that or less. Is that worth the trouble of splitting a DB? Seems like you would be carrying coals to Newcastle for that little amount of data. What would be the advantage?

 

[Pat Hartman]

I too am a refugee from the mainframe camp. I came to Access from COBOL in the early 90's (after more than 20 years in the business) and thought I had died and gone to heaven. I could link to DB2 tables on the mainframe and create an Access application to update them. It doesn't get any better than that. It was however, an uphill battle retraining myself to think in events. What should help you is recognizing that the form's class module which probably has thousands of lines of code running to do everything Access is doing for you is the mainline. Form/Report/Control events are "hooks" from the object's mainline to give you the opportunity to add your own code to customize a process when the user takes some specified action. The event model is NOT RANDOM. Every event has a purpose and is designed to let you add code at that point. Easy to understand is the click event of a button. It's use is obvious and people rarely misuse it. But they frequently confuse the purpose of the Before and After update events of the form and controls. I once removed over 5,000 lines of code from an application!!! The developer did not understand the purpose of the BeforeUpdate event. He kept trying to find a way to prevent a form from saving bad data but after 5,000 lines of code, he was totally unsuccessful. He put code in at least 4 control events - but never the BeforeUpdate event of a control. He put code in several form level events but never the form's BeforeUpdate event. He went so far as to duplicate code. That's how we got so many rows. For each control on the form, he would add the validation code for that control to the four events. This didn't work, the form was still saving bad data, so he copied the code. Control1 validated control1. Control2 validated control1 and control2, Control3 validated 1,2,3, Control4 validated 1,2,3,4, you see how this can add up. And apparently he added it all at once without stopping to test since of course this didn't fix his problem either and yet every single form in the database had all this useless code and he never thought to use the form's BeforeUpdate event.

Well, the most important event in the entire form is the form's BeforeUpdate event. Think of it as the flapper at the bottom of a funnel. If you want to save a record, it has to pass through this event and YOU don't call this event. Access calls this event whenever it encounters a situation where it thinks it needs to save a record whether you specifically initiated the save or whether you triggered the save by doing something else like scrolling to a new record or clicking from the main form to a subform or vice versa, among other situations. Access takes data safety as its personal mission. NO DATA WILL EVER BE LOST if Access can help it Therefore, it wants to save a lot. As you get to understand Access better, you will know when Access wants to save, not that it matters. What matters, is that you have your validation code in the form's BeforeUpdate event (or occasionally the control's BeforeUpdate event) so that YOU ALWAYS are in control over what is valid and what is not valid and therefore should not be saved.

So, your validation code goes in the BeforeUpdate event (or sometimes in a common procedure that is called from this one). When your code identifies an error you use
Cancel = True
and that tells Access to not save this record. PERIOD. Access will not save the record if you cancel the BeforeUpdate event. So you have absolute control over whether or not a record gets saved. Experienced developers come to Access and never discover this event but they know how to code so they decide they can do it better and they use unbound forms. Well, they end up with control but at the expense of giving up the most useful tool Access has to offer plus they end up writing hundreds of lines of unnecessary code and having to make up their own event triggers which is pretty hard when you get right down to it. Essentially they are duplicating code which already exists in the mainline of the form's event model because they didn't understand the concept of "hooks" and how to take advantage of them to control importing things like - should this record be saved?

A common mistake is putting validation code in a control's lostFocus event. Sounds good. Access won't let you leave a control if the value in that control is not valid. There are two flaws with. One is logical and one is technical. The logical error is that if a field is required but focus never enters that particular control, your validation code will NEVER run because the LostFocus event will not run. So, your validation can not possibly ever catch an "empty" field problem using that event - or any other control event for that matter. The other is technical. Ok you try to move to another control but can't because the data is invalid. You click OK on the error message and you're back in the control. You simply cannot move to another control on the form without fixing this error. You get frustrated so you close the form. Well you'll get the message one more time but the form closes. Whoops. The bad data is still in that control and you blew by the error message one too many times when you said it was OK to close the form so the bad data got saved Clearly, the LostFocus event has holes as big as the Grand Canyon and yet you will STILL see "experts" putting validation code in this event.

Is that worth the trouble of splitting a DB?

Splitting has nothing to do with the volume of data. It is all about splitting the data from the code. Would you want your data to be inside your executable? How would you update the code without worrying about the data. What would the user do while you took his application off line so you could make a change. Clearly he can't keep updating and adding data while you are modifying the code. How are you going to merge your changed code into his version of the app and not affect his data?

The second issue that splitting resolves is one of sharing the code base which leads to corruption. You have a monolithic .accdb in a server folder. User A opens the app and the MSAccess.exe opens the .accdb and loads various code modules into memory. User B opens the app and the MSAccess.exe opens the same file and loads various code modules into memory. Now we have parts of the same physical file in memory on two computers. Access creates a lock file which is always the same name as the main file but with an .laccdb extension. In this log file, it keeps track of who is doing what. Technically users cannot open objects in design view and change them when the database is open by a different user (versions prior to A2007 did allow concurrent updating but that's a different issue) but certain properties can be saved when you close an object. For example, if you sort a continuous subform, Access saves the sort when you close the form. Now what user B has in memory does not match what just got saved to the physical file.

So, if you follow this, you will also understand that you don't want UserA and UserB opening the same shared copy of the FE. This is of course less dangerous since corruption is unlikely to affect the data in the BE but it is still dangerous because a failure on one PC affects all other users, so each user needs to have his own personal copy of the FE that he runs from his LOCAL c: drive NOT from a network folder for efficiency reasons.

Access is pretty good about not having data conflicts when multiple users are updating the shared tables in the BE but having to share the objects just offers too much opportunity for corruption. Also, if user B has a power failure or an internet blip, that crushes user A also.

Here's two videos and a sample you might find interesting

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

On page 2, I uploaded the sample database I was using in the video to show why you should put your validation in the form's BeforeUPdate event and why you should be careful when you put validation in a control's BeforeUpdate event.

Here's another one that shows the bad effects of using certain function and property names as column names so you can see why they should be avoided at all costs. Date, Time, and Name are the worst offenders.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[jpl458]

I too am a refugee from the mainframe camp. I came to Access from COBOL in the early 90's (after more than 20 years in the business) and thought I had died and gone to heaven. I could link to DB2 tables on the mainframe and create an Access application to update them. It doesn't get any better than that. It was however, an uphill battle retraining myself to think in events. What should help you is recognizing that the form's class module which probably has thousands of lines of code running to do everything Access is doing for you is the mainline. Form/Report/Control events are "hooks" from the object's mainline to give you the opportunity to add your own code to customize a process when the user takes some specified action. The event model is NOT RANDOM. Every event has a purpose and is designed to let you add code at that point. Easy to understand is the click event of a button. It's use is obvious and people rarely misuse it. But they frequently confuse the purpose of the Before and After update events of the form and controls. I once removed over 5,000 lines of code from an application!!! The developer did not understand the purpose of the BeforeUpdate event. He kept trying to find a way to prevent a form from saving bad data but after 5,000 lines of code, he was totally unsuccessful. He put code in at least 4 control events - but never the BeforeUpdate event of a control. He put code in several form level events but never the form's BeforeUpdate event. He went so far as to duplicate code. That's how we got so many rows. For each control on the form, he would add the validation code for that control to the four events. This didn't work, the form was still saving bad data, so he copied the code. Control1 validated control1. Control2 validated control1 and control2, Control3 validated 1,2,3, Control4 validated 1,2,3,4, you see how this can add up. And apparently he added it all at once without stopping to test since of course this didn't fix his problem either and yet every single form in the database had all this useless code and he never thought to use the form's BeforeUpdate event.

Well, the most important event in the entire form is the form's BeforeUpdate event. Think of it as the flapper at the bottom of a funnel. If you want to save a record, it has to pass through this event and YOU don't call this event. Access calls this event whenever it encounters a situation where it thinks it needs to save a record whether you specifically initiated the save or whether you triggered the save by doing something else like scrolling to a new record or clicking from the main form to a subform or vice versa, among other situations. Access takes data safety as its personal mission. NO DATA WILL EVER BE LOST if Access can help it Therefore, it wants to save a lot. As you get to understand Access better, you will know when Access wants to save, not that it matters. What matters, is that you have your validation code in the form's BeforeUpdate event (or occasionally the control's BeforeUpdate event) so that YOU ALWAYS are in control over what is valid and what is not valid and therefore should not be saved.

So, your validation code goes in the BeforeUpdate event (or sometimes in a common procedure that is called from this one). When your code identifies an error you use
Cancel = True
and that tells Access to not save this record. PERIOD. Access will not save the record if you cancel the BeforeUpdate event. So you have absolute control over whether or not a record gets saved. Experienced developers come to Access and never discover this event but they know how to code so they decide they can do it better and they use unbound forms. Well, they end up with control but at the expense of giving up the most useful tool Access has to offer plus they end up writing hundreds of lines of unnecessary code and having to make up their own event triggers which is pretty hard when you get right down to it. Essentially they are duplicating code which already exists in the mainline of the form's event model because they didn't understand the concept of "hooks" and how to take advantage of them to control importing things like - should this record be saved?

A common mistake is putting validation code in a control's lostFocus event. Sounds good. Access won't let you leave a control if the value in that control is not valid. There are two flaws with. One is logical and one is technical. The logical error is that if a field is required but focus never enters that particular control, your validation code will NEVER run because the LostFocus event will not run. So, your validation can not possibly ever catch an "empty" field problem using that event - or any other control event for that matter. The other is technical. Ok you try to move to another control but can't because the data is invalid. You click OK on the error message and you're back in the control. You simply cannot move to another control on the form without fixing this error. You get frustrated so you close the form. Well you'll get the message one more time but the form closes. Whoops. The bad data is still in that control and you blew by the error message one too many times when you said it was OK to close the form so the bad data got saved Clearly, the LostFocus event has holes as big as the Grand Canyon and yet you will STILL see "experts" putting validation code in this event.


Splitting has nothing to do with the volume of data. It is all about splitting the data from the code. Would you want your data to be inside your executable? How would you update the code without worrying about the data. What would the user do while you took his application off line so you could make a change. Clearly he can't keep updating and adding data while you are modifying the code. How are you going to merge your changed code into his version of the app and not affect his data?

The second issue that splitting resolves is one of sharing the code base which leads to corruption. You have a monolithic .accdb in a server folder. User A opens the app and the MSAccess.exe opens the .accdb and loads various code modules into memory. User B opens the app and the MSAccess.exe opens the same file and loads various code modules into memory. Now we have parts of the same physical file in memory on two computers. Access creates a lock file which is always the same name as the main file but with an .laccdb extension. In this log file, it keeps track of who is doing what. Technically users cannot open objects in design view and change them when the database is open by a different user (versions prior to A2007 did allow concurrent updating but that's a different issue) but certain properties can be saved when you close an object. For example, if you sort a continuous subform, Access saves the sort when you close the form. Now what user B has in memory does not match what just got saved to the physical file.

So, if you follow this, you will also understand that you don't want UserA and UserB opening the same shared copy of the FE. This is of course less dangerous since corruption is unlikely to affect the data in the BE but it is still dangerous because a failure on one PC affects all other users, so each user needs to have his own personal copy of the FE that he runs from his LOCAL c: drive NOT from a network folder for efficiency reasons.

Access is pretty good about not having data conflicts when multiple users are updating the shared tables in the BE but having to share the objects just offers too much opportunity for corruption. Also, if user B has a power failure or an internet blip, that crushes user A also.

Here's two videos and a sample you might find interesting

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

On page 2, I uploaded the sample database I was using in the video to show why you should put your validation in the form's BeforeUPdate event and why you should be careful when you put validation in a control's BeforeUpdate event.

Here's another one that shows the bad effects of using certain function and property names as column names so you can see why they should be avoided at all costs. Date, Time, and Name are the worst offenders.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

That's a lot to digest, and I really appreciate the lesson. Before and Afterupdate events ares things I have no clue about, but will read, your stuff and whatever I can find. My instincts tell me that you have given me clue to a minor, but really nagging, bugglette in my app. It happen during an update of a table. I will try on my own to fix it, and learn in the process.

Thanks Pat.