Group option boxes 1

[nycbigapple]

Hello,
I have an issue with an option group. I have 3 options in the box that has a combobox seelct a list to whichever option is selected. On value #3 instead of it using the combobox, I am trying to let the user input "details" rather than have it selected from a combo. I have an if statement it works. However, when I close the db and reopen it, if option 3 was selected for record 1, it doesn't show that it was selected. Options1 and 2 do show it was selected. When I check the corresponding field in the table it shows that it has a value of 3 in there.

How can I have it show that option3 was is selected. Thanks

Private Sub Form_Current()
If grpCodeType.Value = 3 Then
cboAssessName.Visible = False
txtProcedure.Visible = True
Else
cboAssessName.Visible = True
txtProcedure.Visible = False
'txtProcedure.Value = Null
End If

On Error Resume Next
Dim strCodeType As String
If IsNull(cboAssessName.Value) Then
grpCodeType.Value = Null
End If
' Synchronise CodeType combo with existing AssessmentName
strCodeType = DLookup("[CodeType]", "tblAssess", "[AssessName]='" & cboAssName.Value & "'")
Select Case strCodeType
Case "Equipment"
grpCodeType.Value = 1
Case "Product"
grpCodeType.Value = 2
Case "Detail"
grpCodeType.Value = 3
End Select
' Synchronise AssessmentName combo with existing AssessmentName
cboAssessName.RowSource = "Select tblAssess.AssesName " & _
"FROM tblAssess " & _
"WHERE tblAssess.CodeType = '" & strCodeType & "' " & _
"ORDER BY tblAsses.AssessName;"








Private Sub grpCodeType_AfterUpdate()
cboAssessName.Value = Null

On Error Resume Next
Dim strCodeType As String

If grpCodeType.Value = 3 Then
cboAssessName.Visible = False
txtProcedure.Visible = True
Else
cboAssessName.Visible = True
txtProcedure.Visible = False
txtProcedure.Value = Null
End If

Select Case grpCodeType.Value
Case 1
strCodeType = "Equipment"
Case 2
strCodeType = "Product"
Case 3
' strCodeType = "Detail"
End Select
cboAssName.RowSource = "Select tblAssess.AssessName " & _
"FROM tblAssess " & _
"WHERE tblAssess.CodeType = '" & strCodeType & "' " & _
"ORDER BY tblAssess.AssName;"

End Sub

 

[Remou]

I am a little puzzled. Is this a typing error:
cboAssName.Value
You have previously referred to:
cboAssessName.Value

I suspect that:
strCodeType = DLookup("[CodeType]", "tblAssess", "[AssessName]='" & cboAssName.Value & "'")
Is not returning a value. Also, why not bind the group to the relevant field?

 

[nycbigapple]

Yes, that is a tpyping error.

the group is bound to a relevant field and when I check the table, the value of 3 is in there but not showing up on the form.

 

[Remou]

If the group is bound to the field, why do you need this (?):

' Synchronise CodeType combo with existing AssessmentName
strCodeType = DLookup("[CodeType]", "tblAssess", "[AssessName]='" & cboAssName.Value & "'")
Select Case strCodeType
Case "Equipment"
grpCodeType.Value = 1
Case "Product"
grpCodeType.Value = 2
Case "Detail"
grpCodeType.Value = 3
End Select

Have you tried commenting out On Error Resume Next, which seems quite an unsafe line to use?

 

[nycbigapple]

Initially, I was structuring it so that it behaved like a cascading combo box, however, for value (3), rather than have it behave that way, I just need the user to input what is needed rather than a combo box listing.

 

[Remou]

I mean in the On Current event. I think if you put in a messagebox to show what is being returned by:
strCodeType = DLookup("[CodeType]", "tblAssess", "[AssessName]='" & cboAssName.Value & "'")
You will find that the problem is there.

 

[nycbigapple]

I tried what you suggested and I am getting a null value when it is value 3, when I select the other 2 it displays correctly

 

[Remou]

This is because, as far as I can tell, you do not have an AssetName, when it is three:
[tt]If grpCodeType.Value = 3 Then
cboAssessName.Visible = False
txtProcedure.Visible = True[/tt]
I suggest you comment out all the On Current code and see what happens. If the Option Group is bound, as you say, you should get it working out right.

 

[nycbigapple]

I took out all of the On current and it is still doing the same thing where, on record 2, if I select option 3 and go to record 3 and back it is fine; however, if I select option 2 on record 3 and go back to record 2, it isn't there.

I am thinking that maybe it is this
strCodeType = DLookup("[CodeType]", "tblAssess", "[AssessName]='" & cboAssessName.Value & "'")

options 1 and 2 reference cboAssessName
option 3 rferences txtProcedure, which is not in tblAssess.

Is there a work around this?

 

[Remou]

I think you need to look at the way the form is set up. The Option Group is bound to a field in the table, which is bound to your form, therefore the Option should show up correctly without any intervention. I think you have allowed the form to get a little complicated when you were switching from the combobox. Perhaps you could create a scratch form, based on the relevant table, with an option group bound to the appropriate field, to test this idea.
It seems to me that all you really need is an option group that looks like this:

[tt] Select Option
_O_ Equipment
_O_ Product
_O_ Detail[/tt]

Where Equipment, Product and Detail are labels that return values of 1,2 and 3 to your table.

 

[nycbigapple]

Yes, I have done that. However, each option has it's own subcategory except for Detail, which is entered in manually.

If I were to select Equipment, in the cbo it would give me a listing of all the relevant equipments.

Product - list of products

Detail - just a procedure that is explained

 

[nycbigapple]

Thank you Remou, it worked. I just added an if statement on the on current and it worked. I am not getting any errors or nulls in the fields, even if I switch back-forth and make changes.