subform code question / additional advice requested

[Slez via AccessMonster.com]

I have some code written in the BeforeUpdate event so that when users enter a
new record into a subform, it sort of auto-numbers those records. It works
perfectly and is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SampleID) Then
Me!SampleID = Nz(DMax("SampleID", "tblFinishSample", "[JobNumber]='"
& [JobNumber] & "'"), 0) + 1
End If
End Sub

I am now working in another database with a very similar application, so I
pasted the above code into this other database and switched the table and
field references, thinking it should be a perfect fit. The revised version
is as follows.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.BidNumber) Then
Me!BidNumber = Nz(DMax("BidNumber", "Bid", "[ProjectID]='" &
[ProjectID] & "'"), 0) + 1
End If
End Sub

However, I get a "Run-time error '3464': Data type mismatch in criteria
expression". The only difference with this other database is that there is
one additional linked field. Besides ProjectID, there is also ProjectName,
which is an additional primary key in the tables. It seems to me that this
would need to be included in the expression, but I am uncertain how to
include it. If anyone can offer assistance, I would greatly appreciate it.

This leads me to also requesting some additional advice. When I was
initially creating this database a year ago, I had someone help me work
through some glitches. They added the field ProjectID to my tables, which
already had a unique field called ProjectName. At the time, it worked great,
but now that I am creating more forms and subforms, I am having that
additional master/child link which does make some expressions more
complicated. I'm considering eliminating the ProjectName field from all
tables except the "Project" table (which is the "one" side of all other
relationships). Being part of the primary keys for each table requires
deletion of relationships and recreating them. With all that said, does this
seem like the right thing to do in the long-term?...Or is that viewed as a
trivial thing and I should just leave things alone. I feel having an extra
primary key is unnecessary, but am curious if others have an opinion.
Eliminating this would also result in the previously mentioned code working
as is...I think!

Thanks
Slez

 

[Marshall Barton]

I have some code written in the BeforeUpdate event so that when users enter a
new record into a subform, it sort of auto-numbers those records. It works
perfectly and is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SampleID) Then
Me!SampleID = Nz(DMax("SampleID", "tblFinishSample", "[JobNumber]='"
& [JobNumber] & "'"), 0) + 1
End If
End Sub

I am now working in another database with a very similar application, so I
pasted the above code into this other database and switched the table and
field references, thinking it should be a perfect fit. The revised version
is as follows.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.BidNumber) Then
Me!BidNumber = Nz(DMax("BidNumber", "Bid", "[ProjectID]='" &
[ProjectID] & "'"), 0) + 1
End If
End Sub

However, I get a "Run-time error '3464': Data type mismatch in criteria
expression". The only difference with this other database is that there is
one additional linked field. Besides ProjectID, there is also ProjectName,
which is an additional primary key in the tables. It seems to me that this
would need to be included in the expression, but I am uncertain how to
include it. If anyone can offer assistance, I would greatly appreciate it.

This leads me to also requesting some additional advice. When I was
initially creating this database a year ago, I had someone help me work
through some glitches. They added the field ProjectID to my tables, which
already had a unique field called ProjectName. At the time, it worked great,
but now that I am creating more forms and subforms, I am having that
additional master/child link which does make some expressions more
complicated. I'm considering eliminating the ProjectName field from all
tables except the "Project" table (which is the "one" side of all other
relationships). Being part of the primary keys for each table requires
deletion of relationships and recreating them. With all that said, does this
seem like the right thing to do in the long-term?...Or is that viewed as a
trivial thing and I should just leave things alone. I feel having an extra
primary key is unnecessary, but am curious if others have an opinion.
Eliminating this would also result in the previously mentioned code working
as is...I think!

That error message usually means what it says. In this
case, it probably indicates that the ProjectID is a numeric
type field and you are trying to compare it to a text
string.


As for your primary key situation, a table can only have one
primary key. THE primary key may be a compound index that
includes two fields, but it still one index with the Primary
attribute.

