Another Auto Number question

[lc]

Forgot I had that bogus email address for groups other than Autodesk. I
changed it to the right one in case there's anything I can do for you guys.
I've fixed hundreds of models for the fella's in my group and been answering
questions for about 3 years, but can't think how I could be much help in
your fields. One never knows though. <G>
~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]

Steve,
Just thought I'd mention your code works without adding the close quote.
Interesting, that. Glad I don't have to do this stuff every day. I have to
be doing something else wrong to get the #Name? in all the form fields
except the lookups I guess. Wonder why that doesn't seem unusual. Since the
WA is a lookup field, should it still be in there?
~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]

The #Name? error suggests that the name of a control on the form or a field
in the form's recordsource is different from what is typed in the code. Not
knowing the real names in your form and query, I can only suggest that you
compare them to see what's not correct.


--
Ken Snell
<MS ACCESS MVP>

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

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]

Ah ha ... that sounds like a plan. I'll go through and see that everything
that should have the same actually name does. Thanks Ken.
~Larry

The #Name? error suggests that the name of a control on the form or a field
in the form's recordsource is different from what is typed in the code. Not
knowing the real names in your form and query, I can only suggest that you
compare them to see what's not correct.


--
Ken Snell
<MS ACCESS MVP>

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

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]

Ah ha ... that sounds like a plan. I'll go through and see that everything
that should have the same actually name does. Thanks Ken. Hope this isn't a
duplicate post. Restarted the news reader and it still didn't show but from
here it looks like the server might be scrambling messages like it does on
ours some times.
~Larry

The #Name? error suggests that the name of a control on the form or a field
in the form's recordsource is different from what is typed in the code. Not
knowing the real names in your form and query, I can only suggest that you
compare them to see what's not correct.


--
Ken Snell
<MS ACCESS MVP>

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

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]

Partial success to report. Got the AssyNum coming in okay. Thank you Ken and
Steve very much.

One more little problem ... from your perspective anyway I'm sure. The same
code doesn't want to work for the PartNum, I think because the part number
doesn't reference a lookup table.

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

All I "think" I need are the Part Number, PartNum and PROJECTS bits for the
part number to work. I've been trying to whack the assembly code bits off
for the last couple hours but I suppose guessing, try-this-try-that and
aimlessly paging through help files is a less-than-adequate approach. Any
ideas on how I should approach this? Thanks again for your help fella's
~Larry

The #Name? error suggests that the name of a control on the form or a field
in the form's recordsource is different from what is typed in the code. Not
knowing the real names in your form and query, I can only suggest that you
compare them to see what's not correct.


--
Ken Snell
<MS ACCESS MVP>

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

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, maybe manual entry isn't such a bad option after all. Thanks for your
time all. I'll quit bug'n ya.
~Larry

 

[Ken Snell]

To try to answer your question, you'll need to tell us more about the
structure of the table PROJECTS. If PartNum is a field name in that table,
and you can find a unique value of it based on just the AssyCode field's
value, then what you've posted should work.

--
Ken Snell
<MS ACCESS MVP>

Partial success to report. Got the AssyNum coming in okay. Thank you Ken and
Steve very much.

One more little problem ... from your perspective anyway I'm sure. The same
code doesn't want to work for the PartNum, I think because the part number
doesn't reference a lookup table.

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

All I "think" I need are the Part Number, PartNum and PROJECTS bits for the
part number to work. I've been trying to whack the assembly code bits off
for the last couple hours but I suppose guessing, try-this-try-that and
aimlessly paging through help files is a less-than-adequate approach. Any
ideas on how I should approach this? Thanks again for your help fella's
~Larry

The #Name? error suggests that the name of a control on the form or a field
in the form's recordsource is different from what is typed in the code. Not
knowing the real names in your form and query, I can only suggest that you
compare them to see what's not correct.


--
Ken Snell
<MS ACCESS MVP>

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

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, let me see if I can lay it out any better.

PROJECTS (table)

Field Name..............................Data Type.........Content

ProjCode (Lookup).................Number.............TS
ProjNum (Manual Entry)..........Text..................<blank>
ProjName (Manual Entry).........Text.................<blank>
AssyCode (Lookup).................Number............WA
..........................................................................BA
..........................................................................MP
AssyNum (NextNumber)..........Text.................<blank>
AssyName (Manual Entry)........Text.................<blank>
PartNum (NextNumber).. ..Number...........<blank>
PartName(Manual Entry)..........Text.................<blank>
ID (key)...................................AutoNumber....<blank>

PROJECTS (form) (wizzard made it)
Fields:
ProjCode
.....ProjNum...ProjName....AssyCode....AssyNum....AssyName....PartNum...PartN
ame

Reference (Lookup) Tables:

ref01project codes (table)
Fields:
ProjCode...............................Text...................TS
ProjID...................................Text...................AutoNumber

ref02 assembly codes (table)
Fields:
AssyCode.............................Text....................same as above
AssyCodeID.........................Text...................AutoNumber

To place the code I:

Opened the PROJECTS Form
Picked Design View
Under Data Tab: picked ref02 assembly codes
Under Event Tab/AfterUpdate: picked dots (...)
Entered VBA: pasted code
Closed VBA/Returned to Form Design View
Found AssyNum field had replaced #Number? with a 1
Jumped up
Did a little dance
Came to my senses
Sat down
Changed to Form View
Became lost

That seems to be pretty much the meat & potatoes.

I realize hand-holding can be tedious and I don't want to impose on your
generosity. So if you'd rather not mess with this, it's quite alright. I
never harbor grudges or ill feelings because I'm the only one would feel the
effect. Amazing how long it took me to figure that out. Thanks either way.
~Larry

To try to answer your question, you'll need to tell us more about the
structure of the table PROJECTS. If PartNum is a field name in that table,
and you can find a unique value of it based on just the AssyCode field's
value, then what you've posted should work.

--
Ken Snell
<MS ACCESS MVP>

Partial success to report. Got the AssyNum coming in okay. Thank you Ken and
Steve very much.

One more little problem ... from your perspective anyway I'm sure. The same
code doesn't want to work for the PartNum, I think because the part number
doesn't reference a lookup table.

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

All I "think" I need are the Part Number, PartNum and PROJECTS bits for the
part number to work. I've been trying to whack the assembly code bits off
for the last couple hours but I suppose guessing, try-this-try-that and
aimlessly paging through help files is a less-than-adequate approach. Any
ideas on how I should approach this? Thanks again for your help fella's
~Larry

The #Name? error suggests that the name of a control on the form or a field
in the form's recordsource is different from what is typed in the

code.
Not

knowing the real names in your form and query, I can only suggest that you
compare them to see what's not correct.


--
Ken Snell
<MS ACCESS MVP>

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

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 >

 

[Ken Snell]

If I'm understanding correctly, the Part Number value needs to be "filtered"
based on the AssyCode, ProjNum, and ProjCode values (if I am reading the
earlier posts correctly), as all three of those values define a specific
sequence, correct?

As such, change your expression to something like this (use actual names in
place of my generic ones):

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


--
Ken Snell
<MS ACCESS MVP>

Well, let me see if I can lay it out any better.

PROJECTS (table)

Field Name..............................Data Type.........Content

ProjCode (Lookup).................Number.............TS
ProjNum (Manual Entry)..........Text..................<blank>
ProjName (Manual Entry).........Text.................<blank>
AssyCode (Lookup).................Number............WA
..........................................................................BA..........................................................................MP
AssyNum (NextNumber)..........Text.................<blank>
AssyName (Manual Entry)........Text.................<blank>
PartNum (NextNumber).. ..Number...........<blank>
PartName(Manual Entry)..........Text.................<blank>
ID (key)...................................AutoNumber....<blank>

PROJECTS (form) (wizzard made it)
Fields:
ProjCode
.....ProjNum...ProjName....AssyCode....AssyNum....AssyName....PartNum...PartN
ame

Reference (Lookup) Tables:

ref01project codes (table)
Fields:
ProjCode...............................Text...................TS
ProjID...................................Text...................AutoNumber

ref02 assembly codes (table)
Fields:
AssyCode.............................Text....................same as above
AssyCodeID.........................Text...................AutoNumber

To place the code I:

Opened the PROJECTS Form
Picked Design View
Under Data Tab: picked ref02 assembly codes
Under Event Tab/AfterUpdate: picked dots (...)
Entered VBA: pasted code
Closed VBA/Returned to Form Design View
Found AssyNum field had replaced #Number? with a 1
Jumped up
Did a little dance
Came to my senses
Sat down
Changed to Form View
Became lost

That seems to be pretty much the meat & potatoes.

I realize hand-holding can be tedious and I don't want to impose on your
generosity. So if you'd rather not mess with this, it's quite alright. I
never harbor grudges or ill feelings because I'm the only one would feel the
effect. Amazing how long it took me to figure that out. Thanks either way.
~Larry

To try to answer your question, you'll need to tell us more about the
structure of the table PROJECTS. If PartNum is a field name in that table,
and you can find a unique value of it based on just the AssyCode field's
value, then what you've posted should work.

--
Ken Snell
<MS ACCESS MVP>

Partial success to report. Got the AssyNum coming in okay. Thank you

Ken
and

