Need text value from Option Button

Question

Okay, I have been racking my brain trying to figure out how to get this to work. I have been to multiple forums and have not found an answer that actually helps yet. Or I am not applying the fix correctly. 

I have a form, and I need option buttons in the form, not an option group but option buttons because I need the user to be able to pick more than one. The trick is I need the selection of the option button to prompt a value in a text field. This text field is used to tell my SQL what to query for in the data set. 

Things I have tried: coding an After Update event. 

Private Sub Opt2_AfterUpdate()
If Opt2.Value = True Then
Forms!QueryForm.2Txt.Value = "2"
Else
Forms!QueryForm.2Txt.Value = ""
End If
End Sub

This method has netted me an error that the OLEP connection wasn't working and there was an issue with the active X control. I have tried a few different methodologies for the above VBA, and either I am not getting the syntax right or I'm really just not understanding how this operates. 

Any help is greatly appreciated. 

Thanks!

This is the ERROR message I recieve:

The expression After Update you entered as the event property setting produced the following error: A Problem occurred whil Microsoft Access was communicating with the OLE server or ActiveX control

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].

*There may have been an error evaluating the function, event, or macro.

Answer 1

An Option button or group is mutually exclusive - you only pick one. That is standard user interface design. Why don't you use multiple CheckBoxes instead?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

I tried using check boxes as well. Same issue with the macro error, unless I am just referencing the checkbox incorrectly in the code. It doesn't matter which I use. I just need something a user can click and indicate a selection has been made, but also need that to trigger an event to fill in a text box with a value or a blank (based on whethere the checkbox has been selected or not)

Answer 3

The reason I reference the option group in here is because most of the answers I have found concerning converting option buttons have been sourced around option groups and not singular buttons. 

Answer 4

I tried using check boxes as well. Same issue with the macro error, unless I am just referencing the checkbox incorrectly in the code. It doesn't matter which I use. I just need something a user can click and indicate a selection has been made, but also need that to trigger an event to fill in a text box with a value or a blank (based on whethere the checkbox has been selected or not)

Is there a reason that you cannot just use the Click Event?

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    'do whatever
End If

End Sub

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 5

Did you add the option buttons to the form directly from the Controls set in the Design tab of the ribbon?  If so, and you didn't add some sort of ActiveX control instead of the standard Access option button, then I think the problem isn't actually with the option button.

This specific error has been known to occur due to some corruption in the database, and not necessarily anything your code is doing.  However, I notice something odd in the code you posted.  You appear to have named a control named "2Txt".  Is that really the name of the control?  Technically, that's a legal name, but because it begins with a numeric digit, you *ought* to be getting a syntax error on any statement that refers to it like this:

    Forms!QueryForm.2Txt

Instead, I would expect you to have to refer to it like this:

    Forms!QueryForm.Controls("2Txt")

I don't know why your code even compiles; it doesn't for me.  But in case this is the problem, try either changing the name of the text box to "Opt2Text" and modify the code to use that name, or else change your code to refer to it using the quoted syntax I showed above.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 6

I must not be getting my syntax correct. 

Private Sub CheckAB_Click()

If QueryForm!CheckAB.Value = True Then
QueryForm!ctlABTxt.Value = "A"
Else
QueryForm!ctlABTxt.Value = "B"
End If
End Sub

Tried running it in a module, and getting an Object Required error on the first line. 

Answer 7

Did you add the option buttons to the form directly from the Controls set in the Design tab of the ribbon?  If so, and you didn't add some sort of ActiveX control instead of the standard Access option button, then I think the problem isn't actually with the option button.

This specific error has been known to occur due to some corruption in the database, and not necessarily anything your code is doing.  However, I notice something odd in the code you posted.  You appear to have named a control named "2Txt".  Is that really the name of the control?  Technically, that's a legal name, but because it begins with a numeric digit, you *ought* to be getting a syntax error on any statement that refers to it like this:

    Forms!QueryForm.2Txt

Instead, I would expect you to have to refer to it like this:

    Forms!QueryForm.Controls("2Txt")

I don't know why your code even compiles; it doesn't for me.  But in case this is the problem, try either changing the name of the text box to "Opt2Text" and modify the code to use that name, or else change your code to refer to it using the quoted syntax I showed above.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

I added the option button directly from the design group. 2Txt is not the actual name of the box, just the way I posted it on here. It is made up completely of alpha characters. I will try referencing that way though instead and see if it makes a difference. 

Answer 8

I have a form, and I need option buttons in the form, not an option group but option buttons because I need the user to be able to pick more than one.

Do the multiple selections represent different values of a single attribute, i.e. is the intention to return rows where the value in a column is any one of those selected in the form?  If so, a multi-select list box can be used.  You'll find an example in DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, in the section on retrieving data from the database a multi-select list box is used to return rows for one or more employees.  There are various ways of doing this, but in this example a value list of the selected items is written to a hidden text box control in the form.  This control is then referenced as a parameter by a query which makes use of the InParam and GetToken functions published by Microsoft.  Other methods can be found in the MultiSelect demo in the same OneDrive folder.

The great merit of a multi-select list box in this sort of context is that it is data driven, unlike an option group or similar, where the selections are limited to those available in the form, whose definition would consequently require amendment in the event of the possible values changing. 

---

