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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

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

Reply | Quote

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) & “, “

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Regards, Leigh

Reply | Quote

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.

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

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

Reply | Quote

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.

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

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

Reply | Quote

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

Regards, Leigh

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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) & “, “

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote