Help With Looping

[LaurieW]

I have a form where the user will do data entry. This form also contains a subform.

I have a long If/End If statement that checks the user's data entry for errors. I then want to look at the subform to check to see if the user's data entry conflicts with the data on the subform. I can get my statements to look at the first record in the subform, but am having trouble looking at all the records in the subform. I also need to check for multiple conditions on the subform.

I've looked at other posts and tried many things, but am unsure of the correct procedure. Do I use Do/Loop or For statement? If anyone can help, it would much appreciated. Below is a simplified version of what I have. Thanks, Laurie

Dim StrMsg As String
StrMsg = "The check out information has been changed."
StrMsg = StrMsg & " Do you want to save the changes?"

If MsgBox(StrMsg, vbYesNo, "") = vbNo Then
'DoCmd.RunCommand acCmdUndo
Me.Undo
DoCmd.Close
Else
If IsNull(Me.DateOut) Then
Cancel = True
MsgBox "Message Here", vbExclamation, "Equipment Check Out"
Me.DateOut.SetFocus
ElseIf IsNull(Me.TimeOut) Then
Cancel = True
MsgBox "Message Here", vbExclamation, "Equipment Check Out"
Me.TimeOut.SetFocus
ElseIf IsNull(Me.DateProjectedReturn) Then

... (more if statements here to check data entry on main form)

... (here is where I need to check the subform records for different conditions)
ElseIf 1 check all subform records for 1st condition Then
ElseIf 2 check all subform records for 2nd condition Then
ElseIf 3 check all subform records for 3rd condition Then

 

[boblarson]

You don't need to use ElseIF for most of what you are doing. Just use IF's as it is cleaner and easier to read through. You only need an ElseIF if the condition is truly more than an If...Then...Else and separate If's won't do it for you:

If MsgBox(StrMsg, vbYesNo, "") = vbNo Then
   'DoCmd.RunCommand acCmdUndo
   Me.Undo
   DoCmd.Close
Else
   If IsNull(Me.DateOut) Then
     Cancel = True 
     MsgBox "Message Here", vbExclamation, "Equipment Check Out"
     Me.DateOut.SetFocus
     Exit Sub   
   End If

   If IsNull(Me.TimeOut) Then
      Cancel = True
      MsgBox "Message Here", vbExclamation, "Equipment Check Out"
      Me.TimeOut.SetFocus
      Exit Sub
   End If

   If IsNull(Me.DateProjectedReturn) Then
      etc...
   End If

   If Me.YourTextBoxOnMainForm <> = Forms!YourMainFormNameHere.YourSubFormContainerNameHere.Form.YourTextBoxNameHere Then
      Do stuff here...
   End If

A good reference for subform syntax is here:
http://www.mvps.org/access/forms/frm0031.htm

Also, when the code sample I have says YourSubformCONTAINERNameHere, it is meaning the name of the container on the main form that houses the subform. It CAN be the same name as the subform, but it isn't necessarily (the wizard gives you the opportunity to name it differently when you add the subform). It is important to know it's name as that is what is referred to when you see subform listed in my code and also in the reference material at that link I provided.

 

[LaurieW]

Thanks for your reply, but my problem is not the syntax for referring to the subform, rather how do I loop through the records in the subform? I can get to the subform just fine, but it only looks at the first record. I need it to look at all the records in the subform for each condition.

Thanks...L.

 

[LaurieW]

Looping thru subform records

Does anyone have any helpful hints for me on how to loop thru subform records?

 

[Rich]

Unless the subform's unbound your code seems to be pointless, with respect to checking multiple records

 

[LaurieW]

The subform needs to show only records for the item on screen, so needs to be linked. I'm not sure what this has to do with looping through these records?

 

[chergh]

dim rs as recordset


set rs = db.openrecordset(rs_name_as_string)
rs.movefirst
do while not rs.eof
check all subform records for 1st condition
check all subform records for 2nd condition
.
.
.
rs.movenext

Loop

You should have a query somewhere for you subform that pulls out the associated records with your main record. It's that query that you put where I have rs_name_as_string. Once you have done that and added your criteria it should work.

 

[LaurieW]

Thank you so much for your assistance; I'll give that a try.

 

[Rich]

Like I said, what's the point of this, using the open recordset as posted means the records have already been saved, there is no Undo option for that or record validation

 

[LaurieW]

The point is I need to check the records in the subform against the record on the main form to see if there is a conflict.

 

[chergh]

Like I said, what's the point of this, using the open recordset as posted means the records have already been saved, there is no Undo option for that or record validation

Well if you couldn't change the record once it had been saved then you would have a point but it can be changed so you don't have a point.

 

[boblarson]

The point is I need to check the records in the subform against the record on the main form to see if there is a conflict.

If that can occur then I propose that you haven't designed your database correctly. What could conflict?

 

[neileg]

Sureley it would be easier to capture the user input in unbound controls, one subrecord at a time, validate the data and only save if the validation is passed. Saving invalid data sounds like a bad idea to me.