Master child form issue 1

[NVF]

Background: Using a form wizard, I created a form with (1) subform. I'll call this form frmIssue. It has (2) wizard generated record navigation bars (1 each) for master and child records. There is a (1) to many relationship between Master and child records. There can be many Master records. There can be many child records for (each) Master record. Child records are obviously displayed on the subform. When frmIssue is first presented, the form's Master records have already been created through a prior form. On frmIssue, the user is able to navigate through master records but they are strickly for user reference as he inputs child records on the subform. Master data is intentionally locked on this form. FYI - I have no problem validating fields on frmIssue.
The Problem: I have a requirement that this application's users must enter at least one child record for (each) master record. After entering data for the first Master record I need to validate that all Master record have at least (1) child record before allowing the user to go to the next form. How can I do this? Any suggestions for testing/validating this condition would be much appreciated. Please bear in mind that Access is not my strong suit when you reply. The simpler the answer the better.

Thanks for your help!

NickV

 

[JBinQLD]

Too easy mate.

Turn off the Navigation Buttons for the main form (in form properties) and add buttons to move between the records.

The button wizard can do this for you.

On these buttons logic (code/Event procedures) check at least one record exists in sub form otherwise don't move on.

A post earlier today showed how to count records in subform, you can use that.

NB: You may also want to turn the "Cycle" property to "Current Record" otherwise the user could press Tab a few times to move onto the next record. Swine users....

 

[NVF]

JBinQLD - Thanks for your prompt reply. I'm not certain that I made myself clear. The issue is not testing/requiring that data be entered on the subform. This is being done. Bear in mind that all the Master data on this form was previously collected in a prior form process. I.e., no master data is being collected in this form - it already exits. The issue is ensuring that the user doesn't move off the form until it has collected at least on child record for each Master record. What typically happens is that the user will update a subform for master record 1 and then move on to the next form. The problem is that he should have entered at least 1 subform for each of Master Records 2,3,4,5 etc.

Thanks again.

NickV.

 

[JBinQLD]

I/We must be missing something here! You say:

"..all the master data on this form was previously collected"

So the issue is "Ensuring the user doesn't move off the form until it has collected at least on[sic] child record"

So you have the master data, I agree, my idea was to stop the user moving on until records in the subform had been counted and were greater than 1?

Whats the problem? Just turn off tabbing and navigation buttons so that they have to click your button and then do a recordcount on your button logic.

Really sorry for repeating myself, but I can't see the drama mate. Perhaps an example if this doesn't help?

 

[TheAceMan1]

How are ya NVF . . .

The core of what your after is a query that returns all [blue]PrimaryKeys[/blue] of the parent table that have no matching [blue]ForeignKeys[/blue] in the child table. The SQL of this query would look like (note PK & FK are primary/foreign keys respectively):

[blue]SELECT tblMain.PK 
FROM tblMain 
WHERE tblMain.PK NOT IN (SELECT tblChild.FK 
                         FROM tblChild;);[/blue]

You have several options with the query:
[ol][li]It could be used as the [blue]RecordSource[/blue] for the main form (you'd simply add approriate fields in the 1st select statement). Here the user is only able to navigate thru records that required subform updates.[/li]
[li]If the user is hitting a button to goto the next form, a recordset based on the query would tell all ... wether to goto the next mainform record that required adding a subform record, or moving on if no records are returned thru the query![/li][/ol]
There are other secenarios I can think of but I belive you have the idea! ([blue]tracking recordcount of the query![/blue]) ... From a navigational standpoint that provides smooth transitions from one form to the next, the choice is yours.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JBinQLD]

From Acemans solution I see what you were trying to do, sorry for my mis-understanding.

At least you have an answer now!

 

[TheAceMan1]

Howdy JBinQLD . . .

Take it with a grain of salt ... it happens to all of us!
Its a [blue]rare talent[/blue] to be able to decipher every post you read! ... I'be been at it for years, and still miss understand far too great a many posts (at least for me).

Just be aware: here at tek-tips we have people from all walks of life with 100 times as many ways to express themselves. If you findout anything at all here, its just how complicated people can be ... [blue]not in how complicated they are, but in how complicated in can be to understand them![/blue]

[blue]You Take Care! . . . Ya Hear! . . .[/blue]

BTW: What happened to [purple]NVF![/purple] . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[NVF]

Hello All - I want to thank everyone for offering their expert advise. You will reap what you've sown, which is getting help when you need it. TheAceMan1 ultimately put me on the right track. Simple is good. Here's the actual solution:

If DCount("*", "qryObjectivesWithoutMatchingtblStrategies") > 0 Then
MsgBox "Check each of your objectives on this form using the lower navigagtion bar. At least (1) strategy per each objective is required.", vbOKOnly
Exit Sub
End If

The Lord has used you guys.

Be blessed in the Son! - NVF

 

[TheAceMan1]

NVF . . .

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886