By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Select Case error

imrosie
100+
P: 222
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub sendpay_Click()
  2. If Me![paymnt].Value = 1 Then
  3. DoCmd.OpenForm "paymntcrdtcrd"
  4. Else
  5.  DoCmd.OpenForm "paymntchk"
  6. End If
  7.  
I've searched all over, can't seem to nail this one.

Any ideas??? thanks in advance.
imrosie
Aug 8 '07 #1
Share this Question
Share on Google+
12 Replies


JKing
Expert 100+
P: 1,206
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub sendpay_Click()
  2. Select Case Me.paymnt.ItemData(Me.paymnt.ItemsSelected(0))
  3.   Case "Check"
  4.     DoCmd.OpenForm "paymntcrdtcrd"
  5.   Case "Credit Card"
  6.     DoCmd.OpenForm "paymentchk"
  7.   Case Else
  8. End Select
  9. End Sub
  10.  
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.
Aug 8 '07 #2

imrosie
100+
P: 222
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub sendpay_Click()
  2. Select Case Me.paymnt.ItemData(Me.paymnt.ItemsSelected(0))
  3.   Case "Check"
  4.     DoCmd.OpenForm "paymntcrdtcrd"
  5.   Case "Credit Card"
  6.     DoCmd.OpenForm "paymentchk"
  7.   Case Else
  8. End Select
  9. End Sub
  10.  
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
Aug 8 '07 #3

JKing
Expert 100+
P: 1,206
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:
Expand|Select|Wrap|Line Numbers
  1. Row Source Type.... Value List
  2. Row Source............ "Check";"Credit Card"
  3. Column Count......... 1
  4. Bound Column........ 1
  5.  
You should be able to use your original code with this setting.
Expand|Select|Wrap|Line Numbers
  1. Private Sub sendpay_Click()
  2. Select Case Me.paymnt.Value
  3.   Case "Check"
  4.     DoCmd.OpenForm "paymntcrdtcrd"
  5.   Case "Credit Card"
  6.     DoCmd.OpenForm "paymentchk"
  7.   Case Else
  8.     Msgbox "Please choose a payment method"
  9. End Select
  10. End Sub
  11.  
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.
Aug 8 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
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)>
Aug 8 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub sendpay_Click()
  2.   Select Case paymnt
  3.    Case "Check"
  4.     DoCmd.OpenForm "paymntcrdtcrd"
  5.   Case "Credit Card"
  6.    DoCmd.OpenForm "paymentchk"
  7. Case Else
  8. End Select
  9. 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)>
Aug 8 '07 #6

JKing
Expert 100+
P: 1,206
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!
Aug 8 '07 #7

missinglinq
Expert 2.5K+
P: 3,532
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)>
Aug 8 '07 #8

imrosie
100+
P: 222
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:
Expand|Select|Wrap|Line Numbers
  1. Row Source Type.... Value List
  2. Row Source............ "Check";"Credit Card"
  3. Column Count......... 1
  4. Bound Column........ 1
  5.  
You should be able to use your original code with this setting.
Expand|Select|Wrap|Line Numbers
  1. Private Sub sendpay_Click()
  2. Select Case Me.paymnt.Value
  3.   Case "Check"
  4.     DoCmd.OpenForm "paymntcrdtcrd"
  5.   Case "Credit Card"
  6.     DoCmd.OpenForm "paymentchk"
  7.   Case Else
  8.     Msgbox "Please choose a payment method"
  9. End Select
  10. End Sub
  11.  
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
Aug 8 '07 #9

imrosie
100+
P: 222
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
Aug 8 '07 #10

JKing
Expert 100+
P: 1,206
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub sendpay_Click()
  2. Debug.Print Me.paymnt.Value
  3. 'rest of code here
  4. End Sub
  5.  
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.
Aug 8 '07 #11

imrosie
100+
P: 222
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub sendpay_Click()
  2. Private Sub sendpay_Click()
  3. Debug.Print Me.paymnt.Value
  4. Select Case Me.paymnt.Value
  5.   Case "Check"
  6.     DoCmd.OpenForm "paymntchk"
  7.   Case "Credit Card"
  8.     DoCmd.OpenForm "paymentcrdtcrd"
  9.   Case Else
  10.     MsgBox "Please choose a payment method"
  11. End Select
  12. End Sub
  13.  
thanks again,,,I don't know what was wrong before.
Rosie
Aug 8 '07 #12

missinglinq
Expert 2.5K+
P: 3,532
Glad you got it fixed, Rosie!

Linq ;0)>
Aug 9 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.