I am creating an Invoice Application for a company. They need to select
Invoice or Credit Memo. If Credit Memo is selected it should change all
values to negative, if Invoice is selected it changes all values to positive.

I have made it change all exisiting data by using:

Me!Line1Item = -Me!Line1Item

If the value is positive this will change it to negative, and if the value
is negative it will change it to positive.

2 Issues with this:
If the user manually changes that field, while Credit Memo is selected, it
will make it positive. Also, any non existant fields (ie. $0.00) are
neutral, so once edited will make them positive. Then you change Credit Memo
to Invoice and it changes the positves to negative.

There has to be a way to make the field negative, not the value of the
field. I don't know VBA, but I am using it for this program. PHP and MySQL
is where I normally develop.

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

Hi,
you can do this 2 ways:
- you can store always positive values, and then use query to change Credit
Memo to negative - IIF([CreditMemo], -[Line1Item], [Line1Item]) as
NewLine1Item. This query you can use to calculations, etc
- you can use form's BeforeUpdate event to change Line1Item:
if [CreditMemo] then
me.Line1Item= - ABS(me.[Line1Item])
else
me.Line1Item=ABS(me.[Line1Item])
end if

assuming that CreditMemo is some Boolean flag

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



"Johnie Karr" <Johnie (E-Mail Removed)> wrote in message
news:8542B9A3-1B3D-451F-890D-(E-Mail Removed)...
>I am creating an Invoice Application for a company. They need to select
> Invoice or Credit Memo. If Credit Memo is selected it should change all
> values to negative, if Invoice is selected it changes all values to
> positive.
>
> I have made it change all exisiting data by using:
>

> Me!Line1Item = -Me!Line1Item
>

> If the value is positive this will change it to negative, and if the value
> is negative it will change it to positive.
>
> 2 Issues with this:
> If the user manually changes that field, while Credit Memo is selected, it
> will make it positive. Also, any non existant fields (ie. $0.00) are
> neutral, so once edited will make them positive. Then you change Credit
> Memo
> to Invoice and it changes the positves to negative.
>
> There has to be a way to make the field negative, not the value of the
> field. I don't know VBA, but I am using it for this program. PHP and
> MySQL
> is where I normally develop.
>
> Thanks,
> Johnie Karr
> Data Management Technologies
> www.datamt.org

Johnie, you have 2 fields:
- one is the dollar value;
- the other (a transaction type) indicates whether it is an Invoice or a
Credit.

How about setting up the TransactionType field as a Number field, using the
value:
1 for Invoice;
-1 for Credit Memo
(or the reverse if you prefer.)
Mark it as a Required field, and set the Validation Rule to:
1 or -1
so other values are not possible.

Then in a query, you can enter this into a fresh column in the Field row:
[TransactionType] * [Amount]
The multiplier causes one type to be positive, and the other type to be
negative.

You can use an combo box for the TransactionType, with properties:
Row Source Type Value List
Row Source 1; "Invoice"; -1; "Credit Memo"
Column Count 2
Column Widths 0
To the user, they are just selecting the type, but the multiplier does what
you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Johnie Karr" <Johnie (E-Mail Removed)> wrote in message
news:8542B9A3-1B3D-451F-890D-(E-Mail Removed)...
>I am creating an Invoice Application for a company. They need to select
> Invoice or Credit Memo. If Credit Memo is selected it should change all
> values to negative, if Invoice is selected it changes all values to
> positive.
>
> I have made it change all exisiting data by using:
>

> Me!Line1Item = -Me!Line1Item
>

> If the value is positive this will change it to negative, and if the value
> is negative it will change it to positive.
>
> 2 Issues with this:
> If the user manually changes that field, while Credit Memo is selected, it
> will make it positive. Also, any non existant fields (ie. $0.00) are
> neutral, so once edited will make them positive. Then you change Credit
> Memo
> to Invoice and it changes the positves to negative.
>
> There has to be a way to make the field negative, not the value of the
> field. I don't know VBA, but I am using it for this program. PHP and
> MySQL
> is where I normally develop.
>
> Thanks,
> Johnie Karr
> Data Management Technologies
> www.datamt.org

I have created a similar application and used the 2 field approach
(Transaction type & Amount ) that the others are recommending. MAKE SURE
you validity check the user input that they amount they are entering is
always positive. You will have users that try to enter credit memos as
negative invoices and could cause you a lot of heartache down the road....
trust me, i know.

best of luck,
Ron

"Johnie Karr" <Johnie (E-Mail Removed)> wrote in message
news:8542B9A3-1B3D-451F-890D-(E-Mail Removed)...
>I am creating an Invoice Application for a company. They need to select
> Invoice or Credit Memo. If Credit Memo is selected it should change all
> values to negative, if Invoice is selected it changes all values to
> positive.
>
> I have made it change all exisiting data by using:
>

> Me!Line1Item = -Me!Line1Item
>

> If the value is positive this will change it to negative, and if the value
> is negative it will change it to positive.
>
> 2 Issues with this:
> If the user manually changes that field, while Credit Memo is selected, it
> will make it positive. Also, any non existant fields (ie. $0.00) are
> neutral, so once edited will make them positive. Then you change Credit
> Memo
> to Invoice and it changes the positves to negative.
>
> There has to be a way to make the field negative, not the value of the
> field. I don't know VBA, but I am using it for this program. PHP and
> MySQL
> is where I normally develop.
>
> Thanks,
> Johnie Karr
> Data Management Technologies
> www.datamt.org

Thank you everyone for your replies and help.

Forgive my ignorance, but do I need two fields for every currency field?

Right now I have the 16 or so currency fields, and one drop box with Invoice
and Credit Memo choices.

I guess I just don't fully understand what you mean with the two fields.

I will try playing around with the 'before update' that Alex refered me to.

Thanks again, and I have a seperate question.....When I try to type my
response in the reply window, certain letters require me to use my shift key
in order to type them. It's almost as if sticky keys is turned on for just
that window. If I type certain letters, it brings up the set up voice
recogniztion feature. I had to type this in notepad and copy and paste it
over.

I've never seen a problem like this before, has any of you?

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

"Johnie Karr" wrote:

> I am creating an Invoice Application for a company. They need to select
> Invoice or Credit Memo. If Credit Memo is selected it should change all
> values to negative, if Invoice is selected it changes all values to positive.
>
> I have made it change all exisiting data by using:
>

> Me!Line1Item = -Me!Line1Item
>

> If the value is positive this will change it to negative, and if the value
> is negative it will change it to positive.
>
> 2 Issues with this:
> If the user manually changes that field, while Credit Memo is selected, it
> will make it positive. Also, any non existant fields (ie. $0.00) are
> neutral, so once edited will make them positive. Then you change Credit Memo
> to Invoice and it changes the positves to negative.
>
> There has to be a way to make the field negative, not the value of the
> field. I don't know VBA, but I am using it for this program. PHP and MySQL
> is where I normally develop.
>
> Thanks,
> Johnie Karr
> Data Management Technologies
> www.datamt.org

Just a side question...btw, the letters work correctly now for some
reason...anyway, what is ABS that Alex is pointing me to? I'm assuming that
we are telling the field something for after update?

"Alex Dybenko" wrote:

