Listbox based on text box and combo box.

[BerkshireGuy]

On an unbound form, I have a combobox called 'cboproducttype' and a
text box called 'txtamountappliedfor'.

I have an Add button that I would like the user to be able to hit once
a product and amount applied for has been entered. This should
populate an unbound listbox to display their selections. A user can
select one or more sets of product types and the amount applied for.
They should also have the functionality to remove a 'set' if they made
a mistake without the need to rebuild the whole listbox of
selections.

Then - I want to loop through the listbox of selections to see what is
being requested. (I know how to do this part - just wanted to let you
all know why I am doing this)

My question - to populate the listbox - I was starting with just
updating the recordsource and having the recordsource type set to
'value list'. However this seems to cause a few issues. For
instance, I couldn't display the amount as applied for as currency.

Am I going down the wrong road? Should I consider a querydef? Or
should the way I was thinking work?

Thanks,
-Brian

[Tom van Stiphout]

On Tue, 31 Jul 2007 18:40:16 -0700, BerkshireGuy
<be*************************@yahoo.comwrote:

The listbox displays text. You can format a value as currency before
adding to the listbox:
MyListbox.RowSource = Format$(txtAmountAppliedFor, "Currency") 'etc.

>
On an unbound form, I have a combobox called 'cboproducttype' and a
text box called 'txtamountappliedfor'.

I have an Add button that I would like the user to be able to hit once
a product and amount applied for has been entered. This should
populate an unbound listbox to display their selections. A user can
select one or more sets of product types and the amount applied for.
They should also have the functionality to remove a 'set' if they made
a mistake without the need to rebuild the whole listbox of
selections.

Then - I want to loop through the listbox of selections to see what is
being requested. (I know how to do this part - just wanted to let you
all know why I am doing this)

My question - to populate the listbox - I was starting with just
updating the recordsource and having the recordsource type set to
'value list'. However this seems to cause a few issues. For
instance, I couldn't display the amount as applied for as currency.

Am I going down the wrong road? Should I consider a querydef? Or
should the way I was thinking work?

Thanks,
-Brian

[BerkshireGuy]

On Jul 31, 10:25 pm, Tom van Stiphout <[e-mail]:

On Tue, 31 Jul 2007 18:40:16 -0700, BerkshireGuy

<[e-mail]:

The listbox displays text. You can format a value as currency before
adding to the listbox:
MyListbox.RowSource = Format$(txtAmountAppliedFor, "Currency") 'etc.

On an unbound form, I have a combobox called 'cboproducttype' and a
text box called 'txtamountappliedfor'.

I have an Add button that I would like the user to be able to hit once
a product and amount applied for has been entered. This should
populate an unbound listbox to display their selections. A user can
select one or more sets of product types and the amount applied for.
They should also have the functionality to remove a 'set' if they made
a mistake without the need to rebuild the whole listbox of
selections.

Then - I want to loop through the listbox of selections to see what is
being requested. (I know how to do this part - just wanted to let you
all know why I am doing this)

Tom:

I tried that earlier and it's causing a problem because of the "," in
the currency amount. It seperates everything after the common and
thinks it's another valuie, thus moving the 0's to the it's own
column.

Here is the code:

Private Sub cmdAdd_Click()

If Len(Me.lstbxSelectedProductTypes.RowSource) = 0 Then
Me.lstbxSelectedProductTypes.RowSource =
Me.cboProductType.Column(0) & ";" & Format$(Me.txtAmountAppliedFor,
"Currency") & ";"
Else
Me.lstbxSelectedProductTypes.RowSource =
Me.lstbxSelectedProductTypes.RowSource & _
Me.cboProductType.Column(0) & ";" & Format$
(Me.txtAmountAppliedFor, "Currency") & ";"
End If
End Sub

Thanks
B

>

My question - to populate the listbox - I was starting with just
updating the recordsource and having the recordsource type set to
'value list'. However this seems to cause a few issues. For
instance, I couldn't display the amount as applied for as currency.

Am I going down the wrong road? Should I consider a querydef? Or
should the way I was thinking work?

Thanks,
-Brian- Hide quoted text -

- Show quoted text -

[Tom van Stiphout]

On Tue, 31 Jul 2007 19:48:33 -0700, BerkshireGuy
<be*************************@yahoo.comwrote:

Fortunately there is a simple solution: wrap the currency values in
double-quotes.

-Tom.

>On Jul 31, 10:25 pm, Tom van Stiphout <[e-mail]:

>On Tue, 31 Jul 2007 18:40:16 -0700, BerkshireGuy

<[e-mail]:

The listbox displays text. You can format a value as currency before
adding to the listbox:
MyListbox.RowSource = Format$(txtAmountAppliedFor, "Currency") 'etc.

>On an unbound form, I have a combobox called 'cboproducttype' and a
text box called 'txtamountappliedfor'.

>I have an Add button that I would like the user to be able to hit once
a product and amount applied for has been entered. This should
populate an unbound listbox to display their selections. A user can
select one or more sets of product types and the amount applied for.
They should also have the functionality to remove a 'set' if they made
a mistake without the need to rebuild the whole listbox of
selections.

