Another Auto Number question

[l]

Hey Folks, first time on this NG, and after searching/browsing the Auto
Number threads, I couldn't find anything to match.

I design machines, not databases so it probably won't be that hard to fathom
my gross inadequacies. I don't even know a cuss-word in VBA and from the
threads I've read, seems that may be some hump to climb ... but I'll ask
anyway. I use Autodesk Inventor to design machines and I have a database
started which seems to link to the bill of material from Inventor. ( I can
export as a *.mdb.) So I think I can get the data in the from the tables.
The problem is I haven't a clue how to get the database to give me the next
part number. The part numbers are something like: WA-#-2438-# (Welded
Assembly-unique identifier-project number-unique identifier) others would
have different prefixes such as MP (Machined Part-unique identifier-project
number-unique identifier) etc. From the perspective of the fast-gun database
gurus on the group ... does this seem hopeless? BTW, I bought a book (Using
Access 2000 - Roger Jennings), but none of his examples deal with
manufacturing or part numbers and I haven't been able to glean a technique
for doing this. Seems like this is something a database would need to do all
the time even in non manufacturing environments ... but then again ... what
do I know! Any help/advice/suggestions/Samurai Swords/trap-door scaffolds or
whatever would be greatly appreciated. Thanks!
~Larry

 

[Ken Snell]

What do you mean by "give me the next part number"? Please provide specific
example of this, using data values.

 

[l]

For instance:
Project Number: 2438

WA1-2438-1
WA1-2438-2
WA1-2438-3
WA1-2438-4
First welded assembly with 4 parts

WA2-2438-1
WA2-2438-2
Second welded assembly with 2 parts


MP1-2438-1
MP1-2438-2
MP1-2438-3
First machined part assembly with 3 parts

BA1-2438-1
BA1-2438-2
BA1-2438-3
BA1-2438-4
First bolted assembly with 4 parts.

BA2-2438-1
BA2-2438-2
Second bolted assembly with 2 parts.

Is that what you mean? Thanks for the response!
~Larry

 

[Ken Snell]

OK - I see the type of part numbers. But I'm not understanding what you
actually want to happen. Do you want the database to display the parts in
this order? Do you want the database to assign a new part number? Still
trying to understand here!

 

[John Vinson]

Hey Folks, first time on this NG, and after searching/browsing the Auto
Number threads, I couldn't find anything to match.

I design machines, not databases so it probably won't be that hard to fathom
my gross inadequacies. I don't even know a cuss-word in VBA and from the
threads I've read, seems that may be some hump to climb ... but I'll ask
anyway. I use Autodesk Inventor to design machines and I have a database
started which seems to link to the bill of material from Inventor. ( I can
export as a *.mdb.) So I think I can get the data in the from the tables.
The problem is I haven't a clue how to get the database to give me the next
part number. The part numbers are something like: WA-#-2438-# (Welded
Assembly-unique identifier-project number-unique identifier) others would
have different prefixes such as MP (Machined Part-unique identifier-project
number-unique identifier) etc. From the perspective of the fast-gun database
gurus on the group ... does this seem hopeless? BTW, I bought a book (Using
Access 2000 - Roger Jennings), but none of his examples deal with
manufacturing or part numbers and I haven't been able to glean a technique
for doing this. Seems like this is something a database would need to do all
the time even in non manufacturing environments ... but then again ... what
do I know! Any help/advice/suggestions/Samurai Swords/trap-door scaffolds or
whatever would be greatly appreciated. Thanks!
~Larry

<chuckle> Thanks for a question, phrased both so clearly and so
entertainingly!

The answer to your question from my point of view is... DON'T. This
kind of field, combining existing fields redundantly into a new field,
is called an "Intelligent Key". This is unfortunately *not* a
compliment.

I'd suggest instead that you have *four* fields in your table -
PartType (e.g. WA, MP, etc.); Project Number; and UniqueID. I don't
quite understand what your two unique identifiers might be!

You can concatenate the fields in a Query or on the Control Source of
a form or report to display your four-part ID; but there is no need to
put a field in your table for that purpose. And you can put some
pretty simple VBA in your data-entry Form to increment the UniqueID
field (or fields) - if you could explain why there are two and what
they mean, I'd be happy to help with the code.

 

[l]

When I'm designing a machine each part needs a part number. It would be much
less tedious and error prone if I could type into the database, for
instance
WA and it would give me the next higher number, like WA206 if the previous
number was WA205. The project number will stay the same for each new
project, Project 2438, then the next new project would be 2439, 2440... .
Then there should be a unique number for the part. For instance using
WA206-2438-204 (204 being the two hundred and forth part in the welded
assembly WA206). The next part number would be WA206-2438-205. The next
project would follow the same convention, but it's number would change to
2439 and the first welded assembly would be WA1-2439-1. Clear as mud? If so,
I will try again. Thanks again.
~Larry

 