> Hi,
> you can do this 2 ways:
> - you can store always positive values, and then use query to change Credit
> Memo to negative - IIF([CreditMemo], -[Line1Item], [Line1Item]) as
> NewLine1Item. This query you can use to calculations, etc
> - you can use form's BeforeUpdate event to change Line1Item:
> if [CreditMemo] then
> me.Line1Item= - ABS(me.[Line1Item])
> else
> me.Line1Item=ABS(me.[Line1Item])
> end if
>
> assuming that CreditMemo is some Boolean flag
>
> --
> Alex Dybenko (MVP)
> http://alexdyb.blogspot.com
> http://www.PointLtd.com
>
>
>
> "Johnie Karr" <Johnie (E-Mail Removed)> wrote in message
> news:8542B9A3-1B3D-451F-890D-(E-Mail Removed)...
> >I am creating an Invoice Application for a company. They need to select
> > Invoice or Credit Memo. If Credit Memo is selected it should change all
> > values to negative, if Invoice is selected it changes all values to
> > positive.
> >
> > I have made it change all exisiting data by using:
> >

> > Me!Line1Item = -Me!Line1Item
> >

> > If the value is positive this will change it to negative, and if the value
> > is negative it will change it to positive.
> >
> > 2 Issues with this:
> > If the user manually changes that field, while Credit Memo is selected, it
> > will make it positive. Also, any non existant fields (ie. $0.00) are
> > neutral, so once edited will make them positive. Then you change Credit
> > Memo
> > to Invoice and it changes the positves to negative.
> >
> > There has to be a way to make the field negative, not the value of the
> > field. I don't know VBA, but I am using it for this program. PHP and
> > MySQL
> > is where I normally develop.
> >
> > Thanks,
> > Johnie Karr
> > Data Management Technologies
> > www.datamt.org
>
>

ABS = Absolute Value of a number
ABS(-1) = 1
ABS(-2) = 2
ABS(2) = 2

Brian

"Johnie Karr" <(E-Mail Removed)> wrote in message
news:3B4A77A0-2821-4EFF-A252-(E-Mail Removed)...
> Just a side question...btw, the letters work correctly now for some
> reason...anyway, what is ABS that Alex is pointing me to? I'm assuming
that
> we are telling the field something for after update?
>
> "Alex Dybenko" wrote:
>
> > Hi,
> > you can do this 2 ways:
> > - you can store always positive values, and then use query to change
Credit
> > Memo to negative - IIF([CreditMemo], -[Line1Item], [Line1Item]) as
> > NewLine1Item. This query you can use to calculations, etc
> > - you can use form's BeforeUpdate event to change Line1Item:
> > if [CreditMemo] then
> > me.Line1Item= - ABS(me.[Line1Item])
> > else
> > me.Line1Item=ABS(me.[Line1Item])
> > end if
> >
> > assuming that CreditMemo is some Boolean flag
> >
> > --
> > Alex Dybenko (MVP)
> > http://alexdyb.blogspot.com
> > http://www.PointLtd.com
> >
> >
> >
> > "Johnie Karr" <Johnie (E-Mail Removed)> wrote in message
> > news:8542B9A3-1B3D-451F-890D-(E-Mail Removed)...
> > >I am creating an Invoice Application for a company. They need to
select
> > > Invoice or Credit Memo. If Credit Memo is selected it should change
all
> > > values to negative, if Invoice is selected it changes all values to
> > > positive.
> > >
> > > I have made it change all exisiting data by using:
> > >

> > > Me!Line1Item = -Me!Line1Item
> > >

> > > If the value is positive this will change it to negative, and if the
value
> > > is negative it will change it to positive.
> > >
> > > 2 Issues with this:
> > > If the user manually changes that field, while Credit Memo is
selected, it
> > > will make it positive. Also, any non existant fields (ie. $0.00) are
> > > neutral, so once edited will make them positive. Then you change
Credit
> > > Memo
> > > to Invoice and it changes the positves to negative.
> > >
> > > There has to be a way to make the field negative, not the value of the
> > > field. I don't know VBA, but I am using it for this program. PHP and
> > > MySQL
> > > is where I normally develop.
> > >
> > > Thanks,
> > > Johnie Karr
> > > Data Management Technologies
> > > www.datamt.org
> >
> >

