Drop down list

T

tina

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

oops, sorry! <smacks self upside the head>


Graham Mandeno said:
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 :)

--
Cheers!

Graham Mandeno [Access MVP]
Auckland, New Zealand

tina said:
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
Click to expand...
Click to expand...
 
M

max

ok!
Graham Mandeno said:
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


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...
 
Top