Option Group bound to a field

[klynshoe]

Hello,

I am using the instructions at this website adapted to Access 2013. I am trying to create an option box where the numerical values get translated to my desired text values in a table field.

The Option Group is name 'Frame64' where my bounded text box is named 'SponsorName'. The Option Group Values are numbered 1 through 8.

Here's my VBA code to make the translation:

Private Sub Frame64_AfterUpdate()​

Select Case Me.Frame64.Value​

Case 1​

SponsorName.Value = "21st/71st"​

Case 2​

SponsorName.Value = "DNDO"​

Case 3​

SponsorName.Value = "DTRA"​

Case 4​

SponsorName.Value = "FBI"​

Case 5​

SponsorName.Value = "JSOC"​

Case 6​

SponsorName.Value = "NECC"​

Case 7​

SponsorName.Value = "NECC(P)"​

Case 8​

SponsorName.Value = "USNORTHCOM"​

End Select​

End Sub


​

This code is not working... the text box (and table field) is picking up the numerical value, not the translated text value. How do I fix this?

USING ACCESS 2013 - I AM A BEGINNER ACCESS AND VBA USER.

 

[xenou]

Seems right. What kind of field is SponsorName bound to?

Also noting that you don't have the Me qualifier on your sponsorname fields inside the select statement (which may or may not matter - I would think not, but who knows? I.e., Me.SponsorName.Value vs just SponsorName.Value)

You can add some messages to your code to get more information at runtime (this is an old debugging trick from way back).

Private Sub Frame64_AfterUpdate()
dim msg as String

msg = ""
msg = msg & "in Frame64_AfterUpdate..."
msg = msg & vbnewline & "SponsorName value is " & SponsorName.Value
msg = msg & vbnewline & "Frame64 value is " & Me.Frame64.Value
msgbox msg

Select Case Me.Frame64.Value
Case 1
SponsorName.Value = "21st/71st"
Case 2
SponsorName.Value = "DNDO"
Case 3
SponsorName.Value = "DTRA"
Case 4
SponsorName.Value = "FBI"
Case 5
SponsorName.Value = "JSOC"
Case 6
SponsorName.Value = "NECC"
Case 7
SponsorName.Value = "NECC(P)"
Case 8
SponsorName.Value = "USNORTHCOM"
End Select

msg = ""
msg = msg & "after Frame64_AfterUpdate..."
msg = msg & vbnewline & "SponsorName value is " & SponsorName.Value
msgbox msg

End Sub

Also you can set a breakpoint on the first line of the code and step through it.
See here for essential vba debugging skills:
A Day in the Life: Programming: Excel VBA Debugging for Beginners

 

[klynshoe]

The SponsorName field is just a ShortText data type. I tried the "Me' qualifier and the messages to no avail. The Options Group is unbound, so I'm still not understanding why this isn't working. I suspect it's not recognizing the name of the bound (hidden) text box, is there a way to confirm that? I'll give the vba debug a go.

 

[xenou]

Also unhide the text box while your debugging - you should be able to see it changing in real time. Does it really change to the value of the frame? Are you sure the frame is unbound?