Require help on slight twist to auto-incrementing a number

[CB]

Hi everyone,

I’m having some difficulty auto-incrementing a Quote Request number on a
sub-form I’ve created. I’ve searched the newsgroups and haven’t yet found
anything quite like this.

My main form includes contractor information and contract numbers (record
source is a query pulling info from two tables: t_ContractorInfo and
t_ContractSpecifics) and the sub-form is where quote requests will be
entered. CSID is the field that links the main form to the sub-form.

The record source for my sub-form is the table t_QuoteRequests with the
following fields (among others):
QRID – autonum, PK (otherwise meaningless)
QRNum – number (quote request number)
CSID – number (FK – contract specifics ID)

CSID and QRNum are indexed together since I want to prevent duplicate Quote
Request numbers for a given contract.

Overall, the form works fine. What I now need is that when a new quote
request record is added, QRNum increments appropriately by 1 **depending on
the CSID field.**

In other words, I need all contracts to have sequential QRNums all starting
at one. The way things currently stand, QRNum increments the same way the
QRID field does so the QRNums for a given contract are not sequential.

Currently,
QRID QRNum CSID
1 1 1
2 2 1
3 3 4
4 4 1
5 5 3

What I need,
QRID QRNum CSID
1 1 1
2 2 1
3 1 4
4 3 1
5 1 3

The following code is what I currently have in the sub-form’s BeforeInsert
event. FWIW, I originally had this in the sub-form’s BeforeUpdate event (per
what I found on the newsgroups) but the field didn’t seem to populate until
after I left the record.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![QRNum] = Nz(DMax("[QRNum]", "t_QuoteRequests"), 0) + 1
End Sub

Thanks for any and all assistance!

Warm regards,

Chris

 

[Dirk Goldgar]

Hi everyone,

I’m having some difficulty auto-incrementing a Quote Request number on a
sub-form I’ve created. I’ve searched the newsgroups and haven’t yet found
anything quite like this.

My main form includes contractor information and contract numbers (record
source is a query pulling info from two tables: t_ContractorInfo and
t_ContractSpecifics) and the sub-form is where quote requests will be
entered. CSID is the field that links the main form to the sub-form.

The record source for my sub-form is the table t_QuoteRequests with the
following fields (among others):
QRID – autonum, PK (otherwise meaningless)
QRNum – number (quote request number)
CSID – number (FK – contract specifics ID)

CSID and QRNum are indexed together since I want to prevent duplicate
Quote
Request numbers for a given contract.

Overall, the form works fine. What I now need is that when a new quote
request record is added, QRNum increments appropriately by 1 **depending
on
the CSID field.**

In other words, I need all contracts to have sequential QRNums all
starting
at one. The way things currently stand, QRNum increments the same way the
QRID field does so the QRNums for a given contract are not sequential.

Currently,
QRID QRNum CSID
1 1 1
2 2 1
3 3 4
4 4 1
5 5 3

What I need,
QRID QRNum CSID
1 1 1
2 2 1
3 1 4
4 3 1
5 1 3

The following code is what I currently have in the sub-form’s BeforeInsert
event. FWIW, I originally had this in the sub-form’s BeforeUpdate event
(per
what I found on the newsgroups) but the field didn’t seem to populate
until
after I left the record.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![QRNum] = Nz(DMax("[QRNum]", "t_QuoteRequests"), 0) + 1
End Sub

Thanks for any and all assistance!

How about this, Chris?

'----- start of code -----
Private Sub Form_BeforeInsert(Cancel As Integer)

Me![QRNum] = _
Nz(DMax("QRNum", "t_QuoteRequests", "CSID=" & Me.CSID), 0) + 1

End Sub

'----- end of code -----

The only question in my mind is whether the CSID field on the subform will
be populated yet in the subform's BeforeInsert event. If it isn't, the code
can be modified to pick up the current CSID from the parent form:

"CSID=" & Me.Parent!CSID

 

[CB]

