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