Loops

[cksm4]

I need to loop the below code so that it runs on all rows in the subform:

If Me.TblEquipmentInventory_subform!Return = True Then
Me.TblEquipmentInventory_subform!EmployeeID = Null
End If

Any ideas? (I know this is basic... but I am new to VBA and learning) Thanks!

In case ur interested, I am using this in order to allow the user to check a yes/no box next to an item assigned to an employee and delete the employee ID if the item is no longer assigned to them.

[ajetrumpet]

I would check the help articles on the "recordsetclone" property. the other thing too, is that your syntax is incorrect:

Me.TblEquipmentInventory_subform.form!Return

OR

Me.TblEquipmentInventory_subform.form.controls("Return")

[cksm4]

Thanks for your reply! I have looked up the "recordsetclone" property and am still unable to get it to work?? The code will work for the first record but then stops. I need it to continue to the next record in the subform.

[ajetrumpet]

Thanks for your reply! I have looked up the "recordsetclone" property and am still unable to get it to work?? The code will work for the first record but then stops. I need it to continue to the next record in the subform.

if it is working for the first only, then you need to move to last and first to open the whole dataset. Don't ask me why this is necessary, it just is.

rs.movelast
rs.movefirst

does that make sense? Once you get that down, the loop should be fine.

[cksm4]

Thanks for the reply. I think this is way over my level of VBA understanding. Every I have tried returns errors. Do I use a "Do Until .EOF" statement?

[ajetrumpet]

Thanks for the reply. I think this is way over my level of VBA understanding. Every I have tried returns errors. Do I use a "Do Until .EOF" statement?

you stated earlier that whatever you are doing is working on the first record. that's the number one symptom of not using those two lines of code. what you said earlier implies that you are using code already to modify your dataset.

and yes, you are right. in general, to modify all records in a dataset, you do this:

rs.movelast
rs.movefirst

do until rs.eof
     'DO SOMETHING
          rs.movenext
loop

make sense?

[cksm4]

Here is the code I have been using. It still runs on the first record and then stops:

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM TblEquipmentInventory WHERE (TblEquipmentInventory.Return)=True")
Rs.MoveLast
Rs.MoveFirst
Do Until Rs.EOF
If Me.TblEquipmentInventory_subform!Return = True Then
Me.TblEquipmentInventory_subform!EmployeeID = Null
End If
Rs.MoveNext
Loop
DoCmd.Requery

Thanks for your help on this... I really appreciated the quick responses!

[ajetrumpet]

Here is the code I have been using. It still runs on the first record and then stops:

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM TblEquipmentInventory WHERE (TblEquipmentInventory.Return)=True")
Rs.MoveLast
Rs.MoveFirst
Do Until Rs.EOF
If Me.TblEquipmentInventory_subform!Return = True Then
Me.TblEquipmentInventory_subform!EmployeeID = Null
End If
Rs.MoveNext
Loop
DoCmd.Requery

Thanks for your help on this... I really appreciated the quick responses!

ck,

apparently you didn't read the second post. read that again first. your subform control referencing is incorrect. and..are you SURE it's actually working on the first rec? I'm inclined to think it's not working at all and that the first rec field is null to start with, because the syntax is wrong.

[cksm4]

I have tried your correction to the subform control:

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM TblEquipmentInventory WHERE (TblEquipmentInventory.Return)=True")
Rs.MoveLast
Rs.MoveFirst
Do Until Rs.EOF
If Me.TblEquipmentInventory_subform.Form!Return = True Then
Me.TblEquipmentInventory_subform.Form!EmployeeID = Null
End If
Rs.MoveNext
Loop
DoCmd.Requery

I still get the same response. TblEquipmentInventory lists all the items in the inventory. I use the field EmployeeID to assign an item to a employee. I then use the TblEquipmentInventory_Subform to list the equipment assigned to a specific employee (using EmployeeID as the parent/child). I added the "Return" yes/no field to allow a user to check multiple items from the TblEquipmentInventory_Subform that need to be unassigned (returned to inventory). So basically this code needs to find records from TblEquipmentIntentory where the Return field value is Yes... and then delete the EmployeeID from that record.

It works on the first record as I see it disappear from the TblEquipmentInventor_Subform (as the EmployeeID is null and no longer a parent/child relationship to the current record).

Thanks for your time helping me with this.

[ajetrumpet]

upload the file and I'll take a look for ya

give me specific instructions as to what to look for and what to do in order to duplicate the error. I don't have too much time to browse today as I'm working on another project at home.

[cksm4]

No error occurs with the code posted. It just stops after successfully deleting the value of the EmployeeID field in the first record despite there being additional records where the value of the Return field is set to "yes". I want all records with the value of Return = yes to have the value of EmployeeID be null. Do you still want me to upload?

[ajetrumpet]

No error occurs with the code posted. It just stops after successfully deleting the value of the EmployeeID field in the first record despite there being additional records where the value of the Return field is set to "yes". I want all records with the value of Return = yes to have the value of EmployeeID be null. Do you still want me to upload?

yes. otherwise i wouldn't ask. see here on how: https://www.accessforums.net/faq.php...b3_attachments

[cksm4]

Thanks! File attached

[ajetrumpet]

put this behind the button:

Dim rs As DAO.Recordset
Set rs = Me.TblEquipmentInventory_subform.Form.RecordsetClone

With rs
   .MoveLast
   .MoveFirst
      Do Until .EOF
         If !Return = -1 Then
            .Edit
               !EmployeeID = Null
            .Update
         End If
               .MoveNext
      Loop
   .Close
End With

Me.TblEquipmentInventory_subform.Form.Requery
Me.Requery

Set rs = Nothing

take care.

[cksm4]

That did it! Well written and it makes sense now that I see it. Greaty appreciated. Have a good remainder of the weekend