Drop down list

J

JB

Hi all.
I have tried to find this solution but keep coming up against a brick wall.
All I want to do is create a drop-down list in a table or it's form that,
when I enter the data, I can either choose from what is already there, or
Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I already
created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever I do
to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width column
but it makes things worse or I don't get a blank list. Or I choose from the
list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the field
in as I go along. So that each new item shows in the dropdown list next
time?
Please help.
J
 
G

Graham Mandeno

Hi JB

First, *don't* use a combo box in a table. It only serves to confuse (the
user) and corrupt (the data). A form is for data entry. A table is for
data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all records
in the table, and the DISTINCT keyword will prevent duplicates being
included.

The LimitToList set to No will allow new values to be entered that are not
already in the table.
 
J

JB

Thank you Graham! Going to give it a go right a way.
Hope you'll still be around if I screw up!
JB


Graham Mandeno said:
Hi JB

First, *don't* use a combo box in a table. It only serves to confuse (the
user) and corrupt (the data). A form is for data entry. A table is for
data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all records
in the table, and the DISTINCT keyword will prevent duplicates being
included.

The LimitToList set to No will allow new values to be entered that are not
already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JB said:
Hi all.
I have tried to find this solution but keep coming up against a brick
wall.
All I want to do is create a drop-down list in a table or it's form that,
when I enter the data, I can either choose from what is already there, or
Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever I
do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width
column but it makes things worse or I don't get a blank list. Or I choose
from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
 
M

Marcel

JB
I dont agree with Graham regarding combo boxes in tables anyhow

I think its a combination of a query using distinctrow

for instance

SELECT DISTINCTROW Products.* FROM Products ORDER BY Products.ProductName

and in the properties event tab using the On Not in List to add the new item
to the list

regards


Marcel
 
T

tina

just to let you know, JB - we are all Access users who post here, and we all
have opinions, sometimes differing, on various aspects of database
development. the overwhelming (not total, but overwhelming) consensus in
these newsgroups is that the cons of using Lookup fields in tables far
outweigh the pros. MVP Graham Mandeno's recommendation is in line with that
consensus. for more information, to help you make an informed decision for
yourself, see http://www.mvps.org/access/lookupfields.htm

hth
 
J

JB

Hiya
I did everything you said. Entering all these properties in the MyField in
the Form
but I still can't set the
LimitToList: to No
The error message comes up with
"The first visible column, which is determined by the columnwidths property,
is not equal to the bound column. Adjust the columnwidths property first"

Just to check: I still have to have a field of MyField in the main table
right? and the MyField that's going in the Form is coming from the MyField
table but data is also stored in the main table. right?
Thanks for your help. I'm almost done but this was holding me back.
JB.




Graham Mandeno said:
Hi JB

First, *don't* use a combo box in a table. It only serves to confuse (the
user) and corrupt (the data). A form is for data entry. A table is for
data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all records
in the table, and the DISTINCT keyword will prevent duplicates being
included.

The LimitToList set to No will allow new values to be entered that are not
already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JB said:
Hi all.
I have tried to find this solution but keep coming up against a brick
wall.
All I want to do is create a drop-down list in a table or it's form that,
when I enter the data, I can either choose from what is already there, or
Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever I
do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width
column but it makes things worse or I don't get a blank list. Or I choose
from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
 
J

JB

I'd like to try your way as well, so I'll give that a go.
So this is with a query? but in the form right?
Thanks
JB
 
J

JB

It did work, sorry.
I changed the columnwidth from 0cm,2.5cm to 2.5cm,2.5cm
So that works well and I can add and choose. The only thing is when I
checked in the MyField table, it still only listed the original values that
were there which were 8. I've now added 3 more.
Do I need to change the ListRow property? it says 8 which is the first
amount before I added more.
Thanks for your patience!
JB


Graham Mandeno said:
Hi JB

First, *don't* use a combo box in a table. It only serves to confuse (the
user) and corrupt (the data). A form is for data entry. A table is for
data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all records
in the table, and the DISTINCT keyword will prevent duplicates being
included.

