RecordsetClone Question

[Zachariah]

I have a form frmApplAddr where I enter city/state/zip
information. In the form's AfterUpdate() event I have
code that creates a RecordsetClone and verifies that
city/state/zip has been entered for all records. However
not all the records are verified. If I have 2 records, one
for John Doe and one for Mary Smith, only John Doe is
checked. I checked the rs.RecordCount and it shows a
value of 2, but it seems that John Doe's data is in both
records and the code never finds Mary Smith's record.
What is going on?!

 

[JohnFol]

Can you post some of the code??

 

[Zachariah]

Private Sub Form_AfterUpdate()

Dim intGoodRecs As Integer
Dim rs As DAO.Recordset
Dim stCity As String
Dim stState As String
Dim stZipCode As String

intGoodRecs = 0
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
If IsNull(City.Value) Or Trim(City.Value) = "" Then
'No value in City
Else
If IsNull(State.Value) Or Trim(State.Value)
= "" Then
'No value in State
Else
If IsNull(ZipCode.Value) Or Trim
(ZipCode.Value) = "" Then
'No value in ZipCode
Else
'Everything has a value
intGoodRecs = intGoodRecs + 1
End If
End If
End If
rs.MoveNext
Loop

If intGoodRecs = rs.RecordCount Then
'All records are verified, enable the button
Forms!frmNewSubmission.CredRepReq.Enabled = True
Else
'missing information, disable the button
Forms!frmNewSubmission.CredRepReq.Enabled = False
End If

Set rs = Nothing

End Sub

 

[Zachariah]

I figured out a workaround by just creating a public sub
that has the same code in the calling form. In my called
form in the close() event I just call that public sub and
it does the job.

 

[Dirk Goldgar]

Private Sub Form_AfterUpdate()

Dim intGoodRecs As Integer
Dim rs As DAO.Recordset
Dim stCity As String
Dim stState As String
Dim stZipCode As String

intGoodRecs = 0
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
If IsNull(City.Value) Or Trim(City.Value) = "" Then
'No value in City
Else
If IsNull(State.Value) Or Trim(State.Value)
= "" Then
'No value in State
Else
If IsNull(ZipCode.Value) Or Trim
(ZipCode.Value) = "" Then
'No value in ZipCode
Else
'Everything has a value
intGoodRecs = intGoodRecs + 1
End If
End If
End If
rs.MoveNext
Loop

If intGoodRecs = rs.RecordCount Then
'All records are verified, enable the button
Forms!frmNewSubmission.CredRepReq.Enabled = True
Else
'missing information, disable the button
Forms!frmNewSubmission.CredRepReq.Enabled = False
End If

Set rs = Nothing

End Sub

This code is not checking the values of fields in the recordset, but
rather those of the current record on the form. Everywhere in the code
that you have a reference like this:

If IsNull(City.Value) Or Trim(City.Value) = "" Then

the field name must be qualified with the recordset object, like this:

If IsNull(rs!City.Value) Or Trim(rs!City.Value) = "" Then

.... although it would be more efficient to write the combined test as

If Len(rs!City & vbNullString) = 0 Then

You could also use a With block to establish rs as the object of
reference, and recode it like this:

With rs
.MoveFirst
Do Until .EOF
If Len(!City & vbNullString) = 0 Then
'No value in City
Else
If Len(!State & vbNullString) = 0 Then
'No value in State
Else
If Len(!ZipCode & vbNullString) = 0 Then
'No value in ZipCode
Else
'Everything has a value
intGoodRecs = intGoodRecs + 1
End If
End If
End If
.MoveNext
Loop

If intGoodRecs = .RecordCount Then
'All records are verified, enable the button
Forms!frmNewSubmission.CredRepReq.Enabled = True
Else
'missing information, disable the button
Forms!frmNewSubmission.CredRepReq.Enabled = False
End If

End With

Set rs = Nothing

Possibly more efficient would be to use FindFirst to locate the first
(if any) record that has a null value in any of those three fields:

With Me.RecordsetClone
.FindFirst "City Is Null OR State Is Null OR ZipCode Is Null"
If .NoMatch Then
'All records are verified, enable the button
Forms!frmNewSubmission.CredRepReq.Enabled = True
Else
'missing information, disable the button
Forms!frmNewSubmission.CredRepReq.Enabled = False
End If
End With

Note that, in the search criteria above, I've left out the possibility
that these fields may have values that are zero-length strings or
spaces. These are fields that probably should not be allowed to contain
such values -- they should have real values or be Null -- so I'm
guessing you don't actually have to test for anything but Null.