Value of option group

[CEH]

This should be an easy one.... I had an existing form. Needed a second form. But the two were so close I just added and option group to make certian fields visible when that option was picked. Now.... The problem is sorting or doing a query by the value of the option group. What I am thinking is I need to store the value of the option to a field. ie My option group is named "PickWO" the value is "1" or "2", so I added a field to my WO table... so how do I store the value of "1" or "2" to the new field? Hope this makes sense!

 

[ShaneMan]

One thought is, in your After Update Event of the option group, you could put.

If Me.OptGroupName = 1 Then
Me.FieldName = "1"
Else
Me.FieldName = "2"
End If

HTH,
Shane

 

[mresann]

or even...

Me.Text1.Value = Me.OptGroupName.Value

You don't have to add the .Value, but it is always wise to explicitely show properties rather than use the imiplicit default.

By the way, the code should go in the OptGroupName_Change event.

 

[Uncle Gizmo]

I had an existing form. Needed a second form. But the two were so close I just added an option group to make certian fields visible when that option was picked

This is a very good strategy for your database design as it makes maintenance so much easier. Imagine a situation where you have an address table, it is quite common to have three forms, one to enter details, one to view details, and another to edit details. If it becomes necessary to add extra fields, then you find yourself adding the extra fields to three forms.

There was a very good article from "Smart Access" that explain how to utilize one form only, and reap the benefits of easier maintenance.

I used the ideas in a particular project I was working on at the time, and there is a downloadable copy of it here on my website.
Item 7: One Form Example

 

[CEH]

Looks fine....but

One little problem... and it may be the solution. I already have an "AfterUpdate" event.... DB doesnt seem to like 2 of them. And the option group doesnt have a "changeEvent"
My AfterUpdate is as follows:

Private Sub PickWO_AfterUpdate()

Call NotVisible

Select Case Me.PickWO

Case Is = 1
Me.ReInspectionDate.Visible = True
Me.Price.Visible = False
Me.WOPreview.Visible = True
Me.PrtWO.Visible = True
Me.WBMInvoice_.Visible = False


Case Is = 2
Me.ReInspectionDate.Visible = False
Me.Price.Visible = True
Me.cmdPreTag.Visible = True
Me.cmdPrtTag.Visible = True
Me.WBMInvoice_.Visible = True

End Select

End Sub

Now if I could just add the new value it seems to cause problems not saving correctly ...Like under Case 1 "Me.PickWOvalue = 1" and Case 2 "Me.PickWOvalue = 2"
It doesnt seem to save after moving from record to record.
And I had to add a non visible field to the form to use the "Me."
Can I just write directly to the table from this code? Instead of "me.PickWOvalue = 1" to something like tblWorkOrder.PickWOvalue = 1" <this didnt seem to want to work.
Sorry to be lengthy..... But I'm still new to VBA

Thanks to all!!!

 

[CEH]

To add to that....

Also... Now it is storing the value in the new field, but if you select to view records, as you go thru them the option button does not change nor do the fields become visible until you change the option button yourself.
So right now its storing the value in the new field, but thats all.

 

[ShaneMan]

Hey CEH,

I think I'm following what your wanting to do. If you want the value of the option group to match the value of the [NewText] you've recently added, then you would need to write another Select Case statement at the forms On Open event telling the OptionGroups.Value to equal what's in the [TextField]. Then as you go from record to record the option group knows what value to have.

Hope this helps,
Shane

 

[CEH]

Doesnt work

Follow that...... but........ Put this code in

Private Sub Form_Open(Cancel As Integer)
Select Case Me.PickWOvalue.Value
Case Is = 1
Me.PickWO.Value = 1

Case Is = 2
Me.PickWO.Value = 2

End Select

End Sub

Doesnt do it...............

Also tried .......

Me.PickWOvalue.Value = Me.PickWO.value

Doesnt work either

Also tried putting the Field "PickWOvalue" as the control source in properties of the option group.........
Doesnt work either..................

 

[CEH]

OK....... got it. I hope

Was looking thru some post..... Tried putting
Me.PickWOvalue.Value = Me.PickWO.value in the "BeforeUpdate" of the form..... and using the field as the control source for the option group....... seems to work. Changes as records are displayed. If this is not the correct way to do this someone let me know.

Thanks to all

 

[CEH]

Spoke too soon..........

Well, the buttons on my option group change now........ But with the addition mentioned above... my text visibility - on- off, no longer work when looking thru records......... frustrating