White space

[Guest]

Hi again.
I have several combo boxes on a form. If I pick say a company name from one
combo box it then gets all the records for that company and displays them in
a list box. If then I click on say the location combo box, it shows all the
locations for the selected company. However, sometimes on some of the
records, there is not a location. When this is the case, the combo box
displays it as a blank line. Is there any way I can stop the combo box
displaying the blank line?

Many thanks in advance

 

[Rob Parker]

Your post is a little short on details, so I'll be making a few assumptions
here.

I assume that the "several comboboxes on a form" are in the form header (or
footer), and are being used to filter the records being displayed, and are
set up as cascading comboboxes (ie. the content of the second is limited by
the content of the frst, etc.).

The RowSource for the location combobox will be a SQL statement, generated
in code, which looks something like:

cboxLocation.RowSource = "SELECT DISTINCT Location FROM tblCompanies
WHERE CompanyName = '" & cboxCompanyName & "';"

Or, the code may be written something like:
Dim strSQL as String
...
strSQL = "SELECT DISTINCT Location FROM tblCompanies WHERE CompanyName =
'" & cboxCompanyName & "';"
cboxLocation.RowSource = strSQL
...

You can exclude null locations (preventing the blank line) by adding an
additional criterion to the WHERE clause - something like:

cboxLocation.RowSource = "SELECT DISTINCT Location FROM tblCompanies
WHERE CompanyName = '" & cboxCompanyName & "' AND Location IS NOT NULL;"

Doing this means, of course, that companies with no location will not appear
in the form's recordset after you select an entry in the location combobox.

HTH,

Rob

 

[Rob Parker]

Ooops, that last sentence could have been expressed a little clearer. What I
really meant was:

Doing this means, of course, that companies with no location will not be
able to be selected to display in the form by using the location combobox.

Rob

 

[Guest]

Thanks for that Rob. I've tried it and tested it and it works fine. Im now
currently putting that into the code for each of the combo boxes. However,
What I didn't understand was, why you assumed they were all in the Header or
Footer. They're not, they're in the main detail part of the form. Is there
any reason why they should be put in the Header or Footer. The form they're
on is the "Advanced Search" form where I want the user to be able to search
for a record/records by whittling down the information. The user can then
click on one, some or all of the records that are shown in the list box and
the Advanced Search form closes and the rcords are then put into the main
form. Like you said each of the combo boxes is set up to be cascading so that
the data in each of them is restricted by whatever is chosen from another
one. Anyway, the information you gave me has been very useful so thanks very
much.
Jim

 

[Rob Parker]

Hi Jim,

Glad to have helped.

As for your question about my assumption: if your form is in continuous
mode, the (unbound) comboboxes which you are using to filter the form's
records will be repeated for each record - there will be multiple copies of
them visible. If the form is in single record mode, then you won't really
notice the difference. Most developers place unbound controls for
searching/filtering/etc in either the form's header or footer.

Rob

 

[Guest]

Thanks again Rob. I think I know what you mean. The form is in single record
mode so there doesn't seem to be a problem there. but it's certainly
something to think about in the future.
Cheers
Jim