Select Case error

[imrosie]

I have a listbox called 'paymnt' ; it has 2 values (check & creditcard). Just below paymnt, I have a command button called 'sendpay' . I am trying to use a Select statement in 'sendpay' based on the paymnt control. I think it can be done.. but it's not working. Can anyone see what a newbie can't? thanks so much. Here's the code:
[code]Private Sub sendpay_Click()
Select Case Me.paymnt.Value
Case "Check"
DoCmd.OpenForm "paymntcrdtcrd"
Case "Credit Card"
DoCmd.OpenForm "paymentchk"
Case Else
End Select
End Sub


I've tried the first line without Value, still didn't work. I've tried using an exclamation between Me and paymnt on first line.

I get a 2447 run-time error "invalid use of the . (dot) or ! operator or invalid parenthesis"

Where am I going wrong?
I've tried to simplify it with this:

Private Sub sendpay_Click()
If Me![paymnt].Value = 1 Then
DoCmd.OpenForm "paymntcrdtcrd"
Else
 DoCmd.OpenForm "paymntchk"
End If
 

I've searched all over, can't seem to nail this one.

Any ideas??? thanks in advance.
imrosie

[JKing]

List boxes can be tricky. There's a few things you can do but here's one that will flow with what you already have.

Private Sub sendpay_Click()
Select Case Me.paymnt.ItemData(Me.paymnt.ItemsSelected(0))
  Case "Check"
    DoCmd.OpenForm "paymntcrdtcrd"
  Case "Credit Card"
    DoCmd.OpenForm "paymentchk"
  Case Else
End Select
End Sub
 

Me.paymnt.ItemsSelected(0) returns the row that is selected.
Me.paymnt.ItemData returns the value for the specified row.

Give that a try and let me know how it worked out.

[imrosie]

List boxes can be tricky. There's a few things you can do but here's one that will flow with what you already have.

Private Sub sendpay_Click()
Select Case Me.paymnt.ItemData(Me.paymnt.ItemsSelected(0))
  Case "Check"
    DoCmd.OpenForm "paymntcrdtcrd"
  Case "Credit Card"
    DoCmd.OpenForm "paymentchk"
  Case Else
End Select
End Sub
 

Me.paymnt.ItemsSelected(0) returns the row that is selected.
Me.paymnt.ItemData returns the value for the specified row.

Give that a try and let me know how it worked out.

Thanks so much for this...I tried it and it failed though with the following error message:
run-time error 2480 "You referred to a property by a numeric argument that isn't one of the property numbers in the collection......

that's over my head (I still speak Newbie)...any ideas?
thanks
Rosie

[JKing]

ItemsSelected Property is a collection that holds all the row values for any selected items in the listbox. If no item is selected you'll get that error. I think I may have over complicated this.

Let's take a look at your listbox properties.
If you're using a value list They should look like this:

Row Source Type.... Value List
Row Source............ "Check";"Credit Card"
Column Count......... 1
Bound Column........ 1
 

You should be able to use your original code with this setting.

Private Sub sendpay_Click()
Select Case Me.paymnt.Value
  Case "Check"
    DoCmd.OpenForm "paymntcrdtcrd"
  Case "Credit Card"
    DoCmd.OpenForm "paymentchk"
  Case Else
    Msgbox "Please choose a payment method"
End Select
End Sub
 

On the other hand if you are using an alternate record source then it will change things slightly. The value property of a list box returns the value in the bound column for the item selected in the list. So, if you're using a query and hiding the key column the listbox is bound to you will need your Case statements to correspond with the values of the key column.

[missinglinq]

Just slightly off subject , but if the user selects "Check" do you really want to open a form named "paymntcrdtcrd" and vice versa, open a form named "paymentchk" if "Credir Card" is chosen?

Linq ;0)>

[missinglinq]

While Access' error messages are often esoteric and sometimes even slam off the wall, you should at least glance at them and see if they apply.

"invalid use of the . (dot) or ! operator or invalid parenthesis"

Now where in your code did you use the . (dot) or ! operator?

Right, in Select Case Me.paymnt.value. This code works just fine, just using the name of the control:

Private Sub sendpay_Click()
  Select Case paymnt
   Case "Check"
    DoCmd.OpenForm "paymntcrdtcrd"
  Case "Credit Card"
   DoCmd.OpenForm "paymentchk"
Case Else
End Select
End Sub

Notice, I left your code as you posted it, but as I said in my previous post, it looks like you have your forms reversed!

Good Luck!

Linq ;0)>

[JKing]

Should also work using .value as access has default properties for all controls and the listbox's is the .value property. Still if it's not functioning properly I believe it would have to do with the listbox's bound column.

P.S. Good spot on the name mix up Linq!

[missinglinq]

Actually, I went back and ran her original code and it works for me. I have to assume Rosie used the listbox wizard to make the listbox, so everything should be set on the default settings and it's not like it's complicated, you got two choices! Of course, anytime something simple that should work in Access doesn't work , you have to think about $)@*%^&^!@ corruption!

If you still can't get it working, Rosie, you might want try deleting the listbox and re-creating it.

Linq ;0)>

[imrosie]

ItemsSelected Property is a collection that holds all the row values for any selected items in the listbox. If no item is selected you'll get that error. I think I may have over complicated this.

Let's take a look at your listbox properties.
If you're using a value list They should look like this:

Row Source Type.... Value List
Row Source............ "Check";"Credit Card"
Column Count......... 1
Bound Column........ 1
 

You should be able to use your original code with this setting.

Private Sub sendpay_Click()
Select Case Me.paymnt.Value
  Case "Check"
    DoCmd.OpenForm "paymntcrdtcrd"
  Case "Credit Card"
    DoCmd.OpenForm "paymentchk"
  Case Else
    Msgbox "Please choose a payment method"
End Select
End Sub
 

On the other hand if you are using an alternate record source then it will change things slightly. The value property of a list box returns the value in the bound column for the item selected in the list. So, if you're using a query and hiding the key column the listbox is bound to you will need your Case statements to correspond with the values of the key column.

No, JKing,

You're exactly right with my listbox properties. I'm scratching my head...
Rosie

[imrosie]

Just slightly off subject , but if the user selects "Check" do you really want to open a form named "paymntcrdtcrd" and vice versa, open a form named "paymentchk" if "Credir Card" is chosen?

Linq ;0)>

Missinglinq,
you're right, I must have criss crossed the "check" with the "credit card" statements, I'll fix that. thanks

Rosie

[JKing]

I'm quite curious to see what value is being produced by the list box.

Throw a Debug.Print under the click event like this:

Private Sub sendpay_Click()
Debug.Print Me.paymnt.Value
'rest of code here
End Sub
 

Make sure you have the immediate window open so you can see the results. Ctrl+G will open it from the VBA editor window. Try this twice. Once with check selected and once with Credit card selected and see what results.

[imrosie]

Hello JKing and Missinglinq,

I re-created the listbox...it works beautifully (also made sure the "check" and "creditcard" select statements were correct.
thanks for your help.
Here's the code:

Private Sub sendpay_Click()
Private Sub sendpay_Click()
Debug.Print Me.paymnt.Value
Select Case Me.paymnt.Value
  Case "Check"
    DoCmd.OpenForm "paymntchk"
  Case "Credit Card"
    DoCmd.OpenForm "paymentcrdtcrd"
  Case Else
    MsgBox "Please choose a payment method"
End Select
End Sub
 

thanks again,,,I don't know what was wrong before.
Rosie

[missinglinq]

Glad you got it fixed, Rosie!

Linq ;0)>