Combo Box with occasionally empty columns (1 Viewer)

Endojoe

Registered User.
Local time
Today, 10:30
Joined
Apr 7, 2009
Messages
20
Good Day All,

I have a table sourced Combo Box on a Form and it populates several Text Boxes and Option Groups using the following code:

Code:
Private Sub cbo_CUSTOMER_NAME_AfterUpdate()

Me.Txt_Country = Me.cbo_CUSTOMER_NAME.Column(1)
Me.opt_Cert_Type = Me.cbo_CUSTOMER_NAME.Column(2)
Me.opt_Assy_SubAssy = Me.cbo_CUSTOMER_NAME.Column(4)
opt_Assy_SubAssy_AfterUpdate
Me.opt_Export_Domestic = Me.cbo_CUSTOMER_NAME.Column(3)
opt_Export_Domestic_AfterUpdate
Me.txt_ADDL_STATEMENT = Me.cbo_CUSTOMER_NAME.Column(5)
Me.txt_TRACK_NUMBER_CERT = Me.cbo_CUSTOMER_NAME.Column(6)

End Sub

Unfortunately, column 5 often has no data in it, and because of that, for reasons I do not understand, the code appears to quit running when it encounters an empty column (or null column, if this is an appropriate instance to use the term). So, where ever I place the empty column in the code, nothing is filled in after it. I would place it at the end of my code, which is something I've done in other Combo Boxes as a quick dirty work around. But in this instance, Column 5 and Column 6 both are empty sometimes, other times only Column 5 is empty when Column 6 has data in it, and vise-a-versa. I've searched the Forum as best I could and not found anything that seems to answer my problem. I wasn't sure if this should go in the 'Forms' section or the 'Modules & VBA' section, so Mods, please feel free to move this if you feel it is better placed somewhere else.

Thank you all in advance for your help!

-Will-
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:30
Joined
Jan 23, 2006
Messages
15,364
Instead of basing your combo on the table, you could base it on a query that removes the Nulls in column 5 and 6. If then NULLS are the issue,
Something like (not tested)
Select (use your column names)
col1, col2... colx
from YourTableNameHere
Where
col5 is NOT NULL and col6 is NOT NULL
 

Endojoe

Registered User.
Local time
Today, 10:30
Joined
Apr 7, 2009
Messages
20
Jdraw, thank you for your response. I think I follow you, but the problem with that solution is that it eliminates records I need from the query. In the Query, I put 'Is Not Null' in the criteria box of column 5 and it eliminated all records that were not Null in Column 5, but I need those in my combo box. To clarify with a little deeper example, the table I'm pulling from is a Customer List of all our customers, but not all of our customers have data in column 5 or 6, some have data in 5 but not in 6 and some have data in 6 but not 5, some have data in neither, but I need all the Customer names showing up in my Combo Box regardless of whether or not they have data in column 5 or 6.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 11:30
Joined
Jan 23, 2006
Messages
15,364
Well there's a few things to try. You said you need all the Customer Names showing up in the Combobox. So you can get the CustomerName if you only select that field. It's getting all of the fields for your Combo when some have NULL in 1 0r 2 or Both columns.

Perhaps you could adjust your query to put in a default value if the column is NULL.
Something like

Select fld1, fld2, fld3, fl4, iif(fld5 is NULL,"F5",fld5) as Populated_Fld5 ,
iif(fld6 is NULL,"F6",fld6) as populated_Fld6
from myTable

Perhaps you should tell us more about your tables and relationships and exactly what you are trying to do.
 

Endojoe

Registered User.
Local time
Today, 10:30
Joined
Apr 7, 2009
Messages
20
Well, putting a default value in the column for NULL would end up putting bogus information on my form as far as I can tell, unless I'm missing something. Here's some more information. This is a pretty complicated DB, more of a mess than complicated, really, but I inherited it with the job. I can't sit down and re-write the thing from scratch all at once, because of time constraints coupled with ability constraints, so I'm re-writing it bit by bit. My actual job is Quality Assurance for aerospace parts manufacturing, and I had never touched Access until 4 years ago when I got this job, so I'm learning as I go.

For the purposes of this explanation, lets say I have 2 tables and 2 forms. One Form and Table is 'Customer_Data' with approximately 100 records, the other form and table is 'Certification' with somewhere north of 11,000 records. 'Certification' is being used to generate and track the information that goes on Government Certification documents we have to provide with our parts. There are no relationships established between the two tables, primarily because the 'Customer_Data' table was created just a few weeks ago by me, and the 'Certification' table has been a part of the DB for a decade.

The Certification Form has several Option Groups I have put in for both query purposes and to place certain verbiage on the Certification Document, which is why I have to trigger the After_Update event for a couple of the option groups. These option groups require user selection in order to successfully create a record in the Certification Table. The Option Group selections I want made on the Certification Form and Table vary from Customer to Customer, but do not change, IE. 'Customer A' has a set option group selection that will not change over time, but is may very well be different than the option group selections for 'Customer B'. In order to save time and prevent the possibility of mistakes being made, I have decided to enter the option group selections for each customer in the Customer Data Form and Table, then carry them over to the Certification Form in the Combo Box along with the Customer Name. Column 5 is a column for 'Remarks' that some customers want on their Certification Documents, Column 6 is an internal company tracking number prefix that some customers need on their Certification Documents.

I hope that helps clarify things better. I'm basically trying to figure out why the heck the code just up and quits when it encounters Null in a column, and hopefully a simple way to force the code to finish running anyways when it encounters Null.

Thank you again for your time and suggestions thus far.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:30
Joined
Jan 23, 2006
Messages
15,364
I wasn't thinking of bogus info, but perhaps N/A for Not Applicable, Not Known whatever makes sense in your environment. You said the fields were Null, but you need the other data.

If you have different Options for different Customers, then you might consider 3 tables.
A Customer Table, which you have already; An Options/Requirements table that identifies all possible options; and a Customer_Options Junction Table that identifies all the options that relate to a specific Customer.

Because you said the Customer options don't change, you could build that in a test environment and move it into your "operational database" once thoroughly tested. I could see how you could adjust your Form's contents/layout based on a particular Customer valid options.

Anyway, just a few ideas for your consideration.
Good luck.
 

Users who are viewing this thread

Top Bottom