Solved

Code not working  ( option box)

Posted on 2002-03-14
14
192 Views
Last Modified: 2006-11-17
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
Comment
Question by:G4B
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
and you forgot the .Update to.

Jim.
0
 

Author Comment

by:G4B
Comment Utility
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
 
LVL 6

Expert Comment

by:blakeh1
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:G4B
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 6

Expert Comment

by:devtha
Comment Utility
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
 
LVL 6

Expert Comment

by:devtha
Comment Utility
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
 

Author Comment

by:G4B
Comment Utility
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
 
LVL 6

Accepted Solution

by:
devtha earned 100 total points
Comment Utility
Send me a 97 db and I will make it work as I have commented.
devthaa@yahoo.com
0
 

Author Comment

by:G4B
Comment Utility
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
 

Author Comment

by:G4B
Comment Utility
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
 
LVL 6

Expert Comment

by:devtha
Comment Utility
Glad it works.
Thanks
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now