subform and autonumbering

[Nancy]

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

 

[Roger Carlson]

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.

 

[Sandra Daigle]

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.

 

[quest4]

-----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

 

[Guest]

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.

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

.

 

[Tom Ross]

Thanks Sandra. I needed that too

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.

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

 

[Sandra Daigle]

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.steele/AccessReferenceErrors.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.

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.

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

.

 

[Sandra Daigle]

You're welcome - glad to help.

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

Thanks Sandra. I needed that too

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.

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

 

[grep]

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!