Record Numbering on form by month

[Lin]

Hello and thank you

I had code from one fellow to help with my problem. I needed to be able to
Count each new record (form) over a month period and restart from 1 each
month.

The code I was given works fine for a while then something happens it just
stops advancing. This has been happening for some time both on my test
database and work database.

Here is the code:

Private Sub AdmitDate_BeforeUpdate(Cancel As Integer)
Dim MaxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "AdmitDate"
namIdx = "AdmitNum"
namTable = "tblAdmit"
Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) & " And "
& "Year([" & namDate & "]) = " & Year(Me(namDate))
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(MaxIdx) Then
Me(namIdx) = 1 'Start over on new month & year
Else
Me(namIdx) = MaxIdx + 1
End If
End If
End Sub

Can anyone figure out why?

Thanks

 

[Klatuu]

Your code looks okay. I don't see any really obvious errors, but there is
one thing I am puzzled about. In this line:
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
MaxIdx is Dimmed as String. The problem here is that the only data type
that can accept a Null value is a Variant. The puzzlement is that I would
expect a data type error, so this may not be the problem.
It may not hurt to change the dim on MaxIdx to Variant and see if that does
any good.

A question, when it stopped advancing, is it always on 1 or is it stuck on
another number?

 

[Douglas J. Steele]

Actually, Dim MaxIdx, Criteria As String does declare MaxIdx as a variant:
to have it a string, you'd need Dim MaxIdx As String, Criteria As String

It might be worth trapping what value is contained in Criteria, just to see
exactly what's being passed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Your code looks okay. I don't see any really obvious errors, but there is
one thing I am puzzled about. In this line:
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
MaxIdx is Dimmed as String. The problem here is that the only data type
that can accept a Null value is a Variant. The puzzlement is that I would
expect a data type error, so this may not be the problem.
It may not hurt to change the dim on MaxIdx to Variant and see if that
does
any good.

A question, when it stopped advancing, is it always on 1 or is it stuck on
another number?

Hello and thank you

I had code from one fellow to help with my problem. I needed to be able
to
Count each new record (form) over a month period and restart from 1 each
month.

The code I was given works fine for a while then something happens it
just
stops advancing. This has been happening for some time both on my test
database and work database.

Here is the code:

Private Sub AdmitDate_BeforeUpdate(Cancel As Integer)
Dim MaxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "AdmitDate"
namIdx = "AdmitNum"
namTable = "tblAdmit"
Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) & " And
"
& "Year([" & namDate & "]) = " & Year(Me(namDate))
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(MaxIdx) Then
Me(namIdx) = 1 'Start over on new month & year
Else
Me(namIdx) = MaxIdx + 1
End If
End If
End Sub

Can anyone figure out why?

Thanks

 

[Klatuu]

Doug,
I am thinking MaxIdx should be a Variant. Please correct me if I am wrong,
but my understanding is that only a Variant can be assigned Null, so in the
line where the DMax is, would it not error if the DMax returned a Null?

Actually, Dim MaxIdx, Criteria As String does declare MaxIdx as a variant:
to have it a string, you'd need Dim MaxIdx As String, Criteria As String

It might be worth trapping what value is contained in Criteria, just to see
exactly what's being passed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Your code looks okay. I don't see any really obvious errors, but there is
one thing I am puzzled about. In this line:
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
MaxIdx is Dimmed as String. The problem here is that the only data type
that can accept a Null value is a Variant. The puzzlement is that I would
expect a data type error, so this may not be the problem.
It may not hurt to change the dim on MaxIdx to Variant and see if that
does
any good.

A question, when it stopped advancing, is it always on 1 or is it stuck on
another number?

Hello and thank you

I had code from one fellow to help with my problem. I needed to be able
to
Count each new record (form) over a month period and restart from 1 each
month.

The code I was given works fine for a while then something happens it
just
stops advancing. This has been happening for some time both on my test
database and work database.

Here is the code:

