Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox to Textbox (VBA/Access/2002-3)

    Hi

    I want a user to choose from a listbox (lstChoices) using the Extended property (multiple choices) and have
    their choices appear in an adjacent textbox (txtChoices) as shown in the attachment. This information will
    then be passed back to the table that shows client details.

    I believe I should be using For Next VBA code but am unsure of how to go about this.

    Any suggestions would be appreciated. Thanks, Leigh

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    It is not a very good idea to store multiple items in a text field. Instead, you should create a separate table tbClientlInterests in which each client-interest combination is a separate record. If you don't have one yet, I would also create a table tblInterests. The tblClientInterests table implements a many-to-many relationship between clients and interests.

    You would present the interests in a continuous subform based on tblClientInterests, not in a list box.

    See screenshot for the relationships between the tables.
    Attached Images Attached Images
    • File Type: png x.png (3.5 KB, 3 views)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    Thanks Hans

    I thought long about whether to post it in the Access forum or VB/VBA and decided that because I do want
    to display the user's selections in a textbox (as per the original post's attachment) that it would need to be
    a VB/VBA question. That said, I see what you mean about the many to many relationship and the
    introduction of the tblClientInterests. The items in the lstChoices are presently stored in a separate table.

    So, do you have any suggestions on how I can make the results of their choices appear as a spread out
    textbox if I persist with the layout?

    Thanks, Leigh

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    The question of how you present them , and how you store them are not necessarily the same.

    You could present them as you want, as a concatenation in a single text box, but store them in a table tblclientinterests when the users clicks an OK button.

    Here is some code that will display the selection in a text box.
    <pre>Private Sub LstInterests_AfterUpdate()
    Me!txtinterests = ""
    Dim vntItem As Variant
    For Each vntItem In LstInterests.ItemsSelected

    Me!txtinterests = Me!txtinterests & LstInterests.Column(0, vntItem) & ", "
    Next
    ' now remove thr trailing ,
    Me!txtinterests = Left(Me![txtinterests], Len(Me![txtinterests]) - 2)
    End Sub

    </pre>

    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    We prefer to keep ALL questions about Access, including programming in Access VBA, in the Access forum. Access controls are quite different from the MSForms controls available in the other Office applications, with different properties, methods and events, so programming them is specific to Access.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    Hi John

    Thanks for your reply. My question is now how to display the actual values in the txtInterests box rather
    than just their position value in the list.

    I am aiming for text rather than numbers so that the user can recognise the results of their choices.

    Thanks, Leigh

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    When I did this, my listbox had just one column, with the actual values.

    If yours has two columns with the first one hidden a hidden number , you just need to change:

    Me!txtinterests = Me!txtinterests & LstInterests.Column(0, vntItem) & ", "

    to

    Me!txtinterests = Me!txtinterests & LstInterests.Column(1, vntItem) & ", "
    Regards
    John



  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    Thanks, that was it. - But now I note that the same data is selected for all the other clients. Is there some
    property of the listbox that I should be considering so that its results are tied only to one record?

    Thanks again.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    When you move to a new record, you need to clear the textbox, so that you start afresh with each new person.

    Assuming that you tetbox is unbound (i.e. it is not connected to a field in the underlying table) then you could just use:
    Me!txtinterests = Null

    in the oncurrent event of the form.

    All this, of course, leaves the question of how you store the interests, and how you display them for old data, unresolved.
    Regards
    John



  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    Thanks John - do you have any suggestions on how to store the data that the textbox now proudly displays?

    Regards, Leigh

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Listbox to Textbox (VBA/Access/2002-3)

    As Hans told you back at the start of this thread, you should store the interests in tblclientinterests.

    After you have selected the items from the listbox, you will click a button initiates the saving. Here is some code it could run.
    <pre>If ListInterests.ItemsSelected.Count > 0 Then
    Dim vntItem As Variant
    Dim lngClientID As Long
    lngClientID = Me!ClientID
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Set db = CurrentDb
    sql = "Select * from tblclientinterests"
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    For Each vntItem In ListInterests.ItemsSelected
    rs.AddNew
    rs!ClientId = lngClientId
    rs!interest= ListInterests.Column(0, vntItem)
    rs.Update
    next
    rs.Close
    End If

    Set rs = nothing
    Set db = Nothing
    </pre>


    This code assumes that the clients are new, so you don't already have any interests stored. (i have copied some existing code here and modifed it - I hope I haven't left in any errors.)

    To display interests for existing clients, just use a standard subform based on the tblclientInterests. (This of course would be an easier way to do the whole job, and leave out the listbox altogether.)
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •