Solved Combo box question...

[NIUHuskie23]

Hi everyone,

My name is Brian and I'm brand new here. As I mentioned in my introductory post, I've had some experience with Access in the past but as part of a new role I'm managing a database that's been around for quite a while. I've handled it pretty well over the last year since taking the new job but I've run across an issue that has me stumped.

For background, our database is actually two separate files. A back-end file that holds all of our data and a front-end file that is linked to the back-end but has all of the forms/reports we use to populate our data and get reports. One of my tables has 90 fields, but I'm trying to add #91 for a new reporting requirement. We use a few combo boxes on a form in the front end file and that data populates in the back end file. I was able to add the new field to the back end file successfully. Now I'm working on the form to add the new combo box to populate that field. I'm using the Combo Box Wizard and selected "I will type in the values that I want". I then populated my drop down choices and clicked next. My next step should be to "Store that value in this field:" My problem is that the new field I created is not populating in that list of available fields. I've checked, and of the 90 fields in the back-end file, only 80 of them are showing up on that list. I'm wondering if there's a cap on the number of fields that will show up there. If not, can anyone help me figure out how to get that field to show up?

I will add that we have two other tables connected similarly, but with fewer fields and I was able to update those forms with no issue. That's why I'm wondering if it's a capacity issue. Any help is greatly appreciated.

Brian

 

[June7]

I checked this with a table having 128 fields. I see all of them in the wizard.

 

[NIUHuskie23]

I checked this with a table having 128 fields. I see all of them in the wizard.

Thanks. I actually just tried this with a separate table in my database with over 100 fields just to see if I could. I was able to do it as well. Now I'm really stumped!

 

[The_Doc_Man]

First and foremost, this IS your database so any comments we make are based on general observations.

Second, the front-end/back-end split is actually preferred for shared databases anyway so nothing to say about that. We will commonly refer to the two as FE and BE files so if the conversation goes that way, don't be surprised.

Third, having 90 fields in a table (or 91 now) SOUNDS excessive, such as might occur if you have database normalization issues. Not saying you actually DO have such issues - but that is a very large number of fields. If you are not familiar with database normalization, you should do some reading on that topic. It will be a bit of work if conversion is required, but it is DEFINITELY a case of pay me now or pay me later.

How many unique values would be expected for the 91st field that has a new combo box? Having five or six ... or even as many as ten might be OK. If you have more than that, the preferred method is to populate the combo from a table listing the possible values. Then to add a new possible value is a simple manual table edit (in datasheet mode) or an append query or a recordset operation to that table.

You mentioned having trouble seeing the new field in order to use it in a combo box. Just for snorts and giggles, close the FE, make a backup copy of it, and perform a Compact & Repair (Database Tools ribbon). That forces a few things to happen that might make the new field work better.

I tried to find documentation on the limit to a manual combo-box list size, but the way the documentation is written, it will be either 255 bytes or 65535 bytes. Even if the limit actually IS 65535, I would switch from manual lists to table lists LONG before I got to 255 bytes.

 

[CJ_London]

Another thought - what is the recordsource to your form? Is the new field included there?

 

[The_Doc_Man]

Great suggestion, CJ. It jogged something in my ragged memory. (Cerebral, not computer memory.) I answered this one late in the evening. Shows that I should at least try to be awake.

@NIUHuskie23 - you must remember that the form doesn't always get its fields from the table. It gets it from the defined recordsource for that form. Which COULD be a query. OR - thanks to CJ jogging my memory, COULD be Access's "memory" of what the table looked like when you first named the recordsource. The fact that you only see 80 out of 90 fields means that the field list is desynchronized from the table and has been that way for a long time. Even if you DID simply name the table as the recordsource, go in and edit that table name and replace it - with the same table name. That will force Access to re-evaluate table contents.