Steve very much.

One more little problem ... from your perspective anyway I'm sure. The same
code doesn't want to work for the PartNum, I think because the part number
doesn't reference a lookup table.

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

All I "think" I need are the Part Number, PartNum and PROJECTS bits

for
the

part number to work. I've been trying to whack the assembly code bits off
for the last couple hours but I suppose guessing, try-this-try-that and
aimlessly paging through help files is a less-than-adequate approach. Any
ideas on how I should approach this? Thanks again for your help fella's
~Larry

The #Name? error suggests that the name of a control on the form or a
field
in the form's recordsource is different from what is typed in the code.
Not
knowing the real names in your form and query, I can only suggest

that
you

compare them to see what's not correct.


--
Ken Snell
<MS ACCESS MVP>

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

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]

Thanks Ken,
I'll go give'er a try right now.
~Larry

If I'm understanding correctly, the Part Number value needs to be "filtered"
based on the AssyCode, ProjNum, and ProjCode values (if I am reading the
earlier posts correctly), as all three of those values define a specific
sequence, correct?

As such, change your expression to something like this (use actual names in
place of my generic ones):

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


--
Ken Snell
<MS ACCESS MVP>

Well, let me see if I can lay it out any better.

PROJECTS (table)

Field Name..............................Data Type.........Content

ProjCode (Lookup).................Number.............TS
ProjNum (Manual Entry)..........Text..................<blank>
ProjName (Manual Entry).........Text.................<blank>
AssyCode (Lookup).................Number............WA

..........................................................................BA..........................................................................MP

AssyNum (NextNumber)..........Text.................<blank>
AssyName (Manual Entry)........Text.................<blank>
PartNum (NextNumber).. ..Number...........<blank>
PartName(Manual Entry)..........Text.................<blank>
ID (key)...................................AutoNumber....<blank>

PROJECTS (form) (wizzard made it)
Fields:
ProjCode

.....ProjNum...ProjName....AssyCode....AssyNum....AssyName....PartNum...PartN

ame

Reference (Lookup) Tables:

ref01project codes (table)
Fields:
ProjCode...............................Text...................TS
ProjID...................................Text...................AutoNumber

ref02 assembly codes (table)
Fields:
AssyCode.............................Text....................same as above
AssyCodeID.........................Text...................AutoNumber

To place the code I:

Opened the PROJECTS Form
Picked Design View
Under Data Tab: picked ref02 assembly codes
Under Event Tab/AfterUpdate: picked dots (...)
Entered VBA: pasted code
Closed VBA/Returned to Form Design View
Found AssyNum field had replaced #Number? with a 1
Jumped up
Did a little dance
Came to my senses
Sat down
Changed to Form View
Became lost

That seems to be pretty much the meat & potatoes.

I realize hand-holding can be tedious and I don't want to impose on your
generosity. So if you'd rather not mess with this, it's quite alright. I
never harbor grudges or ill feelings because I'm the only one would feel the
effect. Amazing how long it took me to figure that out. Thanks either way.
~Larry

To try to answer your question, you'll need to tell us more about the
structure of the table PROJECTS. If PartNum is a field name in that table,
and you can find a unique value of it based on just the AssyCode field's
value, then what you've posted should work.

--
Ken Snell
<MS ACCESS MVP>

Partial success to report. Got the AssyNum coming in okay. Thank you Ken
and
Steve very much.

One more little problem ... from your perspective anyway I'm sure. The
same
code doesn't want to work for the PartNum, I think because the part number
doesn't reference a lookup table.

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

All I "think" I need are the Part Number, PartNum and PROJECTS bits for
the
part number to work. I've been trying to whack the assembly code

bits
off

for the last couple hours but I suppose guessing, try-this-try-that and
aimlessly paging through help files is a less-than-adequate

approach.
Any

ideas on how I should approach this? Thanks again for your help fella's
~Larry

The #Name? error suggests that the name of a control on the form

or

a

field
in the form's recordsource is different from what is typed in the code.
Not
knowing the real names in your form and query, I can only suggest that
you
compare them to see what's not correct.


--
Ken Snell
<MS ACCESS MVP>

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

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 ... think I'm going to take the example of the politicians: declare
victory and give up. I'm sure it's not your code, but rather my ignorance in
implementing it. Really sorry to waste your time on this but it seems to be
well beyond my databasic capabilities. This exercise does, however, point
out a basic difference in developing and engineering: developers have to
know how to type and spell. <G> Thanks for your patience.
~Larry


[SNIP]

 

[Ken Snell]

Sorry that we couldn't get you completely working! What you want to do is
very doable in ACCESS...but is very dependent on the setup and how the
sequences are related.

Good luck.