Loop Construction

[radek225]

I want to copy value from Form to subform (master filed and child field is "ID_DTP/CTP"). The problem is, that subform (Datasheet) has many rows and Ms Access with my last code past value only to first row. So I thought that there's should be loop, but I never use loop before. Now when I click button, Ms Access crashes:/

Form "frmDTP/CTP"
Subform "frm_cyfra"

value which I want to copy - from me.imie to Forms![frmDTP/CTP].Form!frm_cyfra!Id_pracownicy

If Not Len(Nz(Forms![frmdtp/ctp].Form!frm_cyfra!ID_cyfra)) = 0 Then

Do
Forms![frmdtp/ctp].Form!frm_cyfra!Id_Pracownicy = Me!Imie
Loop Until Forms![frmdtp/ctp].Form!frm_cyfra![ID_DTP/CTP] <> Me![ID_DTP/CTP]

End If

What did I wrong?

 

[pr2-eugin]

I am a bit lost, if they are Master Child, then should the column not be entered already?

 

[radek225]

Form "frmDTP/CTP" is a Single form
subform "frm_cyfra" is a Datasheet

Master/child field is "ID_DTP/CTP" and this is automatically entered
but I want to copy me.imie form form and past it to every row in subform using button

 

[James Dudden]

You need to loop through the recordset so the code would look something like this:

If Not Len(Nz(Forms![frmdtp/ctp].Form!frm_cyfra!ID_cyfra)) = 0 Then
 
with Me!frm_cyfra.form.recordsetclone
 if .recordcount <> 0 then
  .movefirst
  do until .eof
   .edit
   !Id_Pracownicy = Me!Imie
   .update
  .movenext
  .loop
 
 end if
end with
 
End If

 

[pr2-eugin]

Something along the lines of..

Private Sub updateSubFormBtn_Click()
    Dim rsObj As DAO.RecordSet
    Set rsObj = Me!Subform1.Form.RecordSource
    Do While Not rsObj.EOF
        rsObj.Edit
        rsObj.Fields("theFieldYouWantToUpdate") = "someNewValue"
        rsObj.Update
        rsObj.MoveNext
    Loop
    Forms![MainForm]![Subform1].Requery
    Forms![MainForm]![Subform1].Refresh
End Sub

NOTE: Air coded. Not tested.

 

[radek225]

James - End if without block if error. What is .eof?
pr2-eugin
about second line - error "object required"

Dim rsObj As DAO.Recordset
    Set rsObj = Me![frm_cyfra].Form.RecordSource
    Do While Not rsObj.EOF
        rsObj.Edit
        rsObj.Fields("id_pracownicy") = Me.Imie
        rsObj.Update
        rsObj.MoveNext
    Loop
    Forms![frmdtp/ctp]![frm_cyfra].Requery
    Forms![frmdtp/ctp]![frm_cyfra].Refresh

 

[pr2-eugin]

Hmmm.. Which line exactly is the error?

Try RecordsetClone

 

[radek225]

This line "Set rsObj = Me![frm_cyfra].Form.RecordSource"

 

[pr2-eugin]

Is frm_cyfra the name of the Subform? Use this link to construct the statement.. http://www.btabdevelopment.com/ts/ewtrhtrts

Did you try RecordSetClone as mentioned?

 

[radek225]

yes it is name. I tried what James Dudden wrote, and there's error "end if without block if"

 

[pr2-eugin]

No what I mentioned was in my code instead of RecordSource use RecordsetClone

 

[radek225]

Ok I tried:

Dim rsObj As DAO.Recordset
    Set rsObj = Me![frm_cyfra].Form.RecordsetClone
    Do While Not rsObj.EOF
        rsObj.Edit
        rsObj.Fields("id_pracownicy") = Me.Imie
        rsObj.Update
        rsObj.MoveNext
    Loop
    Forms![frmdtp/ctp]![frm_cyfra].Requery
    'Forms![frmdtp/ctp]![frm_cyfra].Refresh

I have to "'" in last line because Ms Access had problem with it, It was refreshed by me, but the code doesn't work, nothing happens

 

[pr2-eugin]

Okay put a stripped DB

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[radek225]

Ok, now is working. That was error which was described in your link pr2-eugin (but not about subform, it's about field in subform).
Thank you very much pr2-eugin!!!
"id_pracownicy" - name
"id_pracownika" - control source

BTW What is ".eof"?

 

[Pat Hartman]

I realize I'm coming late to this discussion but you probably shouldn't be copying the data at all. What you are doing violates second normal form and leaves your table open to data anomalies.

When every row in the child table must contain the same value in columnA (except for the FK column), then columnA belongs in the parent table.

Simply copying the value to the child table with this loop doesn't solve your problem. What happens when a new record is added?

Applications have far fewer problems and are far easier to maintain if they adhere to good practice principles. What you are doing is quite the opposite. It is poor practice.