>Then - I want to loop through the listbox of selections to see what is
being requested. (I know how to do this part - just wanted to let you
all know why I am doing this)

Tom:

I tried that earlier and it's causing a problem because of the "," in
the currency amount. It seperates everything after the common and
thinks it's another valuie, thus moving the 0's to the it's own
column.

Here is the code:

Private Sub cmdAdd_Click()

If Len(Me.lstbxSelectedProductTypes.RowSource) = 0 Then
Me.lstbxSelectedProductTypes.RowSource =
Me.cboProductType.Column(0) & ";" & Format$(Me.txtAmountAppliedFor,
"Currency") & ";"
Else
Me.lstbxSelectedProductTypes.RowSource =
Me.lstbxSelectedProductTypes.RowSource & _
Me.cboProductType.Column(0) & ";" & Format$
(Me.txtAmountAppliedFor, "Currency") & ";"
End If
End Sub

Thanks
B

>>

>My question - to populate the listbox - I was starting with just
updating the recordsource and having the recordsource type set to
'value list'. However this seems to cause a few issues. For
instance, I couldn't display the amount as applied for as currency.

>Am I going down the wrong road? Should I consider a querydef? Or
should the way I was thinking work?

>Thanks,
-Brian- Hide quoted text -

- Show quoted text -

[BerkshireGuy]

Ok - but will that maintain the value as currency when I read the
values back?

-B
On Jul 31, 11:14 pm, Tom van Stiphout <[e-mail]:

On Tue, 31 Jul 2007 19:48:33 -0700, BerkshireGuy

<[e-mail]:

Fortunately there is a simple solution: wrap the currency values in
double-quotes.

-Tom.

On Jul 31, 10:25 pm, Tom van Stiphout <[e-mail]:

On Tue, 31 Jul 2007 18:40:16 -0700, BerkshireGuy

<[e-mail]:

The listbox displays text. You can format a value as currency before
adding to the listbox:
MyListbox.RowSource = Format$(txtAmountAppliedFor, "Currency") 'etc.

On an unbound form, I have a combobox called 'cboproducttype' and a
text box called 'txtamountappliedfor'.

I have an Add button that I would like the user to be able to hit once
a product and amount applied for has been entered. This should
populate an unbound listbox to display their selections. A user can
select one or more sets of product types and the amount applied for.
They should also have the functionality to remove a 'set' if they made
a mistake without the need to rebuild the whole listbox of
selections.

Then - I want to loop through the listbox of selections to see what is
being requested. (I know how to do this part - just wanted to let you
all know why I am doing this)

Tom:

I tried that earlier and it's causing a problem because of the "," in
the currency amount. It seperates everything after the common and
thinks it's another valuie, thus moving the 0's to the it's own
column.

Here is the code:

Private Sub cmdAdd_Click()

If Len(Me.lstbxSelectedProductTypes.RowSource) = 0 Then
Me.lstbxSelectedProductTypes.RowSource =
Me.cboProductType.Column(0) & ";" & Format$(Me.txtAmountAppliedFor,
"Currency") & ";"
Else
Me.lstbxSelectedProductTypes.RowSource =
Me.lstbxSelectedProductTypes.RowSource & _
Me.cboProductType.Column(0) & ";" & Format$
(Me.txtAmountAppliedFor, "Currency") & ";"
End If
End Sub

Thanks
B

My question - to populate the listbox - I was starting with just
updating the recordsource and having the recordsource type set to
'value list'. However this seems to cause a few issues. For
instance, I couldn't display the amount as applied for as currency.

Am I going down the wrong road? Should I consider a querydef? Or
should the way I was thinking work?

Thanks,
-Brian- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[Tom van Stiphout]

On Tue, 31 Jul 2007 20:54:46 -0700, BerkshireGuy
<be*************************@yahoo.comwrote:

Please post back after you have taken 10 seconds to try that.
-Tom.

>Ok - but will that maintain the value as currency when I read the
values back?

-B
On Jul 31, 11:14 pm, Tom van Stiphout <[e-mail]:

>On Tue, 31 Jul 2007 19:48:33 -0700, BerkshireGuy

<[e-mail]:

Fortunately there is a simple solution: wrap the currency values in
double-quotes.

-Tom.

>On Jul 31, 10:25 pm, Tom van Stiphout <[e-mail]:
On Tue, 31 Jul 2007 18:40:16 -0700, BerkshireGuy

><[e-mail]:

>The listbox displays text. You can format a value as currency before
adding to the listbox:
MyListbox.RowSource = Format$(txtAmountAppliedFor, "Currency") 'etc.

>On an unbound form, I have a combobox called 'cboproducttype' and a
text box called 'txtamountappliedfor'.

>I have an Add button that I would like the user to be able to hit once
a product and amount applied for has been entered. This should
populate an unbound listbox to display their selections. A user can
select one or more sets of product types and the amount applied for.
They should also have the functionality to remove a 'set' if they made
a mistake without the need to rebuild the whole listbox of
selections.

