Check for Required Data in Subform Before Saving Main Form

[happybunnyj]

I have a Form on which I enter info about each Item in my library. I created a subform on which I check a control for each copy of the item (in order to generate a unique ID number for each copy).

I am trying to add a validation check for the subform to make sure that my user checks the box to indicate at least 1 copy before saving the main Item record. I found the following code and added it to as an Before Update on my main form and it works, EXCEPT

1. It will not let me leave the main form to get to the subform (the warning message keeps popping up)

2. This query searches the entire table instead of checking to see if the current item has an associated copy ID.

If DCount("*", "qry-Items without Copy IDs") > 0 Then
MsgBox "The Item has no copies associated with it. Please add at least 1 Copy ID in the sub-form below.", vbOKOnly
Exit Sub
End If

Here's the "qry-Items without Copy IDs"):

SELECT [tbl-ITEMS].ItemID
FROM [tbl-ITEMS]
WHERE ((([tbl-ITEMS].ItemID) Not In (SELECT [tbl-Item Copies Join].ItemID
FROM [tbl-Item Copies Join]));


Thank you in advance for any advice/assistance.

 

[TheAceMan1]

How are ya happybunnyj . . .

Be aware: whenever you edit a record on the mainform (new or previously saved) then set focus to a control on a subform, [purple]the record in the mainform is automatically saved! ... and vice versa! ... moving focus from subform to main.[/purple]

I expect this will modify your thinking.

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

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[happybunnyj]

Ok, then, good advice. I guess all I really need is to be able to pop up a warning when I move on the next record that I have to add at least one copy on the subform. I am not sure how to do that. Thanks again.

 

[Pampers]

Hi happybunnyj,
Maybe you could check if the subform holds a record by using the RecordsetClone-method. Something like:

Dim rstRecordset As DAO.Recordset
Dim intCount As Integer

Set rstRecordset = Me.Form1.Form.RecordsetClone

intCount = rstRecordset.RecordCount
If intCount = 0 Then
    MsgBox ("You have to set a copy")
Else
    MsgBox ("All ok")
End If

Pampers
Keeping it simple can be complicated