Close a form only if a field in another form is NOT null

[Hulm1]

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 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. I say that my abilities are not overestimated for any replies. I need a fairly clear blow by blow explanation how to do that complete with code examples if that is not too much to ask.


Thanks

 

[maxhugen]

A form has an event called Unload, which can be cancelled. So you could check to ensure that the subform has at least one record, otherwise you cancel the Unload. eg

Private Sub Form_Unload(Cancel As Integer)
    If me!JobTask_Edit_JobSubTask_List.Form.RecordsetClone.RecordCount = 0 then
        msgbox "Please enter a subtask first"
        Cancel=True
    End If
End Sub

Max Hugen
Australia

 

[Hulm1]

maxhugen.

This looks very very good thank you. With one exception, however. This assumes that the user has opened the form JobTask_Edit_JobSubTask_List. At present he has to click a check box (with the label "Choose Subtasks"). At the moment, there is nothing to stop him from closing the form without clicking that button for any of the tasks.

Perhaps you can advise if there is a way to make it such that selecting a Task AUTOMATICALLY opens that form OR find some other means to check that all the JobTasks have Subtasks??

 

[maxhugen]

OK, think I'm getting the drift....

Presumably, form 1 "Edit Job" opens one Job. It has a subform that shows the existing Tasks for that Job, and you can also edit or add new Tasks there.

The issue is that when adding a new Task, the user must enter at least one SubTask.

I would have designed this differently, but I understand you've inherited this, so... What you could do is add code to the task subform's AfterInsert event to open form 3 "Edit SubTasks":

Private Sub Form_AfterInsert()
    DoCmd.OpenForm "JobTask_Edit", , , "JobTaskID=" & JobTaskID, , acDialog, Me.Parent.Name
End Sub

With the Tasks form now open, and the previous code in the Unload event, the user will have to enter a subTask. How does that sound?

Max Hugen
Australia

 

[Hulm1]

This is most helpful. I feel like we are almost there.

However!
On your after insert code I am getting "Ambiguous name detected. Form After insert"

 

[Hulm1]

Belay that. There an extra After Insert event which was empty for some reason.

However, When I choose a task the form JobTask_Edit does not open.

Heres the surprise though. When I click my Add new record button (I thought I would try with a fresh copy) JobTask_Edit OPENS!!!

EH!?

 

[maxhugen]

The AfterInsert event is triggered only after you Insert a new record. The idea was that a user is forced to add a SubTask immediately after adding a new Task.

Max Hugen
Australia

 

[Hulm1]

I have been trying so many things, I had a few loose ends. I have picked them all out. Your code works fine. It's operability is slightly odd (sorry!). The reason I say that is that if I choose a record, the next obvious thing to do is hit the look-up button on the next row down for the next task. This triggers your form opening event (works great!). I choose the subtask(s) and close it, The view is then of an open look-up box which is slightly messy. How picky can I be?

Also: Here's another query: Tell me where to go if you have done enough for me

