Solved

Code not working  ( option box)

Posted on 2002-03-14
14
197 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
ID: 6865237
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
ID: 6865243
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
ID: 6865248
and you forgot the .Update to.

Jim.
0
 

Author Comment

by:G4B
ID: 6865303
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
ID: 6865345
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
ID: 6865350
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
ID: 6865388
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 6

Expert Comment

by:devtha
ID: 6865508
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
ID: 6865510
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
ID: 6865694
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
ID: 6865904
Send me a 97 db and I will make it work as I have commented.
devthaa@yahoo.com
0
 

Author Comment

by:G4B
ID: 6877881
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
ID: 6877885
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
ID: 6878124
Glad it works.
Thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

919 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

14 Experts available now in Live!

Get 1:1 Help Now