[l]

Thanks John,
Actually that's the way I have it set up now. I have a Lookup list dropdowns
with the WA, etc prefixes listed and right now I have Autonumber fields
place-holding for the relevant new numbers but of course they won't generate
new numbers. I responded to Ken's post above and tried to explain how I need
the part numbers to generate. From what I gather so far, what I "think" I
need is some code that will generate consecutive multi-digit (+1) numbers
and I assume there is some mysterious little cauldron somewhere in Access
where the potion gets stirred ... even though I may not know where to find
it. If you need more information don't hesitate to ask. Thanks again John1
~Larry

 

[Ken Snell]

To get the next higher number in a sequence, you don't want to use
Autonumbers for the field type. Instead, define your field as Number (Long
Integer) and then you can use the DMax function to return the current,
highest value, and then add 1 to it. So, for example, you could use this
expression to get the next number in a sequence:

NextNumber = Nz(DMax("NumberFieldName", "TableName", "[LetterFieldName]='" &
"WA" & "'"), 0) + 1

You can use multiple criteria (the third argument of the DMax function) if
you want to have the next number for the WA205-2438-___ (where ___ is the
next number of the sequence) to properly select the right combination.

 

[l]

Hey, thanks a bunch Ken. I'll give that a try. You guys are great!
~Larry

To get the next higher number in a sequence, you don't want to use
Autonumbers for the field type. Instead, define your field as Number (Long
Integer) and then you can use the DMax function to return the current,
highest value, and then add 1 to it. So, for example, you could use this
expression to get the next number in a sequence:

NextNumber = Nz(DMax("NumberFieldName", "TableName", "[LetterFieldName]='" &
"WA" & "'"), 0) + 1

You can use multiple criteria (the third argument of the DMax function) if
you want to have the next number for the WA205-2438-___ (where ___ is the
next number of the sequence) to properly select the right combination.


--
Ken Snell
<MS ACCESS MVP>

When I'm designing a machine each part needs a part number. It would be much
less tedious and error prone if I could type into the database, for
instance
WA and it would give me the next higher number, like WA206 if the previous
number was WA205. The project number will stay the same for each new
project, Project 2438, then the next new project would be 2439, 2440... ..
Then there should be a unique number for the part. For instance using
WA206-2438-204 (204 being the two hundred and forth part in the welded
assembly WA206). The next part number would be WA206-2438-205. The next
project would follow the same convention, but it's number would change to
2439 and the first welded assembly would be WA1-2439-1. Clear as mud? If so,
I will try again. Thanks again.
~Larry

hard
to Inventor.
( I me
the need
to again
...

 

[Ken Snell]

First, this newsgroup *frowns severely* on attachments. Please do not post
attachments; many people who read these groups must pay by the minute for
connection/download time.

Second, the example that I'd given you was not meant to be used in the
Default Value property in the table design. It's meant to be used in a
form -- either in VBA code (to assign a value to a field/control) or as the
control source for a control (and then you need to copy that value to
another control that is bound to the desired field or to the field itself).

Third, your picture doesn't show what expression you actually typed into the
Default Value property, so no one can actually see the full expression in
order to understand what the error message means.

Please type the expression into a post, type the error message into the
post, and tell people what/where you're trying to do things. Because you'd
posted to a "formscoding" newsgroup, we assume that you're working in a form
for what you ask to do.

Post back with more info and details, please.

--
Ken Snell
<MS ACCESS MVP>

The more I search for that spark of brilliance in designing this database,
the more the true nature of my capacity for brilliance is revealed.

I included a screen cap of the error message I'm getting below in the hope
that someone might see whatdaheck I did now ... while I'm off swallowing a
bottle of Anti-Oxidants.
~Larry

To get the next higher number in a sequence, you don't want to use
Autonumbers for the field type. Instead, define your field as Number (Long
Integer) and then you can use the DMax function to return the current,
highest value, and then add 1 to it. So, for example, you could use this
expression to get the next number in a sequence:

NextNumber = Nz(DMax("NumberFieldName", "TableName",

"[LetterFieldName]='"

&
"WA" & "'"), 0) + 1

You can use multiple criteria (the third argument of the DMax function) if
you want to have the next number for the WA205-2438-___ (where ___ is the
next number of the sequence) to properly select the right combination.


--
Ken Snell
<MS ACCESS MVP>

be
much

2440...

. If
so, parts
in and
from but
I'll

give

me a
book

 

[l]

Sorry about the attachment ... Inventor NG allows small attachments. Guess I
don't know the difference in the groups ... hope you and your collogues can
suffer my ignorance this time.

The Expression:
"NextNumber = 1(DMax("PartNumber", "PROJECT", "[AssyCode]='" & "WA" & "'"),
0) + 1



The Error Message"

The expression you entered has invalid . (dot) or ! operator or invalid
parentheses.

