By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,018 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,018 IT Pros & Developers. It's quick & easy.

Control Reference Question

P: n/a
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 " & _
"BillingInformationSK = " & Me.Parent!SK & " AND ProjectSK = "
& _
Me.Parent!cboProjectSK & ";"

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

' Fill in values on main form
Me.Parent!AmountsPaid = rst!SumAmountPaid
Me.Parent!BalanceDue = Nz(Me.Parent!AmountsPaid) -
Nz(Me.Parent!AmountBilled)

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.

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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*******@netzero.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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 " & _
"BillingInformationSK = " & Me.Parent!SK & " AND ProjectSK = "
& _
Me.Parent!cboProjectSK & ";"

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

' Fill in values on main form
Me.Parent!AmountsPaid = rst!SumAmountPaid
Me.Parent!BalanceDue = Nz(Me.Parent!AmountsPaid) -
Nz(Me.Parent!AmountBilled)

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.

Nov 13 '05 #2

P: n/a
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****************@newssvr30.news.prodigy.co m>...
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*******@netzero.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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 " & _
"BillingInformationSK = " & Me.Parent!SK & " AND ProjectSK = "
& _
Me.Parent!cboProjectSK & ";"

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

' Fill in values on main form
Me.Parent!AmountsPaid = rst!SumAmountPaid
Me.Parent!BalanceDue = Nz(Me.Parent!AmountsPaid) -
Nz(Me.Parent!AmountBilled)

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.

Nov 13 '05 #3

P: n/a
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.NameOfControl_AfterUpdate
or
Forms!NameOfParentForm!NameOfcontrol_AfterUpdate
--
Wayne Morgan
MS Access MVP
"Ecohouse" <vi*******@netzero.com> wrote in message
news:dd**************************@posting.google.c om...
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?

Nov 13 '05 #4

P: n/a
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!AmountsPaid = rst!SumAmountPaid
Me.Parent!BalanceDue = Nz(Me.Parent!AmountsPaid) -
Nz(Me.Parent!AmountBilled)

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****************@newssvr11.news.prodigy.co m>...
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.NameOfControl_AfterUpdate
or
Forms!NameOfParentForm!NameOfcontrol_AfterUpdate
--
Wayne Morgan
MS Access MVP
"Ecohouse" <vi*******@netzero.com> wrote in message
news:dd**************************@posting.google.c om...
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?

Nov 13 '05 #5

P: n/a
This is what I ended up doing:

'Update the AmountsPaid field on the main form
strSQL = "UPDATE BillingInformation SET AmountsPaid = " &
rst!SumAmountPaid & " WHERE " & _
"SK = " & Me.BillingInformationSK & ";"

cnn.Execute strSQL

'Update the BalanceDue field on the main form
strSQL = "UPDATE BillingInformation SET BalanceDue = " & BalanceDue & "
WHERE " & _
"SK = " & Me.BillingInformationSK & ";"

cnn.Execute strSQL

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

Nov 13 '05 #6

P: n/a
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*******@netzero.com> wrote in message
news:dd**************************@posting.google.c om...
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!AmountsPaid = rst!SumAmountPaid
Me.Parent!BalanceDue = Nz(Me.Parent!AmountsPaid) -
Nz(Me.Parent!AmountBilled)

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****************@newssvr11.news.prodigy.co m>...
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.NameOfControl_AfterUpdate
or
Forms!NameOfParentForm!NameOfcontrol_AfterUpdate
--
Wayne Morgan
MS Access MVP
"Ecohouse" <vi*******@netzero.com> wrote in message
news:dd**************************@posting.google.c om...
> 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?

Nov 13 '05 #7

P: n/a
Thanks again for all the help.

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.