Equations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am making a purchase order database and i want the P.O. # to automatically
increase with each new order. What equation do i use and where do i use it i
can't seem to figure it out. The p.o. # is within an order database.
 
that didn't work thanks anyways
Klatuu said:
Dim varNewPO as Variant

NewPO = Nz(DMax("[PO_Number]","MyPOTable"),0) + 1

JennBard1 said:
I am making a purchase order database and i want the P.O. # to automatically
increase with each new order. What equation do i use and where do i use it i
can't seem to figure it out. The p.o. # is within an order database.
Click to expand...
Click to expand...
 
that didn't work thanks anyways
Klatuu said:
Dim varNewPO as Variant

NewPO = Nz(DMax("[PO_Number]","MyPOTable"),0) + 1

JennBard1 said:
I am making a purchase order database and i want the P.O. # to automatically
increase with each new order. What equation do i use and where do i use it i
can't seem to figure it out. The p.o. # is within an order database.
Click to expand...
Click to expand...
Click to expand...

Just a helpful tip to help you get good responses when asking
questions in newsgroups.

Because "Klatuu" gave you a good expression to use, I would suspect
you didn't use it properly.
Words like 'That didn't work' gives any potential reader who might
want to help you absolutely no useful information.
What didn't happen?
What did happen?
What did you expect to happen?
Exactly how did you implement the help given you?
Did you enter your table and field names in place of the generic ones
given?
Where did you place the code?
What is the exact code you wrote (copied directly from your database
and pasted here so we can see if you didn't simply mis-write the
code)?
Answers to those questions would be helpful to us .... to help you!
 
Thank you fredg. I know the expresion works. It is only the 3rd time this
week I have answered the same question. I probably wont have to answer it
again until tomorrow.

One thing I neglected to include was where to use it. That is not that easy
to answer given the level of detail in the question; however, he could try
using it in the Default Value property of the control where the PO number is
displayed.

fredg said:
that didn't work thanks anyways
Klatuu said:
Dim varNewPO as Variant

NewPO = Nz(DMax("[PO_Number]","MyPOTable"),0) + 1

:

I am making a purchase order database and i want the P.O. # to automatically
increase with each new order. What equation do i use and where do i use it i
can't seem to figure it out. The p.o. # is within an order database.
Click to expand...
Click to expand...

Just a helpful tip to help you get good responses when asking
questions in newsgroups.

Because "Klatuu" gave you a good expression to use, I would suspect
you didn't use it properly.
Words like 'That didn't work' gives any potential reader who might
want to help you absolutely no useful information.
What didn't happen?
What did happen?
What did you expect to happen?
Exactly how did you implement the help given you?
Did you enter your table and field names in place of the generic ones
given?
Where did you place the code?
What is the exact code you wrote (copied directly from your database
and pasted here so we can see if you didn't simply mis-write the
code)?
Answers to those questions would be helpful to us .... to help you!
Click to expand...
 
Sorry, your right i didn't mean it that way.
When i changed the PO_Number & MyPOTable to the names of my text boxes there
was an error so i have no idead what i should be changing these generics to
in order to stop getting the #Name? reading or the Error reading in that text
box.

NewPO = Nz(DMax("[PO_Number]","MyPOTable"),0) + 1

i also tried
=DMax["Order ID","P O #"]+1 which someone else gave me and i can't get that
to work for me either. I don't know what i am doing wrong. I may be using the
wrong text box names i don't really know
 
Please post back you code, the name of your table, the name of the control
where the PO is on your form, and the name of the field that carries the PO
number, and we will see if we can get it working.
 
The table name is: Apollo Purchase Orders
The Control Name is: P O #
the field that carries the p o is: P O #
 
=Nz(DMax("[P O #]","Apollo Purchase Orders"),0) + 1

In design view of you form, click on you P O # control, select properties,
select Default Value and put the line of code above in it. If you have
problems with it, let me know and give me details and error descriptions so
we ca debug it.
Now, some pointers:
Avoid using any thimg other than letters, digits, and the underscore ( _ )
in names.
Use different names for different objects so you know what it is easily
Like instead of P O # use PO_NUMBER ( most database developers will
recognize this as a field name because it is all uppercase and has an _ in it.
for control names, use a prefix that easily identifies it. Look for a copy
of "The Leszynski/Reddick Guidelines for Microsoft Access". I think you can
find it on MSDN.
For a text box, for example, a text box is usually prefixed with txt. For
example:
txtPONumber

