Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Code not working ( option box)

I have the following code behind a form. I have option boxes to select and the result is going into the table "Orders" fine. but,,, it is not putting the results the way I would like to see it. For example
In my code I have that when the type of payment is option box 1 then I would like to see in the table "CK" not number 1 but is not doing what I want, instead it is putting the number 1 instead of the "CK".

Any ideas of what I could be doing wrong or what should I change or add. Please give sample codes.

Thank you

Here is what I have.

'Takes the information from the Option group and puts it into the table in the correct
'string or text form

Dim MyDatabase As Database, Temporder As Recordset
     
    Set MyDatabase = CurrentDb 'DBEngine.Workspaces(0).Databases(0)                  ' Get current database.
    Set Temporder = MyDatabase.OpenRecordset("Orders", dbOpenTable)      ' Create Recordset.

    With Temporder

        ' check that records exists
        If .BOF And .EOF Then
            ' no records in the table
        Else
            .MoveLast

        ''''''here begin option box for the deposit fund numbers
             
            .Edit
                         
           Select Case Trim(![Type of Payment])
                Case "1": ![Type of Payment] = "ck"
                Case "2": ![Type of Payment] = "po"
                Case "3": ![Type of Payment] = "rn"
                Case "4": ![Type of Payment] = "ca"
                Case "5": ![Type of Payment] = "eft"
                Case Else: ' any other value?
            End Select
0
G4B
Asked:
G4B
  • 5
  • 4
  • 3
  • +2
1 Solution
 
joekendallCommented:
Is the option group's control source the Type of Payment field in the table? If so, leave it unbound.

Also, is the code you gave above all of it. I don't see an Update to update the record in the table.

Thanks!

Joe
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
What's wrong is this:

          Select Case Trim(![Type of Payment])


  You need to reference the control on the form. Will be something like:

  Select Case Me![myControlName]

Also, what type of control is this?  If it's a combo, double check what the bound column property is.  If it's option group, then you will get numeric values like you are expecting in the case statements.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
and you forgot the .Update to.

Jim.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
G4BAuthor Commented:
Nothing works.. I just tried all your suggestions.. Sorry I also forgot the last part of my code which is:

Here is where the update comes..

            ![Amount Due] = Forms![Order Entry]![Total]
            ![FirstName] = Forms![Order Entry]![FirstName]
            ![LastName] = Forms![Order Entry]![LastName]
            ![OrganizationName] = Forms![Order Entry]![OrganizationName]
            ![Address1-street] = Forms![Order Entry]![Address1-street]
            ![Address2-PO Box] = Forms![Order Entry]![Address2-PO Box]
            ![State] = Forms![Order Entry]![State]
            ![City] = Forms![Order Entry]![City]
            ![Zip Code] = Forms![Order Entry]![Zip Code]
            ![County] = Forms![Order Entry]![County]
            .Update
            .Close
             
        End If    ' (.BOF And .EOF)
         
    End With



Now..
This works very well when I compact the databse but then a couple entries and it breaks.. I really can't tell what is the problem. It works in Access 97 because I just converted to Access 2000. But like I say this works WELL when I compact the DB. In the same time I don't want users to be compacting the database everytime they enter 7 to 9  records.

Thank you.

0
 
blakeh1Commented:
Is your field numeric or text?
Option boxes normally have a numeric value, try

           Select Case Trim(![Type of Payment])
               Case 1: ![Type of Payment] = "ck"
               Case 2: ![Type of Payment] = "po"
               Case 3: ![Type of Payment] = "rn"
               Case 4: ![Type of Payment] = "ca"
               Case 5: ![Type of Payment] = "eft"
               Case Else: ' any other value?
           End Select

Alternatively, you may want to consider using a lookup table, this way you can leave the option group bound and in queries and reports, join to your lookup table top get the display value
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Hum not understanding everything...what happens if you don't compact the database?

and you do understand that your updating the last record in the table?

Maybe if you could outline what is happening with this form a bit that would help.

Also is this code running in the form?

Jim.



0
 
G4BAuthor Commented:
My option box is not a numeric value is a text value.. because I want to put "CK","PO","RN" ... instead of the number in my table.. so I can't have it numeric.

For some reason the value won't go into the table maybe I am doing something wrong here
Dim MyDatabase As Database, Temporder As Recordset
   
   Set MyDatabase = CurrentDb 'DBEngine.Workspaces(0).Databases(0)                  ' Get current database.
   Set Temporder = MyDatabase.OpenRecordset("Orders", dbOpenTable)      ' Create Recordset.

   

Now, I am not sure why this works after I compact my database. I have never had this problem before..
0
 
devthaCommented:
Here is the problem when you refer to Optiongroups' values
it is 1,2,3,....
Also this option group should not be bound.
It should be unbound.
I see that [Type of Payment] is a field on the table refered on the form.
Use a diff name (OptGroupTypeofPayment) for option group and change the code accordingly or as follows.
Do not forget to make it unbound
so you must check like this

         Select Case ![OptGroupTypeofPayment]
              Case 1: ![Type of Payment] = "ck"
              Case 2: ![Type of Payment] = "po"
              Case 3: ![Type of Payment] = "rn"
              Case 4: ![Type of Payment] = "ca"
              Case 5: ![Type of Payment] = "eft"
              Case Else: ' any other value?
          End Select

0
 
devthaCommented:
What I meant to say
The values are numeric for option group
but you want to store string so you must do as I have stated above.
0
 
G4BAuthor Commented:
I follow what you said. It did not work either. This is really frustrating.. I have not been able to fix this for a while...
any other suggestions please.

Thank you.
0
 
devthaCommented:
Send me a 97 db and I will make it work as I have commented.
devthaa@yahoo.com
0
 
G4BAuthor Commented:
Devtha,
Thank you so much!!! I think it works.. there are just a couple little things but not major.. I think I can continue with the rest..

I appreciate so much for all the help that everybdody has done it for me.

Thank you
0
 
G4BAuthor Commented:
Devtha,
Thank you so much!!! I think it works.. there are just a couple little things but not major.. I think I can continue with the rest..

I appreciate so much for all the help that everybdody has done it for me.

Thank you
0
 
devthaCommented:
Glad it works.
Thanks
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now