Private Sub AdmitDate_BeforeUpdate(Cancel As Integer)
Dim MaxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "AdmitDate"
namIdx = "AdmitNum"
namTable = "tblAdmit"
Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) & " And
"
& "Year([" & namDate & "]) = " & Year(Me(namDate))
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(MaxIdx) Then
Me(namIdx) = 1 'Start over on new month & year
Else
Me(namIdx) = MaxIdx + 1
End If
End If
End Sub

Can anyone figure out why?

Thanks

 

[Douglas J. Steele]

You're absolutely correct that only a variant can be assigned a Null value.
However, if you reread what I wrote, you'll see that I was pointing out that
MaxIdx was already being declared as a variant: when you declare a variable
and don't explicitly give it a type, it's created as a variant.

In other words, the following two lines are identical:

Dim MaxIdx, Criteria As String
Dim MaxIdx As Variant, Criteria As String

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
I am thinking MaxIdx should be a Variant. Please correct me if I am
wrong,
but my understanding is that only a Variant can be assigned Null, so in
the
line where the DMax is, would it not error if the DMax returned a Null?

Actually, Dim MaxIdx, Criteria As String does declare MaxIdx as a
variant:
to have it a string, you'd need Dim MaxIdx As String, Criteria As String

It might be worth trapping what value is contained in Criteria, just to
see
exactly what's being passed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Your code looks okay. I don't see any really obvious errors, but there
is
one thing I am puzzled about. In this line:
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
MaxIdx is Dimmed as String. The problem here is that the only data
type
that can accept a Null value is a Variant. The puzzlement is that I
would
expect a data type error, so this may not be the problem.
It may not hurt to change the dim on MaxIdx to Variant and see if that
does
any good.

A question, when it stopped advancing, is it always on 1 or is it stuck
on
another number?

:

Hello and thank you

I had code from one fellow to help with my problem. I needed to be
able
to
Count each new record (form) over a month period and restart from 1
each
month.

The code I was given works fine for a while then something happens it
just
stops advancing. This has been happening for some time both on my
test
database and work database.

Here is the code:

Private Sub AdmitDate_BeforeUpdate(Cancel As Integer)
Dim MaxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "AdmitDate"
namIdx = "AdmitNum"
namTable = "tblAdmit"
Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) & "
And
"
& "Year([" & namDate & "]) = " & Year(Me(namDate))
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(MaxIdx) Then
Me(namIdx) = 1 'Start over on new month & year
Else
Me(namIdx) = MaxIdx + 1
End If
End If
End Sub

Can anyone figure out why?

Thanks

 

[Lin]

Hi Klatuu thanks

It seems to be stuck on one. But the weird thing, is that it counts for a
while then stops.

Your code looks okay. I don't see any really obvious errors, but there is
one thing I am puzzled about. In this line:
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
MaxIdx is Dimmed as String. The problem here is that the only data type
that can accept a Null value is a Variant. The puzzlement is that I would
expect a data type error, so this may not be the problem.
It may not hurt to change the dim on MaxIdx to Variant and see if that does
any good.

A question, when it stopped advancing, is it always on 1 or is it stuck on
another number?

Hello and thank you

I had code from one fellow to help with my problem. I needed to be able to
Count each new record (form) over a month period and restart from 1 each
month.

The code I was given works fine for a while then something happens it just
stops advancing. This has been happening for some time both on my test
database and work database.

Here is the code:

Private Sub AdmitDate_BeforeUpdate(Cancel As Integer)
Dim MaxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "AdmitDate"
namIdx = "AdmitNum"
namTable = "tblAdmit"
Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) & " And "
& "Year([" & namDate & "]) = " & Year(Me(namDate))
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(MaxIdx) Then
Me(namIdx) = 1 'Start over on new month & year
Else
Me(namIdx) = MaxIdx + 1
End If
End If
End Sub

Can anyone figure out why?

Thanks

 

[Lin]

Thanks Doug, but I am confussed, are you saying that this should work? I
did'nt understand what you meant by trap.

Thanks again

You're absolutely correct that only a variant can be assigned a Null value.
However, if you reread what I wrote, you'll see that I was pointing out that
MaxIdx was already being declared as a variant: when you declare a variable
and don't explicitly give it a type, it's created as a variant.

