For statement problem

R

Rick in NS

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.
 
M

Mike Painter

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.)
 
J

JohnFol

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
 
M

Mike Painter

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.
 
R

Rick in NS

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
 
S

SteveS

***** snip *******
If you notice anything out of order please advise.
Click to expand...
***** snip *******

Rick,

I've been following your thread; I looked at the code and added some
comments. These are only my opinion - no criticism intended (The MVP's
would probably take away my keyboard if they looked at some of my coding
<g>)

This looked better in the IDE - the comments were in green

'****** begin code ***********
Private Sub UpdateAll_Click()
Dim frmRst As DAO.Recordset
Dim i As Integer
Dim msg As String '*** Added
Dim btns As Long '*** Added

'These can be deleted - they are not used
'Dim myform As Form
'Dim r As Integer
'Set myform = Me.GroupMembers.Form
'r = frmRst.RecordCount

Set frmRst = Me.GroupMembers.Form.Recordset

On Error GoTo Err_NextRecord

' this just makes the code easier to read
msg = "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."
'*** Added - easier to see what buttons are used
btns = vbDefaultButton2 + vbCritical + vbYesNo

If MsgBox(msg, btns, CurrentUser()) = vbNo Then ' vbNo = 7 ****
'You can't cancel the Click event *****
'Cancel = True
Exit Sub
End If

frmRst.MoveFirst

'***CHANGED
' moved "WITH frmRst...END WITH" outside the loop.
' Only have to resolve the reference once which is
'faster - not 23 times
With frmRst
Do While Not .EOF
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
'if the code works then the next 2 lines are not needed
' and can be deleted. See the bottom of the code
'.Edit
'.Update

.MoveNext
Loop
End With

Exit_NextRecord:
'**** You can't cancel the Click event ***
'Cancel = True
Exit Sub
DoCmd.Close acForm, "GroupHead"

Err_NextRecord:
Response = acDataErrContinue

'*** CHANGED the following IF()
If Err.Number = 3021 Then
Resume Exit_NextRecord
ElseIf Err.Number = 3020 Then
'**** You can't cancel the Click event ***
'Cancel = True
DoCmd.Close acForm, "GroupHead"
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub
'*********** end code ************


'Like Mike Painter posted, When using a DAO recordset,
' the fields in a record are changed like this:

With rs
.Edit
!FieldName1 = Me.Textbox1OnForm
!DateField = Date
!UserNameField = CurrentUser()
.Update
End With


HTH
 
R

Rick in NS

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
 
S

SteveS

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>
 
Top