The LimitToList set to No will allow new values to be entered that are not
already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JB said:
Hi all.
I have tried to find this solution but keep coming up against a brick
wall.
All I want to do is create a drop-down list in a table or it's form that,
when I enter the data, I can either choose from what is already there, or
Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever I
do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width
column but it makes things worse or I don't get a blank list. Or I choose
from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi JB

On the first problem (which you appear to have solved :) If ColumnCount is
1 then ColumnWidths should be blank. It doesn't make sense to allocate
widths for multiple columns of you have only one! If ColumnWidths is blank
then the column widths will be assigned automatically, and for a single
column that means it will be the same as the width of the textbox.

On the second problem, do you mean that the new values you add don't appear
in the combo box list until you have closed the form and reopened it? This
is understandable. After you have saved a record with a new value, you will
need to requery the combobox so that that new one is included in the list.

Unfortunately, I can't think of any easy way to tell if the value in the
combobox is a pre-existing one or not. So perhaps the best way is to
unconditionally requery the combo after an updated record has been saved
(the AfterUpdate event).

Private Sub Form_AfterUpdate()
Me.YourComboName.Requery
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JB said:
It did work, sorry.
I changed the columnwidth from 0cm,2.5cm to 2.5cm,2.5cm
So that works well and I can add and choose. The only thing is when I
checked in the MyField table, it still only listed the original values
that were there which were 8. I've now added 3 more.
Do I need to change the ListRow property? it says 8 which is the first
amount before I added more.
Thanks for your patience!
JB


Graham Mandeno said:
Hi JB

First, *don't* use a combo box in a table. It only serves to confuse
(the user) and corrupt (the data). A form is for data entry. A table is
for data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all
records in the table, and the DISTINCT keyword will prevent duplicates
being included.

The LimitToList set to No will allow new values to be entered that are
not already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JB said:
Hi all.
I have tried to find this solution but keep coming up against a brick
wall.
All I want to do is create a drop-down list in a table or it's form
that, when I enter the data, I can either choose from what is already
there, or Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever
I do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width
column but it makes things worse or I don't get a blank list. Or I
choose from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mandeno

Sorry, Marcel - I'm missing something here.

What has the NotInList event got to do with using combobox lookup fields in
a table??

Incidentally, the DISTINCTROW predicate does not have any effect on a
singe-table query. It is DISTINCT that causes duplicate values to be
eliminated.
 
T

tina

JB and Graham, PMFJI, but i'm wondering if the two of you are talking about
two different scenarios.

Graham, you're talking about populating a combo box droplist with values
that are pulled from the bound field in the form's underlying table,
correct?

JB, do you have a *separate* table of values that you're using to populate
the combo box? and you want to be able to add new values (records) to that
"supporting" table during normal data entry, when necessary?

don't mean to stick my nose in, guys, i'm just getting the feeling that
you're talking at cross-purposes and neither one realizes it. or maybe i'm
all wet, in which case please excuse my intrusion and carry on! ;)

hth


Graham Mandeno said:
Hi JB

On the first problem (which you appear to have solved :) If ColumnCount is
1 then ColumnWidths should be blank. It doesn't make sense to allocate
widths for multiple columns of you have only one! If ColumnWidths is blank
then the column widths will be assigned automatically, and for a single
column that means it will be the same as the width of the textbox.

On the second problem, do you mean that the new values you add don't appear
in the combo box list until you have closed the form and reopened it? This
is understandable. After you have saved a record with a new value, you will
need to requery the combobox so that that new one is included in the list.

Unfortunately, I can't think of any easy way to tell if the value in the
combobox is a pre-existing one or not. So perhaps the best way is to
unconditionally requery the combo after an updated record has been saved
(the AfterUpdate event).

Private Sub Form_AfterUpdate()
Me.YourComboName.Requery
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JB said:
It did work, sorry.
I changed the columnwidth from 0cm,2.5cm to 2.5cm,2.5cm
So that works well and I can add and choose. The only thing is when I
checked in the MyField table, it still only listed the original values
that were there which were 8. I've now added 3 more.
Do I need to change the ListRow property? it says 8 which is the first
amount before I added more.
Thanks for your patience!
JB


