need hep with Max function

[Slez via AccessMonster.com]

I posted a question yesterday and got some very helpful advice, but have
never used this function and have a few additional questions. I have pasted
my original posting below for reference. I have the following expression
with the help I received yesterday, but am struggling with where to take it
from there.

=Nz(DMax("[PunchID]","tblPunchItems","[JobNumber]=' " & [Me].[txtJobNumber] &
" ' "),0)+1

I placed this in the Default Value property of the PunchID control. Is this
the correct location?...or is this supposed to be in one of the events?
In the help I received yesterday, it listed NewPunch in the beginning of the
expression. Am I supposed to name the control this, or create a new control?
Access added brackets around [Me].[txtJobNumber] automatically. Is this
affecting how the expression works?

I've tried altering the expression slightly. I get either of the following
messages in the new record no matter what I try: #Name? or #Error

As mentioned my original posting is pasted below. I really appreciate any
guidance on this!!
Slez


Original posting:
I am creating a form where users enter new records that relate to the field
[JobNumber]. I am wondering how to write an expression or code that will add
1 to the New Record in the field [PunchID]. It would be sort of like an
AutoNumber, but each JobNumber would start with "001". Here is an example:

JobNumber PunchID
065555 001
065555 002
065555 003
- new record would default to 004
065577 001
065577 002
- new record would default to 003
...and so on...

JobNumber is the one side of a one-to-many relationship with PunchID.
Thank you in advance for any help!

Klatuu responded:
NewPunch = Nz(DMax("[PunchID]", "MyTableName", "[JobNumber] = '" &
Me.txtJobNumber & "'"),0) + 1

 

[Allen Browne]

There are several issues here.

1. Default value is too early. txtJobNumber has not been assigned at this
time. Use the BeforeUpdate event of the form instead. This event runs at the
very last moment before the record is saved, which also reduces the chance
that 2 users creating records at the same time will get the same PunchID
number.

2. You can't use Me in the context of Default Value.

3. If txtJobNumber is a Text type field, the spaces inside the quotes will
give problems. If it is a Number type field, you need to drop the quotes.

So, something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord And Not IsNull(Me.txtJobNumber) Then
strWhere = "JobNumber = " & Me.txtJobNumber
Me.PunchID = Nz(DMax("PunchID", "tblPunchItems", strWhere),0) + 1
End If
End Sub

If the JobNumber is a Text type field:
strWhere = "JobNumber = """ & Me.txtJobNumber & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I posted a question yesterday and got some very helpful advice, but have
never used this function and have a few additional questions. I have
pasted
my original posting below for reference. I have the following expression
with the help I received yesterday, but am struggling with where to take
it
from there.

=Nz(DMax("[PunchID]","tblPunchItems","[JobNumber]=' " &
[Me].[txtJobNumber] &
" ' "),0)+1

I placed this in the Default Value property of the PunchID control. Is
this
the correct location?...or is this supposed to be in one of the events?
In the help I received yesterday, it listed NewPunch in the beginning of
the
expression. Am I supposed to name the control this, or create a new
control?
Access added brackets around [Me].[txtJobNumber] automatically. Is this
affecting how the expression works?

I've tried altering the expression slightly. I get either of the
following
messages in the new record no matter what I try: #Name? or #Error

As mentioned my original posting is pasted below. I really appreciate any
guidance on this!!
Slez


Original posting:
I am creating a form where users enter new records that relate to the
field
[JobNumber]. I am wondering how to write an expression or code that will
add
1 to the New Record in the field [PunchID]. It would be sort of like an
AutoNumber, but each JobNumber would start with "001". Here is an
example:

JobNumber PunchID
065555 001
065555 002
065555 003
- new record would default to 004
065577 001
065577 002
- new record would default to 003
..and so on...

JobNumber is the one side of a one-to-many relationship with PunchID.
Thank you in advance for any help!