It is possible to have two separate, one field, unique
indexes, in which case either one, but not both, could be
designated as the primary key index. If both your
ProjectName and ProjectID fields are unique on their own,
then you should not have a compound primary key and you
should use one or the other as the foreign key in the other
tables.

 

[Slez via AccessMonster.com]

I have some code written in the BeforeUpdate event so that when users enter a
new record into a subform, it sort of auto-numbers those records. It works

[quoted text clipped - 41 lines]

Eliminating this would also result in the previously mentioned code working
as is...I think!

That error message usually means what it says. In this
case, it probably indicates that the ProjectID is a numeric
type field and you are trying to compare it to a text
string.

As for your primary key situation, a table can only have one
primary key. THE primary key may be a compound index that
includes two fields, but it still one index with the Primary
attribute.

It is possible to have two separate, one field, unique
indexes, in which case either one, but not both, could be
designated as the primary key index. If both your
ProjectName and ProjectID fields are unique on their own,
then you should not have a compound primary key and you
should use one or the other as the foreign key in the other
tables.

After further review, you are right on the mark! ProjectID is a Number field
whereas JobNumber is text in the first code. How do I alter that portion of
the code to reference a numeric type field? Thanks again for any help!

Also, thanks for the response on the primary key topic. That certainly
helped!
Slez

 

[Slez via AccessMonster.com]

I have some code written in the BeforeUpdate event so that when users enter a
new record into a subform, it sort of auto-numbers those records. It works

[quoted text clipped - 41 lines]

Eliminating this would also result in the previously mentioned code working
as is...I think!

That error message usually means what it says. In this
case, it probably indicates that the ProjectID is a numeric
type field and you are trying to compare it to a text
string.

As for your primary key situation, a table can only have one
primary key. THE primary key may be a compound index that
includes two fields, but it still one index with the Primary
attribute.

It is possible to have two separate, one field, unique
indexes, in which case either one, but not both, could be
designated as the primary key index. If both your
ProjectName and ProjectID fields are unique on their own,
then you should not have a compound primary key and you
should use one or the other as the foreign key in the other
tables.

I got it to number incrementally by 1 with the following code. The only
trouble I am now having is that it always starts with the number 25. I am
going to re-post this as a new question.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.BidNumber) Then
Me!BidNumber = Nz(DMax("BidNumber", "Bid", "[ProjectID]"), 0) + 1
End If
End Sub

 

[Marshall Barton]

I have some code written in the BeforeUpdate event so that when users enter a
new record into a subform, it sort of auto-numbers those records. It works

[quoted text clipped - 41 lines]

Eliminating this would also result in the previously mentioned code working
as is...I think!

That error message usually means what it says. In this
case, it probably indicates that the ProjectID is a numeric
type field and you are trying to compare it to a text
string.

As for your primary key situation, a table can only have one
primary key. THE primary key may be a compound index that
includes two fields, but it still one index with the Primary
attribute.

It is possible to have two separate, one field, unique
indexes, in which case either one, but not both, could be
designated as the primary key index. If both your
ProjectName and ProjectID fields are unique on their own,
then you should not have a compound primary key and you
should use one or the other as the foreign key in the other
tables.

I got it to number incrementally by 1 with the following code. The only
trouble I am now having is that it always starts with the number 25. I am
going to re-post this as a new question.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.BidNumber) Then
Me!BidNumber = Nz(DMax("BidNumber", "Bid", "[ProjectID]"), 0) + 1
End If
End Sub

I think that should be:

Me!BidNumber = Nz(DMax("BidNumber", "Bid", _
"ProjectID = " & ProjectID), 0) + 1

 

[Slez via AccessMonster.com]

[quoted text clipped - 29 lines]

End If
End Sub

I think that should be:

Me!BidNumber = Nz(DMax("BidNumber", "Bid", _
"ProjectID = " & ProjectID), 0) + 1

That works perfectly Marshall! Thanks so much for your help!
Slez