Me.GoingCrazyOverForm.Visible = True

[Ray Stantz]

Good Day! I have a form setup with mutiple fields:

Deliverables 1-35 (text fields)
Results 1-35 (Combo Boxes)
Comments 1-35 (Memo)

All fields are necessary for this form but depending on the circumstances, we may only use a few as oppose to all 35 for any given record. So what i've done is set delveribales 1-3, Results 1-3 and Comment 1-3 to always display and i've set Deliverables 4-35, Results 4-35 and Comments 4-35 to only display if the criteria is satisfied in the VBA code. For example i have Fields 4-6 to display only if Deliverables #3 contains a value.

If IsNull(Deliverable3) Or Deliverable3 = "" Then
Me.Deliverable4.Visible = False
Me.Results4.Visible = False
Me.Deliverable5.Visible = False
Me.Results5.Visible = False
Me.Deliverable6.Visible = False
Me.Results6.Visible = False
Else
Me.Deliverable4.Visible = True
Me.Results4.Visible = True
Me.Deliverable5.Visible = True
Me.Results5.Visible = True
Me.Deliverable6.Visible = True
Me.Results6.Visible = True
End If

It works in the AfterUpdate procedure of Deliverable3 while the form still has focus; however, i placed the same code in the OnLoad and OnCurrent events of the form and these same fields disappear and never display again if i move away from the record or close the form. Any idea of how i can get these fields to display when i re-enter the record?

I've already tried changing the Me.Deliverable4.Visible = True

to

Me![Deliverable4]!Visible = True

and i also tried

Me![Deliverable4].Visible = True

and none have worked.

Any assistance is greatly appreciated.

 

[Ray Stantz]

Also, it was working before i split the database and changed the control source from the table to a simple query that resides on the FE.

 

[WayneRyan]

Ray,

You can't use the AfterUpdate event for something like that.
The OnCurrent event should do fine though.

Even better would be to toss out the code and look into using
Conditional Formatting.

Even better would be to normalize your table and not have the
repeating fields. You will expend far more effort on your
forms/reports with "spreadsheet type" tables.

Use the Search Facility here and look for "normalize" and/or
"normalise".

hth
Wayne

 

[The_Doc_Man]

As a hint of the direction Wayne is suggesting:

If you make your Deliverables / Results / Comments a separate table with two more fields - the (foreign) key to the record for which these items relate and an ordinal number field showing which record you have for the current record.

Make this table go through a columnar type sub-form in continuous mode - sub-form being a control you could put on the form for the master record. That subform bears a parent/child relationship with the data of master record. You will be able to add records to the CHILD form as needed, exactly as many and no more. You wont' be limited to 35 entries and you won't waste space in your database. Access can be made to automagically handle everything but the child table's sequence number, and you do that through the form in a (relatively) simple event routine.