subform and autonumbering

Hi,

I have a quote form (with header info) and a related
subform with line item detail. The 2 forms are related by
the quote# which is the primary field in the main quote
form and autonumbered. I need to automatically number
each line item on the subform as data is entered.
Example: each quote# can have multiple line item #s
1,2,3,4, etc. Does anyone have an idea on how I can
accomplish this?

Thanks for your help!
Nancy

You can use the DMax() function to accomplish this. On website, see sig
below, there is a small sample database called "FormSubform.mdb" which
illustrates how.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Nancy" wrote in message
...
Hi,

I have a quote form (with header info) and a related
subform with line item detail. The 2 forms are related by
the quote# which is the primary field in the main quote
form and autonumbered. I need to automatically number
each line item on the subform as data is entered.
Example: each quote# can have multiple line item #s
1,2,3,4, etc. Does anyone have an idea on how I can
accomplish this?

Thanks for your help!
Nancy

Hi Nancy,

I generally use code like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.LineNum = Nz(DMax("Linenum", "tblQuoteDetails", "QuoteNumber=" &
Me.Parent.QuoteNumber), 0) + 1
End Sub

Where "tblQuoteDetails" is the name of the table for Quote Details. If you
do this in the BeforeInsert event you have to get the PK field (QuoteNumber)
from the parent form because it will not have been copied into the subform
record when the code executes.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nancy wrote:
Hi,

I have a quote form (with header info) and a related
subform with line item detail. The 2 forms are related by
the quote# which is the primary field in the main quote
form and autonumbered. I need to automatically number
each line item on the subform as data is entered.
Example: each quote# can have multiple line item #s
1,2,3,4, etc. Does anyone have an idea on how I can
accomplish this?

Thanks for your help!
Nancy

-----Original Message-----
Hi,

I have a quote form (with header info) and a related
subform with line item detail. The 2 forms are related
by
the quote# which is the primary field in the main quote
form and autonumbered. I need to automatically number
each line item on the subform as data is entered.
Example: each quote# can have multiple line item #s
1,2,3,4, etc. Does anyone have an idea on how I can
accomplish this?

Thanks for your help!
Nancy
.

Try this in the BeforeUpDate event of the LineNo:
If IsNull(Me!LineNo) Then
'Sequential numbering for Items entered
Me!LineNo= Nz(DMax
("[LineNo]", "YourTable", "[YourPKey] = " & Me!YourPKey),
0) + 1
This works for me. This kind of stuff is better answered
over at Utter Access or Tek-Tips. You are just lucky I am
hunting for something and happened across this. Good luck.
End If

Hi Sandra,

I've tried copying your code as show below on the subform
beforeinsert event.


Private Sub Form_BeforeInsert(Cancel As Interger)
Me!Line# = Nz(DMax("Line#", "tbl_Quote Line Item
File", "Quote#=" & Me.Parent.Quote#), 0) + 1

End Sub

However, I get the following error message when I try to
use the form:

The expression before insert you entered as the event
property setting produced the following error: Can't find
the project or library.

Do you have any idea of what I have done wrong?
I really appreciate your help.

Best regards,
Nancy

-----Original Message-----
Hi Nancy,

I generally use code like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.LineNum = Nz(DMax
("Linenum", "tblQuoteDetails", "QuoteNumber=" &
Me.Parent.QuoteNumber), 0) + 1
End Sub

Where "tblQuoteDetails" is the name of the table for
Quote Details. If you
do this in the BeforeInsert event you have to get the PK
field (QuoteNumber)
from the parent form because it will not have been copied
into the subform
record when the code executes.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nancy wrote:
Hi,

I have a quote form (with header info) and a related
subform with line item detail. The 2 forms are related
by
the quote# which is the primary field in the main quote
form and autonumbered. I need to automatically number
each line item on the subform as data is entered.
Example: each quote# can have multiple line item #s
1,2,3,4, etc. Does anyone have an idea on how I can
accomplish this?

Thanks for your help!
Nancy


.

Thanks Sandra. I needed that too

"Sandra Daigle" wrote in message
...
Hi Nancy,

I generally use code like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.LineNum = Nz(DMax("Linenum", "tblQuoteDetails", "QuoteNumber=" &
Me.Parent.QuoteNumber), 0) + 1
End Sub

Where "tblQuoteDetails" is the name of the table for Quote Details. If you
do this in the BeforeInsert event you have to get the PK field
(QuoteNumber)
from the parent form because it will not have been copied into the subform
record when the code executes.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nancy wrote:
Hi,

