Solved Display text field on Main Form after text entered on SubForm (1 Viewer)

Mick3911

Registered User.
Local time
Today, 14:06
Joined
Jul 31, 2018
Messages
40
I have a main form ‘frmUpdateTraining’ and a sub form ‘subfrmTrainingTimes’ which is a continuous form.

On the main form there are (amongst others) 2 fields ‘DateCompleted’ and ‘TrainedBy’

On the sub form there a 3 text fields ‘TrainingDate’, ‘StartTime’ and ‘EndTime’.

When a user enters a time in the ‘StartTime’ text field, I would like the text fields ‘DateCompleted’ and ‘TrainedBy’ on the main form to either become visible or enabled.

The code that I have used is in the BeforeUpdateEvent is

Code:
If IsNull(Me.StartTime) Then
Me.([frmUpdateTraining]![DateCompleted]).Visible = False
Me.([frmUpdateTraining]![TrainedBy]) = False
Else
Me.([frmUpdateTraining]![DateCompleted]).Visible = False
Me.([frmUpdateTraining]![TrainedBy]) = False
End If

I am struggling with my coding to achieve this; can someone please help.
 

bastanu

AWF VIP
Local time
Today, 07:06
Joined
Apr 13, 2010
Messages
1,401
You need to reference the subform's parent:
Code:
If IsNull(Me.StartTime) Then
    Me.Parent![DateCompleted].Visible = False
    Me.Parent[TrainedBy] = False
Else
    Me.Parent![DateCompleted].Visible = False
    Me.Parent![TrainedBy] = False
End If
And I would advide you to move the code to the AfterUpdate event of the StartTime textbox.

Cheers,
Vlad
 

bastanu

AWF VIP
Local time
Today, 07:06
Joined
Apr 13, 2010
Messages
1,401
😊 I just copied what the OP had, I should have looked closer....
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Feb 19, 2002
Messages
42,970
Mainform = one record
subform = many records

Exactly which subform record do you want to update the main form? The logic doesn't work. If there are 6 classes, which TrainedBy refer to. There is something wrong with your schema or your logic or perhaps both. If you post the schema, we can help you sort it out.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:06
Joined
May 7, 2009
Messages
19,169
this time i will agree with post#5.

if the subform is holding multiple "training modules (course)", then
the main form should GoTo correct record related (master?) record
before the two fields should be shown/hidden.
 

Mick3911

Registered User.
Local time
Today, 14:06
Joined
Jul 31, 2018
Messages
40
The training times relate to one training item/course. So an employee could receive just one training session say on the 1st or could receive multiple training sessions say on the 3rd, 10th, 11th and 21st.
 

Mick3911

Registered User.
Local time
Today, 14:06
Joined
Jul 31, 2018
Messages
40
My code should have read

If IsNull(Me.StartTime) Then
Me.([frmUpdateTraining]![DateCompleted]).Visible = False
Me.([frmUpdateTraining]![TrainedBy]).Visible = False
Else
Me.([frmUpdateTraining]![DateCompleted]).Visible = True
Me.([frmUpdateTraining]![TrainedBy]).Visible = True
End If
 
Last edited:

bastanu

AWF VIP
Local time
Today, 07:06
Joined
Apr 13, 2010
Messages
1,401
So can you try what I suggested... :)?
Cheers,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:06
Joined
May 7, 2009
Messages
19,169
The training times relate to one training item/course. So an employee could receive just one training session say on the 1st or could receive multiple training sessions say on the 3rd, 10th, 11th and 21st.
then you should put the TrainedBy and DateCompleted on same table (subform).
 

Mick3911

Registered User.
Local time
Today, 14:06
Joined
Jul 31, 2018
Messages
40
But training won't be completed until the trainer is satisfied that the trainee have grasped the training which could take just one training session or more.
 

Mick3911

Registered User.
Local time
Today, 14:06
Joined
Jul 31, 2018
Messages
40
When I have completed the StartTime textbox I get a Run-time error '438', Object doesn't support this property or method.

Is this because the subform is a continuous form?
 

bastanu

AWF VIP
Local time
Today, 07:06
Joined
Apr 13, 2010
Messages
1,401
Could you upload a small sample with just the two forms and the table with some dummy data to show the issue?

Based on your comments in post # 8 and 12 I think you are best to move the code to the Current event of the main form and use a Dmax on the training date from the subforms record source. Or you could create a totals query grouped by the trainee ID with Max on the TrainingDate and having Not Is Null in the Starting time then use a dCount > 0 to make the two textboxes visible (and use dlookup in that totals query as the control source for the date completed).

Regarding your error maybe try this:
Code:
If IsNull(Me.StartTime) Then
    Me.Parent.Form.Controls("DateCompleted").Visible = False
    Me.Parent.Form.Controls("TrainedBy").Visible = False
Else
    Me.Parent.Form.Controls("DateCompleted").Visible = True
    Me.Parent.Form.Controls("TrainedBy").Visible = True
End If
Cheers,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Feb 19, 2002
Messages
42,970
Why are you using the subform to control what is visible on the main form? The start time for the set of courses can be derived by finding the Min() start time of the training sessions and the end time can be derived by finding the max() complete time of the training sessions. Neither of these fields need to be stored in the parent form.

When you use the subform to control what is visible on the main form, that could change as you move through the records of the subform which is why this doesn't make sense.

Can you give us an explanation (not technical) of what you are trying to accomplish by toggling the visibility of these main form fields?

If you want to control modifications to the subform so that once the trainer marks the training as complete, no more modifications can be made to the subform, that can be don easily. In the Dirty Event of the subform add:
Code:
If Me.Parent.txtTrainerID & "" <> "" Then
    Msgbox "Training is complete, Changes cannot be made.", vbOKOnly
    Me.Undo
    Exit Sub
End If
 

Mick3911

Registered User.
Local time
Today, 14:06
Joined
Jul 31, 2018
Messages
40
I have got this to work how I want by setting the visible properties for the ‘DateCompleted’ and ‘TrainedBy’ textboxes on the main form to No and then used this code in ‘StartTime’ After Update property on the subform


Code:
Private Sub StartTime_AfterUpdate()
If Not IsNull([StartTime]) Then
Me.Parent.[Date_Taken].Visible = True
Me.Parent.[Trained_By].Visible = True
End If
End Sub

Gents, I would like to thank you for your time and effort in providing your help and suggestions, much appreciated. (I will no doubt return when I am close to throwing the PC out of the window when I come across another problem :)).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:06
Joined
May 7, 2009
Messages
19,169
‘StartTime’ After Update property on the subform
what if i go back to the record and blank the starttime again, still the 2 fields are showing?
 

Mick3911

Registered User.
Local time
Today, 14:06
Joined
Jul 31, 2018
Messages
40
Yes, but you can only close the form by a command button which checks if relevant fields are completed.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Feb 19, 2002
Messages
42,970
Having code in your command button isn't doing you any good since the data is stored in the child table and is already saved. So your validation is akin to locking the barn door after the horse has escaped.
 

Users who are viewing this thread

Top Bottom