autosum to a txtbox

[pato.jamriska]

Hi,
I been reading quite a lot the boards here and I believe I might either
need directions or simply the wise word of someone who has been
programming in vba for a while.

My problem I believe is really easy to solve but until now, I been
strugling on finding a solution.

I want to change a macro that sums the fields of 2 colums and displays
the answer in a text box. An example is on the inventory tracking
database provided by access 2003.

I would like to produce the same result but by using VBA. I understand
the use of macros for this case is easier, but this is more for the
learning purpose. If anyone out there has the answer, I thank you for
your time in hearing my request.

with my best regards,

Patrick the novice programmer.

 

[Douglas J. Steele]

Access has the ability to convert macros into VBA.

Right-click on the macro, choose Save As from the context-sensitive menu,
and save as a Module. Once you've done that, check the code that it
generated.

 

[Patrick J.]

Dear Douglas,

Thank you for your quick response, I was able to save the macro in that
way, but I still encounter the same problem, the field still stays
empty. Initially the maro was the control source of the etxt box, I
don't know if this might be the cause.

Thanks again for your help,

Patrick

 

[BruceM]

I believe the VBA procedure needs to be associated with an event in order to
run. In order for the text box to display the calculation when you navigate
to a record, the procedure would be in the form's Current event. To display
the calculation when one of the fields you are adding together is changed
the procedure needs to be in the After Update event of each of the text
boxes bound to those fields.
I may be missing the point of your question, but you may be overcomplicating
this. One way to add two fields in an unbound text box is to set the
control source of the text box to =[Field1] + [Field2]

 

[Patrick J.]

Bruce,

I had it working with the control source, but I want to implement it
into code. The reason why is because I would like to have it all behind
in code, and I don't know if that way it would be easier to implement,
as later on we will have all tables and qry's on sql. Either way, for
some weird reason i get now a #error when I use the control source
mehod. Any ideas?
I will check the reply later, I am heading to bed for now.

Thanks guys.

Patrick

I believe the VBA procedure needs to be associated with an event in order to
run. In order for the text box to display the calculation when you navigate
to a record, the procedure would be in the form's Current event. To display
the calculation when one of the fields you are adding together is changed
the procedure needs to be in the After Update event of each of the text
boxes bound to those fields.
I may be missing the point of your question, but you may be overcomplicating
this. One way to add two fields in an unbound text box is to set the
control source of the text box to =[Field1] + [Field2]

Dear Douglas,

Thank you for your quick response, I was able to save the macro in that
way, but I still encounter the same problem, the field still stays
empty. Initially the maro was the control source of the etxt box, I
don't know if this might be the cause.

Thanks again for your help,

Patrick

 

[BruceM]

You had what "working with the control source"? The macro? What do you
mean "have it all behind in code"? Adding the two fields, either as I have
suggested or in a calculated query field, is transparent to the user.
What do you mean by "later on we will have all tables and qry's on sql"?
Are you talking about SQL server? That won't affect code or expressions in
the front end. How are you attempting to use a VBA procedure as a control
source? What is the code in the VBA procedure?
If you want to use VBA, you could start by summing the fields in a procedure
in the form's Current event.

Me.txtSumFields = Me.Field1 + Me.Field2

where txtSumFields is an unbound text box on your form, and Field1 and
Field2 are the fields you want to add together.
The form's Current event runs only when you navigate to a new record (or
when you first open the form, or when you requery the record source). This
is fine for viewing the sum for a completed record, but it won't change the
value in txtSumFields when you enter an amount into Field1 or Field2. In
order to see the sum as soon as you change the value in Field1 or Field2 you
would need to have the same procedure in the After Update event of the
controls (text boxes) for Field1 and Field2. You could also put the event
in the Click event of a command button, or whatever suits you. One way to
accomplish this is to create a public procedure or function and call it as
needed.

Or you could set the control source of an unbound text box to:
=Field1 + Field2
and go on to other projects.

Bruce,

I had it working with the control source, but I want to implement it
into code. The reason why is because I would like to have it all behind
in code, and I don't know if that way it would be easier to implement,
as later on we will have all tables and qry's on sql. Either way, for
some weird reason i get now a #error when I use the control source
mehod. Any ideas?
I will check the reply later, I am heading to bed for now.

Thanks guys.

Patrick