By the way, IF you actually used the table name as your recordsource for the form, that is not always best practice. I ALWAYS used a single-table query that listed the fields. Among other things, using queries rather than tables avoided some issues with Access losing track of where my BE tables were located. Technically, tables are UNORDERED record sets. Queries, on the other hand, CAN specify order, filtration, and formatting - which makes form design easier sometimes.

 

[isladogs]

Refresh the link to the BE table to make the new field visible to the FE. Then add the field to the row source for the combo.
Also, what is the datatype of the field you have added?

 

[NIUHuskie23]

Great suggestion, CJ. It jogged something in my ragged memory. (Cerebral, not computer memory.) I answered this one late in the evening. Shows that I should at least try to be awake.

@NIUHuskie23 - you must remember that the form doesn't always get its fields from the table. It gets it from the defined recordsource for that form. Which COULD be a query. OR - thanks to CJ jogging my memory, COULD be Access's "memory" of what the table looked like when you first named the recordsource. The fact that you only see 80 out of 90 fields means that the field list is desynchronized from the table and has been that way for a long time. Even if you DID simply name the table as the recordsource, go in and edit that table name and replace it - with the same table name. That will force Access to re-evaluate table contents.

By the way, IF you actually used the table name as your recordsource for the form, that is not always best practice. I ALWAYS used a single-table query that listed the fields. Among other things, using queries rather than tables avoided some issues with Access losing track of where my BE tables were located. Technically, tables are UNORDERED record sets. Queries, on the other hand, CAN specify order, filtration, and formatting - which makes form design easier sometimes.

I took a break last night from working on this because I just needed to give my brain a rest. I appreciate the helpful responses. This morning I have tried running a compact and repair on both the FE and BE files and that did not work. I also relinked the table in the FE file to get it to refresh, which did not work either.

I checked the recordsource for this particular form and it is actually using a query. The other two forms that I was able to update successfully use the tables directly as their recordsource. If I update the query that the form is using to include my new field, it should then populate in my list, right?

 

[Pat Hartman]

If I update the query that the form is using to include my new field, it should then populate in my list, right?

You mean that you haven't tried this yet? What are you waiting for?

When the FE is linked to a Jet/ACE BE, updates to the BE tables become visible as soon as they are saved (unless the object is open in the FE). It is only when the BE is SQL Server or some other RDBMS that you have to relink the ables to see the changes.

Regardless, if the form is bound to a Query that selects only some of the columns from a table, NOTHING short of changing the query will make the added columns show up. That's just the way queries work. You select the rows and columns you want and that is what you get. If you use Select * in the query, the query would be updated with the new columns but unless you close and reopen the form, the form won't see the update. However, Select * is generally poor practice. Best practice is to select ONLY the columns and rows you actually need for the form.

Also, it is a little strange to quote Doc rather than CJ since it was CJ who finally suggested the solution.

 

[The_Doc_Man]

Doc is perfectly willing to let credit fall where it is due. After all it was CJ's suggestion that rattled around in my old brain until it clicked for me.

 

[Pat Hartman]

Just pointing that out for the newbie

 

[NIUHuskie23]

My thanks to both CJ and Doc. To be honest, I read CJ's question and did not fully understand it until I read what Doc had posted. I have experience with Access, but my prior experience never included forms so that part is all new to me. I did not realize the recordsource could be a table or a query. I just assumed it was a table, and with two out of the three forms I was working on that assumption was correct, and which is also why I was so stumped. I did eventually find and update the query and it pulled in the field correctly and all is well. I've tested the database and put it back into production. Hopefully my co-workers don't find any side effect issues tomorrow, but I think I'll be ok.

So, again, thank you to CJ for suggesting the answer and to Doc for adding on to that suggestion in a way that made me recognize the problem.

I'm sure I'll be lurking around on the boards from now on as I get deeper into managing/updating this database. It was built 10+ years ago by a co-worker who retired last year. It has served our team well in that time and I don't want to be the one who breaks it. I appreciate the help with this problem and the help you guys will provide me with in the future.

Brian