From the Main form, How to make sure at least 1 item was entered on a subform?

In the Access 2013 app I am working on I have the pretty standard main form 'frmDocumentDefinition', subform 'frmDocumentInterestedParties_Sub' set up.

In this case the main form is used to enter specific about a mail merge document to be created.  Information such as:

The location and name of the mail merge document
Document type, which is an internal category.
Whether the output of the mail merge should be PDF, Word or both

The subform is where the distribution (who gets this output) is entered.  It is a subform because At least one person must be specified in the distribution but there is no limit to how many recipients there can be.

Since I can't force them into the suform, or can I, I want to make sure they entered at least one recipient.  This has to be done form the main form.

I've been developing in Access for many years but have not encountered a requirement like this before.

From the main form, how can I check whether at least one entry has been made on the subform?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Hi,

If your subform is linked to a data source, you can simply check if its recordset isn't empty (that is checking that the recordset BOF property and EOF property arn't True)
Something like:
Dim rs As DAO.Recordset
Set rs = subform.recordset
If Not(rs.BOF and rs.EOF) Then
    '// recordset isn't empty
End If
Set rs = Nothing

Open in new window

0
PatHartmanCommented:
Set rs = subform.recordset
is probably going to have to be more like:
Set rs = Me.nameofsubformcontrol.Form.Recordset

But, what do you want to do if the count is zero?  Refusing to let them close the main form is an option but not a very good one since they can always push the power button.  It really depends on how important it is to enforce the rule.  Typically, I add a column to the main form's table to indicate "complete" or whatever makes sense to you.  In the AfterUpdate event of the subform, I count the number of records in the subform's recordset and set the main form field to true or false or a count if that makes sense.

Then two other things are required.
1. Any query that needs to use the subform's data table should check the "complete" field and bypass the records if there are no details.
2. Every time the application opens, It should check for documents with no distribution entries and open the form so they can be fixed.  This will be sufficiently annoying that people will clean up after themselves.  If you log changed by and changed date, this can help you to decide how adamant about completing the entry you want to get.  You can also restrict the message so that it only happens when personA opens the app if PersonA is the one who created the document header.  I would include you and any admin in this message so that it is easy for you to keep on top of this also but it won't bother PersonB who is innocent.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thanks you both for your comments.  I'm trying to implement this edit and noticed some odd behavior that I'm not sure how to resolve.

I have a 'Save and Exit' button on this form.  The logic related to this button makes sure that all edits are passed before exiting the form.

When entering a new entry on the main form:  
1. If I don't enter any recipients the edit catches the error, puts up the
MsgBox "There must be a least one entry in Document Recipients" and does not exit the form.

2. After receiving the message I enter the subform, enter a recipient and press the 'Save and Exit' button.  I still get the
MsgBox "There must be a least one entry in Document Recipients", even though there is now a recipient entered.

Second Scenario
I open an existing record on the main form.  From prior entry, there is already one recipient in the subform. If I make no changes and press the 'Save and Exit' button the form closes without issue.
1. If I delete the recipient from the subform and press the 'Save and Exit' button I get the MsgBox "There must be a least one entry in Document Recipients".  This is good.
2.However, I now enter a recipient in the subform and press the 'Save and Exit' button again and get MsgBox "There must be a least one entry in Document Recipients".  No matter how many recipients I enter I still get the Msgbox and cannot exit the form using the 'Save and Exit' button.
However, if, after entering the recipients I exit the form using the 'Exit No Save' button, which doesn't invoke the edits and then re-open the form all of the recipients are there.

Is there something I have to do to refresh the subform after I enter a recipient?  The deletion of all recipients will trigger the error but once I've received the error it will not go away no matter how many recipients I enter in the subform.

What is the resolution to this issue?

The new version of my 'Save and Exit' button routine looks like this:
Private Sub btnSaveAndExit_Click()
'
If AnyEditErrors Then
    Exit Sub
End If
'
' before exiting must check if at least one recipient is entered
'
If checkAtLeastOneRecipientEntered = True Then
Else
    MsgBox "There must be a least one entry in Document Recipients"
   ' Me.frmDocumentInterestedParties_Sub.SetFocus
    Exit Sub
End If

DoCmd.Close acForm, Me.Name
'
End Sub

Open in new window

This is the new routine called form the 'Save and Exit' button logic to check if any recipients have been entered
Private Function checkAtLeastOneRecipientEntered() As Boolean

Dim rs As DAO.Recordset

Set rs = Me![frmDocumentInterestedParties_Sub].Form.RecordsetClone

If rs.EOF Then
    checkAtLeastOneRecipientEntered = False
Else
    checkAtLeastOneRecipientEntered = True
End If
'
rs.Close

Set rs = Nothing

