Question ComboBox list displays blank data entries - annoying!

[hunterw]

I have a table I'm using for ComboBox list entries. Each column has a varying amount of fields (rows) utilized for their specific list.
The ComboBox which uses the column with the greatest amount of fields in the table has no problems, but every other ComboBox displays blank data entries. Aside from making a separate table for every single ComboBox, is there any way to prevent the ComboBox's list from displaying all those blank, useless fields?
Thanks in advance!

 

[Brianwarnock]

You need a Where clause in the selection.

Brian

 

[hunterw]

Is that a parameter for RowSource? Where is it entered? Is it simply the number of rows to use?

I guess I can automate finding the number of fields with data in them with a bunch of VBA. I'm not sure of the syntax of looking up a field in a table, though - would it be something like this?

If [Tablename].[ColumnName].8 = "" Then
WhereClauseNumber = 7
End If

 

[pbaldy]

Brian is offline, but I believe he means a row source of

SELECT FieldName
FROM TableName
WHERE FieldName Is Not Null

 

[hunterw]

Thank you!

 

[hunterw]

Before I lose you, how could I write syntax for checking if a column exists in a table?

If "[TableName].[ColumnName]" Is Not Null Then

does not work

 

[pbaldy]

I'm already lost.

The only way I know of would be in VBA. I can't think of an SQL only way. Generally fields are pretty static, so there's no need for it.

 

[hunterw]

Yes I'm using it for a VBA If statement - sorry, should have clarified that haha.

The reason for it is a bit convoluded - I will explain.

I have two comboboxes. One is simple, and one is dynamic, with its Row Source column defined by whatever is entered in to the first combobox. The comboboxes cannot be limited to the list options, so when something unique is typed in to the simple combobox, the dynamic combobox tries to use it as its row source column lookup, which results in an error. I just want to make an if statement to prevent that and just give a blank dropdown list instead of bleeping with errors.

 

[pbaldy]

See if this helps:

http://www.mdbmakers.com/forums/showthread.php?t=22583