Show next number

[hobbit2612 via AccessMonster.com]

Hi, I wonder whether someone can help me please.

I am currently using the following coding to auto populate a reference number
within a form:

If Me.NewRecord Then
Me.ReferenceNumber = Nz(DMax("ReferenceNumber", "tblJobs"), 0) + 1
End If
End Sub


This works a treat. But what I'm trying to do is to get it so each work area
has it's own autonumbering.

For example at the moment, I may enter three jobs for work area A, they
follow in sequence as jobs 1, 2 and 3.

I then add two jobs for work area B, which follow on in sequence as 4 and 5.

Can someone point my in the direction please as to how I may get it so , it
creates job numbers 1, 2 and 3 for area A, but then goes back to allocating
number 1 and 2 for work area B instead of continuing from those in work area
A.

For information, the form that autocalculates is a subform linked by work
area ID.

Many thanks and regards

Chris

 

[Douglas J. Steele]

You'll need to add a Where clause to your DMax statement.

Without knowing how you're storing the data, I can only get a generalized
example:

If Me.NewRecord Then
Me.ReferenceNumber = _
Nz(DMax("ReferenceNumber", "tblJobs", _
"WorkArea = '" & Me.txtArea & "'"), 0) + 1
End If

 

[Ron2006]

Be aware of the following possible problem with your logic unless you
take steps to avoid it.

If two users are adding records for the same area at the same time,
then they BOTH are going to show the SAME number so that you can end
up with TWO records both pointing to the same number as a reference
number.

You need to think about the consequences for your application and
users.

Ron

 

[hobbit2612 via AccessMonster.com]

Hi Douglas,

Many thanks for taking the time to reply to this problem I have.

I've tried the code you posted and unfortunately I'm still coming up against
some problems. When I go onto the first work area, the Reference Number
starts at number 1 as expected, but so do all of the subsequent jobs. When I
the go to add jobs to another work area, again it starts at number 1 and
again all the subsequent jobs are referenced as number 1.

Any ideas please?

Regards

Chris

You'll need to add a Where clause to your DMax statement.

Without knowing how you're storing the data, I can only get a generalized
example:

If Me.NewRecord Then
Me.ReferenceNumber = _
Nz(DMax("ReferenceNumber", "tblJobs", _
"WorkArea = '" & Me.txtArea & "'"), 0) + 1
End If

Hi, I wonder whether someone can help me please.

[quoted text clipped - 31 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Ron,

I really appreciate your comments and it was issue I had thought about.

Fortunately it's only going to be me entering the data, so hopefully this
won't occur.

Kind regards

Chris

 

[Douglas J. Steele]

Hopefully you realize that I was assuming that you had a control on your
form named txtArea which contains the name of the work area in the same
format as how it's stored in field WorkArea in the table (and that it's a
text field). Is that the case with your data?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

Many thanks for taking the time to reply to this problem I have.

I've tried the code you posted and unfortunately I'm still coming up
against
some problems. When I go onto the first work area, the Reference Number
starts at number 1 as expected, but so do all of the subsequent jobs. When
I
the go to add jobs to another work area, again it starts at number 1 and
again all the subsequent jobs are referenced as number 1.

Any ideas please?

Regards

Chris

You'll need to add a Where clause to your DMax statement.

Without knowing how you're storing the data, I can only get a generalized
example:

If Me.NewRecord Then
Me.ReferenceNumber = _
Nz(DMax("ReferenceNumber", "tblJobs", _
"WorkArea = '" & Me.txtArea & "'"), 0) + 1
End If

Hi, I wonder whether someone can help me please.

[quoted text clipped - 31 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Hi Douglas,

Yes that's right I've changed WorkArea and txtWorkArea to match my field name.


Regards

Chris

Hopefully you realize that I was assuming that you had a control on your
form named txtArea which contains the name of the work area in the same
format as how it's stored in field WorkArea in the table (and that it's a
text field). Is that the case with your data?

Hi Douglas,

[quoted text clipped - 30 lines]

 

[Douglas J. Steele]

Hmm. It should work.

What's the exact code you're using now? In what event is it running?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

Yes that's right I've changed WorkArea and txtWorkArea to match my field
name.


Regards

Chris

Hopefully you realize that I was assuming that you had a control on your
form named txtArea which contains the name of the work area in the same
format as how it's stored in field WorkArea in the table (and that it's a
text field). Is that the case with your data?

Hi Douglas,