Thanks Dirk! What a simple fix. You made my weekend!

Chris

Hi everyone,

I’m having some difficulty auto-incrementing a Quote Request number on a
sub-form I’ve created. I’ve searched the newsgroups and haven’t yet found
anything quite like this.

My main form includes contractor information and contract numbers (record
source is a query pulling info from two tables: t_ContractorInfo and
t_ContractSpecifics) and the sub-form is where quote requests will be
entered. CSID is the field that links the main form to the sub-form.

The record source for my sub-form is the table t_QuoteRequests with the
following fields (among others):
QRID – autonum, PK (otherwise meaningless)
QRNum – number (quote request number)
CSID – number (FK – contract specifics ID)

CSID and QRNum are indexed together since I want to prevent duplicate
Quote
Request numbers for a given contract.

Overall, the form works fine. What I now need is that when a new quote
request record is added, QRNum increments appropriately by 1 **depending
on
the CSID field.**

In other words, I need all contracts to have sequential QRNums all
starting
at one. The way things currently stand, QRNum increments the same way the
QRID field does so the QRNums for a given contract are not sequential.

Currently,
QRID QRNum CSID
1 1 1
2 2 1
3 3 4
4 4 1
5 5 3

What I need,
QRID QRNum CSID
1 1 1
2 2 1
3 1 4
4 3 1
5 1 3

The following code is what I currently have in the sub-form’s BeforeInsert
event. FWIW, I originally had this in the sub-form’s BeforeUpdate event
(per
what I found on the newsgroups) but the field didn’t seem to populate
until
after I left the record.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![QRNum] = Nz(DMax("[QRNum]", "t_QuoteRequests"), 0) + 1
End Sub

Thanks for any and all assistance!

How about this, Chris?

'----- start of code -----
Private Sub Form_BeforeInsert(Cancel As Integer)

Me![QRNum] = _
Nz(DMax("QRNum", "t_QuoteRequests", "CSID=" & Me.CSID), 0) + 1

End Sub

'----- end of code -----

The only question in my mind is whether the CSID field on the subform will
be populated yet in the subform's BeforeInsert event. If it isn't, the code
can be modified to pick up the current CSID from the parent form:

"CSID=" & Me.Parent!CSID


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[CB]

Hi again,

As I was doing final tweaks on my form and sub-form, I realized that I had
done something that was a no-no. I had forgotten to rename my form controls
so that they weren’t the same as the table field names.

While renaming my controls (by prefixing the names with ‘txt’), I kept
reminding myself that I was going to have to review the code for the form.
Thankfully, there isn’t much.

Unfortunately, I haven’t been able to rework the following code to run
properly so I would appreciate some advice as to what I am missing.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![QRNum] = Nz(DMax("QRNum", "t_QuoteRequests", "CSID=" & Me.CSID), 0)
+ 1
End Sub

As I understand the code for the DMax function, “QRNum†is the table field
name so it is okay as written. I believe the optional criteria string “CSID=â€
& Me.CSID should be rewritten as “CSID=†& Me.txtCSID since the “Me….â€
portion is referring to the name of the form control.

Am I correct with my assumptions above?

Here’s what happens …

When I change the code such that Me.CSID becomes Me.txtCSID (only change I
made), the sub-form doesn’t display the appropriate quote request records
based on the contractor (i.e., CSID) record selected on the main form. What I
mean is that when I select the contractor with CSID = 1, ALL quote requests
for all contractors are visible. Likewise, when I select any other
contractor. What should happen is only the quote requests related to the
given contractor should be displayed.

The overall form still functions properly in that when I use sub-form record
selector to go enter a new quote request, the CSID field on the sub-form
updates appropriately based on the main form and the QRNum increments
appropriately.

I just don’t understand why records are no longer being displayed properly.

FWIW, I did remember to change the names of the “Link Child Fields†and
“Link Master Fields†on the sub-form’s property data tab from CSID to
txtCSID. As well, I didn’t change Me![QRNum] to Me![txtQRNum] as testing
didn’t show any difference (that I noticed anyway).

