multiple field subform check 1

[Ali29J]

Hi All

Managed to establish the code with a little help from MajP, but come to grinding halt!


I have the below code which checks that a value in subform has been completed in each of the filtered records, but at present it only checks the field "boxqty", i would like it to also check "boxweight" and "Shipmentqty" under the same conditions, but have no idea how to proceed...


Dim rs As DAO.Recordset
Dim intCount As Integer

Set rs = Forms!Logistics!LogisticsDetail.Form.RecordsetClone
Do While Not rs.EOF
If Trim(rs.Fields("boxqty") & " ") = "" Then
intCount = intCount + 1
End If
rs.MoveNext
Loop
If intCount > 0 Then
MsgBox "You must fill in values for remaining " & intCount & " box quantities"

Anyone have any ideas to help me get started!

Cheers

Ali

 

[rubbernilly]

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.

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!

 

[MajP]

How about the tough-love approach. On the on current event of the main form you check to see if the subform is completely filled out. If it finds a record that is not filled out, it goes to that record. You can never go to another record until it is filled out.

Private Sub Form_Current()
  Dim rs As DAO.Recordset
  Dim intCount As Integer
  Dim mycontrol As Access.TextBox
  Dim strMessage As String
  Dim bk As Long
  Dim subFrm As Form
  
  Set subFrm = Me.LogisticDetail.Form
  strMessage = "Hey Buddy, you forgot a value for"
  Set rs = subFrm.Recordset
  Me.LogisticDetail.SetFocus
  Do While Not rs.EOF
    strMessage = "Hey Buddy, you forgot a value for "
    If Trim(rs.Fields("boxqty") & " ") = "" Then
      MsgBox strMessage & "Box Quantity. How about fixing it."
      GoTo ExitSub:
    ElseIf Trim(rs.Fields("bxWeight") & " ") = "" Then
       MsgBox strMessage & "Box Weight. How about fixing it."
       GoTo ExitSub:
    ElseIf Trim(rs.Fields("Shipmentqty") & " ") = "" Then
       MsgBox strMessage & "Shipment Quantity. How about fixing it."
       GoTo ExitSub:
    End If
    rs.MoveNext
   Loop
   Exit Sub
ExitSub:
       subFrm.Bookmark = rs.Bookmark
       DoCmd.CancelEvent
       Exit Sub
End Sub

Might want to put this error checking in some other events, such as form close.

 

[Ali29J]

Hi all

OK thanks for your help so far but still got some issues.

below is current code

Dim rs As DAO.Recordset
Dim iBoxQty As Integer, iBoxWeight As Integer, iShipmentQty As Integer, icombo59 As Integer

Set rs = Forms!Logistics!LogisticsDetail.Form.RecordsetClone
Do While Not rs.EOF

If Trim(rs.Fields("Shipmentqty") & " ") = "" Then
iShipmentQty = iShipmentQty + 1
GoTo ReadyForNext
End If
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("combo59") & " ") = "" Then
icombo59 = icombo59 + 1
End If

ReadyForNext:
rs.MoveNext
Loop

If iBoxQty > 0 Or iBoxWeight > 0 Or iShipementQty > 0 Or icombo59 > 0 Then
MsgBox "You must fill in datain the following records: " & vbCrLf & "" & iBoxQty & " for Box Quantities" & vbCrLf & iBoxWeight & " for Box Weights" & vbCrLf & iShipmentQty & " for Shipment Quantity" & vbCrLf & icombo59 & " for Box Dimensions", vbOKOnly, "Information Required"
Else
Me.Check44.Value = True
Exit Sub
End If

when first enterring form if some of the records are missing it picks it up fine, problems occur after this, as below

- when returning to the form it completely ignores the code and checks the check box

- also if all records are present as they should be it returns "item not found in this collection"

I m stumped from here...can anyone help??

 

[MajP]

In my example I had to use the Recordset property not the clone because the clone is a snapshot in time where Recordset is a pointer. I was forced back to the form. In your design, that could be the problem as well .

Set rs = Forms!Logistics!LogisticsDetail.Form.Recordset

You have one collection the Fields collection. This is the fields in your recordset, not controls on a form.
I doubt you have a field called Combo59, that is a name of a control. What is the underlying field that the control is bound to?
If Trim(rs.Fields("combo59") & " ")

Therefore can not find a field called Combo59 in the collection.

 

[MajP]

The other reason it does not fire the second time is you dropped the "Cancel Event". Actually you might want to try this code in the Before Update event instead of the Current event. Make this fix then try executing out of the before update. Most times you do your error checking in the Before Update. The Current Event would help to fix a database where errors exist already.

If iBoxQty > 0 Or iBoxWeight > 0 Or iShipementQty > 0 Or icombo59 > 0 Then
MsgBox "You must fill in datain the following records: " & vbCrLf & "" & iBoxQty & " for Box Quantities" & vbCrLf & iBoxWeight & " for Box Weights" & vbCrLf & iShipmentQty & " for Shipment Quantity" & vbCrLf & icombo59 & " for Box Dimensions", vbOKOnly, "Information Required"
Docmd.CancelEvent