You may have entered an invalid identifier or typed parentheses following
the Null constant.



Fields:............Type:....................Desired Function:



ProjCode........Text.....................Lookup Alpha

ProjNumber....Text.....................Manual Entry

ProjName.......Text.....................Manual Entry

AssyCode.......Text.....................Lookup Alpha

AssyNumber...Number..............Auto Next Number

AssyName......Text................... .Manual Entry

PartNumber....Number...............Auto Next Number

ID Number.....Number...............Number



Hope this is more helpful. Sorry about my fowl up. Thanks

~Larry

First, this newsgroup *frowns severely* on attachments. Please do not post
attachments; many people who read these groups must pay by the minute for
connection/download time.

Second, the example that I'd given you was not meant to be used in the
Default Value property in the table design. It's meant to be used in a
form -- either in VBA code (to assign a value to a field/control) or as the
control source for a control (and then you need to copy that value to
another control that is bound to the desired field or to the field itself).

Third, your picture doesn't show what expression you actually typed into the
Default Value property, so no one can actually see the full expression in
order to understand what the error message means.

Please type the expression into a post, type the error message into the
post, and tell people what/where you're trying to do things. Because you'd
posted to a "formscoding" newsgroup, we assume that you're working in a form
for what you ask to do.

Post back with more info and details, please.

--
Ken Snell
<MS ACCESS MVP>

The more I search for that spark of brilliance in designing this database,
the more the true nature of my capacity for brilliance is revealed.

I included a screen cap of the error message I'm getting below in the hope
that someone might see whatdaheck I did now ... while I'm off

swallowing

a
bottle of Anti-Oxidants.
~Larry

To get the next higher number in a sequence, you don't want to use
Autonumbers for the field type. Instead, define your field as Number (Long
Integer) and then you can use the DMax function to return the current,
highest value, and then add 1 to it. So, for example, you could use this
expression to get the next number in a sequence:

NextNumber = Nz(DMax("NumberFieldName", "TableName",

"[LetterFieldName]='"

&
"WA" & "'"), 0) + 1

You can use multiple criteria (the third argument of the DMax

function)

if 2440... change
to

mud?

If what
you searching/browsing
the that
hard

have

a from
the give

bought

a

glean

a would
need

 

[Ken Snell]

Your expression:
"NextNumber = 1(DMax("PartNumber", "PROJECT", "[AssyCode]='" & "WA" & "'"),
0) + 1

has an extraneous 1 at the beginning, it doesn't need the NextNumber (that
was a generic variable name that I'd used in the example), and it's missing
the Nz function.

But, as I mentioned, you cannot use this approach as an expression for the
Default Value of a field in the table design. You can only use this approach
when you create a new record via a form. It's not possible for you to use
the field's Default Value in the table to do what you want.

What you do is use a form that allows you to enter parts into an order, and
to let you select the type of part (group), etc., and then to use code in
the form's module to assign the next numbers in the sequences.

Have you set up a form for your database? Let's start with it if you have;
post some info about its setup.
--
Ken Snell
<MS ACCESS MVP>

Sorry about the attachment ... Inventor NG allows small attachments. Guess I
don't know the difference in the groups ... hope you and your collogues can
suffer my ignorance this time.

The Expression:
"NextNumber = 1(DMax("PartNumber", "PROJECT", "[AssyCode]='" & "WA" & "'"),
0) + 1



The Error Message"

The expression you entered has invalid . (dot) or ! operator or invalid
parentheses.

You may have entered an invalid identifier or typed parentheses following
the Null constant.



Fields:............Type:....................Desired Function:



ProjCode........Text.....................Lookup Alpha

ProjNumber....Text.....................Manual Entry

ProjName.......Text.....................Manual Entry

AssyCode.......Text.....................Lookup Alpha

AssyNumber...Number..............Auto Next Number

AssyName......Text................... .Manual Entry

PartNumber....Number...............Auto Next Number

ID Number.....Number...............Number



Hope this is more helpful. Sorry about my fowl up. Thanks

~Larry

First, this newsgroup *frowns severely* on attachments. Please do not post
attachments; many people who read these groups must pay by the minute for
connection/download time.

Second, the example that I'd given you was not meant to be used in the
Default Value property in the table design. It's meant to be used in a
form -- either in VBA code (to assign a value to a field/control) or as the
control source for a control (and then you need to copy that value to
another control that is bound to the desired field or to the field itself).

Third, your picture doesn't show what expression you actually typed into the
Default Value property, so no one can actually see the full expression in
order to understand what the error message means.

Please type the expression into a post, type the error message into the
post, and tell people what/where you're trying to do things. Because you'd
posted to a "formscoding" newsgroup, we assume that you're working in a form
for what you ask to do.

Post back with more info and details, please.

--
Ken Snell
<MS ACCESS MVP>