Graham Mandeno said:
Hi JB

First, *don't* use a combo box in a table. It only serves to confuse
(the user) and corrupt (the data). A form is for data entry. A table is
for data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all
records in the table, and the DISTINCT keyword will prevent duplicates
being included.

The LimitToList set to No will allow new values to be entered that are
not already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi all.
I have tried to find this solution but keep coming up against a brick
wall.
All I want to do is create a drop-down list in a table or it's form
that, when I enter the data, I can either choose from what is already
there, or Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever
I do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width
column but it makes things worse or I don't get a blank list. Or I
choose from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
Click to expand...
 
M

Marcel

Graham

JB wants to "All I want to do is create a drop-down list in a table or it's
form that, when I enter the data, I can either choose from what is already
there, or Add to it as I create more records."


regards


Marcel





Graham Mandeno said:
Sorry, Marcel - I'm missing something here.

What has the NotInList event got to do with using combobox lookup fields in
a table??

Incidentally, the DISTINCTROW predicate does not have any effect on a
singe-table query. It is DISTINCT that causes duplicate values to be
eliminated.

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Marcel said:
this one might be better for you

http://support.microsoft.com/default.aspx?scid=kb;en-us;125648

regards

Marcel
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Tina

You might be right. I made my assumption based on:
when I enter the data, I can either choose from what is already there, or
Add to it as I create more records. and
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
Click to expand...

I read this as meaning JB does NOT already have a separate table (and would
prefer not to have one).

JB: Let us know if you DO have the values in a separate related table and
we can go from there. If this is the case, then I apologise for
misunderstanding you so badly :)
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

tina said:
JB and Graham, PMFJI, but i'm wondering if the two of you are talking
about
two different scenarios.

Graham, you're talking about populating a combo box droplist with values
that are pulled from the bound field in the form's underlying table,
correct?

JB, do you have a *separate* table of values that you're using to populate
the combo box? and you want to be able to add new values (records) to that
"supporting" table during normal data entry, when necessary?

don't mean to stick my nose in, guys, i'm just getting the feeling that
you're talking at cross-purposes and neither one realizes it. or maybe i'm
all wet, in which case please excuse my intrusion and carry on! ;)

hth


Graham Mandeno said:
Hi JB

On the first problem (which you appear to have solved :) If ColumnCount is
1 then ColumnWidths should be blank. It doesn't make sense to allocate
widths for multiple columns of you have only one! If ColumnWidths is blank
then the column widths will be assigned automatically, and for a single
column that means it will be the same as the width of the textbox.

On the second problem, do you mean that the new values you add don't appear
in the combo box list until you have closed the form and reopened it? This
is understandable. After you have saved a record with a new value, you will
need to requery the combobox so that that new one is included in the
list.

Unfortunately, I can't think of any easy way to tell if the value in the
combobox is a pre-existing one or not. So perhaps the best way is to
unconditionally requery the combo after an updated record has been saved
(the AfterUpdate event).

Private Sub Form_AfterUpdate()
Me.YourComboName.Requery
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JB said:
It did work, sorry.
I changed the columnwidth from 0cm,2.5cm to 2.5cm,2.5cm
So that works well and I can add and choose. The only thing is when I
checked in the MyField table, it still only listed the original values
that were there which were 8. I've now added 3 more.
Do I need to change the ListRow property? it says 8 which is the first
amount before I added more.
Thanks for your patience!
JB


Hi JB

First, *don't* use a combo box in a table. It only serves to confuse
(the user) and corrupt (the data). A form is for data entry. A table is
for data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all
records in the table, and the DISTINCT keyword will prevent duplicates
being included.

The LimitToList set to No will allow new values to be entered that are
not already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi all.
I have tried to find this solution but keep coming up against a brick
wall.
All I want to do is create a drop-down list in a table or it's form
that, when I enter the data, I can either choose from what is already
there, or Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever
I do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width
column but it makes things worse or I don't get a blank list. Or I
choose from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the
solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
Click to expand...
 
J

JB