Private Sub CertInput_BeforeUpdate(Cancel As Integer)
If CRMEM = "Credit Memo" Then
Me!CertInput = -Abs(Me.[CertInput])
Else
Me!CertInput = Abs(Me.[CertInput])
End If
End Sub

This is what I have, and everytime I run the form, once I type in a value it
says there is an error in the BeforeUpdate and cannot save the data. Then I
click Debug and it highlights this line:
Me!CertInput = Abs(Me.[CertInput])
If I have Credit Memo selected then it updates the line for that.

What is wrong with that line? CertInput is a Currency Field.

"Johnie Karr" wrote:

> Thank you everyone for your replies and help.
>
> Forgive my ignorance, but do I need two fields for every currency field?
>
> Right now I have the 16 or so currency fields, and one drop box with Invoice
> and Credit Memo choices.
>
> I guess I just don't fully understand what you mean with the two fields.
>
> I will try playing around with the 'before update' that Alex refered me to.
>
> Thanks again, and I have a seperate question.....When I try to type my
> response in the reply window, certain letters require me to use my shift key
> in order to type them. It's almost as if sticky keys is turned on for just
> that window. If I type certain letters, it brings up the set up voice
> recogniztion feature. I had to type this in notepad and copy and paste it
> over.
>
> I've never seen a problem like this before, has any of you?
>
> Thanks,
> Johnie Karr
> Data Management Technologies
> www.datamt.org
>
> "Johnie Karr" wrote:
>
> > I am creating an Invoice Application for a company. They need to select
> > Invoice or Credit Memo. If Credit Memo is selected it should change all
> > values to negative, if Invoice is selected it changes all values to positive.
> >
> > I have made it change all exisiting data by using:
> >

> > Me!Line1Item = -Me!Line1Item
> >

> > If the value is positive this will change it to negative, and if the value
> > is negative it will change it to positive.
> >
> > 2 Issues with this:
> > If the user manually changes that field, while Credit Memo is selected, it
> > will make it positive. Also, any non existant fields (ie. $0.00) are
> > neutral, so once edited will make them positive. Then you change Credit Memo
> > to Invoice and it changes the positves to negative.
> >
> > There has to be a way to make the field negative, not the value of the
> > field. I don't know VBA, but I am using it for this program. PHP and MySQL
> > is where I normally develop.
> >
> > Thanks,
> > Johnie Karr
> > Data Management Technologies
> > www.datamt.org

Runtime error '2115' is the error I get.

"Johnie Karr" wrote:

> Private Sub CertInput_BeforeUpdate(Cancel As Integer)
> If CRMEM = "Credit Memo" Then
> Me!CertInput = -Abs(Me.[CertInput])
> Else
> Me!CertInput = Abs(Me.[CertInput])
> End If
> End Sub
>
> This is what I have, and everytime I run the form, once I type in a value it
> says there is an error in the BeforeUpdate and cannot save the data. Then I
> click Debug and it highlights this line:
> Me!CertInput = Abs(Me.[CertInput])
> If I have Credit Memo selected then it updates the line for that.
>
> What is wrong with that line? CertInput is a Currency Field.
>
> "Johnie Karr" wrote:
>
> > Thank you everyone for your replies and help.
> >
> > Forgive my ignorance, but do I need two fields for every currency field?
> >
> > Right now I have the 16 or so currency fields, and one drop box with Invoice
> > and Credit Memo choices.
> >
> > I guess I just don't fully understand what you mean with the two fields.
> >
> > I will try playing around with the 'before update' that Alex refered me to.
> >
> > Thanks again, and I have a seperate question.....When I try to type my
> > response in the reply window, certain letters require me to use my shift key
> > in order to type them. It's almost as if sticky keys is turned on for just
> > that window. If I type certain letters, it brings up the set up voice
> > recogniztion feature. I had to type this in notepad and copy and paste it
> > over.
> >
> > I've never seen a problem like this before, has any of you?
> >
> > Thanks,
> > Johnie Karr
> > Data Management Technologies
> > www.datamt.org
> >
> > "Johnie Karr" wrote:
> >
> > > I am creating an Invoice Application for a company. They need to select
> > > Invoice or Credit Memo. If Credit Memo is selected it should change all
> > > values to negative, if Invoice is selected it changes all values to positive.
> > >
> > > I have made it change all exisiting data by using:
> > >

