You can do this using the same code, just replace the one field name with the other.
Duplicate the code and change this:
If Trim(rs.Fields("[red]boxqty[/red]") & " ") = "" Then
to this:
If Trim(rs.Fields("[red]boxweight[/red]") & " ") = "" Then
This will need to come before the MoveNext command, since you want to test the same record for all three fields.
You have two directions that you can take this.
1) If any of these fields are empty, you can direct the user that they have to fix X number of records
2) You test fields in a particular order, testing the second field only if the first one passes, and testing the third only if the second one passes.
For the first one, you can run your checks all in one line of an If statement, like this:
[maroon]If Trim(rs.Fields("boxqty") & " ") = "" OR Trim(rs.Fields("boxweight") & " ") = "" OR Trim(rs.Fields("Shipmentqty") & " ") = "" Then[/maroon]
Then your current variable would represent records where any of these things could have gone wrong, and you would need to change the verbage you report to the user in your message box so that it wasn't specific to the "boxqty" field.
For the second option, you would need three integer variables where you now have one (intCount). Maybe call these iBoxQty, iBoxWeight, and iShipmentQty. Then you'd have three If-Then statements that test each field individually, incrementing the proper variable if the field is empty.
Code:
Dim rs As DAO.Recordset
Dim iBoxQty as Integer, iBoxWeight as Integer, iShipmentQty as integer
Do While Not rs.EOF
If Trim(rs.Fields("boxqty") & " ") = "" Then
iBoxQty = iBoxQty + 1
Goto ReadyForNext
End If
If Trim(rs.Fields("boxweight") & " ") = "" Then
iBoxWeight = iBoxWeight + 1
Goto ReadyForNext
End If
If Trim(rs.Fields("Shipmentqty") & " ") = "" Then
iShipmentQty = iShipmentQty + 1
End If
ReadyForNext:
rs.MoveNext
Loop
If iBoxQty > 0 or iBoxWeight > 0 or iShipementQty > 0 Then
MsgBox "You must fill in data in the following records: " & vbcrlf & "------------------------------------" & iBoxQty & " for Box Quantities" & vbcrlf & iBoxWeight & " for Box Weights" & vbcrlf & iShipmentQty & " for Shipment Quantity", vbokonly, "Information Required"
The code above will test the BoxQty field first, and if that field turns out to be empty, will add one to the counter for iBoxQty and then move to the next record. Be aware that this means that if a record would fail on BoxQty *and* on ShipmentQty, iShipmentQty will not be incremented. The user should just make sure on the records they are forced to revisit that they look at each of these three fields. The other option is to remove the "ReadyForNext:" line label and the "Goto ReadyForNext" commands from the code and let every counter increment for the total number of fields that the user needs to fix. In this case, that record with an empty BoxQty and ShipmentQty would increment both counters. If you do it that way, you might want to change the verbage given to the user to indicate this somehow (otherwise they may see 10 BoxQty failures and 5 ShipmentQty failures and think that they have to fix 15 records when in fact there might only be 10 records, 5 of which also need the ShipmentQty fixed).
Anyway, hope this is enough to get you started. Good Luck!
