Hide or display a field on a form depending upon what is in a field 2

[TommyTea]

I have a form with a query as the recordsource. The query brings up about 20 questions as records and allows the user to input the answers. Some answers are numeric and I have a numeric field in the underlying table that is displayed on the form as check boxes in a frame. Some questions have textual answers and I have a memo field, displayed as a text box, to capture them. Each question can have only one type of answer so I wish to hide the checkboxes and frame if the answer is textual or to hide the text box if the answer is numeric. I am trying to use the following code:

Private Sub Form_Load()
On Error GoTo PROC_ERR

If [Answer_type] = "T" Then
Me.frameNumber.Visible = False
Me.txtComment.Visible = True
ElseIf [Answer_type] = "N" Then
Me.txtComment.Visible = False
Me.frameNumber.Visible = True
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT

End Sub

If the Answer_type field in the query is "T" only the text box should show and, conversly, if the Answer_type field in the record is "N", I want only the frame control (with checkboxes) to show.

I seem to get all or nothing (all frame controls or all text boxes). Each form will always have some numeric and some textual questions. Can anyone help? Thanks

 

[jiqjaq]

You need to move the code from the Form_Load to the Form_Current section. Since the Load event only happens once, whatever the [Answer_Type]is at the time will be use - for all records in the query. The Current event captures each record as soon as that record becomes the current record.

 

[WayneRyan]

Tommy,

If your form is a continuous form, or datasheet view, you
will find that the visual appearance that you want requires
a lot more work. Even when you use the OnCurrent event, it
will be visually mapped to all the records displayed.

If it is continuous, repost and I'll try to find you some
links to help.

Wayne

 

[TommyTea]

Wayne

I am using a continuous form. I will greatly appreciate any ideas. Thanks

 

[TommyTea]

jiqjaq

I tried your suggestion and no luck. I think that your analysis of the problem is correct. Please let me know if you think of anything else.

 

[WayneRyan]

Tommy,

This is for colors, but it may help:

http://www.mvps.org/access/forms/frm0024.htm

Wayne

 

[TommyTea]

WayneRyan

This is a great piece of code. But, I think that it is using the row number to alternately set the colors. I am going to go through the code tonight just in case. I am afraid that I am going to have to do something humiliating like having a different form for each answer type - Yechhhh! I really appreciate the help. You guys are great to have around.