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.

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
http://I.Am/DougSteele
(no private e-mails, please)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >

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." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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.
>> >
>

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]
>
> "Patrick J." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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.
> >> >
> >

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.

"Patrick J." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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]
>>
>> "Patrick J." <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >> http://I.Am/DougSteele
>> >> (no private e-mails, please)
>> >>
>> >>
>> >> <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > 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.
>> >> >
>> >
>

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.
>
> "Patrick J." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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]
> >>
> >> "Patrick J." <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >> http://I.Am/DougSteele
> >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > 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.
> >> >> >
> >> >
> >

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?

"Patrick J." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>>
>> "Patrick J." <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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]
>> >>
>> >> "Patrick J." <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > 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
>> >> >> http://I.Am/DougSteele
>> >> >> (no private e-mails, please)
>> >> >>
>> >> >>
>> >> >> <(E-Mail Removed)> wrote in message
>> >> >> news:(E-Mail Removed)...
>> >> >> > 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.
>> >> >> >
>> >> >
>> >
>

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

BruceM wrote:
> 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?
>
> "Patrick J." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >>
> >> "Patrick J." <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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]
> >> >>
> >> >> "Patrick J." <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > 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
> >> >> >> http://I.Am/DougSteele
> >> >> >> (no private e-mails, please)
> >> >> >>
> >> >> >>
> >> >> >> <(E-Mail Removed)> wrote in message
> >> >> >> news:(E-Mail Removed)...
> >> >> >> > 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.
> >> >> >> >
> >> >> >
> >> >
> >

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.

"Patrick J." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> BruceM wrote:
>> 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?
>>
>> "Patrick J." <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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.
>> >>
>> >> "Patrick J." <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > 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]
>> >> >>
>> >> >> "Patrick J." <(E-Mail Removed)> wrote in message
>> >> >> news:(E-Mail Removed)...
>> >> >> > 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
>> >> >> >> http://I.Am/DougSteele
>> >> >> >> (no private e-mails, please)
>> >> >> >>
>> >> >> >>
>> >> >> >> <(E-Mail Removed)> wrote in message
>> >> >> >> news:(E-Mail Removed)...
>> >> >> >> > 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.
>> >> >> >> >
>> >> >> >
>> >> >
>> >
>