BeforeUpdate code question

[Slez via AccessMonster.com]

I believe I am missing portions of code in the BeforeUpdate event and am
wondering if someone can help. Here is my scenario:

I have 3 related tables:
tblProject contains the field ProjectID, which is on the one side of a one-to-
many relationship with...
tblBid contains the fields ProjectID & BidNumber, which is on the one side of
a one-to-many relationship with...
tblScopeNotes contains the fields ProjectID, BidNumber, ScopeNoteID &
ScopeNote

I have a subform where users can enter records into the ScopeNote field and
I'd like to have ScopeNoteID automatically number itself. I have done this
with a second level table, but never a third. So for each ProjectID ;
BidNumber combination, ScopeNoteID would = 1, then 2, then 3, and so on...

In the BeforeUpdate event, I currently have the following code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.ScopeNoteID) Then
Me!ScopeNoteID = Nz(DMax("ScopeNoteID", "tblScopeNotes", "BidNumber
=" & BidNumber), 0) + 1
End If
End Sub

This currently yields the first ScopeNoteID record being 4,5,8,etc. instead
of "1". Once a ScopeNoteID is in, it seems to assign the next available
number, it just doen't start correctly with the first entry.

As mentioned, any help is appreciated!
Thanks!

 

[Ken Snell \(MVP\)]

Is BidNumber field a numeric or text datatype?

What are the data in the table for a specific BidNumber value when you see a
4 as the first number? My guess is that there are records in the table that
already have values of 3, 2, 1, etc. and that is why you're seeing the 4.

The code that you're using is correct for getting a 1 as the first number --
SO LONG AS there are no records in the table with the form's BidNumber value
in the BidNumber field.

 

[Slez via AccessMonster.com]

Thanks for your reply!

BidNumber is a numeric field, but the strange part is that there 4 may be the
highest value in BidNumber, and 8 shows up as the value for the first
ScopeNoteID, and on another project with 6 as the value in BidNumber and 9
shows up as the first ScopeNoteID. There doesn't seem to be a specific
pattern. I don't believe my code is exactly right, in that I should be
referencing the ScopeNoteID differently.

Any further help or suggestion is appreciated!
Slez

Is BidNumber field a numeric or text datatype?

What are the data in the table for a specific BidNumber value when you see a
4 as the first number? My guess is that there are records in the table that
already have values of 3, 2, 1, etc. and that is why you're seeing the 4.

The code that you're using is correct for getting a 1 as the first number --
SO LONG AS there are no records in the table with the form's BidNumber value
in the BidNumber field.

I believe I am missing portions of code in the BeforeUpdate event and am
wondering if someone can help. Here is my scenario:

[quoted text clipped - 31 lines]

As mentioned, any help is appreciated!
Thanks!

 

[Ken Snell \(MVP\)]

If you're not getting the right result with the DMax code, then perhaps you
need to include a second field in the criterion statement of the DMax
function so that a unique record can be found. But, you'll need to give us
full details about the tables in terms of structure and data so that we can
assist further.

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

BidNumber is a numeric field, but the strange part is that there 4 may be
the
highest value in BidNumber, and 8 shows up as the value for the first
ScopeNoteID, and on another project with 6 as the value in BidNumber and 9
shows up as the first ScopeNoteID. There doesn't seem to be a specific
pattern. I don't believe my code is exactly right, in that I should be
referencing the ScopeNoteID differently.

Any further help or suggestion is appreciated!
Slez

Is BidNumber field a numeric or text datatype?

What are the data in the table for a specific BidNumber value when you see
a
4 as the first number? My guess is that there are records in the table
that
already have values of 3, 2, 1, etc. and that is why you're seeing the 4.

The code that you're using is correct for getting a 1 as the first
number --
SO LONG AS there are no records in the table with the form's BidNumber
value
in the BidNumber field.

I believe I am missing portions of code in the BeforeUpdate event and am
wondering if someone can help. Here is my scenario:

[quoted text clipped - 31 lines]

As mentioned, any help is appreciated!
Thanks!