max number plus 1 for ID with prefix

D

deb

I have a field called TransNo in a table I want to use as like a autonumber
but to format it with the field TLPrefix in front of the number and to number
per ProjectID.

TransNo should be max number for the ProjectID in the table and add 1 and
append prefix.

ie
ProjectID TLPrefix TransNo
1 abc abc-1
1 abc abc-2
2 xyz xyz-1 and so on

Thank you in advance
 
A

Al Campagna

deb,
Try a table setup like this...
ProjectID TLPrefix TransNoSuffix TransNo
1 abc 1 abc-1
1 abc 2 abc-2
2 xyz 1 xyz-1

Using the AfterUpdate event of TLPrefix...

Private Sub TLPrefix_AfterUpdate()
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", "ProjectID =
Forms!frmYourForm!ProjectID"),0) +1
End Sub

or... and I think preferred, would be to drop the TransNo field altogether,
and just calculate it from the TLPrefix, and the TransNoSuffix... on the
fly, whenever needed.
= TLPrefix & "-" & TransNoSuffix
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
B

BruceM

I thank that needs to be concatenated in the Where condition:
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", _
"ProjectID = " & Forms!frmYourForm!ProjectID),0) +1

To the OP, without knowing the details of your database's structure it seems
that the record containing TransNoSuffix is in a table that is related to
the main form's table, but it is not clear if TLPrefix is in the main form's
record source, of if it is independent of the main form's record. Do all of
the records where ProjectID = 1 have the same prefix? If so, it should
probably be part of the main form's record. You can still concatenate
TLPrefix and TransNoSuffix as suggested, but the syntax will be different if
one of the fields is in the main form's record source and the other in the
subform's record source.

Al Campagna said:
deb,
Try a table setup like this...
ProjectID TLPrefix TransNoSuffix TransNo
1 abc 1 abc-1
1 abc 2 abc-2
2 xyz 1 xyz-1

Using the AfterUpdate event of TLPrefix...

Private Sub TLPrefix_AfterUpdate()
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", "ProjectID =
Forms!frmYourForm!ProjectID"),0) +1
End Sub

or... and I think preferred, would be to drop the TransNo field
altogether, and just calculate it from the TLPrefix, and the
TransNoSuffix... on the fly, whenever needed.
= TLPrefix & "-" & TransNoSuffix
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


deb said:
I have a field called TransNo in a table I want to use as like a
autonumber
but to format it with the field TLPrefix in front of the number and to
number
per ProjectID.

TransNo should be max number for the ProjectID in the table and add 1
and
append prefix.

ie
ProjectID TLPrefix TransNo
1 abc abc-1
1 abc abc-2
2 xyz xyz-1 and so on

Thank you in advance
 
A

Al Campagna

Bruce,
No, it works just fine.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

BruceM said:
I thank that needs to be concatenated in the Where condition:
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", _
"ProjectID = " & Forms!frmYourForm!ProjectID),0) +1

To the OP, without knowing the details of your database's structure it
seems that the record containing TransNoSuffix is in a table that is
related to the main form's table, but it is not clear if TLPrefix is in
the main form's record source, of if it is independent of the main form's
record. Do all of the records where ProjectID = 1 have the same prefix?
If so, it should probably be part of the main form's record. You can
still concatenate TLPrefix and TransNoSuffix as suggested, but the syntax
will be different if one of the fields is in the main form's record source
and the other in the subform's record source.

Al Campagna said:
deb,
Try a table setup like this...
ProjectID TLPrefix TransNoSuffix TransNo
1 abc 1 abc-1
1 abc 2 abc-2
2 xyz 1 xyz-1

Using the AfterUpdate event of TLPrefix...

Private Sub TLPrefix_AfterUpdate()
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", "ProjectID
= Forms!frmYourForm!ProjectID"),0) +1
End Sub

or... and I think preferred, would be to drop the TransNo field
altogether, and just calculate it from the TLPrefix, and the
TransNoSuffix... on the fly, whenever needed.
= TLPrefix & "-" & TransNoSuffix
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