In other words, the following two lines are identical:

Dim MaxIdx, Criteria As String
Dim MaxIdx As Variant, Criteria As String

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
I am thinking MaxIdx should be a Variant. Please correct me if I am
wrong,
but my understanding is that only a Variant can be assigned Null, so in
the
line where the DMax is, would it not error if the DMax returned a Null?

Actually, Dim MaxIdx, Criteria As String does declare MaxIdx as a
variant:
to have it a string, you'd need Dim MaxIdx As String, Criteria As String

It might be worth trapping what value is contained in Criteria, just to
see
exactly what's being passed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Your code looks okay. I don't see any really obvious errors, but there
is
one thing I am puzzled about. In this line:
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
MaxIdx is Dimmed as String. The problem here is that the only data
type
that can accept a Null value is a Variant. The puzzlement is that I
would
expect a data type error, so this may not be the problem.
It may not hurt to change the dim on MaxIdx to Variant and see if that
does
any good.

A question, when it stopped advancing, is it always on 1 or is it stuck
on
another number?

:

Hello and thank you

I had code from one fellow to help with my problem. I needed to be
able
to
Count each new record (form) over a month period and restart from 1
each
month.

The code I was given works fine for a while then something happens it
just
stops advancing. This has been happening for some time both on my
test
database and work database.

Here is the code:

Private Sub AdmitDate_BeforeUpdate(Cancel As Integer)
Dim MaxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "AdmitDate"
namIdx = "AdmitNum"
namTable = "tblAdmit"
Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) & "
And
"
& "Year([" & namDate & "]) = " & Year(Me(namDate))
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(MaxIdx) Then
Me(namIdx) = 1 'Start over on new month & year
Else
Me(namIdx) = MaxIdx + 1
End If
End If
End Sub

Can anyone figure out why?

Thanks

 

[Douglas J. Steele]

Yes, I was saying it should work, although I just noticed something in the
code that might be an issue.

You're using Me(namDate) in a couple of places, where namDate has been
assigned the value AdmitDate. Is AdmitDate the name of the control, the name
of a field in the underlying recordset or both. While I realize Access names
bound controls to the same name as the field to which they're bound, that
can cause problems at times. I always rename all of my controls so that they
are not the same as the field to which they're bound, and use the control
name, not the recordset field name.

What I meant by trap was to look at the value that's in Criteria, to make
sure it's getting assigned properly.

You could do this by single-stepping through the code in Debug mode, or you
could put a MsgBox Criteria or Debug.Print Criteria after you've assigned
the value but before you use it.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug, but I am confussed, are you saying that this should work? I
did'nt understand what you meant by trap.

Thanks again

You're absolutely correct that only a variant can be assigned a Null
value.
However, if you reread what I wrote, you'll see that I was pointing out
that
MaxIdx was already being declared as a variant: when you declare a
variable
and don't explicitly give it a type, it's created as a variant.

In other words, the following two lines are identical:

Dim MaxIdx, Criteria As String
Dim MaxIdx As Variant, Criteria As String

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
I am thinking MaxIdx should be a Variant. Please correct me if I am
wrong,
but my understanding is that only a Variant can be assigned Null, so in
the
line where the DMax is, would it not error if the DMax returned a Null?

:

Actually, Dim MaxIdx, Criteria As String does declare MaxIdx as a
variant:
to have it a string, you'd need Dim MaxIdx As String, Criteria As
String

It might be worth trapping what value is contained in Criteria, just
to
see
exactly what's being passed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Your code looks okay. I don't see any really obvious errors, but
there
is
one thing I am puzzled about. In this line:
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
MaxIdx is Dimmed as String. The problem here is that the only data
type
that can accept a Null value is a Variant. The puzzlement is that I
would
expect a data type error, so this may not be the problem.
It may not hurt to change the dim on MaxIdx to Variant and see if
that
does
any good.

A question, when it stopped advancing, is it always on 1 or is it
stuck
on
another number?

:

Hello and thank you

I had code from one fellow to help with my problem. I needed to be
able
to
Count each new record (form) over a month period and restart from 1
each
month.

