Drop down boxes / VB help

[Guest]

This is something I posted on a few weeks ago, and has come round again, but
I'm still stumped. (I don't have a coding background unfortunately, but am
very familiar with Access, and have kind of picked up bits along the way - so
I can kind of see and understand how code works, and what it's doing, even if
I can't write it from scratch sort of thing.)

Basically I'm involved in a project to create a database of stock
photography, the purpose of which will be that people can search for photos
based on keywords attached to each in a broswer environment.

I've taken care of that side of things, with a little intranet site up and
running, and working well.

But the data entry side of things isn't quite there. We want users to be
able to select keywords from a pre-defined selection so as to keep
consistancy, and eliminate typos.

At the moment I have a table tbl_Photos with various fields including
'Keywords'.

In the data entry form that holds all the photo details, there is a drop
down box with various categories - eg 'Flora and Fauna', 'Notable Buildings'
etc. which is a bound field 'Categories'.

The idea here is to have manageable lists of keywords rather than one long
list.

How it works is that the After Update has the expression :

Private Sub Categories_AfterUpdate()
Me.Keywords.RowSource = "SELECT Keyword FROM" & _
" tbl_KeywordsTEMP WHERE CategoryID = " & Me.Categories & _
" ORDER BY Keyword"
Me.Keywords = Me.Keywords.ItemData(0)
End Sub

Which selects the Keywords from a tbl_keywordsTEMP and populates a unbound
list box with the relevant keywords for the selected category.

From here, it's possible to select keywords from the current list displayed,
and populate a field 'KeywordsNew' in the tbl_Photos, using the following
expression in the After Update of the unbound list box :

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

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

' 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

So far so good - it all works a treat.

With one caveat - namely, if you want to enterkeywords from more than one
category, it overwrites the KeywordsNew field, rather than append to any
existing keywords already stored there.

Basically, in order for this all to be working exactly as I'd like, I need
the KeywordsNew field to be able to store a list of keywords selected from
multiple categories, rather than only one or other as it is at the moment.

I've tried to explain that as weel as I can - hope someone a. understands
what I'm trying to achieve, and b. can shed any light on a solution.

Many thanks,
Iain

 

[Jon Lewis]

Look up the Static keyword in Access Visual Basic Help.

Try Declaring strItems using the Static keyword. That way the value of
strItems will be retained between calls to Keywords_AfterUpdate. At the
moment you're starting with a zero length string each time this sub is
called.

HTH

This is something I posted on a few weeks ago, and has come round again,
but
I'm still stumped. (I don't have a coding background unfortunately, but am
very familiar with Access, and have kind of picked up bits along the way -
so
I can kind of see and understand how code works, and what it's doing, even
if
I can't write it from scratch sort of thing.)

Basically I'm involved in a project to create a database of stock
photography, the purpose of which will be that people can search for
photos
based on keywords attached to each in a broswer environment.

I've taken care of that side of things, with a little intranet site up and
running, and working well.

But the data entry side of things isn't quite there. We want users to be
able to select keywords from a pre-defined selection so as to keep
consistancy, and eliminate typos.

At the moment I have a table tbl_Photos with various fields including
'Keywords'.

In the data entry form that holds all the photo details, there is a drop
down box with various categories - eg 'Flora and Fauna', 'Notable
Buildings'
etc. which is a bound field 'Categories'.

The idea here is to have manageable lists of keywords rather than one long
list.

How it works is that the After Update has the expression :

Private Sub Categories_AfterUpdate()
Me.Keywords.RowSource = "SELECT Keyword FROM" & _
" tbl_KeywordsTEMP WHERE CategoryID = " & Me.Categories & _
" ORDER BY Keyword"
Me.Keywords = Me.Keywords.ItemData(0)
End Sub

Which selects the Keywords from a tbl_keywordsTEMP and populates a unbound
list box with the relevant keywords for the selected category.

From here, it's possible to select keywords from the current list
displayed,
and populate a field 'KeywordsNew' in the tbl_Photos, using the following
expression in the After Update of the unbound list box :

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

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

' 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

So far so good - it all works a treat.

With one caveat - namely, if you want to enterkeywords from more than one
category, it overwrites the KeywordsNew field, rather than append to any
existing keywords already stored there.

Basically, in order for this all to be working exactly as I'd like, I need
the KeywordsNew field to be able to store a list of keywords selected from
multiple categories, rather than only one or other as it is at the moment.

