Prevent Missing Entries in Subform

[bcooler]

Thanks in advance for the help!

I have a subform which I use in the datasheet view.

When I am finished entering data in the subform, I have a quality assurance person enter their user name and date into the main form.

At this point, I want Access to look at all the subform records to make sure certain serial number fields are completely filled out. If not, notify (color maybe) the user of the missing info, and cancel the quality assurance entry. Once corrected, the quality assurance person can enter their info again, the data will be accepted and all color notifications will be removed.

I am a little new to recordsets and "clone", but was wondering if this was the right direction. I also am guessing I need the beforeupdate event with a cancel=true and me.undo comment, right?

Any ideas?

Again, thanks for any help!

 

[dhookom]

I would use conditional formatting to color fields if the serial number field is blank. You can also set the required property of the field to Yes.

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

I can see the conditional formatting part. No problem.

However, I should explain a little more of what I am doing (WRT forcing the serial number to be filled out). I want to paste a lot of information (part number, description) directly into a few columns of the subform (from Excel, not sure if I can do it this way yet). Then, I later want to allow other users to fill in the missing serial number info.

In this case, I don't really care about whether the serial number is completed UNTIL the quality inspector signs off the form. At that time, I want to do this double check to be sure all fields are complete.

Also, I have the subform requerying as data is being entered, so a "required" field gives me an error after I paste info, but haven't yet entered thte Serial number.

 

[dhookom]

The recordset code might look something like:

Private Sub cmdCheckSubform_Click()
    Dim rs As DAO.Recordset
    Dim strShowText as String
    Set rs = Me.sfrmContinuous.Form.RecordsetClone
    With rs
        Do Until .EOF
            If .Fields("SerialNumber")& "" = "" Then
                strShowText = strShowText & _
                    .Fields("some other field") & ", "
            End If
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    If len(strShowText) > 0 Then
        MsgBox "Missing serial numbers: " & strShowText
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Ok, this looks really great. I changed it a little for my needs (below):

Private Sub IFP_QACompDate_BeforeUpdate(Cancel As Integer)
    Dim rs As DAO.Recordset
    Dim strShowText As String
    Set rs = Me.sfrmIFP_Parts.Form.RecordsetClone
    With rs
        Do Until .EOF 'verifies form is completely filled out
            If .Fields("IFP_GD_SN") & "" = "" Then
                strShowText = strShowText & _
                    txtPartName & " ," '.Fields("IFP_PartNo") & ", "
            End If
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    If Len(strShowText) > 0 Then
        MsgBox "Missing serial numbers: " & strShowText
        Cancel = True
        Me.IFP_QACompDate.Undo
    End If
End Sub

The problem now is it works the first time I try to enter a date with missing serial number info. After that (and before I close the form), it will take the date without question. I've tracked it down to the recordset says its EOF = true, meaning (I guess) the recordset clone is empty. Why would it be empty the second time around?

 

[dhookom]

You might need to movefirst. Try:

Private Sub IFP_QACompDate_BeforeUpdate(Cancel As Integer)
    Dim rs As DAO.Recordset
    Dim strShowText As String
    Set rs = Me.sfrmIFP_Parts.Form.RecordsetClone
    With rs
        .MoveFirst
        Do Until .EOF 'verifies form is completely filled out
            If .Fields("IFP_GD_SN") & "" = "" Then
                strShowText = strShowText & _
                    txtPartName & " ," '.Fields("IFP_PartNo") & ", "
            End If
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    If Len(strShowText) > 0 Then
        MsgBox "Missing serial numbers: " & strShowText
        Cancel = True
        Me.IFP_QACompDate.Undo
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

That's it! Works great.

One final set of questions. I kind of understand what is going on, but:

1.) What is a recordset clone and why/when do we need it?

2.) I hear DAO is going obsolete for ADO (probably "being obsoleted" for a long time). I prefer DAO only because all the users here have it default turned on and ADO is turned off. If I wanted ADO it seems like I'd have to go to each machine and turn it on for them. However, I fear I will have to rewrite this code later when DAO goes obsolete. Any thoughts?

 

[dhookom]

The recordset clone is just a snapshot of the form's records.

I wouldn't worry about having to switch from DAO. It is supported in Access 2010 and won't go away in the near future. Keep using it.

Duane
Hook'D on Access
MS Access MVP