[quoted text clipped - 30 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Hi Douglas,

Thanks for getting back to me again.

The exact code is:

If Me.NewRecord Then
Me.ReferenceNumber = _
Nz(DMax("ReferenceNumber", "tblJobs", "WorkArea = '" & Me.WorkAreaID &
"'"), 0) + 1
End If
End Sub

This is runnig in the On Current event.

Kind regards

Chris

Hmm. It should work.

What's the exact code you're using now? In what event is it running?

Hi Douglas,

[quoted text clipped - 15 lines]

 

[Douglas J. Steele]

What's showing up as Me.WorkAreaID?

After the If Me.NewRecord Then line, put

Debug.Print "WorkArea = " & Me.WorkAreaID

Once you move to a new record, go to the Immediate Window (Ctrl-G). What's
printed there? Is it correct?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

Thanks for getting back to me again.

The exact code is:

If Me.NewRecord Then
Me.ReferenceNumber = _
Nz(DMax("ReferenceNumber", "tblJobs", "WorkArea = '" & Me.WorkAreaID &
"'"), 0) + 1
End If
End Sub

This is runnig in the On Current event.

Kind regards

Chris

Hmm. It should work.

What's the exact code you're using now? In what event is it running?

Hi Douglas,

[quoted text clipped - 15 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Hi Douglas,

Many thanks for your continued help. I'm sorry I haven't been able to reply
sooner, I had a meeting yesterday evening.

I've added the extra code and when I add a new record and press Ctrl and G,
the window shows

WorkArea =

If my thinking is right, should the work area appear?

Regards

Chris

What's showing up as Me.WorkAreaID?

After the If Me.NewRecord Then line, put

Debug.Print "WorkArea = " & Me.WorkAreaID

Once you move to a new record, go to the Immediate Window (Ctrl-G). What's
printed there? Is it correct?

Hi Douglas,

[quoted text clipped - 24 lines]

 

[Douglas J. Steele]

Yes, assuming WorkAreaID is the name of the text box on your form, you
should see a work area after the equal sign.

The fact that you're not is why the numbering is restarting at 1.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

Many thanks for your continued help. I'm sorry I haven't been able to
reply
sooner, I had a meeting yesterday evening.

I've added the extra code and when I add a new record and press Ctrl and
G,
the window shows

WorkArea =

If my thinking is right, should the work area appear?

Regards

Chris

What's showing up as Me.WorkAreaID?

After the If Me.NewRecord Then line, put

Debug.Print "WorkArea = " & Me.WorkAreaID

Once you move to a new record, go to the Immediate Window (Ctrl-G). What's
printed there? Is it correct?

Hi Douglas,

[quoted text clipped - 24 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Hi Douglas,

Well I have to say I'm baffled by this. I'm certainly no expert but even I
can normally get a hang of it eventually I've even rebuilt the database to
see if I could sort the problem out.

I know that both the main and the subform both correctly show the WorkAreaID
and I've also tried linking them using the Work Area but as I said yesterday
the nearest I get is that all the jobs have a reference number of 1, no
matter which Work Area they relate to.

Have you any ideas please?

Regards

Chris

Yes, assuming WorkAreaID is the name of the text box on your form, you
should see a work area after the equal sign.

The fact that you're not is why the numbering is restarting at 1.

Hi Douglas,

[quoted text clipped - 28 lines]

 

[Douglas J. Steele]

Are you sure you're referring to the text box that contains the WorkAreaID
correctly?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

Well I have to say I'm baffled by this. I'm certainly no expert but even I
can normally get a hang of it eventually I've even rebuilt the database
to
see if I could sort the problem out.

I know that both the main and the subform both correctly show the
WorkAreaID
and I've also tried linking them using the Work Area but as I said
yesterday
the nearest I get is that all the jobs have a reference number of 1, no
matter which Work Area they relate to.

Have you any ideas please?

Regards

Chris

Yes, assuming WorkAreaID is the name of the text box on your form, you
should see a work area after the equal sign.

The fact that you're not is why the numbering is restarting at 1.

Hi Douglas,

[quoted text clipped - 28 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Hi Douglas,

I've had a look at the text box and I can confirm that it does say WorkAreaID.


I was thinking though, in the code that you posted and I'm using (see below),
is it right to have it pointing to 'Work Area" i.e. "WorkArea=' because this
is obviously another text box, albeit one that I have set up?

Private Sub Form_Current()

If Me.NewRecord Then
Debug.Print "WorkArea= " & Me.WorkAreaID
Me.ReferenceNumber = Nz(DMax("ReferenceNumber", "tblJobs", "WorkArea = '"_
& Me.WorkAreaID & "'"), 0) + 1
End If
End Sub

Many thanks once again

Chris

Are you sure you're referring to the text box that contains the WorkAreaID
correctly?

Hi Douglas,

[quoted text clipped - 26 lines]

 

[Douglas J. Steele]

WorkArea in that DMax statement refers to a field in the tblJobs table.

You're trying to get the largest value of ReferenceNumber in the table for a
given value of WorkArea.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

I've had a look at the text box and I can confirm that it does say
WorkAreaID.


I was thinking though, in the code that you posted and I'm using (see
below),
is it right to have it pointing to 'Work Area" i.e. "WorkArea=' because
this
is obviously another text box, albeit one that I have set up?

Private Sub Form_Current()

If Me.NewRecord Then
Debug.Print "WorkArea= " & Me.WorkAreaID
Me.ReferenceNumber = Nz(DMax("ReferenceNumber", "tblJobs", "WorkArea =
'"_
& Me.WorkAreaID & "'"), 0) + 1
End If
End Sub

Many thanks once again

Chris

Are you sure you're referring to the text box that contains the WorkAreaID
correctly?

Hi Douglas,

[quoted text clipped - 26 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Hi Douglas,

Ok, I understand.

Thanks and regards

Chris

WorkArea in that DMax statement refers to a field in the tblJobs table.

You're trying to get the largest value of ReferenceNumber in the table for a
given value of WorkArea.

Hi Douglas,

[quoted text clipped - 29 lines]