The more I search for that spark of brilliance in designing this database,
the more the true nature of my capacity for brilliance is revealed.

I included a screen cap of the error message I'm getting below in the hope
that someone might see whatdaheck I did now ... while I'm off

swallowing

a
bottle of Anti-Oxidants.
~Larry





To get the next higher number in a sequence, you don't want to use
Autonumbers for the field type. Instead, define your field as Number (Long
Integer) and then you can use the DMax function to return the current,
highest value, and then add 1 to it. So, for example, you could use this
expression to get the next number in a sequence:

NextNumber = Nz(DMax("NumberFieldName", "TableName", "[LetterFieldName]='"
&
"WA" & "'"), 0) + 1

You can use multiple criteria (the third argument of the DMax

function)

if
you want to have the next number for the WA205-2438-___ (where ___

is
the

next number of the sequence) to properly select the right combination.


--
Ken Snell
<MS ACCESS MVP>

When I'm designing a machine each part needs a part number. It

would
be

much
less tedious and error prone if I could type into the database, for
instance
WA and it would give me the next higher number, like WA206 if the
previous
number was WA205. The project number will stay the same for each new
project, Project 2438, then the next new project would be 2439, 2440...
.
Then there should be a unique number for the part. For instance using
WA206-2438-204 (204 being the two hundred and forth part in the welded
assembly WA206). The next part number would be WA206-2438-205. The next
project would follow the same convention, but it's number would change
to
2439 and the first welded assembly would be WA1-2439-1. Clear as

mud?

If
so,
I will try again. Thanks again.
~Larry

OK - I see the type of part numbers. But I'm not understanding what
you
actually want to happen. Do you want the database to display the parts
in
this order? Do you want the database to assign a new part number?
Still
trying to understand here!

--
Ken Snell
<MS ACCESS MVP>

For instance:
Project Number: 2438

WA1-2438-1
WA1-2438-2
WA1-2438-3
WA1-2438-4
First welded assembly with 4 parts

WA2-2438-1
WA2-2438-2
Second welded assembly with 2 parts


MP1-2438-1
MP1-2438-2
MP1-2438-3
First machined part assembly with 3 parts

BA1-2438-1
BA1-2438-2
BA1-2438-3
BA1-2438-4
First bolted assembly with 4 parts.

BA2-2438-1
BA2-2438-2
Second bolted assembly with 2 parts.

Is that what you mean? Thanks for the response!
~Larry



What do you mean by "give me the next part number"? Please provide
specific
example of this, using data values.

--
Ken Snell
<MS ACCESS MVP>

Hey Folks, first time on this NG, and after searching/browsing
the
Auto
Number threads, I couldn't find anything to match.

I design machines, not databases so it probably won't be that
hard
to
fathom
my gross inadequacies. I don't even know a cuss-word in

VBA
and

from
the
threads I've read, seems that may be some hump to climb

....
but

I'll
ask
anyway. I use Autodesk Inventor to design machines and I

have

a
database
started which seems to link to the bill of material from
Inventor.
( I
can
export as a *.mdb.) So I think I can get the data in the from
the
tables.
The problem is I haven't a clue how to get the database to give
me
the
next
part number. The part numbers are something like: WA-#-2438-#
(Welded
Assembly-unique identifier-project number-unique identifier)
others
would
have different prefixes such as MP (Machined Part-unique
identifier-project
number-unique identifier) etc. From the perspective of the
fast-gun
database
gurus on the group ... does this seem hopeless? BTW, I

bought

a
book
(Using
Access 2000 - Roger Jennings), but none of his examples deal
with
manufacturing or part numbers and I haven't been able to

glean

a
technique
for doing this. Seems like this is something a database would
need
to
do
all
the time even in non manufacturing environments ... but then
again
...
what
do I know! Any help/advice/suggestions/Samurai Swords/trap-door
scaffolds
or
whatever would be greatly appreciated. Thanks!
~Larry

 

[Steve Schapel]

I,

PMFJI here.

As I understand it, the expression you are using is going in the
Default Value property setting of a control on your form. Is this
correct? Ok, if so you don't want any = in there. Plus the "" stuff
could be simplified. Besides which, I see that Ken showed you an
expression with a Nz function, but yours has a "1" in its place.

So, maybe the Default Value expression should be:
Nz(DMax("PartNumber", "PROJECT", "[AssyCode]='WA'"),0)+1

This assumes that the entry in the AssyCode field is WA and that the
other components of the overall code, e.g. Project Number, Part Number
are stored in separate fields. Is this correct? I mean,
WA206-2438-205 isn't an example of what you've got in your AssyCode
field, is it?

- Steve Schapel, Microsoft Access MVP

 

[l]

Yes, I have a form to enter the data in, I'll list what I have below and the
source/desired source.

Labels:

