For statement problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone indicate what is wrong with my first attempt to write a For loop
as follows:

Private Sub UpdateAll_Click()
Dim myform As Form
Dim frmRst As DAO.Recordset
Dim r As Integer
Dim c As Integer
Dim i As Integer

Set myform = Me.GroupMembers.Form
Set frmRst = Me.GroupMembers.Form.RecordsetClone
r = frmRst.RecordCount


For c = 0 To r - 1

For i = 1 To 23

If Me("Dat" & i) <> 0 Then
Forms!ContactProfile!GroupHead!GroupMembers.Form! _
("Cont" & i) = Me("Dat" & i)
End If
Next i

Next c

End Sub

The code updates the first record; but moves through any remaining records
without updating any data.
 
Rick said:
Can someone indicate what is wrong with my first attempt to write a
For loop as follows:

Private Sub UpdateAll_Click()
Dim myform As Form
Dim frmRst As DAO.Recordset
Dim r As Integer
Dim c As Integer
Dim i As Integer

Set myform = Me.GroupMembers.Form
Set frmRst = Me.GroupMembers.Form.RecordsetClone
r = frmRst.RecordCount


For c = 0 To r - 1

For i = 1 To 23

If Me("Dat" & i) <> 0 Then
Forms!ContactProfile!GroupHead!GroupMembers.Form! _
("Cont" & i) = Me("Dat" & i)
End If
Next i

Next c

End Sub

The code updates the first record; but moves through any remaining
records without updating any data.
Click to expand...

Look up the Move and update commands in help

..movefirst, movenext, update.

Incidentally having fields named Dat0,dat1,dat2, etc implies poor table
design about 99.999% of the time. (And a lot of work even if it is needed.)
 
Is GroupMember the subform that is bound to a table?
If so, it's not sure how the values of the main forms header are used by the
subform.

One other point, to move the recordset on, you are correct in movenext, but
the form will stay at the first record as you are calling it against the
clone. You need to re-synchronising the form datasetclone with the clone

myform.BookMark = frmRST.Bookmark
 
See comments in the code
Rick said:
Mike:

Thanks for the response. Actually the "Dat1", "Dat2" etc. are unbound
controls on the form header used to supply the data only for the sub
form updates. Any event, I have tried using the move and update
commands in the code as follows without success. The first record
continues to be updated as expected but the remaining ones are
unchanged. Any suggestion on what I am doing wrong?

frmRst.MoveFirst

Do While Not frmRst.EOF
Click to expand...

The purpose of the With statement is to allow use of
..edit instead of frmRst.Edit, etc
It is meant to save typing.

With frmRst
Click to expand...
'you are not editing, but adding new records
.Edit .addnew

For i = 1 To 23
Click to expand...

Here you are just updating the contents of the current record on the form
If Me("Dat" & i) <> 0 Then
Forms!ContactProfile!GroupHead!GroupMembers.Form! _
("Cont" & i) = Me("Dat" & i)
Click to expand...
' you must update teh contents of the RECORDSET frmRst

!("cont" & i) = Me("Dat" & i)
End If
Click to expand...
Next i

.Update
.MoveNext
End With

Loop
Click to expand...

This still updates a single record with multiple fields cont(x) and does
not relatds those fields to the master table.

Proper relational design demands updating multible records with a single
field named contWhatever as well as the ID of the "header" field.

It would also remove the need for placing unbound fields on the master form
and updating in code. It should be just a matter of adding records to the
subform.
 
Thanks. The help from yourself and others has got me to where I wanted to
go. There are two points I should clarify:

1. I am not adding records; just editing existing Perhaps my original
explanation was unclear.

2. I have changed the reference to
Set frmRst = Me.GroupMembers.Form.RecordsetClone
to
Set frmRst = Me.GroupMembers.Form.Recordset and removed the bookmark
reference.

Hope these change are not significant and they does not appear to be. I
have tested the routine out thoroughly and it runs correctly and
uneventfully. At the risk of presenting too much information here's the
completed code. If you notice anything out of order please advise.
Otherwise; thanks for you help once again.

Private Sub UpdateAll_Click()
Dim myform As Form
Dim frmRst As DAO.Recordset
Dim r As Integer
Dim i As Integer

Set myform = Me.GroupMembers.Form
Set frmRst = Me.GroupMembers.Form.Recordset
r = frmRst.RecordCount

On Error GoTo Err_NextRecord

If MsgBox("Are you sure you wish to update ALL family member's data?" _
& vbCr & vbCr & "If you continue all data for each family member will be
reset to" _
& vbCr & "match the data entered in the top screen." & vbCr & vbCr _
& "Any blank fields in the top screen will NOT overwrite existing " &
vbCr & _
"data for any family member below.", 276, _
CurrentUser()) = 7 Then
Cancel = True
Exit Sub
End If

frmRst.MoveFirst

Do While Not frmRst.EOF
With frmRst

For i = 1 To 23

If Me("Dat" & i) <> 0 Then
Forms!ContactProfile!GroupHead!GroupMembers.Form! _
("Cont" & i) = Me("Dat" & i)
End If

Next i
.Edit
.Update

.MoveNext
End With

Loop

Exit_NextRecord:
Cancel = True
Exit Sub
DoCmd.Close acForm, "GroupHead"

Err_NextRecord:
Response = acDataErrContinue
If Err.Number = 3021 Then
Resume Exit_NextRecord
End If
If Err.Number = 3020 Then
Cancel = True
DoCmd.Close acForm, "GroupHead"
End If
MsgBox Err.Number & " " & Err.Description
End Sub
 
Rick said:
Hey Steve:

Thanks very much for your input. Didn't realize how rough my code was and
the suggestions are well taken. Applied the changes your recommended and
noticed the code does run much faster. This is a long learning curve for me
with only about 10 days training total in Access and VBA a few years ago.
Only now starting to write some "real" code and the help from the newsgroup
is invaluable.

Rick in N S
Click to expand...

Then you are progressing pretty fast. It took me months to start to
figure out Access97 - I spent many years in the XBase world (dBase IV).

Even now I look at my code from a year ago and (smacking my head) wonder
"What was I thinking?". <bg>
 
Back
Top