asked on

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

joekendall

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

Jim Dettman (EE MVE)

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.

Jim Dettman (EE MVE)

and you forgot the .Update to.

Jim.

G4B

ASKER

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.

blakeh1

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

Jim Dettman (EE MVE)

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.

G4B

ASKER

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..

devtha

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

devtha

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.

G4B

ASKER

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.

ASKER CERTIFIED SOLUTION

devtha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

G4B

ASKER

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

G4B

ASKER

devtha

Glad it works.
Thanks