A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Combo Box with Updateable Value List



 
 
Thread Tools Display Modes
  #1  
Old April 18th, 2008, 03:31 PM posted to microsoft.public.access.forms
=Ray=
external usenet poster
 
Posts: 31
Default Combo Box with Updateable Value List

I want to have a combo box on my form that allows the user to enter data that
is not an item in the value list, and where the combo box has as its value
list, all the different values in a field. (The purpose being to minimize
entry error, as in misspellings, etc. so that a previously entered value can
be easily reentered with the same spelling, but where new values may still be
entered)

So for example, when the tables are empty, I have an empty combo box, and
you can enter whatever you want (text field). Then when you start to enter
the next record, the value you entered for the first record would appear as
an item in the combo box, but where you may also enter a new value. If the
same value is used on multiple records, it should still only appear once in
the combo box.

Is there any way I can accomplish this? If I need to do some coding, that is
fine. The database will not likely ever get exceedingly large (probably never
more than 1000 records), but I'd prefer not to use a loop with VBA to go
through all the records in generating the value list.

Thanks for any help,
Ray
  #2  
Old April 18th, 2008, 07:35 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Combo Box with Updateable Value List

This would be better accomplished with using a Table/Query row source instead
of a value list. Then for the row source, use a query that will return one
occurance of each value currently in the form's record source.

SELECT DISTINCT [FieldName] FROM TableName ORDER BY [FieldName];

Set the combo's Limit To List property to No so you can add a new value to
the field.

The last thing to do is add the new value to the combo by requerying it use
the form's After Update event:

Me.MyCombo.Requery


--
Dave Hargis, Microsoft Access MVP


"=Ray=" wrote:

I want to have a combo box on my form that allows the user to enter data that
is not an item in the value list, and where the combo box has as its value
list, all the different values in a field. (The purpose being to minimize
entry error, as in misspellings, etc. so that a previously entered value can
be easily reentered with the same spelling, but where new values may still be
entered)

So for example, when the tables are empty, I have an empty combo box, and
you can enter whatever you want (text field). Then when you start to enter
the next record, the value you entered for the first record would appear as
an item in the combo box, but where you may also enter a new value. If the
same value is used on multiple records, it should still only appear once in
the combo box.

Is there any way I can accomplish this? If I need to do some coding, that is
fine. The database will not likely ever get exceedingly large (probably never
more than 1000 records), but I'd prefer not to use a loop with VBA to go
through all the records in generating the value list.

Thanks for any help,
Ray

  #3  
Old April 22nd, 2008, 02:44 PM posted to microsoft.public.access.forms
=Ray=
external usenet poster
 
Posts: 31
Default Combo Box with Updateable Value List

Thank you very much. That did exactly what I need.
Ray

"Klatuu" wrote:

This would be better accomplished with using a Table/Query row source instead
of a value list. Then for the row source, use a query that will return one
occurance of each value currently in the form's record source.

SELECT DISTINCT [FieldName] FROM TableName ORDER BY [FieldName];

Set the combo's Limit To List property to No so you can add a new value to
the field.

The last thing to do is add the new value to the combo by requerying it use
the form's After Update event:

Me.MyCombo.Requery


--
Dave Hargis, Microsoft Access MVP


"=Ray=" wrote:

I want to have a combo box on my form that allows the user to enter data that
is not an item in the value list, and where the combo box has as its value
list, all the different values in a field. (The purpose being to minimize
entry error, as in misspellings, etc. so that a previously entered value can
be easily reentered with the same spelling, but where new values may still be
entered)

So for example, when the tables are empty, I have an empty combo box, and
you can enter whatever you want (text field). Then when you start to enter
the next record, the value you entered for the first record would appear as
an item in the combo box, but where you may also enter a new value. If the
same value is used on multiple records, it should still only appear once in
the combo box.

Is there any way I can accomplish this? If I need to do some coding, that is
fine. The database will not likely ever get exceedingly large (probably never
more than 1000 records), but I'd prefer not to use a loop with VBA to go
through all the records in generating the value list.

Thanks for any help,
Ray

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.