I have a quote form (with header info) and a related
subform with line item detail. The 2 forms are related by
the quote# which is the primary field in the main quote
form and autonumbered. I need to automatically number
each line item on the subform as data is entered.
Example: each quote# can have multiple line item #s
1,2,3,4, etc. Does anyone have an idea on how I can
accomplish this?

Thanks for your help!
Nancy

Hi Nancy,

Check your references - in the VB Editor (not in debug mode) click
Tools-References. Look for one that is marked Missing. Check Doug Steele's
article for tips on how to resolve reference problems:
http://members.rogers.com/douglas.j....nceErrors.html

Another possible issue is with your field/Control Names. When you include
spaces and/or special characters in a name you must wrap the entire name in
square brackets:

Me![Line#] = Nz(DMax("[Line#]", "[tbl_Quote Line Item File]", "[Quote#]=" &
Me.[Parent.Quote#]), 0) + 1

This is a good reason for avoiding these kinds of names - I prefer names
like LineNum or LineNo. Easy to read and easy to type.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


wrote:
Hi Sandra,

I've tried copying your code as show below on the subform
beforeinsert event.


Private Sub Form_BeforeInsert(Cancel As Interger)
Me!Line# = Nz(DMax("Line#", "tbl_Quote Line Item
File", "Quote#=" & Me.Parent.Quote#), 0) + 1

End Sub

However, I get the following error message when I try to
use the form:

The expression before insert you entered as the event
property setting produced the following error: Can't find
the project or library.

Do you have any idea of what I have done wrong?
I really appreciate your help.

Best regards,
Nancy

-----Original Message-----
Hi Nancy,

I generally use code like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.LineNum = Nz(DMax
("Linenum", "tblQuoteDetails", "QuoteNumber=" &
Me.Parent.QuoteNumber), 0) + 1
End Sub

Where "tblQuoteDetails" is the name of the table for Quote Details.
If you do this in the BeforeInsert event you have to get the PK
field (QuoteNumber) from the parent form because it will not have
been copied into the subform record when the code executes.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nancy wrote:
Hi,

I have a quote form (with header info) and a related
subform with line item detail. The 2 forms are related by
the quote# which is the primary field in the main quote
form and autonumbered. I need to automatically number
each line item on the subform as data is entered.
Example: each quote# can have multiple line item #s
1,2,3,4, etc. Does anyone have an idea on how I can
accomplish this?

Thanks for your help!
Nancy


.

You're welcome - glad to help.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Tom Ross wrote:
Thanks Sandra. I needed that too

"Sandra Daigle" wrote in message
...
Hi Nancy,

I generally use code like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.LineNum = Nz(DMax("Linenum", "tblQuoteDetails",
"QuoteNumber=" & Me.Parent.QuoteNumber), 0) + 1
End Sub

Where "tblQuoteDetails" is the name of the table for Quote Details.
If you do this in the BeforeInsert event you have to get the PK
field (QuoteNumber) from the parent form because it will not have
been copied into the subform record when the code executes.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nancy wrote:
Hi,

I have a quote form (with header info) and a related
subform with line item detail. The 2 forms are related by
the quote# which is the primary field in the main quote
form and autonumbered. I need to automatically number
each line item on the subform as data is entered.
Example: each quote# can have multiple line item #s
1,2,3,4, etc. Does anyone have an idea on how I can
accomplish this?

Thanks for your help!
Nancy

See?! She's "just lucky" he was passing through. The rest of you folks
in here - you know, the one's who've been posting all that code -
obviously don't know jack!

Just lucky... sheesh!


quest4 wrote:

This works for me. This kind of stuff is better answered
over at Utter Access or Tek-Tips. You are just lucky I am
hunting for something and happened across this. Good luck.
End If

is it just me or has anyone else found they can't log in to 'Tek-Tips' (or
for that matter even re-join) that forum? 'they' don't even respond to the
several email attempts at expressing that.

but, i digress; given that i have two correlated test forms ("master" and
"child") having a common field called "linker" and wherein the sub-form
("child") contains a field called "record" which i'd like to have
autonumbered, can anybody reading this tell me why the modified version of
the code posted earlier doesn't seem to have quite the same outcome?
basically, nothing happens as i scroll through the database and
(interestingly) the add record functionality of the sub-form's is stippled
out.





"grep" wrote:

See?! She's "just lucky" he was passing through. The rest of you folks
in here - you know, the one's who've been posting all that code -
obviously don't know jack!

Just lucky... sheesh!


quest4 wrote:

This works for me. This kind of stuff is better answered
over at Utter Access or Tek-Tips. You are just lucky I am
hunting for something and happened across this. Good luck.
End If