Thanks guys for all your help! Wish I was as smart as you, sniff.
Yes I did already have a table but it's only got 8 values at this early
stage so I can easily recreate a new list if needs be.
I thought it may be a good idea to have a separate table as this field is a
Category type, for example 'Profession', would it be better separate when
I want to do a search later on.
(I think)
But the reason I wanted to do a drop-down list is to not have duplicate
values, you would have lawyer, teacher, and when you add a new record, you
would look up the drop-down list and click one there, or if it wasn't listed
you would add it.
Wadyathink?
Jen



tina said:
JB and Graham, PMFJI, but i'm wondering if the two of you are talking
about
two different scenarios.

Graham, you're talking about populating a combo box droplist with values
that are pulled from the bound field in the form's underlying table,
correct?

JB, do you have a *separate* table of values that you're using to populate
the combo box? and you want to be able to add new values (records) to that
"supporting" table during normal data entry, when necessary?

don't mean to stick my nose in, guys, i'm just getting the feeling that
you're talking at cross-purposes and neither one realizes it. or maybe i'm
all wet, in which case please excuse my intrusion and carry on! ;)

hth


Graham Mandeno said:
Hi JB

On the first problem (which you appear to have solved :) If ColumnCount is
1 then ColumnWidths should be blank. It doesn't make sense to allocate
widths for multiple columns of you have only one! If ColumnWidths is blank
then the column widths will be assigned automatically, and for a single
column that means it will be the same as the width of the textbox.

On the second problem, do you mean that the new values you add don't appear
in the combo box list until you have closed the form and reopened it? This
is understandable. After you have saved a record with a new value, you will
need to requery the combobox so that that new one is included in the
list.

Unfortunately, I can't think of any easy way to tell if the value in the
combobox is a pre-existing one or not. So perhaps the best way is to
unconditionally requery the combo after an updated record has been saved
(the AfterUpdate event).

Private Sub Form_AfterUpdate()
Me.YourComboName.Requery
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JB said:
It did work, sorry.
I changed the columnwidth from 0cm,2.5cm to 2.5cm,2.5cm
So that works well and I can add and choose. The only thing is when I
checked in the MyField table, it still only listed the original values
that were there which were 8. I've now added 3 more.
Do I need to change the ListRow property? it says 8 which is the first
amount before I added more.
Thanks for your patience!
JB


Hi JB

First, *don't* use a combo box in a table. It only serves to confuse
(the user) and corrupt (the data). A form is for data entry. A table is
for data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all
records in the table, and the DISTINCT keyword will prevent duplicates
being included.

The LimitToList set to No will allow new values to be entered that are
not already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi all.
I have tried to find this solution but keep coming up against a brick
wall.
All I want to do is create a drop-down list in a table or it's form
that, when I enter the data, I can either choose from what is already
there, or Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever
I do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width
column but it makes things worse or I don't get a blank list. Or I
choose from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the
solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
Click to expand...
 
T

tina

just to let you know, we're not ignoring you, JB! having your CategoryTypes
in a separate "supporting" table is a standard setup and not a problem -
just requires different handling at the form level. Graham said he'll
continue to work with you, and you two have a good dialog going, so i'll
step back out of the thread and you two can move ahead. remember that
Graham's in Australia, so unless you are also, there's a time difference,
but i've no doubt he'll pick up with you again.

hth


JB said:
Thanks guys for all your help! Wish I was as smart as you, sniff.
Yes I did already have a table but it's only got 8 values at this early
stage so I can easily recreate a new list if needs be.
I thought it may be a good idea to have a separate table as this field is a
Category type, for example 'Profession', would it be better separate when
I want to do a search later on.
(I think)
But the reason I wanted to do a drop-down list is to not have duplicate
values, you would have lawyer, teacher, and when you add a new record, you
would look up the drop-down list and click one there, or if it wasn't listed
you would add it.
Wadyathink?
Jen



tina said:
JB and Graham, PMFJI, but i'm wondering if the two of you are talking
about
two different scenarios.

Graham, you're talking about populating a combo box droplist with values
that are pulled from the bound field in the form's underlying table,
correct?

