check missing fields on subform

[Josh]

Hi, I have a frmPurchaseOrder, with subformPurchaseOrderDetails. When User
clicks my Print button, I want to first check to see if all Purchase Order
Details (the separate Items) have a Department in the the cboDepartment field.

If it were only one record in the subform, I could check that easy enough, but
how do I check all records in the subform for ANY Null values in that field?

Person inputting the PO might not be certain which department to charge to, is
why I'm not requiring it until actually will be printed.

There could be other fields that will be Null and will remain Null in the
subformPurchaseOrderDetails record, though (if that makes a difference)

Thanks, Josh

 

[Allen Browne]

Presumably you have these tables:
- PurchaseOrder (with PurchaseOrderID primary key) - the source for the main
form;
- PurchaseOrderDetails (with PurchaseOrderID foreign key) - the source of
the subform.

It that's the case, you can use DCount() to get the number of related
records where the Department field is Null.

Something like this:

Private Sub cmdPrint_Click()
Dim strWhere As String
Dim lngCount as Long
Dim strMsg As String

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select an order to print."
Else
strWhere = "(PurchaseOrderID = " & Me.PurchaseOrderID & _
") AND (Department Is Null)"
lngCount = DCount("*", "PurchaseOrderDetails", strWhere)
If lngCount = 0 Then
strWhere = "PurchaseOrderID = " & Me.PurchaseOrderID
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Else
strMsg = lngCount & " record(s) in the subform need Dept."
End If
End If
End Sub

 

[Allen Browne]

Presumably you have these tables:
- PurchaseOrder (with PurchaseOrderID primary key) - the source for the main
form;
- PurchaseOrderDetails (with PurchaseOrderID foreign key) - the source of
the subform.

It that's the case, you can use DCount() to get the number of related
records where the Department field is Null.

Something like this:

Private Sub cmdPrint_Click()
Dim strWhere As String
Dim lngCount as Long
Dim strMsg As String

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select an order to print."
Else
strWhere = "(PurchaseOrderID = " & Me.PurchaseOrderID & _
") AND (Department Is Null)"
lngCount = DCount("*", "PurchaseOrderDetails", strWhere)
If lngCount = 0 Then
strWhere = "PurchaseOrderID = " & Me.PurchaseOrderID
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Else
strMsg = lngCount & " record(s) in the subform need Dept."
End If
End If
End Sub

 

[Josh]

Great!.......Thanks!
Worked fine, after I added:

MsgBox strMsg

after the line:

strMsg = lngCount & " record(s) in the subform need Dept."

Josh