Close Form conditional on content in subform

[Hulm1]

I am looking to prevent a form closing and pop up an error message if one or more fields in a related form isnull.

I have a Main Form called "Job". In this main form is a subform called "JobTask" which is a continuous form. It contains a look-up box listing all the possible Tasks I could have for this job. Let's say I choose two (Task 1 and Task 2).

Within the subform the next field for each Task record is a check box (Choose a subtask). This checkbox opens another form "JobSubTask" which allows me to choose which SubTasks apply to the selected task (they can be different for each JOB). (For reference the TaskID is passed to the Form "JobSubTask" and so linking the Subtask to the Task)

At present it is possible to close the form "Job" without actually selecting the SubTasks for each Task. I wish to stop that.

I have been looking at code like that shown below:


[if nz(SubTaskID,"")="" Then go to missing]

However, I do not know, how to do this when the fields are on anotehr form.

Of course what I really need is to ensure that for EVERY Task selected, there is at least one SubTask selected.

Thanks

[marcf]

Assuming I get what your asking for then its simple, not that I think your stupid but dont include the <> around subform ;)

if Me!<subform>!fieldname.value = "" then ....



As a tip I tend to disable the big red X in access and make users close forms via a big close button, that way you can validate all you like without having to muck around with events.

Cheers


Marc

[Hulm1]

Thanks for the reply. I tried the following in the Before Update and also the onclick event of the close button. No joy.

[Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me!JobTask_Edit_JobSubTask_List!SubTaskID.Value = "" Then GoTo missing
missing:
Call MsgBox("sorry: Not all fields completed.")
Cancel = vbCancel 'prevents the form closing, because it aborts the record save

End Sub]

Could it be because it is not recognising the link between the Form JobTask_Edit_JobSubTask_List and the Main Form "Job_Edit"?

(Sorry my form names are slightly different to that which I showed earlier). Being a little long winded I did not want to confuse!

I wanted to attach a screenshot in case it helps you, but I can't find that possibility

Thanks again

[marcf]

Sorry if it seems a bit scrappy but its probably the simplest way I can think of. By the way when your talking about a SUBFORM you mean a form within your form, or do you mean a separate form opened from your main one?

If Me!JobTask_Edit_JobSubTask_List!SubTaskID.Value = "" Then GoTo missing
missing:

Should be:

If Me!JobTask_Edit_JobSubTask_List.SubTaskID.Value = "" Then GoTo missing
missing:

Ahh hold on, are you using a combo box for the SubtaskID? As I recall combobox.value doesnt return the value your after, you have to refere to the column?

You could try:

If Me!JobTask_Edit_JobSubTask_List!SubTaskID.Column(1 ) = "" Then GoTo missing

The combobox.column(number) depends on how you set them up...

Infact as a test i'd create a button on your main form where the onclick method sets the label to the value of the combobox on the subform to test your referencing it right. Then simply keep changing the value on the subform and clicking the button on the main one to test it out...

[Hulm1]

I think I am out of my depth here!

Just to confirm the correct form structure

1) Main Form "Job_Edit"
2) Subform "Job_Edit_JobTask_List (contains a look-up for all the tasks in the table zmtTasks). It is a continuous form. Each record also has a check box which has an onclick event. This onclick event as follows:


Private Sub chkEdit_Click()

Dim sWhere$

Me.chkEdit = False
DoCmd.RunCommand acCmdSaveRecord

Me.RecordsetClone.Bookmark = Me.Bookmark
sWhere$ = "JobTaskID = " & Me.RecordsetClone("JobTaskID")
DoCmd.OpenForm "JobTask_Edit", , , sWhere$, , acDialog, Me.Parent.Name

End Sub

This opens the following form

3) JobTask_Edit. This has a subform
4) subform JobTask_Edit_JobSubTask_List. This is similar to form 2 (above) but looks up a list of subtasks from the table zmtSubTask.

And so to refresh the question. Every Task in form 2, should have a least one subtask connected to it in form 4. If not, I want it to be impossible to close Form 1 and for a message to pop up and say why!

I hope you understand!!

I should be clear that the above database was developed by a another. I am learning by leaps and bounds but I could not have developed the above layout. Hence if you still feel able to help, I probably need a bit more clear code!

Thanks again

[marcf]

Ok I think I get ya!

Easy fix:

1) Set the window(Jobtaskedit) to Modal, this means it cant be unfocused until its been closed.

2) Disable the red 'X' at the top of that window(jobtaskedit)

That way the user has to exit the form by the close button and you can do all validation knowing that its not being closed by any other windows!

Sorry for the delay in replying for some reason my topic notification didnt stick!

[Hulm1]

Sorry I think I need to bail out here. I am sure your advice is good but I just don't know how to implement it!

I can set the form to modal, but I don't think I have the correct code for doing what I want.

I guess I really need a blow by blow instruction set with code. I do not expect that so don't worry.

Thanks anyway