I believe the VBA procedure needs to be associated with an event in order
to
run. In order for the text box to display the calculation when you
navigate
to a record, the procedure would be in the form's Current event. To
display
the calculation when one of the fields you are adding together is changed
the procedure needs to be in the After Update event of each of the text
boxes bound to those fields.
I may be missing the point of your question, but you may be
overcomplicating
this. One way to add two fields in an unbound text box is to set the
control source of the text box to =[Field1] + [Field2]

Dear Douglas,

Thank you for your quick response, I was able to save the macro in that
way, but I still encounter the same problem, the field still stays
empty. Initially the maro was the control source of the etxt box, I
don't know if this might be the cause.

Thanks again for your help,

Patrick


Douglas J. Steele wrote:
Access has the ability to convert macros into VBA.

Right-click on the macro, choose Save As from the context-sensitive
menu,
and save as a Module. Once you've done that, check the code that it
generated.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,
I been reading quite a lot the boards here and I believe I might
either
need directions or simply the wise word of someone who has been
programming in vba for a while.

My problem I believe is really easy to solve but until now, I been
strugling on finding a solution.

I want to change a macro that sums the fields of 2 colums and
displays
the answer in a text box. An example is on the inventory tracking
database provided by access 2003.

I would like to produce the same result but by using VBA. I
understand
the use of macros for this case is easier, but this is more for the
learning purpose. If anyone out there has the answer, I thank you
for
your time in hearing my request.

with my best regards,

Patrick the novice programmer.

 

[Patrick J.]

Dear Bruce,
all what you have asked me, and stated is what I am doing. I have the
project done where a macro is used in the control source of the control
box. The problem I have with it is that it returns to me a #error once
the fields are added. I thought it was a but so I decided to try it on
code, but with no luck on it. I will try the line of code that you
sugest in a function and see if it works, because last time I tried it
it returned no value at all.

thank you for all the help.

You had what "working with the control source"? The macro? What do you
mean "have it all behind in code"? Adding the two fields, either as I have
suggested or in a calculated query field, is transparent to the user.
What do you mean by "later on we will have all tables and qry's on sql"?
Are you talking about SQL server? That won't affect code or expressions in
the front end. How are you attempting to use a VBA procedure as a control
source? What is the code in the VBA procedure?
If you want to use VBA, you could start by summing the fields in a procedure
in the form's Current event.

Me.txtSumFields = Me.Field1 + Me.Field2

where txtSumFields is an unbound text box on your form, and Field1 and
Field2 are the fields you want to add together.
The form's Current event runs only when you navigate to a new record (or
when you first open the form, or when you requery the record source). This
is fine for viewing the sum for a completed record, but it won't change the
value in txtSumFields when you enter an amount into Field1 or Field2. In
order to see the sum as soon as you change the value in Field1 or Field2 you
would need to have the same procedure in the After Update event of the
controls (text boxes) for Field1 and Field2. You could also put the event
in the Click event of a command button, or whatever suits you. One way to
accomplish this is to create a public procedure or function and call it as
needed.

Or you could set the control source of an unbound text box to:
=Field1 + Field2
and go on to other projects.

Bruce,

I had it working with the control source, but I want to implement it
into code. The reason why is because I would like to have it all behind
in code, and I don't know if that way it would be easier to implement,
as later on we will have all tables and qry's on sql. Either way, for
some weird reason i get now a #error when I use the control source
mehod. Any ideas?
I will check the reply later, I am heading to bed for now.

Thanks guys.

Patrick

I believe the VBA procedure needs to be associated with an event in order
to
run. In order for the text box to display the calculation when you
navigate
to a record, the procedure would be in the form's Current event. To
display
the calculation when one of the fields you are adding together is changed
the procedure needs to be in the After Update event of each of the text
boxes bound to those fields.
I may be missing the point of your question, but you may be
overcomplicating
this. One way to add two fields in an unbound text box is to set the
control source of the text box to =[Field1] + [Field2]

Dear Douglas,

Thank you for your quick response, I was able to save the macro in that
way, but I still encounter the same problem, the field still stays
empty. Initially the maro was the control source of the etxt box, I
don't know if this might be the cause.

Thanks again for your help,

Patrick


Douglas J. Steele wrote:
Access has the ability to convert macros into VBA.

Right-click on the macro, choose Save As from the context-sensitive
menu,
and save as a Module. Once you've done that, check the code that it
generated.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,
I been reading quite a lot the boards here and I believe I might
either
need directions or simply the wise word of someone who has been
programming in vba for a while.