Thanks for your help!

Warm regards,
Chris

Hi everyone,

I’m having some difficulty auto-incrementing a Quote Request number on a
sub-form I’ve created. I’ve searched the newsgroups and haven’t yet found
anything quite like this.

My main form includes contractor information and contract numbers (record
source is a query pulling info from two tables: t_ContractorInfo and
t_ContractSpecifics) and the sub-form is where quote requests will be
entered. CSID is the field that links the main form to the sub-form.

The record source for my sub-form is the table t_QuoteRequests with the
following fields (among others):
QRID – autonum, PK (otherwise meaningless)
QRNum – number (quote request number)
CSID – number (FK – contract specifics ID)

CSID and QRNum are indexed together since I want to prevent duplicate
Quote
Request numbers for a given contract.

Overall, the form works fine. What I now need is that when a new quote
request record is added, QRNum increments appropriately by 1 **depending
on
the CSID field.**

In other words, I need all contracts to have sequential QRNums all
starting
at one. The way things currently stand, QRNum increments the same way the
QRID field does so the QRNums for a given contract are not sequential.

Currently,
QRID QRNum CSID
1 1 1
2 2 1
3 3 4
4 4 1
5 5 3

What I need,
QRID QRNum CSID
1 1 1
2 2 1
3 1 4
4 3 1
5 1 3

The following code is what I currently have in the sub-form’s BeforeInsert
event. FWIW, I originally had this in the sub-form’s BeforeUpdate event
(per
what I found on the newsgroups) but the field didn’t seem to populate
until
after I left the record.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![QRNum] = Nz(DMax("[QRNum]", "t_QuoteRequests"), 0) + 1
End Sub

Thanks for any and all assistance!

How about this, Chris?

'----- start of code -----
Private Sub Form_BeforeInsert(Cancel As Integer)

Me![QRNum] = _
Nz(DMax("QRNum", "t_QuoteRequests", "CSID=" & Me.CSID), 0) + 1

End Sub

'----- end of code -----

The only question in my mind is whether the CSID field on the subform will
be populated yet in the subform's BeforeInsert event. If it isn't, the code
can be modified to pick up the current CSID from the parent form:

"CSID=" & Me.Parent!CSID


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

(comments inline)

Hi again,

As I was doing final tweaks on my form and sub-form, I realized that I had
done something that was a no-no. I had forgotten to rename my form
controls
so that they weren’t the same as the table field names.

Not to open a whole new can of worms, but ... while some people -- smart
ones, too -- consider having control name = controlsource field name to be a
no-no, I don't. I will frequently rename controls that whose properties
(other than .Value) I intend to manipulate in code, so as to clarify the
distinction between the control and the field. But aside from that, I
generally leave bound controls named for their controlsource fields, and I
have *never* had a problem with it. If you understand what you are doing,
and know that if you later change the controlsource to something else,
you'll have to rename the control, then IMO there is no *technical* reason
to change the control name to make it different from the field name.

That said, now that you've renamed the controls, let's see what's going on.

While renaming my controls (by prefixing the names with ‘txt’), I kept
reminding myself that I was going to have to review the code for the form.
Thankfully, there isn’t much.

Unfortunately, I haven’t been able to rework the following code to run
properly so I would appreciate some advice as to what I am missing.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![QRNum] = Nz(DMax("QRNum", "t_QuoteRequests", "CSID=" & Me.CSID), 0)
+ 1
End Sub

As I understand the code for the DMax function, “QRNum†is the table field
name so it is okay as written. I believe the optional criteria string
“CSID=â€
& Me.CSID should be rewritten as “CSID=†& Me.txtCSID since the “Me….â€
portion is referring to the name of the form control.

Am I correct with my assumptions above?