JB, do you have a *separate* table of values that you're using to populate
the combo box? and you want to be able to add new values (records) to that
"supporting" table during normal data entry, when necessary?

don't mean to stick my nose in, guys, i'm just getting the feeling that
you're talking at cross-purposes and neither one realizes it. or maybe i'm
all wet, in which case please excuse my intrusion and carry on! ;)

hth


Graham Mandeno said:
Hi JB

On the first problem (which you appear to have solved :) If
Click to expand...
ColumnCount
is
1 then ColumnWidths should be blank. It doesn't make sense to allocate
widths for multiple columns of you have only one! If ColumnWidths is blank
then the column widths will be assigned automatically, and for a single
column that means it will be the same as the width of the textbox.

On the second problem, do you mean that the new values you add don't appear
in the combo box list until you have closed the form and reopened it? This
is understandable. After you have saved a record with a new value, you will
need to requery the combobox so that that new one is included in the
list.

Unfortunately, I can't think of any easy way to tell if the value in the
combobox is a pre-existing one or not. So perhaps the best way is to
unconditionally requery the combo after an updated record has been saved
(the AfterUpdate event).

Private Sub Form_AfterUpdate()
Me.YourComboName.Requery
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

It did work, sorry.
I changed the columnwidth from 0cm,2.5cm to 2.5cm,2.5cm
So that works well and I can add and choose. The only thing is when I
checked in the MyField table, it still only listed the original values
that were there which were 8. I've now added 3 more.
Do I need to change the ListRow property? it says 8 which is the first
amount before I added more.
Thanks for your patience!
JB


Hi JB

First, *don't* use a combo box in a table. It only serves to confuse
(the user) and corrupt (the data). A form is for data entry. A
Click to expand...
table
is
for data storage.

So, you add a combo box control to your form and set its properties as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all
records in the table, and the DISTINCT keyword will prevent duplicates
being included.

The LimitToList set to No will allow new values to be entered that are
not already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi all.
I have tried to find this solution but keep coming up against a brick
wall.
All I want to do is create a drop-down list in a table or it's form
that, when I enter the data, I can either choose from what is already
there, or Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever
I do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property
Click to expand...
to
NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the width
column but it makes things worse or I don't get a blank list. Or I
choose from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the
solution.
Crazy thing is I've done this before but I'm just now getting totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Jen

OK - different approach...

Let's say your "reference" table is called "Professions" and it has two
fields:
- ProfessionID (autonumber, primary key)
- ProfessionName (text, indexed, no duplicates)

Let's say you have a table named "People", with a field "Profession" which
is a long integer numeric field.

You create a one-to-many relation between the two tables, linking them on
ProfessionID and Profession.

Now, you might start with some data in your Professions table - say:
1 Lawyer
2 Teacher
3 Plumber

If you look at the People table, you will see numbers in the Profession
column: 1, 3, 2, 2, 1 etc. This might not be very "user friendly", but it
doesn't matter - tables are not meant for users to look at!

Now you create a form for editing your "People" records. Instead of a
textbox displaying the number of the profession, you use a combo box and set
its properties as follows:
Name: Profession (or cboProfession if you prefer)
ControlSource: Profession (the name of the field in your table)
RowSourceType: Table/Query
RowSource: Select ProfessionID, ProfessionName from Professions
order by ProfessionName
ColumnWidths: 0
BoundColumn: 1
LimitToList: Yes
OnNotInList: [Event Procedure]

Now click on the build [...] button next to OnNotInList and you will see the
VBA code window:

Private Sub Profession_NotInList(NewData As String, Response As Integer)
| <<< cursor
End Sub

At the cursor, insert this code:

If MsgBox("Add new profession '" & NewData & "'?", _
vbQuestion or vbYesNo, "Profession not in database") = mbYes Then
CurrentDb.Execute "Insert into Professions(ProfessionName) " _
& "values('" & NewData & "')"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

What this code does is ask the user if he wants to add the new value to the
table, and if so it adds it.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



