Control Reference Question

[vindaloo1]

I'm using Access 2000 and I have a main form and a subform. When a
control on the subform is updated I am trying to update a control on
the mainform. But instead of updating the control on the main form's
current record, it updates the main form's control on every record.

This is the code I'm using for this:
Set cnn = CurrentProject. Connection

strSQL = "SELECT SUM(AmountPaid) AS SumAmountPaid FROM BillPayments
WHERE " & _
"BillingInforma tionSK = " & Me.Parent!SK & " AND ProjectSK = "
& _
Me.Parent!cboPr ojectSK & ";"

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimisti c

' Fill in values on main form
Me.Parent!Amoun tsPaid = rst!SumAmountPa id
Me.Parent!Balan ceDue = Nz(Me.Parent!Am ountsPaid) -
Nz(Me.Parent!Am ountBilled)

So my question is how can I make this code only modify the controls on
the current record in the main form?

Any help would be appreciated.

[Wayne Morgan]

Are AmountsPaid and BalanceDue bound controls? If not, then they will hold
the value you enter until you clear the value or otherwise change it. You
may want to do this in the Current event of the form so that it will change
when you move from one record to the next.

Is the main form in Continuous Form View or Datasheet View? Also, if the
controls aren't bound, you may want to use DSum to get a value for
AmountsPaid and just requery the control when you update the subform's
control. The DSum equation would go in AmountsPaid's Control Source. Since
BalanceDue is based on the value of two other controls on the main form, you
could also use a calculation in its Control Source that should update itself
automatically as the other two controls are updated. If not, issue a ReCalc
to the BalanceDue to force it to update.

--
Wayne Morgan
MS Access MVP
<vi*******@netz ero.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I'm using Access 2000 and I have a main form and a subform. When a
control on the subform is updated I am trying to update a control on
the mainform. But instead of updating the control on the main form's
current record, it updates the main form's control on every record.

This is the code I'm using for this:
Set cnn = CurrentProject. Connection

strSQL = "SELECT SUM(AmountPaid) AS SumAmountPaid FROM BillPayments
WHERE " & _
"BillingInforma tionSK = " & Me.Parent!SK & " AND ProjectSK = "
& _
Me.Parent!cboPr ojectSK & ";"

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimisti c

' Fill in values on main form
Me.Parent!Amoun tsPaid = rst!SumAmountPa id
Me.Parent!Balan ceDue = Nz(Me.Parent!Am ountsPaid) -
Nz(Me.Parent!Am ountBilled)

So my question is how can I make this code only modify the controls on
the current record in the main form?

Any help would be appreciated.

[Ecohouse]

They are unbound controls. The main form is in Form view.

I know that if I put formulas in the controls that works. But I was
trying to get some code to run after updating these controls. And
being that they were using calculations and weren't bound I couldn't
get the code to fire.

So is there a way to get code to run on these unbound controls after
they are updated from the subform?

Thanks for the help.
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message news:<sG******* *********@newss vr30.news.prodi gy.com>...

Are AmountsPaid and BalanceDue bound controls? If not, then they will hold > the value you enter until you clear the
value or otherwise change it. You may want to do this in the Current event of the form so that it will change
when you move from one record to the next.

Is the main form in Continuous Form View or Datasheet View?

Also, if the controls aren't bound, you may want to use DSum to get a value for
AmountsPaid and just requery the control when you update the subform's
control. The DSum equation would go in AmountsPaid's Control Source. Since
BalanceDue is based on the value of two other controls on the main form, you
could also use a calculation in its Control Source that should update itself
automatically as the other two controls are updated. If not, issue a ReCalc
to the BalanceDue to force it to update.

--
Wayne Morgan
MS Access MVP
<vi*******@netz ero.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I'm using Access 2000 and I have a main form and a subform. When a
control on the subform is updated I am trying to update a control on
the mainform. But instead of updating the control on the main form's
current record, it updates the main form's control on every record.

This is the code I'm using for this:
Set cnn = CurrentProject. Connection

strSQL = "SELECT SUM(AmountPaid) AS SumAmountPaid FROM BillPayments
WHERE " & _
"BillingInforma tionSK = " & Me.Parent!SK & " AND ProjectSK = "
& _
Me.Parent!cboPr ojectSK & ";"

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimisti c

' Fill in values on main form
Me.Parent!Amoun tsPaid = rst!SumAmountPa id
Me.Parent!Balan ceDue = Nz(Me.Parent!Am ountsPaid) -
Nz(Me.Parent!Am ountBilled)

So my question is how can I make this code only modify the controls on
the current record in the main form?

Any help would be appreciated.

[Wayne Morgan]

From the help file, "Changing data in a control by using Visual Basic or a
macro containing the SetValue action doesn't trigger these events for the
control. However, if you then move to another record or save the record, the
form's AfterUpdate event does occur."

