more combobox questions . . . .

[Newbie]

Hi,

I have a combobox that is filled using AddItem based on the value of another
combobox. If this dynamically populated combobox doesn't have any records I
set the combobox1.text property to "No records"

However, by requiring to set the .Text property I can't use the built-in
Limit To List function.

How can I recreate the Limit To List functionality in code.

ie. if a user enters "TEA" I want to make sure that is an item in the list

 

[Michel Walsh]

Hi,


Generally, it is required to maintain the data integrity at the design
of the tables, and not just through some entry points (at some VB routines)
since data can be added by other means (such as SQL, or transfer/imports)
and when adding a new possibility to enter data (new form) the developer may
just forget to add the protective code. If the design is protected at the
database level, those problems cannot occur. In that spirit, if a limit to
list exist, probably the list requires to live in a table in order to
participate to the database design. If so, then make the list "bind" to the
table (ie, forget the AddItem functionality, useless in my opinion in
Access), and set the LimitToList to True. Your application will be
protected, without a single line of code, without having to remember it.
AddItem is the way to go in classical VB, since in that environment, you can
be sure you have a database at your disposal, so, you have to read the data
from whatever you can, and push it in the list with a loop involving
AddItem. With Access, you have a data source at your disposal, just mention
to the control the table (or the query) where the data resides, that's all,
no loop required, no AddItem involved.



Hoping it may help,
Vanderghast, Access MVP

 

[Newbie]

Thanks for that and whilst I would usually use the LimitToList I don't want
to on this occasion.

The combobox is populated by calling a stored procedure from SQL Server
which relies on the entry in another combobox as to what the criteria is.

The problem with using the LimitToList is that I can't set the .Text
property of the combobox when there aren't any items to populate the
combobox.

So either I need to be able to check the integrity of the selection for the
combobox without using LimitToList or somehow change the combobox so that it
is not LimitToList when there aren't any records to populate it so that the
..Text property can be set.

What would you suggest?

 

[Michel Walsh]

Hi,


You can change the LimitToList property of the control in the Current
event:


If ..theListWouldBeEmpty... Then
Me.ControlName.LimitToList = False
Else
Me.ControlName.LimitToList = True
End If


and repeat the same pattern when the code modify the list. Note the code can
be written in a single line:

Me.ControlName.LimitToList= CBool( theListIsEmpty? )



Also, note you can change the Text property only if the control has the
focus, since the Text property is what the user is actually in the way of
typing. The Value property is a confirmed "finalized" entry, something not
in the way of being typed, and accessible even without focus.


Hoping it may help,
Vanderghast, Access MVP

 

[Newbie]

Thanks - works a treat .. .

Duh! I was obvioulsy trying to make things a lot more complicated than they
needed to be!!!