My problem I believe is really easy to solve but until now, I been
strugling on finding a solution.

I want to change a macro that sums the fields of 2 colums and
displays
the answer in a text box. An example is on the inventory tracking
database provided by access 2003.

I would like to produce the same result but by using VBA. I
understand
the use of macros for this case is easier, but this is more for the
learning purpose. If anyone out there has the answer, I thank you
for
your time in hearing my request.

with my best regards,

Patrick the novice programmer.

 

[BruceM]

I did not suggest that you use a macro as a control source. I would not use
a macro at all. A VBA procedure and a macro are two different things.
What happens if you set the control source of an unbound text box to:
=[Field1] + [Field2]
Use your actual field names, of course. If you get the correct result, you
could just stop there, but if not you need to sort out why the fields are
not adding properly. Are they both Number fields?

Dear Bruce,
all what you have asked me, and stated is what I am doing. I have the
project done where a macro is used in the control source of the control
box. The problem I have with it is that it returns to me a #error once
the fields are added. I thought it was a but so I decided to try it on
code, but with no luck on it. I will try the line of code that you
sugest in a function and see if it works, because last time I tried it
it returned no value at all.

thank you for all the help.

You had what "working with the control source"? The macro? What do you
mean "have it all behind in code"? Adding the two fields, either as I
have
suggested or in a calculated query field, is transparent to the user.
What do you mean by "later on we will have all tables and qry's on sql"?
Are you talking about SQL server? That won't affect code or expressions
in
the front end. How are you attempting to use a VBA procedure as a
control
source? What is the code in the VBA procedure?
If you want to use VBA, you could start by summing the fields in a
procedure
in the form's Current event.

Me.txtSumFields = Me.Field1 + Me.Field2

where txtSumFields is an unbound text box on your form, and Field1 and
Field2 are the fields you want to add together.
The form's Current event runs only when you navigate to a new record (or
when you first open the form, or when you requery the record source).
This
is fine for viewing the sum for a completed record, but it won't change
the
value in txtSumFields when you enter an amount into Field1 or Field2. In
order to see the sum as soon as you change the value in Field1 or Field2
you
would need to have the same procedure in the After Update event of the
controls (text boxes) for Field1 and Field2. You could also put the
event
in the Click event of a command button, or whatever suits you. One way
to
accomplish this is to create a public procedure or function and call it
as
needed.

Or you could set the control source of an unbound text box to:
=Field1 + Field2
and go on to other projects.

Bruce,

I had it working with the control source, but I want to implement it
into code. The reason why is because I would like to have it all behind
in code, and I don't know if that way it would be easier to implement,
as later on we will have all tables and qry's on sql. Either way, for
some weird reason i get now a #error when I use the control source
mehod. Any ideas?
I will check the reply later, I am heading to bed for now.

Thanks guys.

Patrick

BruceM wrote:
I believe the VBA procedure needs to be associated with an event in
order
to
run. In order for the text box to display the calculation when you
navigate
to a record, the procedure would be in the form's Current event. To
display
the calculation when one of the fields you are adding together is
changed
the procedure needs to be in the After Update event of each of the
text
boxes bound to those fields.
I may be missing the point of your question, but you may be
overcomplicating
this. One way to add two fields in an unbound text box is to set the
control source of the text box to =[Field1] + [Field2]

Dear Douglas,

Thank you for your quick response, I was able to save the macro in
that
way, but I still encounter the same problem, the field still stays
empty. Initially the maro was the control source of the etxt box, I
don't know if this might be the cause.

Thanks again for your help,

Patrick


Douglas J. Steele wrote:
Access has the ability to convert macros into VBA.

Right-click on the macro, choose Save As from the context-sensitive
menu,
and save as a Module. Once you've done that, check the code that it
generated.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,
I been reading quite a lot the boards here and I believe I might
either
need directions or simply the wise word of someone who has been
programming in vba for a while.

My problem I believe is really easy to solve but until now, I
been
strugling on finding a solution.

I want to change a macro that sums the fields of 2 colums and
displays
the answer in a text box. An example is on the inventory tracking
database provided by access 2003.

I would like to produce the same result but by using VBA. I
understand
the use of macros for this case is easier, but this is more for
the
learning purpose. If anyone out there has the answer, I thank you
for
your time in hearing my request.

with my best regards,

Patrick the novice programmer.

 

[Patrick J.]

