How can I create an unbound combobox with a value list that saves the value then show it on a report?

Question

I would like to setup a value list for a combobox and allow users to insert their own values if desired. In this case, teacher names. This is a local database that is not shared. I do not want this list to read values from a table because each year the names will be different and I need users to be able to insert their own name or choose from a list without having to access any design features.

The problem I am having is that I cannot figure out how to create an unbound combobox that saves or stores the value between sessions then use it on different reports. I tried different things using VBA and creating an after update event to set the default value to whatever the value is after it is inserted but I was not able to get that to work. Any help would be appreciated.

Answer 1

Hi,

Unfortunately, the main way to "persist" information in a database is to store it in a table. Why wouldn't you want to do that? There is no need to access the design features just to add a teacher's name to a table.

There are other ways to store small amounts of information without using a table; but if it's for use as a Value List, you will still have to maintain it because, as you say, the list will change over time.

What exactly were you trying to avoid by using an unbound combobox?

Answer 2

I was trying to avoid using a table because at least if I use a value list initially, those users can add or remove what they wish by simply clicking in the input field and clicking the icon to edit the list.

If I use a table, I think they would have to open the table to remove names they don't want in there or add them that way or I would have to add some crazy advanced thing to enable them to add or remove values. A plain textbox would also work fine. If there isn't any way to get that working then I'll just create a table and they will have to deal with that and figure it out.

Answer 3

Actually I just remembered the other problem is that if I create a table, I can't drag and drop that onto a report without having to deal with relationships and such. I tried to get all that working for a week and never got anywhere with it, not at all. I just wanted to create a textbox where a teacher could enter their own name so I could have it on certain reports. If that isn't doable it makes sense.

Answer 4

I was trying to avoid using a table because at least if I use a value list initially, those users can add or remove what they wish by simply clicking in the input field and clicking the icon to edit the list.

If I use a table, I think they would have to open the table to remove names they don't want in there or add them that way or I would have to add some crazy advanced thing to enable them to add or remove values. A plain textbox would also work fine. If there isn't any way to get that working then I'll just create a table and they will have to deal with that and figure it out.

Hi,

Actually, if you use a table for the combobox, they won't need to open the table at all. The best practice approach is to give them a form to edit its contents. You can set the ListItemEditForm property of the combo to the name of this form. Or, you could give them a menu or a button to open the form for editing instead.

Answer 5

Actually I just remembered the other problem is that if I create a table, I can't drag and drop that onto a report without having to deal with relationships and such. I tried to get all that working for a week and never got anywhere with it, not at all. I just wanted to create a textbox where a teacher could enter their own name so I could have it on certain reports. If that isn't doable it makes sense.

Hi,

If you use a table as a lookup for a combobox, there's no need to drag that table onto the report. You can simply use a combobox on the report, rather than a textbox, to display the same information shown in the combo on your form.

Hope it helps...

Answer 6

Thank you. I tried that, several different ways just now but on a report in report view it just shows as a textbox with no way to select a combo value.

Answer 7

I actually looked into that but could not find too much information on getting that to work and was unable to get that to work. I like your combobox idea but I'm still just getting a textbox on the report view even though it is a combobox in the design view and works on an actual form.

Answer 8

Thank you. I tried that, several different ways just now but on a report in report view it just shows as a textbox with no way to select a combo value.

Hi,

Have you tried right-clicking on the textbox and then select "change to" combobox?

Answer 9

I actually looked into that but could not find too much information on getting that to work and was unable to get that to work. I like your combobox idea but I'm still just getting a textbox on the report view even though it is a combobox in the design view and works on an actual form.

Hi,

If you're open to a private online meeting or a phone call, I could probably walk you through all your issues quicker.

Just a thought...

Answer 10

All my lookup tables have a yes/no field named Active. If someone is no longer to be in the list the Active field is changed to No. The query for the combobox has criteria with Active = Yes. Any marked as No would automatically be left off the list.

Doing it this way allows you an audit trail to look at past records for inactive people.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 11

I could link to my database would that work?

Answer 12

Thank you. If sometime I figure out how all that works that sounds like something that might be useful. Very clever.

Answer 13

If that isn't a good option I would be open to a meeting or call sure.

Answer 14

If that isn't a good option I would be open to a meeting or call sure.

You can shoot me an email (address should be in my profile) to set up something.

Just a thought...

Answer 15

I see. I am not interested in hiring someone for help. Thank you for all your recent help and advice. I'll have users export to excel and add their credentials. That will solve the problem for me.

Answer 16

I see. I am not interested in hiring someone for help. Thank you for all your recent help and advice. I'll have users export to excel and add their credentials. That will solve the problem for me.

Hi,

You're welcome. However, I wasn't soliciting you for work. I always offer my help for free in the forums. I just think things will go faster if we can have a dialogue rather than use posts.

Good luck with your project.

The offer still stands, in case you're interested - just let me know.

Cheers!