I currently have a check box for the user to tick which confirms he has done all his subforms (as much a mental trigger as anything else.

Wouldn't it be nice if I had a little bit of code and a text box next to the look-up box which showed EITHER a green YES for Subtask selection done Or Red NO if not, OR how many subtasks had been selected for that Task? possible?

Your help is enormously appreciated.

 

[maxhugen]

The view is then of an open look-up box which is slightly messy

Not sure what you mean here... are you talking about a ComboBox control?

Re your next question... all tasks should now have at least one subtask, no? Or is it that previously entered Tasks don't have SubTasks?

You can do the red/green thingy if need be....

Max Hugen
Australia

 

[Hulm1]

What I mean is that when I choose a task, the natural thing to do is then choose the next one rather than choose the subtask (this has been part of the problem).

When you leave the first task selection, you naturally click the look-up box below for the next task. The look-up box opens it's great long list of tasks, however, of course now the SubTask selection form opens. This is great, it forces the user to choose the subtasks. Then, when he closes the subtask selection form he finds himself faced with the long list of tasks for the next task. I might be being rather pedantic here, it's just that it isn't what you expect. You would expect to see the form with only the first task selected and THEN go to create the next one. This is definitely NOT a biggy! It would just be nice if the Lookup box was not open under the Subtask selection form.

Re: Colours and quantity. It is of course possible that the Project Manager may decide NOT to choose a particular subtasks just now. This is therefore a positive decision and should be allowed. However, I would like it to be clear from the Job_Edit Form which shows the selected tasks that one ( or more) is still missing a subtask.

 

[maxhugen]

If it's permissible to NOT enter a SubTask, you'll need to change the Unload event's code, so the messagebox warns the user and provides a Yes/No option, which allows the option to close anyway. Are you Ok to do that?

For the visual indicator, try something like this:

1. Create a saved query "CountOfSubTasks" that counts the SubTasks by Task, eg

[tt]SELECT JobTask.JobTaskID, Count(JobSubTask.JobSubTaskID) AS CountOfSubTasks
FROM JobTask LEFT JOIN JobSubTask ON JobTask.JobTaskID = JobSubTask.JobTaskID
GROUP BY JobTask.JobTaskID;[/tt]

Nb: change table and field names appropriately. The outer join used in the query will ensure that you get a zero returned if there are no SubTasks.

2. Add this saved query to the recordsource query in form Job_Edit_JobTask_List, linking JobTask.JobTaskID to CountOfSubTasks.JobTaskID

Now you have the number of subtasks returned for each task.

3. Add two textboxes to the subform:

a. txtCountZero, ControlSource: =Iif([CountOfSubTasks]=0,0,""), ForeColor: Red, BackStyle: Transparent
b. txtCountNotZero, ControlSource: =Iif([CountOfSubTasks]<>0,[CountOfSubTasks],""), ForeColor: Green, BackStyle: Transparent

Now align these two textboxes one on top of the other.

As they have a transparent backgound, you'll see whichever one is displaying something - the red 'txtCountZero' if there are no subtasks, or the green 'txtCountNotZero' if there are subtasks.

Max Hugen
Australia

 

[Hulm1]

First question. Not sure I understand your question. At present, when JobTask_Edit (with Subform JobTask_edit_JobSubTask_List) I can close it WITHOUT selecting a Subtask. I assumed you planned this

Bit stuck on the query. I have created a query with tables JobTask and JobSubTask in it. I have linked by JobTaskID.

Not sure about the following: Are these expressions?

SELECT JobTask.JobTaskID, Count(JobSubTask.JobSubTaskID) AS CountOfSubTasks

GROUP BY JobTask.JobTaskID;

Sorry, you will have gathered that I am beginner trying to work with what is for me some pretty complex stuff. Customers and Purchase Order databases are a positive breeze by comparison to this! having said that, It is positively fascinating and a great buzz when something works!

I would be most grateful if you are able to see me through on this one. However! I must go to bed now as it is 1.00am now!

Thanks again

 

[maxhugen]

No probs, we'll get there.

With the query statement, first check the syntax to ensure that the table and field names are correct, as I don't know exactly what they should be.

Next, create the saved Query:

- Select Queries tab in the database window.
- Click <New>
- Select 'Design View> in the window that pops up.
- Next, the 'Show Table' dialog will popup - close this without selecting any tables.
- Now you have an empty 'query grid'. From the menu select View > SQL View.
- In the SQL View, paste the query statement in.
- Test it by selecting View > Datasheet View.

Hopefully you'll see 2 columns, ALL the TaskIDs, plus the count of the subTaskIDs. Some of these should be zero.

- Save the query as "CountOfSubTasks".

We'll continue once you're back on deck. It's 12 noon here in Oz, I'll try to keep an eye on my email tonight!

Max Hugen
Australia

 

[Hulm1]

OK I got on with the query and works just fine. You had all the table names and fields correct.

However, I am getting a syntax error with your code for the control source of the text boxes. It says I am missing or have too many quotation marks or commas. Looks fine to me though, so I am confused.

These were your suggestions
=Iif([CountOfSubTasks]=0,0,""), ForeColor: Red, BackStyle: Transparent

And

=Iif([CountOfSubTasks]<>0,[CountOfSubTasks],""), ForeColor: Green, BackStyle: Transparent

 

[maxhugen]

G'day... just about to hit the sack myself!

There are 3 properties being set - you haven't put that whole string in the ControlSource, have you? ie:

[tt]ControlSource: =Iif([CountOfSubTasks]=0,0,"")
ForeColor: Red
BackStyle: Transparent[/tt]

Max Hugen
Australia

 

[Hulm1]

Hope I catch you before bed. If not, oh well!

That worked!

However!

If I drag the query CountofSubTasks into a column in the MS VB window, my form won't work (same happens if I just drag the countofSubtasks into the query column). I cannot add new tasks and if I click the previously existing button "Select SubTasks" then the debug windown comes up and highlights the first Docmd instruction in yellow

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


If I do NOT drag anything from CountOfSubtasks into a query column, then I get #NEXT instead of a no.

 

[maxhugen]

Hi, looks like we've created a 'non-updateable' query for the subform. But 'there's more than one way to skin a cat'.

We could use a DCount() function to get the values for the 2 textboxes txtCountZero and txtCountNotZero.

As the Domain functions (DCount, DLookup, DSum etc) are a little slow, lets add one more textbox to get the DCount, and then the 2 display textboxes can reference that instead.

Add a new textbox called CountOfSubTasks. Place it somewhere to the right of your other fields, and set the Visible property to False.

Set the ControlSource property to:

[tt]=DCount("JobSubTaskID","JobSubTask","JobTaskID=" & JobTaskID)[/tt]

You now have a hidden textbox with the count of SubTasks in it - check by temporarily making it Visible. The 2 display textboxes will refer to this textbox now, rather than a field in the subform's query.

Just to 'clean up' you can delete the query you saved, 'CountOfSubTasks'.

Max Hugen
Australia

 

[Hulm1]

Good morning to you. Thank you so much. This is great and works fine.

Is it possible to get rid of the word #Error in these red and green boxes when a record does not exist? I mean in the row below the new record you are working on.

 

[maxhugen]

Ok, need to check if JobTaskID is null (which it is for the new record).

In your new textbox called CountOfSubTasks, change the ControlSource property to:

[tt]=Iif(Not IsNull(JobTaskID),
DCount("JobSubTaskID","JobSubTask","JobTaskID=" & JobTaskID),0)[/tt]

Max Hugen
Australia

 

[maxhugen]

Actually, it's probably preferable for the display textboxes to appear empty, rather than display a red zero.

If you prefer that, change the ControlSource for the 3 textboxes like this:

CountOfSubTasks:
[tt]=Iif(Not IsNull(JobTaskID),
DCount("JobSubTaskID","JobSubTask","JobTaskID=" & JobTaskID),[red]""[/red]) [/tt]

txtCountZero:
[tt]=Iif([CountOfSubTasks]=0,0,"")[/tt] --- nothing changed here

txtCountNotZero:
[tt]=Iif([CountOfSubTasks][red]>[/red]0,[CountOfSubTasks],"")[/tt]

Cheers, Max