I've tried to explain that as weel as I can - hope someone a. understands
what I'm trying to achieve, and b. can shed any light on a solution.

Many thanks,
Iain

 

[Jon Lewis]

I should add that if you want Keywords to be added to existing records then
use ctlDest = IIF(ctlDest <> "", ctlDest & ";" & strItems, strItems) instead
of ctlDest = strItems.

HTH

This is something I posted on a few weeks ago, and has come round again,
but
I'm still stumped. (I don't have a coding background unfortunately, but am
very familiar with Access, and have kind of picked up bits along the way -
so
I can kind of see and understand how code works, and what it's doing, even
if
I can't write it from scratch sort of thing.)

Basically I'm involved in a project to create a database of stock
photography, the purpose of which will be that people can search for
photos
based on keywords attached to each in a broswer environment.

I've taken care of that side of things, with a little intranet site up and
running, and working well.

But the data entry side of things isn't quite there. We want users to be
able to select keywords from a pre-defined selection so as to keep
consistancy, and eliminate typos.

At the moment I have a table tbl_Photos with various fields including
'Keywords'.

In the data entry form that holds all the photo details, there is a drop
down box with various categories - eg 'Flora and Fauna', 'Notable
Buildings'
etc. which is a bound field 'Categories'.

The idea here is to have manageable lists of keywords rather than one long
list.

How it works is that the After Update has the expression :

Private Sub Categories_AfterUpdate()
Me.Keywords.RowSource = "SELECT Keyword FROM" & _
" tbl_KeywordsTEMP WHERE CategoryID = " & Me.Categories & _
" ORDER BY Keyword"
Me.Keywords = Me.Keywords.ItemData(0)
End Sub

Which selects the Keywords from a tbl_keywordsTEMP and populates a unbound
list box with the relevant keywords for the selected category.

From here, it's possible to select keywords from the current list
displayed,
and populate a field 'KeywordsNew' in the tbl_Photos, using the following
expression in the After Update of the unbound list box :

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

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

' 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

So far so good - it all works a treat.

With one caveat - namely, if you want to enterkeywords from more than one
category, it overwrites the KeywordsNew field, rather than append to any
existing keywords already stored there.

Basically, in order for this all to be working exactly as I'd like, I need
the KeywordsNew field to be able to store a list of keywords selected from
multiple categories, rather than only one or other as it is at the moment.

I've tried to explain that as weel as I can - hope someone a. understands
what I'm trying to achieve, and b. can shed any light on a solution.

Many thanks,
Iain

 

[Guest]

Thanks Jon.

Had a look at that, but not sure how well I follow it, due to me already
being a little out of my depth coding wise. It certainly sounds about right -
with the idea of not resetting it every time the sub is called.

If you're more familiar, and are able, could you show me what part of the
existing code needs changing with what code exactly?

Because code needs to be exactr or it won't work, I'll probably be there
forever, and this really is the last piece of this functional jigsaw I'm
trying to get working.

With your other post, I changed that bit to :

ctlDest = IIF(ctlDest <> "", ctlDest & ";" & strItems, strItems)

and could see what it was trying to do - again just let me add new keywords
to an existing record, but it didn't quite work.

If for example a records had one keyword attached already, 'Keyword1', and I
moved off the record and went back to it, and added anotherkeyword,
'Keyword2', instead of just adding 'Keyword2' and ending up with
'Keyword1;Keyword2' it added any existing keywords again and ended up as eg
'Keyword1;Keyword1;Keyword2'.

Again, presumably this is a simple enough syntax error somewhere - if you're
familiar with VB?

Thanks again,
Iain

Look up the Static keyword in Access Visual Basic Help.

Try Declaring strItems using the Static keyword. That way the value of
strItems will be retained between calls to Keywords_AfterUpdate. At the
moment you're starting with a zero length string each time this sub is
called.

HTH

This is something I posted on a few weeks ago, and has come round again,
but
I'm still stumped. (I don't have a coding background unfortunately, but am
very familiar with Access, and have kind of picked up bits along the way -
so
I can kind of see and understand how code works, and what it's doing, even
if
I can't write it from scratch sort of thing.)

Basically I'm involved in a project to create a database of stock
photography, the purpose of which will be that people can search for
photos
based on keywords attached to each in a broswer environment.

I've taken care of that side of things, with a little intranet site up and
running, and working well.

But the data entry side of things isn't quite there. We want users to be
able to select keywords from a pre-defined selection so as to keep
consistancy, and eliminate typos.

At the moment I have a table tbl_Photos with various fields including
'Keywords'.

In the data entry form that holds all the photo details, there is a drop
down box with various categories - eg 'Flora and Fauna', 'Notable
Buildings'
etc. which is a bound field 'Categories'.

The idea here is to have manageable lists of keywords rather than one long
list.

How it works is that the After Update has the expression :

Private Sub Categories_AfterUpdate()
Me.Keywords.RowSource = "SELECT Keyword FROM" & _
" tbl_KeywordsTEMP WHERE CategoryID = " & Me.Categories & _
" ORDER BY Keyword"
Me.Keywords = Me.Keywords.ItemData(0)
End Sub

Which selects the Keywords from a tbl_keywordsTEMP and populates a unbound
list box with the relevant keywords for the selected category.

From here, it's possible to select keywords from the current list
displayed,
and populate a field 'KeywordsNew' in the tbl_Photos, using the following
expression in the After Update of the unbound list box :

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

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

' 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

So far so good - it all works a treat.

With one caveat - namely, if you want to enterkeywords from more than one
category, it overwrites the KeywordsNew field, rather than append to any
existing keywords already stored there.

Basically, in order for this all to be working exactly as I'd like, I need
the KeywordsNew field to be able to store a list of keywords selected from
multiple categories, rather than only one or other as it is at the moment.

I've tried to explain that as weel as I can - hope someone a. understands
what I'm trying to achieve, and b. can shed any light on a solution.

Many thanks,
Iain

 

[Jon Lewis]

OK Iain

Forget about Static for the moment and go back to as you were. I had
assumed that you were opening and closing your data entry form each time a
record was edited/added.

Just stick to ctlDest = IIF(ctlDest <> "", ctlDest & ";" & strItems,
strItems) on its own and see if that works for you assuming that ctlDest is
a bound control. If not then what value is ctlDesc displaying each time you
change record?

HTH





HTH

Thanks Jon.

Had a look at that, but not sure how well I follow it, due to me already
being a little out of my depth coding wise. It certainly sounds about
right -
with the idea of not resetting it every time the sub is called.

If you're more familiar, and are able, could you show me what part of the
existing code needs changing with what code exactly?

Because code needs to be exactr or it won't work, I'll probably be there
forever, and this really is the last piece of this functional jigsaw I'm
trying to get working.

With your other post, I changed that bit to :

ctlDest = IIF(ctlDest <> "", ctlDest & ";" & strItems, strItems)

and could see what it was trying to do - again just let me add new
keywords
to an existing record, but it didn't quite work.

If for example a records had one keyword attached already, 'Keyword1', and
I
moved off the record and went back to it, and added anotherkeyword,
'Keyword2', instead of just adding 'Keyword2' and ending up with
'Keyword1;Keyword2' it added any existing keywords again and ended up as
eg
'Keyword1;Keyword1;Keyword2'.

Again, presumably this is a simple enough syntax error somewhere - if
you're
familiar with VB?

Thanks again,
Iain

Look up the Static keyword in Access Visual Basic Help.

Try Declaring strItems using the Static keyword. That way the value of
strItems will be retained between calls to Keywords_AfterUpdate. At the
moment you're starting with a zero length string each time this sub is
called.

HTH

This is something I posted on a few weeks ago, and has come round
again,
but
I'm still stumped. (I don't have a coding background unfortunately, but
am
very familiar with Access, and have kind of picked up bits along the
way -
so
I can kind of see and understand how code works, and what it's doing,
even
if
I can't write it from scratch sort of thing.)

Basically I'm involved in a project to create a database of stock
photography, the purpose of which will be that people can search for
photos
based on keywords attached to each in a broswer environment.

I've taken care of that side of things, with a little intranet site up
and
running, and working well.

But the data entry side of things isn't quite there. We want users to
be
able to select keywords from a pre-defined selection so as to keep
consistancy, and eliminate typos.

At the moment I have a table tbl_Photos with various fields including
'Keywords'.

In the data entry form that holds all the photo details, there is a
drop
down box with various categories - eg 'Flora and Fauna', 'Notable
Buildings'
etc. which is a bound field 'Categories'.

The idea here is to have manageable lists of keywords rather than one
long
list.

How it works is that the After Update has the expression :

Private Sub Categories_AfterUpdate()
Me.Keywords.RowSource = "SELECT Keyword FROM" & _
" tbl_KeywordsTEMP WHERE CategoryID = " & Me.Categories & _
" ORDER BY Keyword"
Me.Keywords = Me.Keywords.ItemData(0)
End Sub

Which selects the Keywords from a tbl_keywordsTEMP and populates a
unbound
list box with the relevant keywords for the selected category.

From here, it's possible to select keywords from the current list
displayed,
and populate a field 'KeywordsNew' in the tbl_Photos, using the
following
expression in the After Update of the unbound list box :

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

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

' 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

So far so good - it all works a treat.

With one caveat - namely, if you want to enterkeywords from more than
one
category, it overwrites the KeywordsNew field, rather than append to
any
existing keywords already stored there.

Basically, in order for this all to be working exactly as I'd like, I
need
the KeywordsNew field to be able to store a list of keywords selected
from
multiple categories, rather than only one or other as it is at the
moment.

I've tried to explain that as weel as I can - hope someone a.
understands
what I'm trying to achieve, and b. can shed any light on a solution.

Many thanks,
Iain

 

[Guest]

Hi Jon -

All of this is happening in the same form - so no opening / closing of any
subform or anything.

the ctlDest is the 'KeywordsNEW' field in the tbl_Photos, so is bound.

Before I changed

Set destination control to string
ctlDest = strItems

to

Set destination control to string
ctlDest = IIF(ctlDest <> "", ctlDest & ";" & strItems, strItems) instead
of ctlDest = strItems

in the after update expression of the unbound list box which is displaying
the list of available keywords for each category.

When you say here to add it on it's own, how do you mean exactly? To replace
the entire current AfterUpdate expression with just

ctlDest = IIF(ctlDest <> "", ctlDest & ";" & strItems, strItems)?

I'm back at home now, but will have another look at it tomorrow - I'm in the
UK, so it's 16:40 here now.

Thanks for your help with this tho' - if I can get it cracked, it'll be
fantastic!

Iain

 

[Jon Lewis]

By on its own I meant just make this change, don't change the declaration of
strItems to Static.

This IFF statement is just to add your ; seperator when adding additional
keywords only if ctlDest is already displaying keywords.

Lets simplify things, try:

ctlDest = ctlDest & ";" & strItems

If this doesn't work then there is some other problem

HTH


"Iain" <[email protected]


t.com> wrote in message

 

[Guest]

I get you. That's what I tried before without looking at the static thing, as
I followed what it should be doing.

But when you selected keyword1, keyword3 and keyword5 (for example), instead
of adding 'keyword3' after keyword1, it added 'keyword1;keyword3' after
'keyword1', etc, so the KeywordNew field would be populated with :

'keyword1; keyword1; keyword3; keyword1; keyword3; keyword5'

instead of just 'keyword1;keyword3;keyword5'.

I'll try just ctlDest = ctlDest & ";" & strItems when I get back to the
office tomorrow and let you know the results.

Thanks again,
Iain

 

[Guest]

Hi John,

Just having a play with this again this morning, and it is almost there
actually.

Simply changing :

ctlDest = strItems to ctlDest = ctlDest & strItems

Has achived two things :

1. It has solved the issue of selecting keywords from more than one category
- which is a big plus, as that was the main objective.

2. It now also lets keywords be added to any existing keywords for any
record, without overwriting them from scratch if the record has been moved
off and returned to.

However, there are now a couple of new issues -

1. Although it works as it should if you leave a record, and return to it,
it doesn't work if you select more than one keyword whilst on a record - this
is basically what I described above.

ie, if you select 'Keyword1' and then 'Keyword2', the KeywordsNew field ends
up with :

'Keyword1';'Keyword1';'Keyword2'; instead of just 'Keyword1';'Keyword2';

2. Also, it is no longer sorting the keywords into alphabetical order, which
it was doing when the code was just ctlDest = strItems. (Although this would
be good, it's less important than the other functionality.

It's almost there so, so if you can spot what would fix this, that would be
hugely appreciated.

Cheers,
Iain

 

[Guest]

Jon,

Just this second noticed what's happening with the duplicates thing.

Basically when you select a keyword, it highlights in the list, and stays
highlighted.

So if you then select another keyword, it adds the two that are highlight.

But a solution is to simply click again on each keyword after it's been
selected, and dumped in the KeywordNew field to unselect it from the list
before selecting another keyword.

Which is probably livable with - unless there's a bit of code that would
automate this and eliminate the need for a second click.

Iain