>Then - I want to loop through the listbox of selections to see what is
being requested. (I know how to do this part - just wanted to let you
all know why I am doing this)

>Tom:

>I tried that earlier and it's causing a problem because of the "," in
the currency amount. It seperates everything after the common and
thinks it's another valuie, thus moving the 0's to the it's own
column.

>Here is the code:

>Private Sub cmdAdd_Click()

>If Len(Me.lstbxSelectedProductTypes.RowSource) = 0 Then
Me.lstbxSelectedProductTypes.RowSource =
Me.cboProductType.Column(0) & ";" & Format$(Me.txtAmountAppliedFor,
"Currency") & ";"
Else
Me.lstbxSelectedProductTypes.RowSource =
Me.lstbxSelectedProductTypes.RowSource & _
Me.cboProductType.Column(0) & ";" & Format$
(Me.txtAmountAppliedFor, "Currency") & ";"
End If
End Sub

>Thanks
B

>My question - to populate the listbox - I was starting with just
updating the recordsource and having the recordsource type set to
'value list'. However this seems to cause a few issues. For
instance, I couldn't display the amount as applied for as currency.

>Am I going down the wrong road? Should I consider a querydef? Or
should the way I was thinking work?

>Thanks,
-Brian- Hide quoted text -

>- Show quoted text -- Hide quoted text -

- Show quoted text -

[BerkshireGuy]

This worked - thanks Tom.

Here is the code if anyone needs it:

Adding to the unbound listbox:

If Len(Me.lstbxSelectedProductTypes.RowSource) = 0 Then
Me.lstbxSelectedProductTypes.RowSource =
Me.cboProductType.Column(0) & ";'" & _
Format$(Me.txtAmountAppliedFor, "Currency") & "';"
Else
Me.lstbxSelectedProductTypes.RowSource =
Me.lstbxSelectedProductTypes.RowSource & _
Me.cboProductType.Column(0) & ";'" & Format$
(Me.txtAmountAppliedFor, "Currency") & "';"
End If

Read the values from the listbiox. Note that I had to force select
all before stepping through the listbox. This necessary?

Dim ctl As Control
Dim varItem As Variant, i As Integer

Set ctl = Me.lstbxSelectedProductTypes

For i = 0 To ctl.ListCount
ctl.Selected(i) = True
Next

For Each varItem In ctl.ItemsSelected
Debug.Print ctl.Column(0, varItem) & " " & CCur(ctl.Column(1,
varItem))

Next varItem





On Aug 1, 12:44 am, Tom van Stiphout <[e-mail]:

On Tue, 31 Jul 2007 20:54:46 -0700, BerkshireGuy

<[e-mail]:

Please post back after you have taken 10 seconds to try that.
-Tom.

Ok - but will that maintain the value as currency when I read the
values back?

-B

On Jul 31, 11:14 pm, Tom van Stiphout <[e-mail]:

On Tue, 31 Jul 2007 19:48:33 -0700, BerkshireGuy

<[e-mail]:

Fortunately there is a simple solution: wrap the currency values in
double-quotes.

-Tom.

On Jul 31, 10:25 pm, Tom van Stiphout <[e-mail]:
On Tue, 31 Jul 2007 18:40:16 -0700, BerkshireGuy

<[e-mail]:

The listbox displays text. You can format a value as currency before
adding to the listbox:
MyListbox.RowSource = Format$(txtAmountAppliedFor, "Currency") 'etc.

On an unbound form, I have a combobox called 'cboproducttype' and a
text box called 'txtamountappliedfor'.

I have an Add button that I would like the user to be able to hit once
a product and amount applied for has been entered. This should
populate an unbound listbox to display their selections. A user can
select one or more sets of product types and the amount applied for.
They should also have the functionality to remove a 'set' if they made
a mistake without the need to rebuild the whole listbox of
selections.

Then - I want to loop through the listbox of selections to see what is
being requested. (I know how to do this part - just wanted to let you
all know why I am doing this)

Tom:

I tried that earlier and it's causing a problem because of the "," in
the currency amount. It seperates everything after the common and
thinks it's another valuie, thus moving the 0's to the it's own
column.

Here is the code:

Private Sub cmdAdd_Click()

If Len(Me.lstbxSelectedProductTypes.RowSource) = 0 Then
Me.lstbxSelectedProductTypes.RowSource =
Me.cboProductType.Column(0) & ";" & Format$(Me.txtAmountAppliedFor,
"Currency") & ";"
Else
Me.lstbxSelectedProductTypes.RowSource =
Me.lstbxSelectedProductTypes.RowSource & _
Me.cboProductType.Column(0) & ";" & Format$
(Me.txtAmountAppliedFor, "Currency") & ";"
End If
End Sub

Thanks
B

My question - to populate the listbox - I was starting with just
updating the recordsource and having the recordsource type set to
'value list'. However this seems to cause a few issues. For
instance, I couldn't display the amount as applied for as currency.

Am I going down the wrong road? Should I consider a querydef? Or
should the way I was thinking work?

Thanks,
-Brian- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -