Numbering in Subform

[Laura]

I am having a terrible time trying to automate some numbering in a
subform.

The first table is for locations:

LocnID LocnName
001 first location
002 second location

The location number is not autonumbered. It is incremented using the
following in the main form:

Function nextLocation()

nextLocation = DMax("LocnID", "Locations") + 1

End Function

The second table is for projects:

LocnID ProjID ProjName
001 00101 first project for first location
001 00102 second project for first location
002 00201 first project for second location

I have a Projects subform set up on the main form for Locations. So
far I have manually entered the Project ID numbers, but I have been
trying to figure out a way to have the number automatically populate
according to location and number of projects for that location. In
other words (following the ProjID numbering scheme in the example), if
there was a third location entered, it would be 003. The first project
input for that location would then be 00301. If the next project
entered was for location 001, then the number would be 00103, and so
on.

I have been searching and trying various things for the last three
days and am ready to pull my hair out! The closest thing I've found is
an example given here:
http://groups.google.com/group/micr...scoding/browse_thread/thread/18f1ba32b57960ea
I tried adapting the code given in the second response by Klatuu
without success. In my situation, the LocID is pre-determined in the
subform because that is how they are linked. The disconnect I seem to
be having is finding the DMax of ProjID where the first 3 digits equal
the current LocID.
Here is what I tried:

Function nextProject()

strNextNum = DMax("ProjID", "Projects", "Left([ProjID],3)=[LocnID]")

strGroup = Left(strNextNum, 3)
intNumber = CInt(Mid(strNextNum, 4))

intNumber = intNumber + 1

nextProject = strGroup & "." & Format(intNumber, "00")

End Function

I think I may have the DMax criteria wrong, but being inexperienced, I
am not sure how it should be. I'm also wondering if maybe I've got the
code placed incorrectly. I tried calling it at the Default Value, but
I keep getting "Run-time error '94': Invalid use of null". I noticed
it does not appear like a new record until you start entering data, so
I thought maybe it does know LocnID yet. So then I tried using the
code as an On Dirty event for the ProjName field - same error.
Any suggestions? If I have to, I'll continue to manually number, but
it seems like there should be a way to automate this and avoid mis-
numbering.
Thanks in advance for any help!

 

[George Nicholson]

strNextNum = DMax("ProjID", "Projects", "Left([ProjID],3)=" & [LocnID])

As you had it, your criteria was: "Where 1st 3 characters of ProjID are
'[LocnID]'". That is: not equal to the value of LocID, but equal to the name
of the field itself.

1) You can't use Functions for the DefaultValue of a table, so you need to
put this in the form & handle it during data entry.

2) you might want to break this up into 2 steps, to make debugging easier:

strCriteria = "Left([ProjID],3)=" & [LocnID]
strNextNum = DMax("ProjID", "Projects", strCriteria)

The reason is this: now you can put a breakpoint on "strNextNum =..." line
and easily check the value assigned to strCriteria. With the string vs
number & formatting issues involved when using leading zeros, I suspect you
will drive yourself nuts without it. (If LocID or ProjID actually contain
leading zeros (rather than just displaying them), they are not number
fields, they are text fields and you would need to write your criteria more
like:
strCriteria = "Left([ProjID],3)=' " & [LocnID] & " ' "
(Note: extra spaces added between single & double quotes for clarity. They
need to be removed.)
If they are actual number fields, you'll probably need to use
CLng(Left([ProjID],3) in there and/or a couple of Format()s.

Good Luck & HTH,

I am having a terrible time trying to automate some numbering in a
subform.

The first table is for locations:

LocnID LocnName
001 first location
002 second location

The location number is not autonumbered. It is incremented using the
following in the main form:

Function nextLocation()

nextLocation = DMax("LocnID", "Locations") + 1

End Function

The second table is for projects:

LocnID ProjID ProjName
001 00101 first project for first location
001 00102 second project for first location
002 00201 first project for second location

I have a Projects subform set up on the main form for Locations. So
far I have manually entered the Project ID numbers, but I have been
trying to figure out a way to have the number automatically populate
according to location and number of projects for that location. In
other words (following the ProjID numbering scheme in the example), if
there was a third location entered, it would be 003. The first project
input for that location would then be 00301. If the next project
entered was for location 001, then the number would be 00103, and so
on.

I have been searching and trying various things for the last three
days and am ready to pull my hair out! The closest thing I've found is
an example given here:
http://groups.google.com/group/micr...scoding/browse_thread/thread/18f1ba32b57960ea
I tried adapting the code given in the second response by Klatuu
without success. In my situation, the LocID is pre-determined in the
subform because that is how they are linked. The disconnect I seem to
be having is finding the DMax of ProjID where the first 3 digits equal
the current LocID.
Here is what I tried:

Function nextProject()

strNextNum = DMax("ProjID", "Projects", "Left([ProjID],3)=[LocnID]")

strGroup = Left(strNextNum, 3)
intNumber = CInt(Mid(strNextNum, 4))

intNumber = intNumber + 1

nextProject = strGroup & "." & Format(intNumber, "00")

End Function

I think I may have the DMax criteria wrong, but being inexperienced, I
am not sure how it should be. I'm also wondering if maybe I've got the
code placed incorrectly. I tried calling it at the Default Value, but
I keep getting "Run-time error '94': Invalid use of null". I noticed
it does not appear like a new record until you start entering data, so
I thought maybe it does know LocnID yet. So then I tried using the
code as an On Dirty event for the ProjName field - same error.
Any suggestions? If I have to, I'll continue to manually number, but
it seems like there should be a way to automate this and avoid mis-
numbering.
Thanks in advance for any help!