So, this means that if you change the value of the control using code, the
AfterUpdate event for that control doesn't fire. However, you can place the
code you would have run in the AfterUpdate event in the code that changed
the value of the control or you can place the code in a procedure (including
the control's AfterUpdate event) that can be called from the code that
changed the value. If you use the AfterUpdate event for this code, you may
need to change its header line from "Private Sub" to "Public Sub". You
should the be able to call if from the subform as:

Me.Parent.NameO fControl_AfterU pdate
or
Forms!NameOfPar entForm!NameOfc ontrol_AfterUpd ate
--
Wayne Morgan
MS Access MVP
"Ecohouse" <vi*******@netz ero.com> wrote in message
news:dd******** *************** ***@posting.goo gle.com...

They are unbound controls. The main form is in Form view.

I know that if I put formulas in the controls that works. But I was
trying to get some code to run after updating these controls. And
being that they were using calculations and weren't bound I couldn't
get the code to fire.

So is there a way to get code to run on these unbound controls after
they are updated from the subform?

[Ecohouse]

Wayne,

Thanks again for the help. What you wrote in your last post is what I
had wound up doing.

But going back to my original question about why all the controls in
the main form were updating when I did the following:

Me.Parent!Amoun tsPaid = rst!SumAmountPa id
Me.Parent!Balan ceDue = Nz(Me.Parent!Am ountsPaid) -
Nz(Me.Parent!Am ountBilled)

There is no way to reference a control on the main on a particular
record form from the subform?

Thanks again for the help.
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message news:<gb******* *********@newss vr11.news.prodi gy.com>...

From the help file, "Changing data in a control by using Visual Basic or a
macro containing the SetValue action doesn't trigger these events for the
control. However, if you then move to another record or save the record, the
form's AfterUpdate event does occur."

So, this means that if you change the value of the control using code, the
AfterUpdate event for that control doesn't fire. However, you can place the
code you would have run in the AfterUpdate event in the code that changed
the value of the control or you can place the code in a procedure (including
the control's AfterUpdate event) that can be called from the code that
changed the value. If you use the AfterUpdate event for this code, you may
need to change its header line from "Private Sub" to "Public Sub". You
should the be able to call if from the subform as:

Me.Parent.NameO fControl_AfterU pdate
or
Forms!NameOfPar entForm!NameOfc ontrol_AfterUpd ate
--
Wayne Morgan
MS Access MVP
"Ecohouse" <vi*******@netz ero.com> wrote in message
news:dd******** *************** ***@posting.goo gle.com...

They are unbound controls. The main form is in Form view.

I know that if I put formulas in the controls that works. But I was
trying to get some code to run after updating these controls. And
being that they were using calculations and weren't bound I couldn't
get the code to fire.

So is there a way to get code to run on these unbound controls after
they are updated from the subform?

[Ecohouse]

This is what I ended up doing:

'Update the AmountsPaid field on the main form
strSQL = "UPDATE BillingInformat ion SET AmountsPaid = " &
rst!SumAmountPa id & " WHERE " & _
"SK = " & Me.BillingInfor mationSK & ";"

cnn.Execute strSQL

'Update the BalanceDue field on the main form
strSQL = "UPDATE BillingInformat ion SET BalanceDue = " & BalanceDue & "
WHERE " & _
"SK = " & Me.BillingInfor mationSK & ";"

cnn.Execute strSQL

This runs after a control on the subform updates. I know it's a bit
convoluted but it works.

[Wayne Morgan]

An unbound control doesn't belong to any particular record (it isn't bound),
so when you set the value in the unbound control it is set of all records.
You have to take over the job Access does with bound controls and update it
yourself when you move from record to record.

--
Wayne Morgan
MS Access MVP
"Ecohouse" <vi*******@netz ero.com> wrote in message
news:dd******** *************** ***@posting.goo gle.com...

Wayne,

Thanks again for the help. What you wrote in your last post is what I
had wound up doing.

But going back to my original question about why all the controls in
the main form were updating when I did the following:

Me.Parent!Amoun tsPaid = rst!SumAmountPa id
Me.Parent!Balan ceDue = Nz(Me.Parent!Am ountsPaid) -
Nz(Me.Parent!Am ountBilled)

There is no way to reference a control on the main on a particular
record form from the subform?

Thanks again for the help.
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in
message news:<gb******* *********@newss vr11.news.prodi gy.com>...

From the help file, "Changing data in a control by using Visual Basic or
a
macro containing the SetValue action doesn't trigger these events for the
control. However, if you then move to another record or save the record,
the
form's AfterUpdate event does occur."

So, this means that if you change the value of the control using code,
the
AfterUpdate event for that control doesn't fire. However, you can place
the
code you would have run in the AfterUpdate event in the code that changed
the value of the control or you can place the code in a procedure
(including
the control's AfterUpdate event) that can be called from the code that
changed the value. If you use the AfterUpdate event for this code, you
may
need to change its header line from "Private Sub" to "Public Sub". You
should the be able to call if from the subform as:

Me.Parent.NameO fControl_AfterU pdate
or
Forms!NameOfPar entForm!NameOfc ontrol_AfterUpd ate
--
Wayne Morgan
MS Access MVP
"Ecohouse" <vi*******@netz ero.com> wrote in message
news:dd******** *************** ***@posting.goo gle.com...

> They are unbound controls. The main form is in Form view.
>
> I know that if I put formulas in the controls that works. But I was
> trying to get some code to run after updating these controls. And
> being that they were using calculations and weren't bound I couldn't
> get the code to fire.
>
> So is there a way to get code to run on these unbound controls after
> they are updated from the subform?

[Ecohouse]

Thanks again for all the help.