deb said:
I have a field called TransNo in a table I want to use as like a
autonumber
but to format it with the field TLPrefix in front of the number and to
number
per ProjectID.

TransNo should be max number for the ProjectID in the table and add 1
and
append prefix.

ie
ProjectID TLPrefix TransNo
1 abc abc-1
1 abc abc-2
2 xyz xyz-1 and so on

Thank you in advance
 
B

BruceM

OK, then. I have never been able to get a where condition to work without
concatenating unless I am using a literal text value, but maybe the
Forms!etc. syntax works differently, or maybe I have been in error all
along. It seems every time I think I understand something it turns out
there is an exception or I got it wrong or whatever.

Al Campagna said:
Bruce,
No, it works just fine.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

BruceM said:
I thank that needs to be concatenated in the Where condition:
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", _
"ProjectID = " & Forms!frmYourForm!ProjectID),0) +1

To the OP, without knowing the details of your database's structure it
seems that the record containing TransNoSuffix is in a table that is
related to the main form's table, but it is not clear if TLPrefix is in
the main form's record source, of if it is independent of the main form's
record. Do all of the records where ProjectID = 1 have the same prefix?
If so, it should probably be part of the main form's record. You can
still concatenate TLPrefix and TransNoSuffix as suggested, but the syntax
will be different if one of the fields is in the main form's record
source and the other in the subform's record source.

Al Campagna said:
deb,
Try a table setup like this...
ProjectID TLPrefix TransNoSuffix TransNo
1 abc 1 abc-1
1 abc 2 abc-2
2 xyz 1 xyz-1

Using the AfterUpdate event of TLPrefix...

Private Sub TLPrefix_AfterUpdate()
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", "ProjectID
= Forms!frmYourForm!ProjectID"),0) +1
End Sub

or... and I think preferred, would be to drop the TransNo field
altogether, and just calculate it from the TLPrefix, and the
TransNoSuffix... on the fly, whenever needed.
= TLPrefix & "-" & TransNoSuffix
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



I have a field called TransNo in a table I want to use as like a
autonumber
but to format it with the field TLPrefix in front of the number and to
number
per ProjectID.

TransNo should be max number for the ProjectID in the table and add 1
and
append prefix.

ie
ProjectID TLPrefix TransNo
1 abc abc-1
1 abc abc-2
2 xyz xyz-1 and so on

Thank you in advance
 
A

Al Campagna

Bruce,
I tested my method using both numeric and text values in the Where, and
they both worked using my "full address" argument.
Let me do a bit of research on this... as to why it works in both cases.
I've used this method for a long time now, but I must admit, I'm not sure
why it works as it does.
Maybe... "I'm right... for the wrong reason." :-D
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

BruceM said:
OK, then. I have never been able to get a where condition to work without
concatenating unless I am using a literal text value, but maybe the
Forms!etc. syntax works differently, or maybe I have been in error all
along. It seems every time I think I understand something it turns out
there is an exception or I got it wrong or whatever.

Al Campagna said:
Bruce,
No, it works just fine.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

BruceM said:
I thank that needs to be concatenated in the Where condition:
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", _
"ProjectID = " & Forms!frmYourForm!ProjectID),0) +1

To the OP, without knowing the details of your database's structure it
seems that the record containing TransNoSuffix is in a table that is
related to the main form's table, but it is not clear if TLPrefix is in
the main form's record source, of if it is independent of the main
form's record. Do all of the records where ProjectID = 1 have the same
prefix? If so, it should probably be part of the main form's record.
You can still concatenate TLPrefix and TransNoSuffix as suggested, but
the syntax will be different if one of the fields is in the main form's
record source and the other in the subform's record source.

deb,
Try a table setup like this...
ProjectID TLPrefix TransNoSuffix TransNo
1 abc 1 abc-1
1 abc 2 abc-2
2 xyz 1 xyz-1

Using the AfterUpdate event of TLPrefix...

Private Sub TLPrefix_AfterUpdate()
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable",
"ProjectID = Forms!frmYourForm!ProjectID"),0) +1
End Sub

or... and I think preferred, would be to drop the TransNo field
altogether, and just calculate it from the TLPrefix, and the
TransNoSuffix... on the fly, whenever needed.
= TLPrefix & "-" & TransNoSuffix
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



I have a field called TransNo in a table I want to use as like a
autonumber
but to format it with the field TLPrefix in front of the number and to
number
per ProjectID.

TransNo should be max number for the ProjectID in the table and add 1
and
append prefix.

ie
ProjectID TLPrefix TransNo
1 abc abc-1
1 abc abc-2
2 xyz xyz-1 and so on

Thank you in advance
 
B

BruceM

What I meant about the literal text value was something like this for the
Where:
"VendorName = 'Xerox'"

Al Campagna said:
Bruce,
I tested my method using both numeric and text values in the Where, and
they both worked using my "full address" argument.
Let me do a bit of research on this... as to why it works in both cases.
I've used this method for a long time now, but I must admit, I'm not
sure why it works as it does.
Maybe... "I'm right... for the wrong reason." :-D
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

BruceM said:
OK, then. I have never been able to get a where condition to work
without concatenating unless I am using a literal text value, but maybe
the Forms!etc. syntax works differently, or maybe I have been in error
all along. It seems every time I think I understand something it turns
out there is an exception or I got it wrong or whatever.

Al Campagna said:
Bruce,
No, it works just fine.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I thank that needs to be concatenated in the Where condition:
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", _
"ProjectID = " & Forms!frmYourForm!ProjectID),0) +1

To the OP, without knowing the details of your database's structure it
seems that the record containing TransNoSuffix is in a table that is
related to the main form's table, but it is not clear if TLPrefix is in
the main form's record source, of if it is independent of the main
form's record. Do all of the records where ProjectID = 1 have the same
prefix? If so, it should probably be part of the main form's record.
You can still concatenate TLPrefix and TransNoSuffix as suggested, but
the syntax will be different if one of the fields is in the main form's
record source and the other in the subform's record source.

deb,
Try a table setup like this...
ProjectID TLPrefix TransNoSuffix TransNo
1 abc 1 abc-1
1 abc 2 abc-2
2 xyz 1 xyz-1

Using the AfterUpdate event of TLPrefix...

Private Sub TLPrefix_AfterUpdate()
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable",
"ProjectID = Forms!frmYourForm!ProjectID"),0) +1
End Sub

or... and I think preferred, would be to drop the TransNo field
altogether, and just calculate it from the TLPrefix, and the
TransNoSuffix... on the fly, whenever needed.
= TLPrefix & "-" & TransNoSuffix
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



I have a field called TransNo in a table I want to use as like a
autonumber
but to format it with the field TLPrefix in front of the number and
to number
per ProjectID.

TransNo should be max number for the ProjectID in the table and add 1
and
append prefix.

ie
ProjectID TLPrefix TransNo
1 abc abc-1
1 abc abc-2
2 xyz xyz-1 and so on

Thank you in advance
 
A

Al Campagna

Bruce,
I have a form (in a small "test/sample" database) I use to work out
answers to OP questions.

The table for that form has 3 fields...
CustomerID (Num/Integer)
LastName (Text)
PlowingOrder (Num/Single)

I created the following (what I call "full address") text control
calculations...
=DLookup("[PlowingOrder]","tblAllCustomers","LastName=Forms!Form2!LastName")
and
=DLookup("[PlowingOrder]","tblAllCustomers","CustomerID=Forms!Form2!CustomerID")

Both work, returning the correct PlowingOrder, but I'm not sure why.

Equivalent Where arguments where the values are not "live" when called,
require two different syntaxes in the Where...
=DMax("[PlowingOrder]","tblAllCustomers","LastName = 'Morin'")
and
=DMax("[PlowingOrder]","tblAllCustomers","CustomerID = 5")

Allen Browne provided this information about using the
Forms!FormName!ControlName Where argument...
(the asterisks are mine)
There's a thing in Access called the Expression Service (ES.)
If a query contains a parameter such as:
Forms!Form2!LastName
JET calls the ES. The ES looks to see if the Forms collection currently
contains Form2, and if so if Form2 can give a value for LastName. If so, the
ES provides the value back to JET, and the *ES handles the delimiters*. If
the
ES does not resolve the name (e.g. if Form2 is not open), JET pops up the
parameter dialog.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

BruceM said:
What I meant about the literal text value was something like this for the
Where:
"VendorName = 'Xerox'"

Al Campagna said:
Bruce,
I tested my method using both numeric and text values in the Where, and
they both worked using my "full address" argument.
Let me do a bit of research on this... as to why it works in both
cases.
I've used this method for a long time now, but I must admit, I'm not
sure why it works as it does.
Maybe... "I'm right... for the wrong reason." :-D
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

BruceM said:
OK, then. I have never been able to get a where condition to work
without concatenating unless I am using a literal text value, but maybe
the Forms!etc. syntax works differently, or maybe I have been in error
all along. It seems every time I think I understand something it turns
out there is an exception or I got it wrong or whatever.

Bruce,
No, it works just fine.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I thank that needs to be concatenated in the Where condition:
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", _
"ProjectID = " & Forms!frmYourForm!ProjectID),0) +1

To the OP, without knowing the details of your database's structure it
seems that the record containing TransNoSuffix is in a table that is
related to the main form's table, but it is not clear if TLPrefix is
in the main form's record source, of if it is independent of the main
form's record. Do all of the records where ProjectID = 1 have the
same prefix? If so, it should probably be part of the main form's
record. You can still concatenate TLPrefix and TransNoSuffix as
suggested, but the syntax will be different if one of the fields is in
the main form's record source and the other in the subform's record
source.

deb,
Try a table setup like this...
ProjectID TLPrefix TransNoSuffix TransNo
1 abc 1 abc-1
1 abc 2 abc-2
2 xyz 1 xyz-1

Using the AfterUpdate event of TLPrefix...

Private Sub TLPrefix_AfterUpdate()
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable",
"ProjectID = Forms!frmYourForm!ProjectID"),0) +1
End Sub

or... and I think preferred, would be to drop the TransNo field
altogether, and just calculate it from the TLPrefix, and the
TransNoSuffix... on the fly, whenever needed.
= TLPrefix & "-" & TransNoSuffix
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



I have a field called TransNo in a table I want to use as like a
autonumber
but to format it with the field TLPrefix in front of the number and
to number
per ProjectID.

TransNo should be max number for the ProjectID in the table and add
1 and
append prefix.

ie
ProjectID TLPrefix TransNo
1 abc abc-1
1 abc abc-2
2 xyz xyz-1 and so on

Thank you in advance
 
B

BruceM

Thanks for doing the extra checking, and for explaining your findings. I
did some testing and found that this syntax does not work:

Dim strWhere as String

