Possible to change text box to list box with code?

[joshapalooza]

Hi all,

What I'm trying to do is two-fold. We'll tackle one and then the other, or whichever is easiest.

First Question/Problem:

Is it possible to write VBA code that would change a text box to a (let's say) list box based on the answer to a question? For example:

Dim Answer As Integer
Answer = MsgBox("Would you like to make multiple selections?", & _
              vbCrLf & "vbYesNo + vbQuestion, "Record Selection")
If Answer = vbYes Then
             ' Code to change text box to list box where list box contains all
             ' customer names
Else
             ' Set focus to the text box in question
Me.CustomerName.SetFocus
End If
 

Second Question/Problem:

I know how to set a list box to enable mutliple selections, but what I don't know how to do is select only those names for either a report or another field. For example, let's say I have 10 customer names, but I only want a report for the 2 that I have highlighted. How would I go about doing that? Another example would be, let's say that I have 20 items of product listed in a list box and a customer buys 5 items. I've figured out how to highlight those 5 items, but not how to move those items into another field that shows what that customer has purchased.

I've heard about array variables, and I have a basic idea of what they do, but I'm still confused about how to actually implement them. Is this a situation where an array variable would solve my problem or am I still wandering around, lost in the woods?

[mshmyob]

Answer to 1st part. Not that I know of. But you could put 2 controls in the same spot and hide them until the selection is made and then unhide the 1 you want.

2nd question - I will get back to you. I am 2 busy to look at it this minute.

Hi all,

What I'm trying to do is two-fold. We'll tackle one and then the other, or whichever is easiest.

First Question/Problem:

Is it possible to write VBA code that would change a text box to a (let's say) list box based on the answer to a question? For example:

Dim Answer As Integer
Answer = MsgBox("Would you like to make multiple selections?", & _
              vbCrLf & "vbYesNo + vbQuestion, "Record Selection")
If Answer = vbYes Then
             ' Code to change text box to list box where list box contains all
             ' customer names
Else
             ' Set focus to the text box in question
Me.CustomerName.SetFocus
End If
 

Second Question/Problem:

I know how to set a list box to enable mutliple selections, but what I don't know how to do is select only those names for either a report or another field. For example, let's say I have 10 customer names, but I only want a report for the 2 that I have highlighted. How would I go about doing that? Another example would be, let's say that I have 20 items of product listed in a list box and a customer buys 5 items. I've figured out how to highlight those 5 items, but not how to move those items into another field that shows what that customer has purchased.

I've heard about array variables, and I have a basic idea of what they do, but I'm still confused about how to actually implement them. Is this a situation where an array variable would solve my problem or am I still wandering around, lost in the woods?

[missinglinq]

The first part of this question is so odd that, having gotten it twice in less than 24 hours, I have to ask if it's a part of a homework assignment. At any rate, take a look at this post:

Programatically convert a textbox to combobox and back

Welcome to TheScripts!

Linq ;0)>

[mshmyob]

Sorry about the delay.

Now for the answer to your second part.

If the MultiSelect property is set to Simple or Extended, you can use the Selected property or the ItemsSelected collection to determine whether a particular item in the list is selected. The Selected property is a zero-based array that contains the selection state of each item in a list box. For example, if you wanted to determine whether the first item in a list box is selected, you would check the value of the Selected property for that item. (Since the array has already been created for you, you do not have to create the array)

The following line of code prints the value of the Selected property for the first item in a list box named List1 to the Debug window:

Debug.Print Me!List1.Selected(0)
 

For more details on how to work with the listbox see the follwoing article from Microsoft.

Programming List Boxes

Hi all,

What I'm trying to do is two-fold. We'll tackle one and then the other, or whichever is easiest.

First Question/Problem:

Is it possible to write VBA code that would change a text box to a (let's say) list box based on the answer to a question? For example:

Dim Answer As Integer
Answer = MsgBox("Would you like to make multiple selections?", & _
              vbCrLf & "vbYesNo + vbQuestion, "Record Selection")
If Answer = vbYes Then
             ' Code to change text box to list box where list box contains all
             ' customer names
Else
             ' Set focus to the text box in question
Me.CustomerName.SetFocus
End If
 

Second Question/Problem:

I know how to set a list box to enable mutliple selections, but what I don't know how to do is select only those names for either a report or another field. For example, let's say I have 10 customer names, but I only want a report for the 2 that I have highlighted. How would I go about doing that? Another example would be, let's say that I have 20 items of product listed in a list box and a customer buys 5 items. I've figured out how to highlight those 5 items, but not how to move those items into another field that shows what that customer has purchased.

I've heard about array variables, and I have a basic idea of what they do, but I'm still confused about how to actually implement them. Is this a situation where an array variable would solve my problem or am I still wandering around, lost in the woods?

[joshapalooza]

Sorry about the delay.

Now for the answer to your second part.

If the MultiSelect property is set to Simple or Extended, you can use the Selected property or the ItemsSelected collection to determine whether a particular item in the list is selected. The Selected property is a zero-based array that contains the selection state of each item in a list box. For example, if you wanted to determine whether the first item in a list box is selected, you would check the value of the Selected property for that item. (Since the array has already been created for you, you do not have to create the array)

The following line of code prints the value of the Selected property for the first item in a list box named List1 to the Debug window:

Debug.Print Me!List1.Selected(0)
 

For more details on how to work with the listbox see the follwoing article from Microsoft.

Programming List Boxes

Thanks! That's exactly what I needed. However, I would still like to be able to see both of the columns that existed in my "selected from" list in my "selected to" list. I messed with changing the column size and count, but all that did was stack my selected items next to each other instead of showing both the UPC and the Item Description side by side as I had wanted. Here's what my code looks like that enables me to create multiple selections; maybe someone can help me figure out if it's possible to do what I'm asking. Also, I know this probably sounds stupid, but how do I preview a report based on the selections I've made? I installed a preview report button, but the report comes up blank when I click it. Hope the following code helps.

Option Compare Database
 
Private Sub cmdSelect1_Click()
    Dim lst1 As ListBox, lst2 As ListBox
    Dim itm As Variant
 
    Set lst1 = Me!lstProduct
    Set lst2 = Me!lstSelected
    ' Check selected items.
    For Each itm In lst1.ItemsSelected
        ' Set RowSource property for first selected item.
        If lst2.RowSource = "" Then
            lst2.RowSource = lst1.ItemData(itm)
        Else
            ' Check whether item has already been copied.
            If Not InStr(lst2.RowSource, lst1.ItemData(itm)) > 0 Then
                lst2.RowSource = lst2.RowSource & ";" & lst1.ItemData(itm)
            End If
        End If
    Next itm
End Sub
 
 
Private Sub cmdUnselect1_Click()
Me!lstSelected.RowSource = ""
Me!lstProduct.SetFocus
Call Form_Load
End Sub
 
Private Sub Form_Load()
 
DoCmd.MoveSize 2000, 1500
 
Dim lst1 As ListBox, lst2 As ListBox
Dim strSQL As String, strMsg As String
 
Set lst1 = Me!lstProduct
Set lst2 = Me!lstSelected
 
' Initialize SQL string.
strSQL = "SELECT [UPC],[ItemDescription] FROM tblProduct ORDER By UPC;"
 
' Notify user if multiple selection is not enabled.
If lst1.MultiSelect = 0 Then
    'strMsg = "The MultiSelect property of List1 is set to None." & _
    '"To enable multiple selection, open the form in Design view " _
    '"and set the MultiSelect property to Simple or Extended."
    MsgBox "Multiple Selection Not Enabled", vbInformation, "Multiple selection not enabled"
End If
 
' Fill List1.
With lst1
    .RowSourceType = "Table/Query"
    .RowSource = strSQL
    .ColumnCount = 2
End With
With lst2
' Specify that List2 is a value list.
    .RowSourceType = "Value List"
    .ColumnCount = 1
    End With
End Sub
Private Sub cmdPreview_Click()
On Error Resume Next
    Dim stDocName As String
    stDocName = "rptProduct"
    DoCmd.OpenReport stDocName, acViewPreview
    DoCmd.Maximize
End Sub

By the way, I've decided I would just make one box invisible instead of trying to programmatically change the style of the box. It just seems easier to me. Thanks for the suggestion.

[vstolmech513]

Thanks! That's exactly what I needed. However, I would still like to be able to see both of the columns that existed in my "selected from" list in my "selected to" list. I messed with changing the column size and count, but all that did was stack my selected items next to each other instead of showing both the UPC and the Item Description side by side as I had wanted. Here's what my code looks like that enables me to create multiple selections; maybe someone can help me figure out if it's possible to do what I'm asking. Also, I know this probably sounds stupid, but how do I preview a report based on the selections I've made? I installed a preview report button, but the report comes up blank when I click it. Hope the following code helps.

Option Compare Database
 
Private Sub cmdSelect1_Click()
    Dim lst1 As ListBox, lst2 As ListBox
    Dim itm As Variant
 
    Set lst1 = Me!lstProduct
    Set lst2 = Me!lstSelected
    ' Check selected items.
    For Each itm In lst1.ItemsSelected
        ' Set RowSource property for first selected item.
        If lst2.RowSource = "" Then
            lst2.RowSource = lst1.ItemData(itm)
        Else
            ' Check whether item has already been copied.
            If Not InStr(lst2.RowSource, lst1.ItemData(itm)) > 0 Then
                lst2.RowSource = lst2.RowSource & ";" & lst1.ItemData(itm)
            End If
        End If
    Next itm
End Sub
 
 
Private Sub cmdUnselect1_Click()
Me!lstSelected.RowSource = ""
Me!lstProduct.SetFocus
Call Form_Load
End Sub
 
Private Sub Form_Load()
 
DoCmd.MoveSize 2000, 1500
 
Dim lst1 As ListBox, lst2 As ListBox
Dim strSQL As String, strMsg As String
 
Set lst1 = Me!lstProduct
Set lst2 = Me!lstSelected
 
' Initialize SQL string.
strSQL = "SELECT [UPC],[ItemDescription] FROM tblProduct ORDER By UPC;"
 
' Notify user if multiple selection is not enabled.
If lst1.MultiSelect = 0 Then
    'strMsg = "The MultiSelect property of List1 is set to None." & _
    '"To enable multiple selection, open the form in Design view " _
    '"and set the MultiSelect property to Simple or Extended."
    MsgBox "Multiple Selection Not Enabled", vbInformation, "Multiple selection not enabled"
End If
 
' Fill List1.
With lst1
    .RowSourceType = "Table/Query"
    .RowSource = strSQL
    .ColumnCount = 2
End With
With lst2
' Specify that List2 is a value list.
    .RowSourceType = "Value List"
    .ColumnCount = 1
    End With
End Sub
Private Sub cmdPreview_Click()
On Error Resume Next
    Dim stDocName As String
    stDocName = "rptProduct"
    DoCmd.OpenReport stDocName, acViewPreview
    DoCmd.Maximize
End Sub

By the way, I've decided I would just make one box invisible instead of trying to programmatically change the style of the box. It just seems easier to me. Thanks for the suggestion.

Subscribed to this thread.

[mshmyob]

Here is the code to have 2 list boxes. One is the 'from' list box and one is the 'to' list box. When you make the selection from list box 1 it will populate list box 2 with the same selections. In this example I had 3 columns in my 2 list boxes. All columns transfer over to the 2nd list box.

List box 2 should have the same number of columns has list box 1 but change the 'ROW SOURCE TYPE' property to VALUE LIST and make sure there is NOTHING entered into the 'ROW SOURCE' property.

The bound column in both list boxes should be the same.

Dim lst1 As ListBox
Dim lst2 As ListBox
Dim itm As Variant
Dim vSearchString As String
 
 
Set lst1 = Me!lstPickFrom
Set lst2 = Me!lstPickTo
 
For Each itm In lst1.ItemsSelected
   ' Set RowSource property for first selected item.
   If lst2.RowSource = "" Then
         lst2.RowSource = lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
        Else
            ' Check whether item has already been copied.
             vSearchString = lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
            If Not InStr(lst2.RowSource, vSearchString) > 0 Then
                lst2.RowSource = lst2.RowSource & ";" & lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
 
            End If
        End If
    Next itm
 

Put this behind your button click event.

Thanks! That's exactly what I needed. However, I would still like to be able to see both of the columns that existed in my "selected from" list in my "selected to" list. I messed with changing the column size and count, but all that did was stack my selected items next to each other instead of showing both the UPC and the Item Description side by side as I had wanted. Here's what my code looks like that enables me to create multiple selections; maybe someone can help me figure out if it's possible to do what I'm asking. Also, I know this probably sounds stupid, but how do I preview a report based on the selections I've made? I installed a preview report button, but the report comes up blank when I click it. Hope the following code helps.

Option Compare Database
 
Private Sub cmdSelect1_Click()
    Dim lst1 As ListBox, lst2 As ListBox
    Dim itm As Variant
 
    Set lst1 = Me!lstProduct
    Set lst2 = Me!lstSelected
    ' Check selected items.
    For Each itm In lst1.ItemsSelected
        ' Set RowSource property for first selected item.
        If lst2.RowSource = "" Then
            lst2.RowSource = lst1.ItemData(itm)
        Else
            ' Check whether item has already been copied.
            If Not InStr(lst2.RowSource, lst1.ItemData(itm)) > 0 Then
                lst2.RowSource = lst2.RowSource & ";" & lst1.ItemData(itm)
            End If
        End If
    Next itm
End Sub
 
 
Private Sub cmdUnselect1_Click()
Me!lstSelected.RowSource = ""
Me!lstProduct.SetFocus
Call Form_Load
End Sub
 
Private Sub Form_Load()
 
DoCmd.MoveSize 2000, 1500
 
Dim lst1 As ListBox, lst2 As ListBox
Dim strSQL As String, strMsg As String
 
Set lst1 = Me!lstProduct
Set lst2 = Me!lstSelected
 
' Initialize SQL string.
strSQL = "SELECT [UPC],[ItemDescription] FROM tblProduct ORDER By UPC;"
 
' Notify user if multiple selection is not enabled.
If lst1.MultiSelect = 0 Then
    'strMsg = "The MultiSelect property of List1 is set to None." & _
    '"To enable multiple selection, open the form in Design view " _
    '"and set the MultiSelect property to Simple or Extended."
    MsgBox "Multiple Selection Not Enabled", vbInformation, "Multiple selection not enabled"
End If
 
' Fill List1.
With lst1
    .RowSourceType = "Table/Query"
    .RowSource = strSQL
    .ColumnCount = 2
End With
With lst2
' Specify that List2 is a value list.
    .RowSourceType = "Value List"
    .ColumnCount = 1
    End With
End Sub
Private Sub cmdPreview_Click()
On Error Resume Next
    Dim stDocName As String
    stDocName = "rptProduct"
    DoCmd.OpenReport stDocName, acViewPreview
    DoCmd.Maximize
End Sub

By the way, I've decided I would just make one box invisible instead of trying to programmatically change the style of the box. It just seems easier to me. Thanks for the suggestion.

[joshapalooza]

That works perfectly! Thank you.

Now, is there any way to preview a report based on the information that I have selected in my PickTo list? I currently have a preview report button, but it doesn't filter the report based on my selections. I'm sure it has something to do with an SQL statement, but as of yet, I haven't really messed with it.

[mshmyob]

You're welcome.

Answer to your report question. Yes. I will work on it and post later.

That works perfectly! Thank you.

Now, is there any way to preview a report based on the information that I have selected in my PickTo list? I currently have a preview report button, but it doesn't filter the report based on my selections. I'm sure it has something to do with an SQL statement, but as of yet, I haven't really messed with it.