Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Drop down boxes / VB help

Discussion in 'Information Technology' started by Iain, Jul 28, 2006.

  1. Iain

    Iain
    Expand Collapse
    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
     
  2. Loading...


  3. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    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

    "Iain" <Iain@discussions.microsoft.com> wrote in message
    news:80E9DC8F-9B2A-4CAA-B458-63E00B122F11@microsoft.com...
    > 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
    >
    >
     
  4. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    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
    "Iain" <Iain@discussions.microsoft.com> wrote in message
    news:80E9DC8F-9B2A-4CAA-B458-63E00B122F11@microsoft.com...
    > 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
    >
    >
     
  5. Iain

    Iain
    Expand Collapse
    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

    "Jon Lewis" wrote:

    > 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
    >
    > "Iain" <Iain@discussions.microsoft.com> wrote in message
    > news:80E9DC8F-9B2A-4CAA-B458-63E00B122F11@microsoft.com...
    > > 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
    > >
    > >

    >
    >
    >
     
  6. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    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

    "Iain" <Iain@discussions.microsoft.com> wrote in message
    news:E2570923-B06B-477B-8791-2B8A371BE82D@microsoft.com...
    > 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
    >
    > "Jon Lewis" wrote:
    >
    >> 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
    >>
    >> "Iain" <Iain@discussions.microsoft.com> wrote in message
    >> news:80E9DC8F-9B2A-4CAA-B458-63E00B122F11@microsoft.com...
    >> > 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
    >> >
    >> >

    >>
    >>
    >>
     
  7. Iain

    Iain
    Expand Collapse
    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" wrote:

    > 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
     
  8. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    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" <Iain@discussions.microsof


    t.com> wrote in message
    news:B1E9CE0B-7B3A-4155-99F6-75F0C7BD2CDA@microsoft.com...
    > 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" wrote:
    >
    >> 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

    >
     
  9. Iain

    Iain
    Expand Collapse
    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

    "Jon Lewis" wrote:

    > 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" <Iain@discussions.microsof
    >
    >
    > t.com> wrote in message
    > news:B1E9CE0B-7B3A-4155-99F6-75F0C7BD2CDA@microsoft.com...
    > > 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" wrote:
    > >
    > >> 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

    > >

    >
    >
    >
     
  10. Iain

    Iain
    Expand Collapse
    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
     
  11. Iain

    Iain
    Expand Collapse
    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
     

Share This Page