If...Then... looping problem

[CEH]

Well, this should be easy... But I'm doing something wrong and can't find anything in the forum as an example.....
I have a Form for creating an invoice billing for segments of time spent on a project. This form (frmInvoice) has a subform
(frmHoursTimeInvoice) This subform has 2 checkboxes, one for "Billed" (HoursBilled) and one to include unbilled time into the invoice total (InvoiceHours)
So... When you open the form to create an invoice the subform shows only unbilled hours.... as you ckeck the boxes (InvoiceHours) it adds the time to the invoice and calculates the total... So far so good........ Now the problem. I attached a "Close" command button to test this code....
Private Sub cmdCloseSave_Click()
On Error GoTo Err_cmdCloseSave_Click


If [frmHoursTimeInvoice].Form![InvoiceHours] = True Then
[frmHoursTimeInvoice].Form![HoursBilled] = True
Else
If [frmHoursTimeInvoice].Form![InvoiceHours] = False Then
[frmHoursTimeInvoice].Form![HoursBilled] = False
End If
End If
DoCmd.Close

Exit_cmdCloseSave_Click:
Exit Sub

Err_cmdCloseSave_Click:
MsgBox Err.Description
Resume Exit_cmdCloseSave_Click

End Sub

Now the problem is after it marks the first record (HoursBilled) True... it stops executing the code. I know it needs a loop here.... But I cannot find an example that works!! It needs to loop through all of the records in the subform and check the correct boxes True and leave the others False.
So whats the best method? AND the correct syntax! Would a "For each... (something).... Next" work here??
Thanks!

PS.... Sorry for the lengthy explanation

 

[MarkK]

I don't see clearly what the difference is between these two check boxes. Why do you need them both?
Seems a make work project to set them equal to each other all the time, no?

 

[CEH]

Rather hard to explain.... But... The record should not be marked as "HoursBilled" = True until after the record closes. The subform that opens is based on a query of all records where "HoursBilled" = False. The name may be confusing... "InvoiceHours" maybe it should be "Include in Invoice" or "Import to Invoice" The problem with using the "HoursBilled" has been... To make the form update the invoice amount I have had to put a .requery on the AfterUpdate event of the second checkbox......... I know that must sound very confusing
But I am still looking for the syntax on the "For each....Next" ????????

 

[MarkK]

For Each ... Next is used for enumerating the members of a collection or an array and is probably not what you are looking for.
So you have an AfterUpdate handler on the second checkbox? Is this the InvoiceHours checkbox? If so do your work there.
Also noting that ...

If a = true Then
  b = true
Else
  b = false
End If

has the same effect as...

  b = a

so you could do...

Private Sub InvoiceHours_AfterUpdate()
  [COLOR="Green"]'set the value of the check boxes equal to each other and requery[/COLOR]
  Me.HoursBilled = Me.InvoiceHours
  Me.Requery
End Sub

Is this heading in a useful direction for you?

 

[unclejoe]

Hi,

Where is the command button “cmdCloseSave” located?

If it’s on the Main Form, than

Me!frmHoursTimeInvoice.Form!InvoiceHours

Me!frmHoursTimeInvoice.Form!HoursBilled

When referencing forms/controls go here

http://www.mvps.org/access/forms/frm0031.htm

Well, this should be easy... But I'm doing something wrong and
Private Sub cmdCloseSave_Click()
On Error GoTo Err_cmdCloseSave_Click

If [frmHoursTimeInvoice].Form![InvoiceHours] = True Then
[frmHoursTimeInvoice].Form![HoursBilled] = True
Else
If [frmHoursTimeInvoice].Form![InvoiceHours] = False Then
[frmHoursTimeInvoice].Form![HoursBilled] = False
End If
End If
DoCmd.Close

Exit_cmdCloseSave_Click:
Exit Sub

Err_cmdCloseSave_Click:
MsgBox Err.Description
Resume Exit_cmdCloseSave_Click

End Sub

PS.... Sorry for the lengthy explanation

 

[CEH]

For Each ... Next is used for enumerating the members of a collection or an array and is probably not what you are looking for.
So you have an AfterUpdate handler on the second checkbox? Is this the InvoiceHours checkbox? If so do your work there.
Also noting that ...

If a = true Then
  b = true
Else
  b = false
End If

has the same effect as...

  b = a

so you could do...

Private Sub InvoiceHours_AfterUpdate()
  [COLOR="Green"]'set the value of the check boxes equal to each other and requery[/COLOR]
  Me.HoursBilled = Me.InvoiceHours
  Me.Requery
End Sub

Is this heading in a useful direction for you?

Thanks for the response......
There is a problem putting the code on the After update of the "InvoiceHours" checkbox. When you start this form it shows all records where "BilledHours" = False. So if the code is on the After update of the "InvoiceHours" the record no longer appears after you check the box and requery.... and doesn't calculate into the invoice. That is why I am putting it on the Close event of the form. This beginning query is also the reason I had to add the second checkbox. The whole problem is it needs to run thru every record in the subform and set the checkbox properties to.... as you said... A=B. Now, it only does it once, first record on subform, then closes.
My whole problem with this DB has been trying to add something "After the fact" so to speak.... It started just being an application to record time as you worked on a project. That portion works great.... But then I decided to add the Section to produce the invoice.... And started running into problems
I may end up doing a total redesign of this DB....

 

[MarkK]

If you have code somewhere that processes a single record by adding it to the invoice, then flag the item as billed in that process.

What you seem to be doing is
1) user clicks item.
2) item is added to invoice, but is not marked as added
3) (you're stuck here) find the item back to mark it as included in the invoice.

Mark the item as billed in the code in 2)