Yes, that sounds right. Though you could probably left the code alone
completely, because I expect you'll find that *both* Me.txtCSID (referring
to the control on the form) and Me.CSID (referring to the field in the
form's recordset) are now valid references.

Here’s what happens …

When I change the code such that Me.CSID becomes Me.txtCSID (only change I
made), the sub-form doesn’t display the appropriate quote request records
based on the contractor (i.e., CSID) record selected on the main form.
What I
mean is that when I select the contractor with CSID = 1, ALL quote
requests
for all contractors are visible. Likewise, when I select any other
contractor. What should happen is only the quote requests related to the
given contractor should be displayed.

The overall form still functions properly in that when I use sub-form
record
selector to go enter a new quote request, the CSID field on the sub-form
updates appropriately based on the main form and the QRNum increments
appropriately.

I just don’t understand why records are no longer being displayed
properly.

FWIW, I did remember to change the names of the “Link Child Fields†and
“Link Master Fields†on the sub-form’s property data tab from CSID to
txtCSID.

I am somewhat puzzled by exactly what is going on, but what are the Link
Master and Link Child Fields now? For the Link Master Fields property, you
can use the names of fields *or* controls from the parent form. For the
Link Child Fields property, though, you should only use the names of fields.
So if you change the Link Child Fields property to "txtCSID", change it back
to "CSID".

As well, I didn’t change Me![QRNum] to Me![txtQRNum] as testing
didn’t show any difference (that I noticed anyway).

You mean, in the BeforeInsert event procedure you quoted above? Yes, you
can probably leave it as Me!QRNum (with or without the square brackets),
because Access goes out of its way to make both the controls on a form and
the fields in the form's recordsource available equally using both the bang
(!) and the dot (.) notation, so long as there isn't a conflict with a
built-in property. It's my unconfirmed belief that this was a decision on
the part of the Access product team, to keep users from having to pay too
much attention to the distinction between fields and controls.

 

[CB]

Hi Dirk,

I apologize for the delay in getting back to you. I was on road for the past
three days.

Comments below...

(comments inline)

Hi again,

As I was doing final tweaks on my form and sub-form, I realized that I had
done something that was a no-no. I had forgotten to rename my form
controls
so that they weren’t the same as the table field names.

Not to open a whole new can of worms, but ... while some people -- smart
ones, too -- consider having control name = controlsource field name to be a
no-no, I don't. I will frequently rename controls that whose properties
(other than .Value) I intend to manipulate in code, so as to clarify the
distinction between the control and the field. But aside from that, I
generally leave bound controls named for their controlsource fields, and I
have *never* had a problem with it. If you understand what you are doing,
and know that if you later change the controlsource to something else,
you'll have to rename the control, then IMO there is no *technical* reason
to change the control name to make it different from the field name.

That said, now that you've renamed the controls, let's see what's going on.

While renaming my controls (by prefixing the names with ‘txt’), I kept
reminding myself that I was going to have to review the code for the form.
Thankfully, there isn’t much.

Unfortunately, I haven’t been able to rework the following code to run
properly so I would appreciate some advice as to what I am missing.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![QRNum] = Nz(DMax("QRNum", "t_QuoteRequests", "CSID=" & Me.CSID), 0)
+ 1
End Sub

As I understand the code for the DMax function, “QRNum†is the table field
name so it is okay as written. I believe the optional criteria string
“CSID=â€
& Me.CSID should be rewritten as “CSID=†& Me.txtCSID since the “Me….â€
portion is referring to the name of the form control.

Am I correct with my assumptions above?

Yes, that sounds right. Though you could probably left the code alone
completely, because I expect you'll find that *both* Me.txtCSID (referring
to the control on the form) and Me.CSID (referring to the field in the
form's recordset) are now valid references.

Here’s what happens …

When I change the code such that Me.CSID becomes Me.txtCSID (only change I
made), the sub-form doesn’t display the appropriate quote request records
based on the contractor (i.e., CSID) record selected on the main form.
What I
mean is that when I select the contractor with CSID = 1, ALL quote
requests
for all contractors are visible. Likewise, when I select any other
contractor. What should happen is only the quote requests related to the
given contractor should be displayed.

The overall form still functions properly in that when I use sub-form
record
selector to go enter a new quote request, the CSID field on the sub-form
updates appropriately based on the main form and the QRNum increments
appropriately.

I just don’t understand why records are no longer being displayed
properly.

FWIW, I did remember to change the names of the “Link Child Fields†and
“Link Master Fields†on the sub-form’s property data tab from CSID to
txtCSID.

I am somewhat puzzled by exactly what is going on, but what are the Link
Master and Link Child Fields now? For the Link Master Fields property, you
can use the names of fields *or* controls from the parent form. For the
Link Child Fields property, though, you should only use the names of fields.
So if you change the Link Child Fields property to "txtCSID", change it back
to "CSID".

That makes two of us!

Currently, I have both the Link Master Fields and Link Child Fields
properties set to txtCSID. When both are set to CSID (or child as CSID and
master as txtCSID), the CSID field on the sub-form displays a 0 until I begin
entering data at which time it will update to reflect what is in the main
form. The problem with this is that QRNum will then only ever be the number
1. Of course, the database doesn't like this as I have a unique index on the
combination of CSID and QRNum. I don't have this issue when both properties
are set to txt CSID.

As well, I didn’t change Me![QRNum] to Me![txtQRNum] as testing
didn’t show any difference (that I noticed anyway).

You mean, in the BeforeInsert event procedure you quoted above? Yes, you
can probably leave it as Me!QRNum (with or without the square brackets),
because Access goes out of its way to make both the controls on a form and
the fields in the form's recordsource available equally using both the bang
(!) and the dot (.) notation, so long as there isn't a conflict with a
built-in property. It's my unconfirmed belief that this was a decision on
the part of the Access product team, to keep users from having to pay too
much attention to the distinction between fields and controls.

Yes, I do mean in the BeforeInsert event.

Thanks for your assistance!

Warm regards,

Chris

 

[Dirk Goldgar]

Currently, I have both the Link Master Fields and Link Child Fields
properties set to txtCSID. When both are set to CSID (or child as CSID and
master as txtCSID), the CSID field on the sub-form displays a 0 until I
begin
entering data at which time it will update to reflect what is in the main
form. The problem with this is that QRNum will then only ever be the
number
1. Of course, the database doesn't like this as I have a unique index on
the
combination of CSID and QRNum. I don't have this issue when both
properties
are set to txt CSID.

I suspect that you have made the error of having a default value of 0 for
the CSID field in the child table. That would explain why the CSID field on
the subform initially displays a 0. Never set a default value for a table's
foreign key field, except in the very rare case where you want it to default
to a specific parent record's primary key.

Changing that, however, won't affect what's happening with QRNum, I think,
as the only difference would be that CSID would be Null, not 0 at the moment
the code fires. I think the problem must be one of timing -- the
BeforeInsert event fires as soon as you dirty a new record, and my guess is
that the parent's CSID has not yet been picked up from the parent form at
that moment.

You could change to the subform's BeforeUpdate event, testing first to see
if you're on a new record:

'----- start of example code #1 -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!QRNum = 1 +_
Nz(DMax("QRNum", "t_QuoteRequests", "CSID=" & Me.CSID), 0)
End If

End Sub
'----- end of example code #1 -----

The only flaw in that is that you won't see the calculated QRNum on the form
until the record is saved. If you care about that, you might use the
BeforeInsert event, as you were before, but pick up the CSID value from the
parent form:

'----- start of example code #2 -----
Private Sub Form_BeforeInsert(Cancel As Integer)

Me!QRNum = 1 +
Nz(DMax("QRNum", "t_QuoteRequests", _
"CSID=" & Me.Parent!txtCSID), 0)

End Sub
'----- end of example code #2 -----

I think that ought to work.

 

[CB]

Hi Dirk,

Thanks for the quick reply. The addition of "parent' to example code #2
worked like a charm.

Thanks again!

Chris