> > > Me!Line1Item = -Me!Line1Item
> > >

> > > If the value is positive this will change it to negative, and if the value
> > > is negative it will change it to positive.
> > >
> > > 2 Issues with this:
> > > If the user manually changes that field, while Credit Memo is selected, it
> > > will make it positive. Also, any non existant fields (ie. $0.00) are
> > > neutral, so once edited will make them positive. Then you change Credit Memo
> > > to Invoice and it changes the positves to negative.
> > >
> > > There has to be a way to make the field negative, not the value of the
> > > field. I don't know VBA, but I am using it for this program. PHP and MySQL
> > > is where I normally develop.
> > >
> > > Thanks,
> > > Johnie Karr
> > > Data Management Technologies
> > > www.datamt.org

The problem is it needs to be in after update, not before update.

Thanks everyone for your help, I'll mark this answered now!

Johnie Karr
Data Management Technologies
www.datamt.org

"Johnie Karr" wrote:

> Private Sub CertInput_BeforeUpdate(Cancel As Integer)
> If CRMEM = "Credit Memo" Then
> Me!CertInput = -Abs(Me.[CertInput])
> Else
> Me!CertInput = Abs(Me.[CertInput])
> End If
> End Sub
>
> This is what I have, and everytime I run the form, once I type in a value it
> says there is an error in the BeforeUpdate and cannot save the data. Then I
> click Debug and it highlights this line:
> Me!CertInput = Abs(Me.[CertInput])
> If I have Credit Memo selected then it updates the line for that.
>
> What is wrong with that line? CertInput is a Currency Field.
>
> "Johnie Karr" wrote:
>
> > Thank you everyone for your replies and help.
> >
> > Forgive my ignorance, but do I need two fields for every currency field?
> >
> > Right now I have the 16 or so currency fields, and one drop box with Invoice
> > and Credit Memo choices.
> >
> > I guess I just don't fully understand what you mean with the two fields.
> >
> > I will try playing around with the 'before update' that Alex refered me to.
> >
> > Thanks again, and I have a seperate question.....When I try to type my
> > response in the reply window, certain letters require me to use my shift key
> > in order to type them. It's almost as if sticky keys is turned on for just
> > that window. If I type certain letters, it brings up the set up voice
> > recogniztion feature. I had to type this in notepad and copy and paste it
> > over.
> >
> > I've never seen a problem like this before, has any of you?
> >
> > Thanks,
> > Johnie Karr
> > Data Management Technologies
> > www.datamt.org
> >
> > "Johnie Karr" wrote:
> >
> > > I am creating an Invoice Application for a company. They need to select
> > > Invoice or Credit Memo. If Credit Memo is selected it should change all
> > > values to negative, if Invoice is selected it changes all values to positive.
> > >
> > > I have made it change all exisiting data by using:
> > >

> > > Me!Line1Item = -Me!Line1Item
> > >

> > > If the value is positive this will change it to negative, and if the value
> > > is negative it will change it to positive.
> > >
> > > 2 Issues with this:
> > > If the user manually changes that field, while Credit Memo is selected, it
> > > will make it positive. Also, any non existant fields (ie. $0.00) are
> > > neutral, so once edited will make them positive. Then you change Credit Memo
> > > to Invoice and it changes the positves to negative.
> > >
> > > There has to be a way to make the field negative, not the value of the
> > > field. I don't know VBA, but I am using it for this program. PHP and MySQL
> > > is where I normally develop.
> > >
> > > Thanks,
> > > Johnie Karr
> > > Data Management Technologies
> > > www.datamt.org