End Function

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PatHartmanCommented:
1. In a parent/child relationship, the parent record MUST exist before any child records may be added.  The reason is obvious if you think about it.  The PK of the parent is stored in the child record where it is referred to as a FK.  If there is no parent record, then you cannot add a child record because the FK is required (assuming you set your properties correctly).  Sometimes FK's may be optional but not in this situation.  This means that you CANNOT prevent the save of the parent record dependent on the presence of child records because initially, the first time you save the parent record, there will not ever be any child records.
2. Validation belongs in the Form's BeforeUpdate event.  If you place the code in other events such as the click event of your save button, it may seem to work but in reality it doesn't and you will end up saving incomplete records unless your table properties prevent it.

Please review my previous suggestion.  An alternative is to use temporary tables.  In this method, you have copies of the Documents and Recipients tables.  You add records here and when the user asks to commit the new record, you validate and then copy from the temp tables to the main tables and delete from the temp tables.  Because you have a parent/child relationship, this is slightly more complicated due to having to manually sync the foreign key.  I'm not going into the details because I don't recommend doing this.  I would (and have) do what I initially suggested.  As it happens, I have also used the temp table method and it is more difficult to implement and control.
0
mlcktmguyAuthor Commented:
Pat,
I have edits in the 'Before update' event on the main form.  No partial or unedited records will ever be written to the main file.  I placed the edit in the 'Save and Exit' button for exactly the reason you mentioned.  There is nothing for the subform records to link to until there is a main form record.  In the 'Save and Exit' button I know there is one, so there should also be recipients.

It see the benefit of your approach but it is a hassle to get new fields added to the DB.

Do you have any idea why my retrieval function still hits eof even after records are added to the subform?  It keeps track of deletes just fine.  After I delete all of the participants I get an error when I try to 'Save and Exit'.  Re-adding the recipeints doesn't change that.  I still get an error in the 'Sane and Exit'


If I do have to go back and add the new 'Complete' field, using your method how do you count the number of records in the subform?
0
PatHartmanCommented:
I placed the edit in the 'Save and Exit' button for exactly the reason you mentioned.
As I said - that is the wrong event.  All your save and exit button should do is save the record and exit.  When the save is initiated, your BeforeUpdate code runs and validates the data and CANCELS the update if any error is found.

It see the benefit of your approach but it is a hassle to get new fields added to the DB.
The alternative is adding new tables.

Do you have any idea why my retrieval function still hits eof even after records are added to the subform?
If you are running the code in the subform, the subform record is probably not saved yet.  If you are running it from the main form, you should probably change the code to go to the end of the recordset before you count the records to ensure that it is fully loaded into memory.

The code as you have it will never ensure that the subform contains data.  The parent record MUST BE SAVED before you can even add a child record.  So how can an edit that happens when you save the parent record ensure that there is a child record when there cannot be a child record until the parent record is added.  You have a circular condition.  You have to allow the parent record to be saved with no validation.  Now the barn door is open.  How do you think you can prevent the user from leaving the current record without first adding child records - you can't!  You can try and you can be really annoying but you simply cannot unless you can somehow stop them from using task manager or the power button.  And you also need a permanently uninterruptible power source, etc. because sometimes the power goes out.  Sometimes the network goes down.
0
mlcktmguyAuthor Commented:
Once again thanks for your input.  I do really appreciate your it and would have adopted your original suggestion if it didn't involve DB revisions.  
Perhaps I didn't explain how things are set up in this form very well but the code works as is.  No chicken/egg scenario exists between the main form and the subform.  I have entered completely new records as well edited and revised existing records.

I needed to make one small change to 'checkAtLeastOneRecipientEntered' to get this to work as desired.  I changed 'If rs.EOF Then' to 'If rs.RecordCount < 1 Then'.  The revised function looks like this:
Private Function checkAtLeastOneRecipientEntered() As Boolean

Dim rs As DAO.Recordset

Set rs = Me![frmDocumentInterestedParties_Sub].Form.RecordsetClone

If rs.RecordCount < 1 Then
'If rs.EOF Then
    checkAtLeastOneRecipientEntered = False
Else
    checkAtLeastOneRecipientEntered = True
End If
'
rs.Close

Set rs = Nothing

End Function

Open in new window


All other logic is just as previously posted.  The editing is being done from the main form.

A user shutting off the power or cancelling with task manager could cause issues no matter what the application is executing at the time. In this case that's one scenario I'm willing to accept.
0
mlcktmguyAuthor Commented:
Thanks very much.
0
PatHartmanCommented:
You're welcome.
No chicken/egg scenario exists between the main form and the subform
It does exist.  You just haven't noticed a problem yet.  Wait until the users have a go at it.  At some time you will find that you have parents without children  but as long as you're happy for now, we can move on.

A user shutting off the power or cancelling with task manager could cause issues no matter what the application is executing at the time
There are other ways.  That's just the method they'll use until they find the holes.  I once removed over 5,000 lines of validation code from a single form.  It was there because the programmer didn't understand event processing and he kept adding code in various places until he was validating every control on the form in 6 events of every control and still wasn't trapping all the situations where the record got saved because the one event he studiously avoided was the ONLY event he actually needed to use - the Form's BeforeUpdate event.  There were about 100 controls (a large form) times 6 events times an average of 10 lines of code for each edit.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.