Ken Sheridan, Stafford, England

Answer 9

Not able to access the link. May be a firewall thing. The purpose is for multiple variables in my the SQL code I am using to query. So for example. 

I have a column that states whether an employee is Seasonal or Permanent. 

I need the user to be able to select to pull only Seasonal only Permanent or both. 

The way I am working to do this is by adding or's into the where clause of my SQL.

Where (Form Part Seasonal_Temp = TxtBox1 OR Form Part Seasonal_Temp = TextBox2)

I figured this was the easiest way to do it, as it would not pull something that had a blank value, but would pull for the rows that matched one or more conditions in the or clause. 

What you wrote seems to be the solution I am looking for. Will try and access the link another way. 

Answer 10

Will try and access the link another way. 

If you are unable to do so mail me at:

    kenwsheridan<at>yahoo<dot>co<dot>uk

and I'll send you the files.

---

Ken Sheridan, Stafford, England

Answer 11

I must not be getting my syntax correct. 

Private Sub CheckAB_Click()

If QueryForm!CheckAB.Value = True Then
QueryForm!ctlABTxt.Value = "A"
Else
QueryForm!ctlABTxt.Value = "B"
End If
End Sub

Tried running it in a module, and getting an Object Required error on the first line. 

You don't need to fully qualify the control name in your example. This should be fine:

Private Sub CheckAB_Click()

 If CheckAB.Value = True Then
     ctlABTxt.Value = "A"
 Else
     ctlABTxt.Value = "B"
 End If

End Sub 

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 12

2Txt is not the actual name of the box, just the way I posted it on here. It is made up completely of alpha characters.

It's never helpful to post code that isn't the actual code being invoked.  But anyway, we can now say that the name of the control isn't the issue here.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 13

I must not be getting my syntax correct. 

Private Sub CheckAB_Click()

If QueryForm!CheckAB.Value = True Then
QueryForm!ctlABTxt.Value = "A"
Else
QueryForm!ctlABTxt.Value = "B"
End If
End Sub

Tried running it in a module, and getting an Object Required error on the first line. 

That would be incorrect because you can't refer to a form without some reference path that leads to it.  If "QueryForm" is the name of the form where this code is running, then you can just use "Me" -- a built-in reference to the object where the code is running, like this:

    Me!ctlABTxt.Value = "A"

or you can let that be implied (so long as the control name doesn't conflict with any other name that is currently in scope:

    ctlABTxt.Value = "A"

(though I much prefer using the "Me" qualification to ensure there can never be any confusion).

If the code is not running in the module of the form you want to refer to, then you need to go through the Forms collection:

    Forms!QueryForm!ctlABTxt.Value = "A"

or

    Forms("QueryForm")!ctlABTxt.Value = "A"

or several even more verbose reference structures.

Incidentally, I don't think it makes any any difference whether you use the Click event rather than the AfterUpdate event in this case.  For a check box or an option button, they will both fire when the control's value is changed by the user.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 14

If it's not a problem with the code syntax, then as I mentioned before it could be that there's some corruption in your database's VB project.  You might try decompiling your database, compacting it, then recompiling it.  If you don't know how to decompile a database, see this link:

    http://datagnostics.com/dtips/decompile.html
    How to Decompile a Database

And by all means, make a backup copy first.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 15

Hi JB_2014,

I try to test the code on my side.

I suggest you to access the control using "ME" like below.

Private Sub Option0_AfterUpdate()
Me.Text6.Value = ""
If Me.Option0.Value = True Then
 Me.Text2.Value = "2"
 Else
 Me.Text2.Value = "4"
 End If
 Dim str As String
 str = "select * from table_name Where (Form Part Seasonal_Temp =" & Me.Text2.Value & ")"
 Me.Text6.Value = str
End Sub

I know that you are first assigning the value to textbox and then pass that value to query.

but if you check the code then you will find that you are passing the static value to textbox and passing that textbox in the query.

overall value will remain same and query also will remain same. it does not make any effect whether you select the option button or not.

so to see the effect in the output. I slightly modify the code posted by you.

now you can see the effect in Output:

further you can try to modify the example as per your requirement to get desired output.

Regards

Deepak 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 16

Thank you all so much for the help. I ended up using a Multiple Selection list as recommended. I am wanting to try the other forms of code for future projects though. I appreciate all of your input!

Answer 17

I try to test the code on my side.

I suggest you to access the control using "ME" like below.

Private Sub Option0_AfterUpdate()
Me.Text6.Value = ""
If Me.Option0.Value = True Then
 Me.Text2.Value = "2"
 Else
 Me.Text2.Value = "4"
 End If
 Dim str As String
 str = "select * from table_name Where (Form Part Seasonal_Temp =" & Me.Text2.Value & ")"
 Me.Text6.Value = str
End Sub

I know that you are first assigning the value to textbox and then pass that value to query.

but if you check the code then you will find that you are passing the static value to textbox and passing that textbox in the query.

overall value will remain same and query also will remain same. it does not make any effect whether you select the option button or not.

so to see the effect in the output. I slightly modify the code posted by you.

I did try this methodology and had it work succesfully. Side not on that, there was a comment about corruption earlier in the thread. I started a fresh database and no longer had the OLE active X error I was getting before. Not sure what I did to the first database to cause that, but not having that issue in the second Database