The code I was given works fine for a while then something happens
it
just
stops advancing. This has been happening for some time both on my
test
database and work database.

Here is the code:

Private Sub AdmitDate_BeforeUpdate(Cancel As Integer)
Dim MaxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "AdmitDate"
namIdx = "AdmitNum"
namTable = "tblAdmit"
Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) &
"
And
"
& "Year([" & namDate & "]) = " & Year(Me(namDate))
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(MaxIdx) Then
Me(namIdx) = 1 'Start over on new month & year
Else
Me(namIdx) = MaxIdx + 1
End If
End If
End Sub

Can anyone figure out why?

Thanks

 

[Lin]

Thanks Doug, I'll give that a try.

P.S are you Doug Steel in Windsor Ontario?

Regards.

Yes, I was saying it should work, although I just noticed something in the
code that might be an issue.

You're using Me(namDate) in a couple of places, where namDate has been
assigned the value AdmitDate. Is AdmitDate the name of the control, the name
of a field in the underlying recordset or both. While I realize Access names
bound controls to the same name as the field to which they're bound, that
can cause problems at times. I always rename all of my controls so that they
are not the same as the field to which they're bound, and use the control
name, not the recordset field name.

What I meant by trap was to look at the value that's in Criteria, to make
sure it's getting assigned properly.

You could do this by single-stepping through the code in Debug mode, or you
could put a MsgBox Criteria or Debug.Print Criteria after you've assigned
the value but before you use it.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug, but I am confussed, are you saying that this should work? I
did'nt understand what you meant by trap.

Thanks again

You're absolutely correct that only a variant can be assigned a Null
value.
However, if you reread what I wrote, you'll see that I was pointing out
that
MaxIdx was already being declared as a variant: when you declare a
variable
and don't explicitly give it a type, it's created as a variant.

In other words, the following two lines are identical:

Dim MaxIdx, Criteria As String
Dim MaxIdx As Variant, Criteria As String

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Doug,
I am thinking MaxIdx should be a Variant. Please correct me if I am
wrong,
but my understanding is that only a Variant can be assigned Null, so in
the
line where the DMax is, would it not error if the DMax returned a Null?

:

Actually, Dim MaxIdx, Criteria As String does declare MaxIdx as a
variant:
to have it a string, you'd need Dim MaxIdx As String, Criteria As
String

It might be worth trapping what value is contained in Criteria, just
to
see
exactly what's being passed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Your code looks okay. I don't see any really obvious errors, but
there
is
one thing I am puzzled about. In this line:
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
MaxIdx is Dimmed as String. The problem here is that the only data
type
that can accept a Null value is a Variant. The puzzlement is that I
would
expect a data type error, so this may not be the problem.
It may not hurt to change the dim on MaxIdx to Variant and see if
that
does
any good.

A question, when it stopped advancing, is it always on 1 or is it
stuck
on
another number?

:

Hello and thank you

I had code from one fellow to help with my problem. I needed to be
able
to
Count each new record (form) over a month period and restart from 1
each
month.

The code I was given works fine for a while then something happens
it
just
stops advancing. This has been happening for some time both on my
test
database and work database.

Here is the code:

Private Sub AdmitDate_BeforeUpdate(Cancel As Integer)
Dim MaxIdx, Criteria As String
Dim namIdx As String, namDate As String, namTable As String

If Me.NewRecord Then
namDate = "AdmitDate"
namIdx = "AdmitNum"
namTable = "tblAdmit"
Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) &
"
And
"
& "Year([" & namDate & "]) = " & Year(Me(namDate))
MaxIdx = DMax("[" & namIdx & "]", namTable, Criteria)

If IsNull(MaxIdx) Then
Me(namIdx) = 1 'Start over on new month & year
Else
Me(namIdx) = MaxIdx + 1
End If
End If
End Sub

Can anyone figure out why?

Thanks

 

[Douglas J. Steele]

P.S are you Doug Steel in Windsor Ontario?

No. (for one thing, I'm Doug Steele, not Doug Steel)