Mysterious Code ... for me anyway.

Greetings,

I'm setting up an access database for my projects with the idea of having it
generate the next part number for new parts. I'm using numbers like: WA1-
2438-1. In that example, both the ones are the new numbers that need to be
generated. The "WA" (Welded Assembly) is the assembly code which is in a
dropdown in my entry form linked to a lookup table that includes the other
assembly codes as well. The code below seems to generate the WA1 next number
okay. BTW, I didn't come up with this code, I don't even know a cuss-word in
VBA, so anything much above kindergarten level will likely be over my head.


Me.[Assembly Number].Value = Nz(DMax(("AssyNum"), _
"PROJECTS", "[AssyCode]='" & _
Me.[Assembly Code].Value & "'"), 0) + 1

The same code, however, seen below, modified to generate the 1 at the end
(the part number) don't fly!

Me.[Part Number].Value = Nz(DMax(("PartNum"), _
"PROJECTS", "[AssyCode]='" & _
Me.[Assembly Code].Value & "'"), 0) + 1

I think ... "think" now ... maybe the reason is that the part number isn't
(is not) linked to a lookup table resulting in the bits after "PROJECTS" and
before the ",0)+1" being erroneous (there's no lookup table to plug it
into). I'm assuming the bits at the end is what generates the next number
when plugged into the AfterUpdate field in my form. The code that works is
plugged into the properties Data list and attached to the AssyCode lookup
table and the code is plugged into the AfterUpdate function/property/ or
whatever it is normally called. Just thought, haven't tried making a null
lookup table if that's even possible (something else for me to try I guess),
but I suppose the PartNum code would be plugged into the "PROJECTS" table
itself. Wondering if anyone might know how to get the next part number to
actually work. Any hints/advice/code/ideas/Samaria Swords/Trap-door
scaffolds or whatever seems most appropriate to my situation will be greatly
appreciated. Thanks a bunch!
~Larry

 

I'm afraid you'll need to post more code than that.
the Nz and DMax functions must be looked at before trying to assist you.

 

Wow! Costa Rica. Always wanted to live in Costa Rica but no speaky de
Espannol. <G>

More code, eh? ... Well ... problem is: don't have any. <G> I have an Access
database set up that will do manual entry, but that's about it. I have two
fields (AssyNum & PartNum) I wanted to be able to give me the next number
for AssyNum and the next number for PartNum in the form, based on the last
numbers generated for each. Been messing with it all weekend and maybe I'm
just not bright enough to do it. I design machines in Inventor and wanted an
easy way to know what the next part number would be. The part numbers will
be something like: WA6-2348-09, where WA6 represents the sixth welded
assembly for the machine, the 2348 is the project number and the 09
represents the ninth part going into the welded assembly. so the numbers I
need to generate are 6 and 09 in this example. The code I posted was
supposed to do that, but I couldn't make it work. Don't know that much about
databases and nothing about VB, so looks like there's a good chance manual
entry is about as far as I can take it. If you have any interest in playing
with it, post back or email me directly by replacing the at, dot and dot
in my email address with the real symbols. Thanks for responding.
~Larry

 

Hola Larry:

It should be easy to do what you want, but since you don´t have that much
experience it's going to take more time than I can handle.
Maybe someone else will jump in and help.

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

 

Hola Jorge,
No problem. Thanks! Better too busy than not busy enough though, eh? <G>
~Larry