Bruce,

Yes they are. I don't understand why the result is a #error. I been
trying different things and the last one i have is to create a new form
and see if that fixes the prolem. if it doesn't then I don't really
know. I will post the code here if it doesn't work at all and maybe
then you can give me a better idea of what I am doing wrong.

Thanks for all th help.
Patrick

I did not suggest that you use a macro as a control source. I would not use
a macro at all. A VBA procedure and a macro are two different things.
What happens if you set the control source of an unbound text box to:
=[Field1] + [Field2]
Use your actual field names, of course. If you get the correct result, you
could just stop there, but if not you need to sort out why the fields are
not adding properly. Are they both Number fields?

Dear Bruce,
all what you have asked me, and stated is what I am doing. I have the
project done where a macro is used in the control source of the control
box. The problem I have with it is that it returns to me a #error once
the fields are added. I thought it was a but so I decided to try it on
code, but with no luck on it. I will try the line of code that you
sugest in a function and see if it works, because last time I tried it
it returned no value at all.

thank you for all the help.

You had what "working with the control source"? The macro? What do you
mean "have it all behind in code"? Adding the two fields, either as I
have
suggested or in a calculated query field, is transparent to the user.
What do you mean by "later on we will have all tables and qry's on sql"?
Are you talking about SQL server? That won't affect code or expressions
in
the front end. How are you attempting to use a VBA procedure as a
control
source? What is the code in the VBA procedure?
If you want to use VBA, you could start by summing the fields in a
procedure
in the form's Current event.

Me.txtSumFields = Me.Field1 + Me.Field2

where txtSumFields is an unbound text box on your form, and Field1 and
Field2 are the fields you want to add together.
The form's Current event runs only when you navigate to a new record (or
when you first open the form, or when you requery the record source).
This
is fine for viewing the sum for a completed record, but it won't change
the
value in txtSumFields when you enter an amount into Field1 or Field2. In
order to see the sum as soon as you change the value in Field1 or Field2
you
would need to have the same procedure in the After Update event of the
controls (text boxes) for Field1 and Field2. You could also put the
event
in the Click event of a command button, or whatever suits you. One way
to
accomplish this is to create a public procedure or function and call it
as
needed.

Or you could set the control source of an unbound text box to:
=Field1 + Field2
and go on to other projects.

Bruce,

I had it working with the control source, but I want to implement it
into code. The reason why is because I would like to have it all behind
in code, and I don't know if that way it would be easier to implement,
as later on we will have all tables and qry's on sql. Either way, for
some weird reason i get now a #error when I use the control source
mehod. Any ideas?
I will check the reply later, I am heading to bed for now.

Thanks guys.

Patrick

BruceM wrote:
I believe the VBA procedure needs to be associated with an event in
order
to
run. In order for the text box to display the calculation when you
navigate
to a record, the procedure would be in the form's Current event. To
display
the calculation when one of the fields you are adding together is
changed
the procedure needs to be in the After Update event of each of the
text
boxes bound to those fields.
I may be missing the point of your question, but you may be
overcomplicating
this. One way to add two fields in an unbound text box is to set the
control source of the text box to =[Field1] + [Field2]

Dear Douglas,

Thank you for your quick response, I was able to save the macro in
that
way, but I still encounter the same problem, the field still stays
empty. Initially the maro was the control source of the etxt box, I
don't know if this might be the cause.

Thanks again for your help,

Patrick


Douglas J. Steele wrote:
Access has the ability to convert macros into VBA.

Right-click on the macro, choose Save As from the context-sensitive
menu,
and save as a Module. Once you've done that, check the code that it
generated.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,
I been reading quite a lot the boards here and I believe I might
either
need directions or simply the wise word of someone who has been
programming in vba for a while.

My problem I believe is really easy to solve but until now, I
been
strugling on finding a solution.

I want to change a macro that sums the fields of 2 colums and
displays
the answer in a text box. An example is on the inventory tracking
database provided by access 2003.

I would like to produce the same result but by using VBA. I
understand
the use of macros for this case is easier, but this is more for
the
learning purpose. If anyone out there has the answer, I thank you
for
your time in hearing my request.

with my best regards,

Patrick the novice programmer.

 

[BruceM]

Your best chance of receiving a useful response depends in part on your
answering *all* of the questions. Once again, what happens if you set the
control source of an unbound text box to:
=[Field1] + [Field2]
??
By the way, posting troublesome code should be a first step. You are having
trouble with the code, but as of your fifth posting in this thread you are
still the only person who has seen it.