Project Code:.........................Lookup Table
Project Number:.....................Manual Entry
Project Name:........................Manual Entry
Assembly Code:.....................Lookup Table
Assembly Number:.................Auto Next Number
Assembly Name:....................Manual Entry
Part Number:.........................Auto Next Number
Part Name:............................Manual Entry
Part Stock Code:...................Lookup Table
Stock Description:.................Lookup Table
Part Material Code:...............Lookup Table
Material Description:..............Lookup Table
Diameter:..............................Inventor OLE parameter from Excel
(hopefully)
Length:...................................Inventor OLE parameter from Excel
(hopefully)
Width:...................................Inventor OLE parameter from Excel
(hopefully)
Height:..................................Inventor OLE parameter from Excel
(hopefully)
Gage:....................................Inventor OLE parameter from Excel
(hopefully)
Linear Feet:...........................Inventor OLE parameter from Excel
(hopefully)
Square Feet:..........................Inventor OLE parameter from Excel
(hopefully)
Total Linear Feet:..................Inventor OLE parameter from Excel
(hopefully)
Total Square Feet:.................Inventor OLE parameter from Excel
(hopefully)


If I left something out please let me know. I think I understand about the
formula or code I guess is the proper term. Thanks for your patience.
~Larry

Your expression:
"NextNumber = 1(DMax("PartNumber", "PROJECT", "[AssyCode]='" & "WA" & "'"),
0) + 1

has an extraneous 1 at the beginning, it doesn't need the NextNumber (that
was a generic variable name that I'd used in the example), and it's missing
the Nz function.

But, as I mentioned, you cannot use this approach as an expression for the
Default Value of a field in the table design. You can only use this approach
when you create a new record via a form. It's not possible for you to use
the field's Default Value in the table to do what you want.

What you do is use a form that allows you to enter parts into an order, and
to let you select the type of part (group), etc., and then to use code in
the form's module to assign the next numbers in the sequences.

Have you set up a form for your database? Let's start with it if you have;
post some info about its setup.

[Snip]

 

[l]

Yeah (wagging head)... I thought the Nz was a default number place-holder
and I had no idea that NextNumber was generic. Really up on this code stuff,
eh?

"...don't want any = in there."
Think that finely penetrated bone as has the code. (Thanks for that example
doesn't look like I have to change anything! Now that's manageable ... well
maybe.)

AssyCode isn't the kind of code used to program with (doubt that's what you
thought it's my shortcoming), it's just a prefix in the part number to
indicate the part is a Welded Assembly. There are also BA: Bolted Assembly,
MP: Machined Part and so on. The "WA" should come from a Lookup Table.

"...other components of the overall code, e.g. Project Number, Part Number
are stored in separate fields."
Yes, everything is in separate fields but I haven't any idea if that is the
best approach.

"WA206-2438-205 isn't an example of what you've got in your AssyCode field,
is it?"
No, they are in separate fields.

Water 'round here's deep, eh? <G>
~Larry

I,

PMFJI here.

As I understand it, the expression you are using is going in the
Default Value property setting of a control on your form. Is this
correct? Ok, if so you don't want any = in there. Plus the "" stuff
could be simplified. Besides which, I see that Ken showed you an
expression with a Nz function, but yours has a "1" in its place.

So, maybe the Default Value expression should be:
Nz(DMax("PartNumber", "PROJECT", "[AssyCode]='WA'"),0)+1

This assumes that the entry in the AssyCode field is WA and that the
other components of the overall code, e.g. Project Number, Part Number
are stored in separate fields. Is this correct? I mean,
WA206-2438-205 isn't an example of what you've got in your AssyCode
field, is it?

- Steve Schapel, Microsoft Access MVP

Sorry about the attachment ... Inventor NG allows small attachments. Guess I
don't know the difference in the groups ... hope you and your collogues can
suffer my ignorance this time.

The Expression:
"NextNumber = 1(DMax("PartNumber", "PROJECT", "[AssyCode]='" & "WA" & "'"),
0) + 1



The Error Message"

The expression you entered has invalid . (dot) or ! operator or invalid
parentheses.

You may have entered an invalid identifier or typed parentheses following
the Null constant.

 

[Ken Snell]

OK - let me be sure that I understand what is to happen here.

1. You'll select an assembly code from a combo box (? lookup table ?).
You'll want the assembly number to be the next sequence number.

2. You'll select or enter an assembly name. You'll want the part number to
be the next sequence number.

OK so far?

If yes, then we can use the AfterUpdate event of the Assembly Code control
to put in the Assembly Number. Same type of approach for Assembly Name in
order to get Part Number.

To do this, open your form in design view. Click on the Properties icon on
the toolbar. In the Properties window, select Assembly Code from the
dropdown list at top of window. Then click on Event tab. Click in box next
to After Update, and then click on the three-little-dots box at far right of
that box. Select Code Builder from the next window.