Let me know how it works out
 
that works perfect thank-you so much
can i change the name (P O #)now to PO_NUMBER or is it late?

Klatuu said:
=Nz(DMax("[P O #]","Apollo Purchase Orders"),0) + 1

In design view of you form, click on you P O # control, select properties,
select Default Value and put the line of code above in it. If you have
problems with it, let me know and give me details and error descriptions so
we ca debug it.
Now, some pointers:
Avoid using any thimg other than letters, digits, and the underscore ( _ )
in names.
Use different names for different objects so you know what it is easily
Like instead of P O # use PO_NUMBER ( most database developers will
recognize this as a field name because it is all uppercase and has an _ in it.
for control names, use a prefix that easily identifies it. Look for a copy
of "The Leszynski/Reddick Guidelines for Microsoft Access". I think you can
find it on MSDN.
For a text box, for example, a text box is usually prefixed with txt. For
example:
txtPONumber

Let me know how it works out

JennBard1 said:
The table name is: Apollo Purchase Orders
The Control Name is: P O #
the field that carries the p o is: P O #
Click to expand...
Click to expand...
 
Great! I knew we could do it.
You would have to change it everywhere you currently reference it.

Good Luck

JennBard1 said:
that works perfect thank-you so much
can i change the name (P O #)now to PO_NUMBER or is it late?

Klatuu said:
=Nz(DMax("[P O #]","Apollo Purchase Orders"),0) + 1

In design view of you form, click on you P O # control, select properties,
select Default Value and put the line of code above in it. If you have
problems with it, let me know and give me details and error descriptions so
we ca debug it.
Now, some pointers:
Avoid using any thimg other than letters, digits, and the underscore ( _ )
in names.
Use different names for different objects so you know what it is easily
Like instead of P O # use PO_NUMBER ( most database developers will
recognize this as a field name because it is all uppercase and has an _ in it.
for control names, use a prefix that easily identifies it. Look for a copy
of "The Leszynski/Reddick Guidelines for Microsoft Access". I think you can
find it on MSDN.
For a text box, for example, a text box is usually prefixed with txt. For
example:
txtPONumber

Let me know how it works out

JennBard1 said:
The table name is: Apollo Purchase Orders
The Control Name is: P O #
the field that carries the p o is: P O #

:

Please post back you code, the name of your table, the name of the control
where the PO is on your form, and the name of the field that carries the PO
number, and we will see if we can get it working.
Click to expand...
Click to expand...
Click to expand...
 
JennBard1 said:
that works perfect thank-you so much
can i change the name (P O #)now to PO_NUMBER or is it late?
Click to expand...

Just a warning that using the DMax() + 1 strategy as the DefaultValue property
will only work if one person is entering records at a time and it will not work
in a continuous or datasheet view of a form at all. If you need to support
multiple users you should assign that value in the BeforeUpdate event of the
form.
 
True, however, the OP stated she was a novice level. I was trying to get the
basics for her so she could get to a point of understanding.
 
The database that i am creating is a multiuser database so that means i need
to put that equation into the before update section?
 
I hate it when that happens!
You could put the equation in the Before Update event of the form. The
problem is the user will not see it before the record updates. A more elegant
way is to create it as I suggested in my first post, then check it in the
Before Update event of the form to make sure another user has not used the
number:

Dim strNewPO as String

strNewPO = Me.txtPONumber
Do While True
If IsNull(DLookup("[PO_Number]","MyPOTable", "[PO_Number] = '" & _
strNewPO & "'") Then
Exit Do
Else
strNewPO = strNewPO + 1
End If
If strNewPO <> Me.txtPONumber Then
If MsgBox "P O Number " & Me.txtPONumber & " Is Already in use " _
& vbNewLine & "New P O Number Changed to " & strNewPO, _
& vbExclamation + vbOkayCancel = vbCancel Then
Cancel = True
Else
Me.PONumber = strNewPO
End If
End If

Be aware this is untested air code and may take some tweeking.
 
Back
Top