Klatuu responded:
NewPunch = Nz(DMax("[PunchID]", "MyTableName", "[JobNumber] = '" &
Me.txtJobNumber & "'"),0) + 1

 

[Slez via AccessMonster.com]

Allen - Thanks for the response!

The JobNumber field is a Text type field with an input mask of ##\-#### (if
that matters).
I placed the code in the BeforeUpdate Event, altering the "strWhere" line as
noted below.
When I try to exit and update the record, I now get the error message:

Compile error:
Method or data member not found ((Error 461)

It highlights in gray part of the "If" line .txtJobNumber
and the "strWhere..." entire line changes to red.
I tried adding/omitting/altering the quotations, but no luck.

Incidentally, PunchID is also a Text type field with an input mask of 000.
If you feel that is having an impact on this, please let me know. I could
change that data type.
Thanks again for any further help!
Slez

There are several issues here.

1. Default value is too early. txtJobNumber has not been assigned at this
time. Use the BeforeUpdate event of the form instead. This event runs at the
very last moment before the record is saved, which also reduces the chance
that 2 users creating records at the same time will get the same PunchID
number.

2. You can't use Me in the context of Default Value.

3. If txtJobNumber is a Text type field, the spaces inside the quotes will
give problems. If it is a Number type field, you need to drop the quotes.

So, something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord And Not IsNull(Me.txtJobNumber) Then
strWhere = "JobNumber = " & Me.txtJobNumber
Me.PunchID = Nz(DMax("PunchID", "tblPunchItems", strWhere),0) + 1
End If
End Sub

If the JobNumber is a Text type field:
strWhere = "JobNumber = """ & Me.txtJobNumber & """"

I posted a question yesterday and got some very helpful advice, but have
never used this function and have a few additional questions. I have

[quoted text clipped - 51 lines]

NewPunch = Nz(DMax("[PunchID]", "MyTableName", "[JobNumber] = '" &
Me.txtJobNumber & "'"),0) + 1

 

[Allen Browne]

Try adding the line:
Debug.Print Me.txtJobNumber

It that also fails, then Access can't find a text box named txtJobNumber, so
that is the problem.

If PunchID is a Text type field, and it returns the value 006, adding 1
might end up with 0061 where you probably expected to see 007.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen - Thanks for the response!

The JobNumber field is a Text type field with an input mask of ##\-####
(if
that matters).
I placed the code in the BeforeUpdate Event, altering the "strWhere" line
as
noted below.
When I try to exit and update the record, I now get the error message:

Compile error:
Method or data member not found ((Error 461)

It highlights in gray part of the "If" line .txtJobNumber
and the "strWhere..." entire line changes to red.
I tried adding/omitting/altering the quotations, but no luck.

Incidentally, PunchID is also a Text type field with an input mask of 000.
If you feel that is having an impact on this, please let me know. I could
change that data type.
Thanks again for any further help!
Slez

There are several issues here.

1. Default value is too early. txtJobNumber has not been assigned at this
time. Use the BeforeUpdate event of the form instead. This event runs at
the
very last moment before the record is saved, which also reduces the chance
that 2 users creating records at the same time will get the same PunchID
number.

2. You can't use Me in the context of Default Value.

3. If txtJobNumber is a Text type field, the spaces inside the quotes will
give problems. If it is a Number type field, you need to drop the quotes.

So, something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord And Not IsNull(Me.txtJobNumber) Then
strWhere = "JobNumber = " & Me.txtJobNumber
Me.PunchID = Nz(DMax("PunchID", "tblPunchItems", strWhere),0) + 1
End If
End Sub

If the JobNumber is a Text type field:
strWhere = "JobNumber = """ & Me.txtJobNumber & """"

I posted a question yesterday and got some very helpful advice, but have
never used this function and have a few additional questions. I have

[quoted text clipped - 51 lines]

NewPunch = Nz(DMax("[PunchID]", "MyTableName", "[JobNumber] = '" &
Me.txtJobNumber & "'"),0) + 1