You'll now be in Visual Basic Editor, and you should see three lines:
Private Sub Assembly_Code_AfterUpdate()

End Sub

with the cursor on that second, blank row.

In that second row, type these rows (substitute real names for "generic
names"):
Me.[Assembly Number].Value = Nz(DMax(("Generic Assembly Number
Name", _
"Generic Table Name", "[Generic Assembly Code Name]='" & _
Me.[Assembly Code].Value & "'"), 0) + 1


Do similar steps for the Assembly Name control, changing names as needed.

Save the changes, and close the VBE window.

This is just a guess, as I don't completely "see" your form's setup (control
names, field names, table names, control sources, etc.) in my head.
--
Ken Snell
<MS ACCESS MVP>

Yes, I have a form to enter the data in, I'll list what I have below and the
source/desired source.

Labels:

Project Code:.........................Lookup Table
Project Number:.....................Manual Entry
Project Name:........................Manual Entry
Assembly Code:.....................Lookup Table
Assembly Number:.................Auto Next Number
Assembly Name:....................Manual Entry
Part Number:.........................Auto Next Number
Part Name:............................Manual Entry
Part Stock Code:...................Lookup Table
Stock Description:.................Lookup Table
Part Material Code:...............Lookup Table
Material Description:..............Lookup Table
Diameter:..............................Inventor OLE parameter from Excel
(hopefully)
Length:...................................Inventor OLE parameter from Excel
(hopefully)
Width:...................................Inventor OLE parameter from Excel
(hopefully)
Height:..................................Inventor OLE parameter from Excel
(hopefully)
Gage:....................................Inventor OLE parameter from Excel
(hopefully)
Linear Feet:...........................Inventor OLE parameter from Excel
(hopefully)
Square Feet:..........................Inventor OLE parameter from Excel
(hopefully)
Total Linear Feet:..................Inventor OLE parameter from Excel
(hopefully)
Total Square Feet:.................Inventor OLE parameter from Excel
(hopefully)


If I left something out please let me know. I think I understand about the
formula or code I guess is the proper term. Thanks for your patience.
~Larry

< snip >

 

[l]

That sounds like exactly what I would like to do, Ken. I'll see if I can
make it work. Thanks a bunch!
~Larry

OK - let me be sure that I understand what is to happen here.

1. You'll select an assembly code from a combo box (? lookup table ?).
You'll want the assembly number to be the next sequence number.

2. You'll select or enter an assembly name. You'll want the part number to
be the next sequence number.

OK so far?

If yes, then we can use the AfterUpdate event of the Assembly Code control
to put in the Assembly Number. Same type of approach for Assembly Name in
order to get Part Number.

To do this, open your form in design view. Click on the Properties icon on
the toolbar. In the Properties window, select Assembly Code from the
dropdown list at top of window. Then click on Event tab. Click in box next
to After Update, and then click on the three-little-dots box at far right of
that box. Select Code Builder from the next window.

You'll now be in Visual Basic Editor, and you should see three lines:
Private Sub Assembly_Code_AfterUpdate()

End Sub

with the cursor on that second, blank row.

In that second row, type these rows (substitute real names for "generic
names"):
Me.[Assembly Number].Value = Nz(DMax(("Generic Assembly Number
Name", _
"Generic Table Name", "[Generic Assembly Code Name]='" & _
Me.[Assembly Code].Value & "'"), 0) + 1


Do similar steps for the Assembly Name control, changing names as needed.

Save the changes, and close the VBE window.

This is just a guess, as I don't completely "see" your form's setup (control
names, field names, table names, control sources, etc.) in my head.
--
Ken Snell
<MS ACCESS MVP>

Yes, I have a form to enter the data in, I'll list what I have below and the
source/desired source.

Labels:

Project Code:.........................Lookup Table
Project Number:.....................Manual Entry
Project Name:........................Manual Entry
Assembly Code:.....................Lookup Table
Assembly Number:.................Auto Next Number
Assembly Name:....................Manual Entry
Part Number:.........................Auto Next Number
Part Name:............................Manual Entry
Part Stock Code:...................Lookup Table
Stock Description:.................Lookup Table
Part Material Code:...............Lookup Table
Material Description:..............Lookup Table
Diameter:..............................Inventor OLE parameter from Excel
(hopefully)
Length:...................................Inventor OLE parameter from Excel
(hopefully)
Width:...................................Inventor OLE parameter from Excel
(hopefully)
Height:..................................Inventor OLE parameter from Excel
(hopefully)
Gage:....................................Inventor OLE parameter from Excel
(hopefully)
Linear Feet:...........................Inventor OLE parameter from Excel
(hopefully)
Square Feet:..........................Inventor OLE parameter from Excel
(hopefully)
Total Linear Feet:..................Inventor OLE parameter from Excel
(hopefully)
Total Square Feet:.................Inventor OLE parameter from Excel
(hopefully)


If I left something out please let me know. I think I understand about the
formula or code I guess is the proper term. Thanks for your patience.
~Larry

< snip >

 

[lc]

Well ... after about a hundred tries the text in the code is still red
except for the two underscores.

The table name is PROJECTS.
The form is named ProjTable
The assembly code number is AssyNum in the PROJECTS table.
The assembly code lookup table is named AssyCode in the PROJECTS table. The
actual assembly codes table is "ref04 assembly codes" where the actual list
resides and is what AssyCode in the PROJECTS table refers to.

Got into VBA as you described and typed the code you provided on the second
line changing only the two generic entries as below: copied from VBA:

Option Compare Database

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


The code I referenced from your email was:

Me.[Assembly Number].Value = Nz(DMax(("Generic Assembly Number
Name", _
"Generic Table Name", "[Generic Assembly Code Name]='" & _
Me.[Assembly Code].Value & "'"), 0) + 1

Sorry to be so dense, but I'm skate'n in thin air here.
~Larry

OK - let me be sure that I understand what is to happen here.

1. You'll select an assembly code from a combo box (? lookup table ?).
You'll want the assembly number to be the next sequence number.

2. You'll select or enter an assembly name. You'll want the part number to
be the next sequence number.

OK so far?

If yes, then we can use the AfterUpdate event of the Assembly Code control
to put in the Assembly Number. Same type of approach for Assembly Name in
order to get Part Number.

To do this, open your form in design view. Click on the Properties icon on
the toolbar. In the Properties window, select Assembly Code from the
dropdown list at top of window. Then click on Event tab. Click in box next
to After Update, and then click on the three-little-dots box at far right of
that box. Select Code Builder from the next window.

You'll now be in Visual Basic Editor, and you should see three lines:
Private Sub Assembly_Code_AfterUpdate()

End Sub

with the cursor on that second, blank row.

In that second row, type these rows (substitute real names for "generic
names"):
Me.[Assembly Number].Value = Nz(DMax(("Generic Assembly Number
Name", _
"Generic Table Name", "[Generic Assembly Code Name]='" & _
Me.[Assembly Code].Value & "'"), 0) + 1


Do similar steps for the Assembly Name control, changing names as needed.

Save the changes, and close the VBE window.

This is just a guess, as I don't completely "see" your form's setup (control
names, field names, table names, control sources, etc.) in my head.
--
Ken Snell
<MS ACCESS MVP>

Yes, I have a form to enter the data in, I'll list what I have below and the
source/desired source.

Labels:

Project Code:.........................Lookup Table
Project Number:.....................Manual Entry
Project Name:........................Manual Entry
Assembly Code:.....................Lookup Table
Assembly Number:.................Auto Next Number
Assembly Name:....................Manual Entry
Part Number:.........................Auto Next Number
Part Name:............................Manual Entry
Part Stock Code:...................Lookup Table
Stock Description:.................Lookup Table
Part Material Code:...............Lookup Table
Material Description:..............Lookup Table
Diameter:..............................Inventor OLE parameter from Excel
(hopefully)
Length:...................................Inventor OLE parameter from Excel
(hopefully)
Width:...................................Inventor OLE parameter from Excel
(hopefully)
Height:..................................Inventor OLE parameter from Excel
(hopefully)
Gage:....................................Inventor OLE parameter from Excel
(hopefully)
Linear Feet:...........................Inventor OLE parameter from Excel
(hopefully)
Square Feet:..........................Inventor OLE parameter from Excel
(hopefully)
Total Linear Feet:..................Inventor OLE parameter from Excel
(hopefully)
Total Square Feet:.................Inventor OLE parameter from Excel
(hopefully)


If I left something out please let me know. I think I understand about the
formula or code I guess is the proper term. Thanks for your patience.
~Larry

< snip >

 

[lc]

Oh forgot, If I edit a letter in the code to the same value it was the code
turns black but then gives an error:
Compile Error:
Expected: )

Does that mean a close quote is missing?
~Larry

That sounds like exactly what I would like to do, Ken. I'll see if I can
make it work. Thanks a bunch!
~Larry

OK - let me be sure that I understand what is to happen here.

1. You'll select an assembly code from a combo box (? lookup table ?).
You'll want the assembly number to be the next sequence number.

2. You'll select or enter an assembly name. You'll want the part number to
be the next sequence number.

OK so far?

If yes, then we can use the AfterUpdate event of the Assembly Code control
to put in the Assembly Number. Same type of approach for Assembly Name in
order to get Part Number.

To do this, open your form in design view. Click on the Properties icon on
the toolbar. In the Properties window, select Assembly Code from the
dropdown list at top of window. Then click on Event tab. Click in box next
to After Update, and then click on the three-little-dots box at far

right

of
that box. Select Code Builder from the next window.

You'll now be in Visual Basic Editor, and you should see three lines:
Private Sub Assembly_Code_AfterUpdate()

End Sub

with the cursor on that second, blank row.

In that second row, type these rows (substitute real names for "generic
names"):
Me.[Assembly Number].Value = Nz(DMax(("Generic Assembly Number
Name", _
"Generic Table Name", "[Generic Assembly Code Name]='" & _
Me.[Assembly Code].Value & "'"), 0) + 1


Do similar steps for the Assembly Name control, changing names as needed.

Save the changes, and close the VBE window.

This is just a guess, as I don't completely "see" your form's setup (control
names, field names, table names, control sources, etc.) in my head.
--
Ken Snell
<MS ACCESS MVP>

Yes, I have a form to enter the data in, I'll list what I have below

and
the

source/desired source.

Labels:

Project Code:.........................Lookup Table
Project Number:.....................Manual Entry
Project Name:........................Manual Entry
Assembly Code:.....................Lookup Table
Assembly Number:.................Auto Next Number
Assembly Name:....................Manual Entry
Part Number:.........................Auto Next Number
Part Name:............................Manual Entry
Part Stock Code:...................Lookup Table
Stock Description:.................Lookup Table
Part Material Code:...............Lookup Table
Material Description:..............Lookup Table
Diameter:..............................Inventor OLE parameter from Excel
(hopefully)
Length:...................................Inventor OLE parameter from Excel
(hopefully)
Width:...................................Inventor OLE parameter from Excel
(hopefully)
Height:..................................Inventor OLE parameter from Excel
(hopefully)
Gage:....................................Inventor OLE parameter from Excel
(hopefully)
Linear Feet:...........................Inventor OLE parameter from Excel
(hopefully)
Square Feet:..........................Inventor OLE parameter from Excel
(hopefully)
Total Linear Feet:..................Inventor OLE parameter from Excel
(hopefully)
Total Square Feet:.................Inventor OLE parameter from Excel
(hopefully)


If I left something out please let me know. I think I understand about the
formula or code I guess is the proper term. Thanks for your patience.
~Larry

< snip >

 

[lc]

Update ... a close quote behind the Generic Assembly Number Name fixes the
code. It's black, but the form still shows #Name? in all but the two
lookup fields.
~Larry

Oh forgot, If I edit a letter in the code to the same value it was the code
turns black but then gives an error:
Compile Error:
Expected: )

Does that mean a close quote is missing?
~Larry

That sounds like exactly what I would like to do, Ken. I'll see if I can
make it work. Thanks a bunch!
~Larry

number

to

icon

on

the toolbar. In the Properties window, select Assembly Code from the
dropdown list at top of window. Then click on Event tab. Click in box next
to After Update, and then click on the three-little-dots box at far

right

of
that box. Select Code Builder from the next window.

You'll now be in Visual Basic Editor, and you should see three lines:
Private Sub Assembly_Code_AfterUpdate()

End Sub

with the cursor on that second, blank row.

In that second row, type these rows (substitute real names for "generic
names"):
Me.[Assembly Number].Value = Nz(DMax(("Generic Assembly Number
Name", _
"Generic Table Name", "[Generic Assembly Code Name]='" & _
Me.[Assembly Code].Value & "'"), 0) + 1


Do similar steps for the Assembly Name control, changing names as needed.

Save the changes, and close the VBE window.

This is just a guess, as I don't completely "see" your form's setup (control
names, field names, table names, control sources, etc.) in my head.
--
Ken Snell
<MS ACCESS MVP>



Yes, I have a form to enter the data in, I'll list what I have below and
the
source/desired source.

Labels:

Project Code:.........................Lookup Table
Project Number:.....................Manual Entry
Project Name:........................Manual Entry
Assembly Code:.....................Lookup Table
Assembly Number:.................Auto Next Number
Assembly Name:....................Manual Entry
Part Number:.........................Auto Next Number
Part Name:............................Manual Entry
Part Stock Code:...................Lookup Table
Stock Description:.................Lookup Table
Part Material Code:...............Lookup Table
Material Description:..............Lookup Table
Diameter:..............................Inventor OLE parameter from Excel
(hopefully)
Length:...................................Inventor OLE parameter from
Excel
(hopefully)
Width:...................................Inventor OLE parameter from Excel
(hopefully)
Height:..................................Inventor OLE parameter from Excel
(hopefully)
Gage:....................................Inventor OLE parameter from Excel
(hopefully)
Linear Feet:...........................Inventor OLE parameter from Excel
(hopefully)
Square Feet:..........................Inventor OLE parameter from Excel
(hopefully)
Total Linear Feet:..................Inventor OLE parameter from Excel
(hopefully)
Total Square Feet:.................Inventor OLE parameter from Excel
(hopefully)


If I left something out please let me know. I think I understand

about
the

formula or code I guess is the proper term. Thanks for your patience.
~Larry

< snip >