Bruce,

Yes they are. I don't understand why the result is a #error. I been
trying different things and the last one i have is to create a new form
and see if that fixes the prolem. if it doesn't then I don't really
know. I will post the code here if it doesn't work at all and maybe
then you can give me a better idea of what I am doing wrong.

Thanks for all th help.
Patrick

I did not suggest that you use a macro as a control source. I would not
use
a macro at all. A VBA procedure and a macro are two different things.
What happens if you set the control source of an unbound text box to:
=[Field1] + [Field2]
Use your actual field names, of course. If you get the correct result,
you
could just stop there, but if not you need to sort out why the fields are
not adding properly. Are they both Number fields?

Dear Bruce,
all what you have asked me, and stated is what I am doing. I have the
project done where a macro is used in the control source of the control
box. The problem I have with it is that it returns to me a #error once
the fields are added. I thought it was a but so I decided to try it on
code, but with no luck on it. I will try the line of code that you
sugest in a function and see if it works, because last time I tried it
it returned no value at all.

thank you for all the help.

Patrick
BruceM wrote:
You had what "working with the control source"? The macro? What do
you
mean "have it all behind in code"? Adding the two fields, either as I
have
suggested or in a calculated query field, is transparent to the user.
What do you mean by "later on we will have all tables and qry's on
sql"?
Are you talking about SQL server? That won't affect code or
expressions
in
the front end. How are you attempting to use a VBA procedure as a
control
source? What is the code in the VBA procedure?
If you want to use VBA, you could start by summing the fields in a
procedure
in the form's Current event.

Me.txtSumFields = Me.Field1 + Me.Field2

where txtSumFields is an unbound text box on your form, and Field1 and
Field2 are the fields you want to add together.
The form's Current event runs only when you navigate to a new record
(or
when you first open the form, or when you requery the record source).
This
is fine for viewing the sum for a completed record, but it won't
change
the
value in txtSumFields when you enter an amount into Field1 or Field2.
In
order to see the sum as soon as you change the value in Field1 or
Field2
you
would need to have the same procedure in the After Update event of the
controls (text boxes) for Field1 and Field2. You could also put the
event
in the Click event of a command button, or whatever suits you. One
way
to
accomplish this is to create a public procedure or function and call
it
as
needed.

Or you could set the control source of an unbound text box to:
=Field1 + Field2
and go on to other projects.

Bruce,

I had it working with the control source, but I want to implement it
into code. The reason why is because I would like to have it all
behind
in code, and I don't know if that way it would be easier to
implement,
as later on we will have all tables and qry's on sql. Either way,
for
some weird reason i get now a #error when I use the control source
mehod. Any ideas?
I will check the reply later, I am heading to bed for now.

Thanks guys.

Patrick

BruceM wrote:
I believe the VBA procedure needs to be associated with an event in
order
to
run. In order for the text box to display the calculation when you
navigate
to a record, the procedure would be in the form's Current event.
To
display
the calculation when one of the fields you are adding together is
changed
the procedure needs to be in the After Update event of each of the
text
boxes bound to those fields.
I may be missing the point of your question, but you may be
overcomplicating
this. One way to add two fields in an unbound text box is to set
the
control source of the text box to =[Field1] + [Field2]

Dear Douglas,

Thank you for your quick response, I was able to save the macro
in
that
way, but I still encounter the same problem, the field still
stays
empty. Initially the maro was the control source of the etxt box,
I
don't know if this might be the cause.

Thanks again for your help,

Patrick


Douglas J. Steele wrote:
Access has the ability to convert macros into VBA.

Right-click on the macro, choose Save As from the
context-sensitive
menu,
and save as a Module. Once you've done that, check the code that
it
generated.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,
I been reading quite a lot the boards here and I believe I
might
either
need directions or simply the wise word of someone who has
been
programming in vba for a while.

My problem I believe is really easy to solve but until now, I
been
strugling on finding a solution.

I want to change a macro that sums the fields of 2 colums and
displays
the answer in a text box. An example is on the inventory
tracking
database provided by access 2003.

I would like to produce the same result but by using VBA. I
understand
the use of macros for this case is easier, but this is more
for
the
learning purpose. If anyone out there has the answer, I thank
you
for
your time in hearing my request.

with my best regards,

Patrick the novice programmer.