Drop down lists to send multiple selections to a text field

[Guest]

Hope someone can help with this. I am currently working on a project to
create a database of stock photography that people can use to search for
photos based on keywords.

The idea is to split the photos into 6-8 categories, and for each category
have 20-40 keywords. But rather than have the keywords typed in manually, we
want to automate it, so as to keep consistency, and eliminate typos.

The ideal would be to have one drop down list containg the categories, and a
second drop down list that populated with the keywords dependent on the
category selected in the first list.

And from there be able to select multiple keywords that would populate a
Keywords field.

Would something like this be possible within an Access form?

Any pointers greatly appreciated.

Cheers,
Iain

 

[Guest]

Hi Iain,

The ideal would be to have one drop down list containg the categories, and a
second drop down list that populated with the keywords dependent on the
category selected in the first list.

Check out this KB article on using synchronized combo boxes:

How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
http://support.microsoft.com/?id=289670

Note that you can use a list box, with the multiselect property set to Yes,
in place of the second combo box. Thus, you can have a combo box containg the
categories, and a filtered list box containing the keywords. The categories
would be in a parent table, related 1:M to keywords in a child table.

And from there be able to select multiple keywords that would populate a
Keywords field.

This gets a little bit trickier, but is still do-able. It sounds like you
might want to implement the idea of a paired multi-select list box. Here is a
tutorial on how to do that:

Paired Multi-Select Listboxes
http://www.helenfeddema.com/access.htm (see article # 72)


There are also very good examples of paired multi-select list boxes in the
Access Developer's Handbook, by Litwin, Getz and Gunderloy, published by
Sybex.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Arvin Meyer [MVP]]

To avoid violation of database normalization, it would be better (and easier
to search) if you made each keyword a separate row in a junction or detail
table, displayed as a subform. Even if you did decide on using multiple
keywords in a field, more than a few of them would be hard to read and
confusing.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thanks Tom -

That helped a lot - I now have the dependent drop downs working - where the
first combo box populates a second list box.

I actually had the second part working from when I looked at this a few
months ago, but annoyingly can't get it to work in conjunction with this part.

The list box had an After Update expression of :

Private Sub List9_AfterUpdate()
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![List9]
Set ctlDest = Me![Keywords]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

End Sub

Where List9 is the list box, and Keywords is the text field the selections
are populating.

I figured it would be a case of adding the same expression to my new list
box, and just change the source and destination names - but it doesn't like
it.

I did try creating a button that would copy and paste with a macro (goto -
this works, but only a single selection - if you select another it pastes
over the first selection.

I went to look at that second link you posted, but just got a 404 tho'...

Cheers,
Iain

 

[Guest]

Hi Iain,

On that second link, I somehow pasted in a white space after the "m" in
..htm. So it's actually looking for ".htm ", not ".htm". With your cursor,
backspace over the blank character, and the page should open okay.

I forgot to mention it in my first post, but I fully intended to recommend,
just like Arvin did, that you want to store each keyword in a separate row in
a junction table. The Helen Feddema sample that I pointed you to does just
that.

By the way, I recommend giving your list box a more reasonable name, such as
"lstKeywords". The current name, "List9", is not exactly descriptive of the
control. Your code will be a lot more readable in the future if you take the
time to assign better names up front, instead of just accepting the default
names when you drop a new control onto a page.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom -

That helped a lot - I now have the dependent drop downs working - where the
first combo box populates a second list box.

I actually had the second part working from when I looked at this a few
months ago, but annoyingly can't get it to work in conjunction with this part.

The list box had an After Update expression of :

Private Sub List9_AfterUpdate()
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![List9]
Set ctlDest = Me![Keywords]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

End Sub

Where List9 is the list box, and Keywords is the text field the selections
are populating.

I figured it would be a case of adding the same expression to my new list
box, and just change the source and destination names - but it doesn't like
it.

I did try creating a button that would copy and paste with a macro (goto -
this works, but only a single selection - if you select another it pastes
over the first selection.

I went to look at that second link you posted, but just got a 404 tho'...

Cheers,
Iain

Hi Iain,


Check out this KB article on using synchronized combo boxes:

How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
http://support.microsoft.com/?id=289670

Note that you can use a list box, with the multiselect property set to Yes,
in place of the second combo box. Thus, you can have a combo box containg the
categories, and a filtered list box containing the keywords. The categories
would be in a parent table, related 1:M to keywords in a child table.


This gets a little bit trickier, but is still do-able. It sounds like you
might want to implement the idea of a paired multi-select list box. Here is a
tutorial on how to do that:

Paired Multi-Select Listboxes
http://www.helenfeddema.com/access.htm (see article # 72)


There are also very good examples of paired multi-select list boxes in the
Access Developer's Handbook, by Litwin, Getz and Gunderloy, published by
Sybex.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks guys -

I've managed to get it working for now - I went back and did step one over
my existing Form that had step two working, and it works a treat.

My List9 was a rogue from when I did it originally - so would get that
changed.

That's me off til Monday now, so will come back and look at the database
structure and you're suggestions then Arvin - but for now, thanks very much -
very impressed to get that working this morning!

Iain

 

[Guest]

Hi there,

Just coming back to this. I see what you mean about the separate table, but
this seems to work quite well for us - there shouldn't be too many keywords
per photo so they should be fine comma separated.

Plus, the idea is to have the front end of this as an online search tool, so
from a development point of view it seems easier to have them all in a
searchable Keywords field.

Iain