RecordsetClone question

  • Thread starter Thread starter Zachariah
  • Start date Start date
Z

Zachariah

frmNewSubmission has a subform frmApplicant.
Subform frmApplicant has hidden city state and zip data
and a button that lauches frmApplAddr.
frmApplAddr is a form where a user can fill in city state
and zip data.

The Form_Load() and Form_Current() events for
frmNewSubmission check to see if hidden city state and zip
data exist in frmApplicant.
If they exist a button called "Request Credit Report" is
enabled.
If they don't the button is disabled.

However I needed code to check data entered in
frmApplAddr. As well.
If frmApplAddr's city state and zip exist then it enables
frmNewSubmission's button.
If not the button is disabled. I put this code in the
Form_AfterUpdate() event

Here's my problem. I noticed that if I have one applicant
in frmApplAddr, the Form_AfterUpdate() code works fine,
but if there are two applicants, i.e. two records,
the second record is ignored. What am I doing wrong.

Here is the current checking code in frmApplAddr's
Form_AfterUpdate() event:

'Verify that City/State/ZipCode exist
Dim rs As DAO.Recordset
Dim p As Integer
Dim stCity As String
Dim stState As String
Dim stZipCode As String


p = 0
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
If IsNull(City.Value) Then
stCity = "Bad"
Else
stCity = Trim(City.Value)
End If
If IsNull(State.Value) Then
stState = "Bad"
Else
stState = Trim(State.Value)
End If
If IsNull(ZipCode.Value) Then
stZipCode = "Bad"
Else
stZipCode = Trim(ZipCode.Value)
End If
If stCity = "Bad" Or stState = "Bad" Or stZipCode
= "Bad" Then
MsgBox ("Something is missing")
Else
MsgBox ("Everything looks okay")
p = p + 1
End If
rs.MoveNext
Loop
If p = rs.RecordCount Then
Forms!frmNewSubmission.CredRepReq.Enabled = True
Else
Forms!frmNewSubmission.CredRepReq.Enabled = False
End If
p = 0
Set rs = Nothing
 
Zach,

I think that your basic problem here is that a button on a form can only
have one state. In other words it is either enabled or disabled. If you have
a continuous subform, I don't believe that you can have a button that is
enabled on one record and disabled on the next. This is because it is the
same subform.

Now that I have said that, there may be a way to make it change as the user
goes to the record through enabling or disenabling using the OnCurrent
property of the form. That would mean when the user goes to that subform
record the button will change state at that point.

Gary Miller
 
The button on frmNewSubmission will have only one state, I
just want the changes made on frmApplAddr to determine
that state. So when a person enters city/state/zip
information and closes frmApplAddr, the code I showed
should check all records entered to make sure city/state
zip/information exists. Currently it only checks the first
record, regardless of how many are shown in the form. If I
have Person A with valid city/state/zip, and Person B with
a missing state, my button shows as enabled when really
something is missing.
 
Try either including rs.MoveLast before the rs.MoveFirst line, or just do
rs.MoveLast and work through the recordset from back to front (Do Until .BOF
and .MovePrevious).

HTH,
Bryan
 
Back
Top