need sequential #'s

[owilson]

Hi,

I have a table called tblRFI that is accessed by several
users entering info dealing with different jobs. I'm
trying to figure out a way to assign a sequential number
for each RFI for each job that starts with 1 and goes on
as far as necessary.

For example Job Num RFI Num
123 1
123 2
345 1
345 2
123 3
345 3

Any suggestions will be greatly appreciated,

TIA

Owen

 

[Jon Furman]

I've encountered variations of this problem in the past. It would be nice
and it would seem that you should be able to assign some kind of expression
to the default value propoerty of the table to get this to work but I don't
believe that you can. At least I wasn't clever enough to come up with one.
How I've dealt with it in the past was to use VBA in the form where the data
is entered to do the calculation to get the next number. What you would have
to do is keep track of the last Job number in the form and when a new record
is created have the VB code populate the field. You can still show the filed
on the form, just make sure that Locked=True and Enabled=False so that the
user can't change it. This won't affect VB's ability to change it.

Hope this helps.

Jon

 

[John Vinson]

Hi,

I have a table called tblRFI that is accessed by several
users entering info dealing with different jobs. I'm
trying to figure out a way to assign a sequential number
for each RFI for each job that starts with 1 and goes on
as far as necessary.

For example Job Num RFI Num
123 1
123 2
345 1
345 2
123 3
345 3

Any suggestions will be greatly appreciated,

Do all your data entry using a Form - datasheets don't have any usable
events.

In the AfterUpdate event of the control bound to Job Num (presumably a
Combo Box which I'll call cboJobNum, selecting from a table of job
numbers, but it doesn't matter) put code like:

Private Sub cboJobNum_AfterUpdate()
If IsNull(Me![RFI Num]) Then ' don't stomp on existing data
Me![RFI Num] = NZ(DMax("[RFI Num]", "[tblRFI]", _
"[Job Num] = " & Me!cboJobNum)) + 1
End If
End Sub


John W. Vinson[MVP]

 

[owilson]

Mr Vinson,

The code works great for the first RFI, but I get a "Data
type mismatch" error on subsequent ones, even if I change
job numbers. The RFINum is a number field set as Integer.

Am I missing something?

Thanks for your help,

Owen

-----Original Message-----

Hi,

I have a table called tblRFI that is accessed by several
users entering info dealing with different jobs. I'm
trying to figure out a way to assign a sequential number
for each RFI for each job that starts with 1 and goes on
as far as necessary.

For example Job Num RFI Num
123 1
123 2
345 1
345 2
123 3
345 3

Any suggestions will be greatly appreciated,

Do all your data entry using a Form - datasheets don't have any usable
events.

In the AfterUpdate event of the control bound to Job Num (presumably a
Combo Box which I'll call cboJobNum, selecting from a table of job
numbers, but it doesn't matter) put code like:

Private Sub cboJobNum_AfterUpdate()
If IsNull(Me![RFI Num]) Then ' don't stomp on existing data
Me![RFI Num] = NZ(DMax("[RFI Num]", "[tblRFI]", _
"[Job Num] = " & Me!cboJobNum)) + 1
End If
End Sub


John W. Vinson[MVP]
.

 

[John Vinson]

The code works great for the first RFI, but I get a "Data
type mismatch" error on subsequent ones, even if I change
job numbers. The RFINum is a number field set as Integer.

If Job Num is a Text field you need quotemark delimiters, or you'll
get this error:

Private Sub cboJobNum_AfterUpdate()
If IsNull(Me![RFI Num]) Then ' don't stomp on existing data
Me![RFI Num] = NZ(DMax("[RFI Num]", "[tblRFI]", _
"[Job Num] = '" & Me!cboJobNum & "'")) + 1
End If
End Sub

If Job Num is in fact numeric I'm not sure what the error would be!

John W. Vinson[MVP]