strWhere =DLookup("[PlowingOrder]","tblAllCustomers","CustomerID =
Me.CustomerID")
but this does:
strWhere =DLookup("[PlowingOrder]","tblAllCustomers","CustomerID = " &
Me.CustomerID)
where CustomerID is a number (different use of quotes if CustomerID is text,
of course).

However, this works:
strWhere =DLookup("[PlowingOrder]","tblAllCustomers","CustomerID =
Forms!frmMain!CustomerID")
as does this:
strWhere =DLookup("[PlowingOrder]","tblAllCustomers","CustomerID = " &
Forms!frmMain!CustomerID)
This seems to make sense based on what you have written about the ES.

As the Control Source of a text box, this works:
=DLookup("[PlowingOrder]","tblAllCustomers","CustomerID =
Forms!frmMain!CustomerID")
as does this:
=DLookup("[PlowingOrder]","tblAllCustomers","CustomerID = [CustomerID]")
as does concatenating either of the above.

In all cases I am working with one form in a test database. I expect only
the full syntax would work if I was comparing to a value on another form.

This seems to indicate that using the full syntax causes Access to step
outside of the expression, in a sense, to use what I now understand to be
the ES. That is, this syntax:
Forms!frmMain!CustomerID
will resolve the value of customer ID using a variety of syntax choices
(concatenated or not). I have learned something new here. I'm not sure
where I will use it, but I wouldn't be surprised if it comes up one of these
days.

Al Campagna said:
Bruce,
I have a form (in a small "test/sample" database) I use to work out
answers to OP questions.

The table for that form has 3 fields...
CustomerID (Num/Integer)
LastName (Text)
PlowingOrder (Num/Single)

I created the following (what I call "full address") text control
calculations...
=DLookup("[PlowingOrder]","tblAllCustomers","LastName=Forms!Form2!LastName")
and
=DLookup("[PlowingOrder]","tblAllCustomers","CustomerID=Forms!Form2!CustomerID")

Both work, returning the correct PlowingOrder, but I'm not sure why.

Equivalent Where arguments where the values are not "live" when called,
require two different syntaxes in the Where...
=DMax("[PlowingOrder]","tblAllCustomers","LastName = 'Morin'")
and
=DMax("[PlowingOrder]","tblAllCustomers","CustomerID = 5")

Allen Browne provided this information about using the
Forms!FormName!ControlName Where argument...
(the asterisks are mine)
There's a thing in Access called the Expression Service (ES.)
If a query contains a parameter such as:
Forms!Form2!LastName
JET calls the ES. The ES looks to see if the Forms collection currently
contains Form2, and if so if Form2 can give a value for LastName. If so,
the
ES provides the value back to JET, and the *ES handles the delimiters*. If
the
ES does not resolve the name (e.g. if Form2 is not open), JET pops up the
parameter dialog.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

BruceM said:
What I meant about the literal text value was something like this for the
Where:
"VendorName = 'Xerox'"

Al Campagna said:
Bruce,
I tested my method using both numeric and text values in the Where,
and they both worked using my "full address" argument.
Let me do a bit of research on this... as to why it works in both
cases.
I've used this method for a long time now, but I must admit, I'm not
sure why it works as it does.
Maybe... "I'm right... for the wrong reason." :-D
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

OK, then. I have never been able to get a where condition to work
without concatenating unless I am using a literal text value, but maybe
the Forms!etc. syntax works differently, or maybe I have been in error
all along. It seems every time I think I understand something it turns
out there is an exception or I got it wrong or whatever.

Bruce,
No, it works just fine.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I thank that needs to be concatenated in the Where condition:
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable", _
"ProjectID = " & Forms!frmYourForm!ProjectID),0) +1

To the OP, without knowing the details of your database's structure
it seems that the record containing TransNoSuffix is in a table that
is related to the main form's table, but it is not clear if TLPrefix
is in the main form's record source, of if it is independent of the
main form's record. Do all of the records where ProjectID = 1 have
the same prefix? If so, it should probably be part of the main form's
record. You can still concatenate TLPrefix and TransNoSuffix as
suggested, but the syntax will be different if one of the fields is
in the main form's record source and the other in the subform's
record source.

deb,
Try a table setup like this...
ProjectID TLPrefix TransNoSuffix TransNo
1 abc 1 abc-1
1 abc 2 abc-2
2 xyz 1 xyz-1

Using the AfterUpdate event of TLPrefix...

Private Sub TLPrefix_AfterUpdate()
TransNoSuffix = NZ(DMax("[TransNoSuffix]", "tblYourTable",
"ProjectID = Forms!frmYourForm!ProjectID"),0) +1
End Sub

or... and I think preferred, would be to drop the TransNo field
altogether, and just calculate it from the TLPrefix, and the
TransNoSuffix... on the fly, whenever needed.
= TLPrefix & "-" & TransNoSuffix
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



I have a field called TransNo in a table I want to use as like a
autonumber
but to format it with the field TLPrefix in front of the number and
to number
per ProjectID.

TransNo should be max number for the ProjectID in the table and add
1 and
append prefix.

ie
ProjectID TLPrefix TransNo
1 abc abc-1
1 abc abc-2
2 xyz xyz-1 and so on

Thank you in advance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top