JB said:
Thanks guys for all your help! Wish I was as smart as you, sniff.
Yes I did already have a table but it's only got 8 values at this early
stage so I can easily recreate a new list if needs be.
I thought it may be a good idea to have a separate table as this field is
a Category type, for example 'Profession', would it be better separate
when I want to do a search later on.
(I think)
But the reason I wanted to do a drop-down list is to not have duplicate
values, you would have lawyer, teacher, and when you add a new record, you
would look up the drop-down list and click one there, or if it wasn't
listed you would add it.
Wadyathink?
Jen



tina said:
JB and Graham, PMFJI, but i'm wondering if the two of you are talking
about
two different scenarios.

Graham, you're talking about populating a combo box droplist with values
that are pulled from the bound field in the form's underlying table,
correct?

JB, do you have a *separate* table of values that you're using to
populate
the combo box? and you want to be able to add new values (records) to
that
"supporting" table during normal data entry, when necessary?

don't mean to stick my nose in, guys, i'm just getting the feeling that
you're talking at cross-purposes and neither one realizes it. or maybe
i'm
all wet, in which case please excuse my intrusion and carry on! ;)

hth


Graham Mandeno said:
Hi JB

On the first problem (which you appear to have solved :) If ColumnCount is
1 then ColumnWidths should be blank. It doesn't make sense to allocate
widths for multiple columns of you have only one! If ColumnWidths is blank
then the column widths will be assigned automatically, and for a single
column that means it will be the same as the width of the textbox.

On the second problem, do you mean that the new values you add don't appear
in the combo box list until you have closed the form and reopened it? This
is understandable. After you have saved a record with a new value, you will
need to requery the combobox so that that new one is included in the
list.

Unfortunately, I can't think of any easy way to tell if the value in the
combobox is a pre-existing one or not. So perhaps the best way is to
unconditionally requery the combo after an updated record has been saved
(the AfterUpdate event).

Private Sub Form_AfterUpdate()
Me.YourComboName.Requery
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

It did work, sorry.
I changed the columnwidth from 0cm,2.5cm to 2.5cm,2.5cm
So that works well and I can add and choose. The only thing is when I
checked in the MyField table, it still only listed the original values
that were there which were 8. I've now added 3 more.
Do I need to change the ListRow property? it says 8 which is the
first
amount before I added more.
Thanks for your patience!
JB


Hi JB

First, *don't* use a combo box in a table. It only serves to confuse
(the user) and corrupt (the data). A form is for data entry. A
table is
for data storage.

So, you add a combo box control to your form and set its properties
as
follows:
ControlSource: YourField
RowSourceType: Table/Query
ColumnCount: 1
BoundColumn: 1
LimitToList: No

and here is the main part:
RowSource: Select DISTINCT [YourField] from [YourTable]
where [YourField] is not null order by
[YourField];

The query will list all the non-null values in your field from all
records in the table, and the DISTINCT keyword will prevent
duplicates
being included.

The LimitToList set to No will allow new values to be entered that
are
not already in the table.
--
HTH!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi all.
I have tried to find this solution but keep coming up against a
brick
wall.
All I want to do is create a drop-down list in a table or it's form
that, when I enter the data, I can either choose from what is
already
there, or Add to it as I create more records.
In the table I create the field and choose Lookup Wizard and since I
already created the table, I choose it.
But when I put it in the form it keeps coming up with errors. What ever
I do to modify it, I get another problem.
It says that I have to choose from list, so I modify the property to NOT
limit to list but then it says something about Column width, which looks
wide enough to me. I've tried changing the bound column and the
width
column but it makes things worse or I don't get a blank list. Or I
choose from the list but it says I can't and comes up with a number.
The help pages talk in another language and I can't find the
solution.
Crazy thing is I've done this before but I'm just now getting
totally
confused.
Does the dropdown list have to have it's own table?
Does it have to have an ID? and a primary Key?
If so how can I choose the name of the item and not it's ID number?
and most importantly how can I add to the list in the Form I put the
field in as I go along. So that each new item shows in the dropdown list
next time?
Please help.
J
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Tina

Thanks for jumping in and for your vote of confidence! I've been busy with
"real" work all day, but have now replied to JB (Jen).

Just remember though, the difference between Australia and New Zealand :)
 
Top