FORMAT DMax FORMAT

[Guest]

I need to be able to put the DMAX inbetween 2 other fields

My PurchaseOrderNumber in the tbl Orders is the following AAAA0123BC

At this point I have a PurchaseOrderNumber that is a txtfield that included
the above and stored it. I have just been changing the number at the end but
it is annoying to do this. It was suggested that I do the following so I made
a lngCountPO field.

"separate number field and NO PurchaseOrderNumber field at all. You can
then combine all three fields together for *display* and call it a PONumber.
It does not need to have a separate field in the table.

If the numeric portion is in its own field then it's fairly easy to DMax() +
1
to get the next number."

So I have 3 fields:
4 letters are the ProjectID (txt field) (tblOrders)
Numbers are lngCountPO (NumberField) (tblOrders)
2 Letters are the PurchaserID (txt field) (tblOrders)

Unfortunately it was never posted back HOW to Display the 3 together and I
am not knowledgeable enough to figure out the syntax myself.

I saw some code that had the ability to take the number portion from the txt
portion and increase it. But again I couldn't figure out how to adjust it
because it was using date fields etc. I tried the following but I could never
get the imax line to not be red

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String

strID = DMax("[PurchaseOrderNumber]", "[Orders]") ' find biggest existing ID
iMax = Val(Mid(strID, 4) ' extract numeric portion

Me!PONumber = Format("ProjectID) & Format(iMax + 1, "0000") & ("PurchaserID")
End Sub

(By the way I don't even know what the iMax line means!!)

If anyone has a suggestion I'd appreciate it.
Thanks!

 

[tina]

since you're working with three separate fields in the table (that's good),
you only need to increment the lngCountPO field, and save that value, as

lngCountPO = DMax("lngCountPO", "tblOrders")+1

to *display* the three fields together in the form (or in a report), add an
unbound textbox control to the form, and set its' ControlSource property to

=[ProjectID] & [lngCountPO] & [PurchaserID]

hth

 

[Guest]

Tina,
Thanks! OK I understand now how to display. So, on my new record button
should I put
lngCountPO = DMax("lngCountPO", "tblOrders")+1

before

DoCmd.GoToRecord , , acNewRec

And one more problem... there are three of us using the database so we don't
duplicate records (replicated BE) On each project we used a new 1st number
like 5 one of us is taking numbers starting with 5000
another starting with 5500
and another with 5700

By using the number that had the ProjectID and PurchaserID it meant that
there was no possiblility of duplicating that field because if you tried to
put a number that had the number it stopped the entry.

Would there be any way to have a criteria on each FE that could be edited by
project and numbers could be assigned within the VBA so when we merge the BE
we won't end up with duplicate POnumbers? Like...

lngCountPO = Nz(DMax("lngCountPO", "tblOrders"), 5000)+1

Thanks!

since you're working with three separate fields in the table (that's good),
you only need to increment the lngCountPO field, and save that value, as

lngCountPO = DMax("lngCountPO", "tblOrders")+1

to *display* the three fields together in the form (or in a report), add an
unbound textbox control to the form, and set its' ControlSource property to

=[ProjectID] & [lngCountPO] & [PurchaserID]

hth

I need to be able to put the DMAX inbetween 2 other fields

My PurchaseOrderNumber in the tbl Orders is the following AAAA0123BC

At this point I have a PurchaseOrderNumber that is a txtfield that included
the above and stored it. I have just been changing the number at the end but
it is annoying to do this. It was suggested that I do the following so I made
a lngCountPO field.

"separate number field and NO PurchaseOrderNumber field at all. You can
then combine all three fields together for *display* and call it a PONumber.
It does not need to have a separate field in the table.

If the numeric portion is in its own field then it's fairly easy to DMax() +
1
to get the next number."

So I have 3 fields:
4 letters are the ProjectID (txt field) (tblOrders)
Numbers are lngCountPO (NumberField) (tblOrders)
2 Letters are the PurchaserID (txt field) (tblOrders)

Unfortunately it was never posted back HOW to Display the 3 together and I
am not knowledgeable enough to figure out the syntax myself.

I saw some code that had the ability to take the number portion from the txt
portion and increase it. But again I couldn't figure out how to adjust it
because it was using date fields etc. I tried the following but I could never
get the imax line to not be red

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String

strID = DMax("[PurchaseOrderNumber]", "[Orders]") ' find biggest existing ID
iMax = Val(Mid(strID, 4) ' extract numeric portion

Me!PONumber = Format("ProjectID) & Format(iMax + 1, "0000") & ("PurchaserID")
End Sub

(By the way I don't even know what the iMax line means!!)

If anyone has a suggestion I'd appreciate it.
Thanks!

 

[tina]

comments inline.

Tina,
Thanks! OK I understand now how to display. So, on my new record button
should I put
lngCountPO = DMax("lngCountPO", "tblOrders")+1

before

DoCmd.GoToRecord , , acNewRec

no, put the code in either the form's BeforeInsert event procedure, or the
form's BeforeUpdate event procedure. in a database that is, or could
potentially be, a multi-user database that writes to a single backend file,
i usually put such code in the form's BeforeUpdate event, to minimize (but
not eliminate) the possibility of duplication of numbers. the only
additional issue is that the user won't see the generated number until the
record is saved - if there is a valid business reason that the user MUST see
the generated number immediately, as s/he begins entering a new record, then
use the BeforeInsert event.

And one more problem... there are three of us using the database so we don't
duplicate records (replicated BE) On each project we used a new 1st number
like 5 one of us is taking numbers starting with 5000
another starting with 5500
and another with 5700

By using the number that had the ProjectID and PurchaserID it meant that
there was no possiblility of duplicating that field because if you tried to
put a number that had the number it stopped the entry.

Would there be any way to have a criteria on each FE that could be edited by
project and numbers could be assigned within the VBA so when we merge the BE
we won't end up with duplicate POnumbers? Like...

lngCountPO = Nz(DMax("lngCountPO", "tblOrders"), 5000)+1

well, the above code will only start you at 5000 if there are no existing
records in the table. if there are records in the table, and highest
lngCountPO value in the table is for instance 8, then the next number
assigned will be 9, not 5001.

since your backend is replicated (you're in different offices, either not
connected at all or not connected on a LAN, i take it?), you'll need to
figure out a way to co-ordinate the use of groups of numbers between the
three of you. regardless of how you manage that issue, i'd suggest storing a
number range in a table with a single record and two fields: lngStart and
lngEnd. then refer to those values in the code that assigns the PO number,
rather than hard-coding a value into the code. it's much easier to change
values in a table when needed.

btw, i take it that all three of you may *potentially* enter records for the
same project?

hth

Thanks!

since you're working with three separate fields in the table (that's good),
you only need to increment the lngCountPO field, and save that value, as

lngCountPO = DMax("lngCountPO", "tblOrders")+1

to *display* the three fields together in the form (or in a report), add an
unbound textbox control to the form, and set its' ControlSource property to

=[ProjectID] & [lngCountPO] & [PurchaserID]

hth

I need to be able to put the DMAX inbetween 2 other fields

My PurchaseOrderNumber in the tbl Orders is the following AAAA0123BC

At this point I have a PurchaseOrderNumber that is a txtfield that included
the above and stored it. I have just been changing the number at the

end
but

it is annoying to do this. It was suggested that I do the following so

I
made

a lngCountPO field.

"separate number field and NO PurchaseOrderNumber field at all. You can
then combine all three fields together for *display* and call it a PONumber.
It does not need to have a separate field in the table.

If the numeric portion is in its own field then it's fairly easy to

DMax()
+

1
to get the next number."

So I have 3 fields:
4 letters are the ProjectID (txt field) (tblOrders)
Numbers are lngCountPO (NumberField) (tblOrders)
2 Letters are the PurchaserID (txt field) (tblOrders)

Unfortunately it was never posted back HOW to Display the 3 together and I
am not knowledgeable enough to figure out the syntax myself.

I saw some code that had the ability to take the number portion from

the
txt

portion and increase it. But again I couldn't figure out how to adjust it
because it was using date fields etc. I tried the following but I

could
never

get the imax line to not be red

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String

strID = DMax("[PurchaseOrderNumber]", "[Orders]") ' find biggest

existing
ID

iMax = Val(Mid(strID, 4) ' extract numeric portion

Me!PONumber = Format("ProjectID) & Format(iMax + 1, "0000") & ("PurchaserID")
End Sub

(By the way I don't even know what the iMax line means!!)

If anyone has a suggestion I'd appreciate it.
Thanks!

 

[Guest]

Boy this is complicated... yes 3 people WILL be entering records into the
same project. (We all do purchasing from our home and we don't have a server)
Having the PurchaserID as part of the PO would eliminate the possiblity of
duplicate numbers as long as the purchaser can't enter the same number again.
Right...? So, do I need to rethink how the purchaserID becomes part of the
number? Rather than just "displaying" with the lngCountPO can it be part of
the number?
Is there a way to adjust the following to do what I need so that I can leave
the projectID and the purchaserID as part of the PO? Is it possible to have a
text field and DMax the number portion within it?

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String
strID = DMax("[PurchaseOrderNumber]", "[Orders]") ' find biggest existingID
iMax = Val(Mid(strID, 4) ' extract numeric portion
Me!PurchaseOrderNumber = Format("ProjectID) & Format(iMax + 1, "0000") &
Format("PurchaserID")
End Sub

Thanks

comments inline.

Tina,
Thanks! OK I understand now how to display. So, on my new record button
should I put
lngCountPO = DMax("lngCountPO", "tblOrders")+1

before

DoCmd.GoToRecord , , acNewRec

no, put the code in either the form's BeforeInsert event procedure, or the
form's BeforeUpdate event procedure. in a database that is, or could
potentially be, a multi-user database that writes to a single backend file,
i usually put such code in the form's BeforeUpdate event, to minimize (but
not eliminate) the possibility of duplication of numbers. the only
additional issue is that the user won't see the generated number until the
record is saved - if there is a valid business reason that the user MUST see
the generated number immediately, as s/he begins entering a new record, then
use the BeforeInsert event.

And one more problem... there are three of us using the database so we don't
duplicate records (replicated BE) On each project we used a new 1st number
like 5 one of us is taking numbers starting with 5000
another starting with 5500
and another with 5700

By using the number that had the ProjectID and PurchaserID it meant that
there was no possiblility of duplicating that field because if you tried to
put a number that had the number it stopped the entry.

Would there be any way to have a criteria on each FE that could be edited by
project and numbers could be assigned within the VBA so when we merge the BE
we won't end up with duplicate POnumbers? Like...

lngCountPO = Nz(DMax("lngCountPO", "tblOrders"), 5000)+1

well, the above code will only start you at 5000 if there are no existing
records in the table. if there are records in the table, and highest
lngCountPO value in the table is for instance 8, then the next number
assigned will be 9, not 5001.

since your backend is replicated (you're in different offices, either not
connected at all or not connected on a LAN, i take it?), you'll need to
figure out a way to co-ordinate the use of groups of numbers between the
three of you. regardless of how you manage that issue, i'd suggest storing a
number range in a table with a single record and two fields: lngStart and
lngEnd. then refer to those values in the code that assigns the PO number,
rather than hard-coding a value into the code. it's much easier to change
values in a table when needed.

btw, i take it that all three of you may *potentially* enter records for the
same project?

hth

Thanks!

since you're working with three separate fields in the table (that's good),
you only need to increment the lngCountPO field, and save that value, as

lngCountPO = DMax("lngCountPO", "tblOrders")+1

to *display* the three fields together in the form (or in a report), add an
unbound textbox control to the form, and set its' ControlSource property to

=[ProjectID] & [lngCountPO] & [PurchaserID]

hth


I need to be able to put the DMAX inbetween 2 other fields

My PurchaseOrderNumber in the tbl Orders is the following AAAA0123BC

At this point I have a PurchaseOrderNumber that is a txtfield that
included
the above and stored it. I have just been changing the number at the end
but
it is annoying to do this. It was suggested that I do the following so I
made
a lngCountPO field.

"separate number field and NO PurchaseOrderNumber field at all. You can
then combine all three fields together for *display* and call it a
PONumber.
It does not need to have a separate field in the table.

If the numeric portion is in its own field then it's fairly easy to DMax()
+
1
to get the next number."

So I have 3 fields:
4 letters are the ProjectID (txt field) (tblOrders)
Numbers are lngCountPO (NumberField) (tblOrders)
2 Letters are the PurchaserID (txt field) (tblOrders)

Unfortunately it was never posted back HOW to Display the 3 together and I
am not knowledgeable enough to figure out the syntax myself.

I saw some code that had the ability to take the number portion from the
txt
portion and increase it. But again I couldn't figure out how to adjust it
because it was using date fields etc. I tried the following but I could
never
get the imax line to not be red

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String

strID = DMax("[PurchaseOrderNumber]", "[Orders]") ' find biggest existing
ID
iMax = Val(Mid(strID, 4) ' extract numeric portion

Me!PONumber = Format("ProjectID) & Format(iMax + 1, "0000") &
("PurchaserID")
End Sub

(By the way I don't even know what the iMax line means!!)

If anyone has a suggestion I'd appreciate it.
Thanks!

 

[tina]

putting two of your three fields together again will not help you, hon. the
issue is that you need to generate a PO number that cannot be duplicated for
any one project, or for any one purchaser within a project. yet there are
three people independently entering records into three different databases -
so there is no way to prevent duplication of PO numbers at the point of data
entry, unless those numbers are controlled by a range of possible values
that are different for each user. for instance, one user's database has the
table i outlined before, with a range of 5000 to 5999. the next user's
database has a range of 6000 to 6999 in his/her database, and the third user
has a range of 7000 to 7999.

you'll have issues of when to change the range in a given user's database,
because the range will be "used up" for one project before another project -
it might be easier to just not allow duplicate PO numbers at all, regardless
of project code or purchaser code. even in that event, you'll still have to
decide if each user can update his/her range as needed, in which case you'll
have to define guidelines to ensure no duplication of ranges between users,
or have the ranges updated centrally by one person.

i've never worked with replicated databases, so there may be standard
processes that are used to handle the issue of needing system-generated
values that are not duplicated between copies of the db. suggest you do a
google search on microsoft.public.access.replication newsgroup, for
discussions that may give you a better solution than mine.

hth

Boy this is complicated... yes 3 people WILL be entering records into the
same project. (We all do purchasing from our home and we don't have a server)
Having the PurchaserID as part of the PO would eliminate the possiblity of
duplicate numbers as long as the purchaser can't enter the same number again.
Right...? So, do I need to rethink how the purchaserID becomes part of the
number? Rather than just "displaying" with the lngCountPO can it be part of
the number?
Is there a way to adjust the following to do what I need so that I can leave
the projectID and the purchaserID as part of the PO? Is it possible to have a
text field and DMax the number portion within it?

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String
strID = DMax("[PurchaseOrderNumber]", "[Orders]") ' find biggest existingID
iMax = Val(Mid(strID, 4) ' extract numeric portion
Me!PurchaseOrderNumber = Format("ProjectID) & Format(iMax + 1, "0000") &
Format("PurchaserID")
End Sub

Thanks

comments inline.

Tina,
Thanks! OK I understand now how to display. So, on my new record button
should I put
lngCountPO = DMax("lngCountPO", "tblOrders")+1

before

DoCmd.GoToRecord , , acNewRec

no, put the code in either the form's BeforeInsert event procedure, or the
form's BeforeUpdate event procedure. in a database that is, or could
potentially be, a multi-user database that writes to a single backend file,
i usually put such code in the form's BeforeUpdate event, to minimize (but
not eliminate) the possibility of duplication of numbers. the only
additional issue is that the user won't see the generated number until the
record is saved - if there is a valid business reason that the user MUST see
the generated number immediately, as s/he begins entering a new record, then
use the BeforeInsert event.

And one more problem... there are three of us using the database so we don't
duplicate records (replicated BE) On each project we used a new 1st number
like 5 one of us is taking numbers starting with 5000
another starting with 5500
and another with 5700

By using the number that had the ProjectID and PurchaserID it meant that
there was no possiblility of duplicating that field because if you

tried
to

put a number that had the number it stopped the entry.

Would there be any way to have a criteria on each FE that could be

edited
by

project and numbers could be assigned within the VBA so when we merge

the
BE

we won't end up with duplicate POnumbers? Like...

lngCountPO = Nz(DMax("lngCountPO", "tblOrders"), 5000)+1

well, the above code will only start you at 5000 if there are no existing
records in the table. if there are records in the table, and highest
lngCountPO value in the table is for instance 8, then the next number
assigned will be 9, not 5001.

since your backend is replicated (you're in different offices, either not
connected at all or not connected on a LAN, i take it?), you'll need to
figure out a way to co-ordinate the use of groups of numbers between the
three of you. regardless of how you manage that issue, i'd suggest storing a
number range in a table with a single record and two fields: lngStart and
lngEnd. then refer to those values in the code that assigns the PO number,
rather than hard-coding a value into the code. it's much easier to change
values in a table when needed.

btw, i take it that all three of you may *potentially* enter records for the
same project?

hth

Thanks!

:

since you're working with three separate fields in the table (that's good),
you only need to increment the lngCountPO field, and save that value, as

lngCountPO = DMax("lngCountPO", "tblOrders")+1

to *display* the three fields together in the form (or in a report),

add
an

unbound textbox control to the form, and set its' ControlSource

property
to

=[ProjectID] & [lngCountPO] & [PurchaserID]

hth


I need to be able to put the DMAX inbetween 2 other fields

My PurchaseOrderNumber in the tbl Orders is the following AAAA0123BC

At this point I have a PurchaseOrderNumber that is a txtfield that
included
the above and stored it. I have just been changing the number at

the
end

but
it is annoying to do this. It was suggested that I do the

following so
I

made
a lngCountPO field.

"separate number field and NO PurchaseOrderNumber field at all.

You
can

then combine all three fields together for *display* and call it a
PONumber.
It does not need to have a separate field in the table.

If the numeric portion is in its own field then it's fairly easy

to
DMax()

+
1
to get the next number."

So I have 3 fields:
4 letters are the ProjectID (txt field) (tblOrders)
Numbers are lngCountPO (NumberField) (tblOrders)
2 Letters are the PurchaserID (txt field) (tblOrders)

Unfortunately it was never posted back HOW to Display the 3

together
and I

am not knowledgeable enough to figure out the syntax myself.

I saw some code that had the ability to take the number portion

from
the

txt
portion and increase it. But again I couldn't figure out how to

adjust
it

because it was using date fields etc. I tried the following but I could
never
get the imax line to not be red

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String

strID = DMax("[PurchaseOrderNumber]", "[Orders]") ' find biggest existing
ID
iMax = Val(Mid(strID, 4) ' extract numeric portion

Me!PONumber = Format("ProjectID) & Format(iMax + 1, "0000") &
("PurchaserID")
End Sub

(By the way I don't even know what the iMax line means!!)

If anyone has a suggestion I'd appreciate it.
Thanks!