Insert the next number on a Record

[Guest]

Help needed please

I have a table that includes a field named 'Report Number'....Not all
records will require a Report Number and also the numbers must follow on and
not have duplicates. Therefore, I would like the users to be able to view a
record and if required, tick a box on the record which automatically inserts
the next available number onto that particular record.

The only way I know is to ask the user to manually enter the next available
number (but this can cause errors etc)

Any idea's please?

 

[tina]

make sure the ReportNumber field in the table has its' Index property set to
No Duplicates. then, in the form, add code to the checkbox's Click event,
something along the lines of

If Me!CheckboxName = True Then
Me!ReportNumber = DMax("ReportNumber", _
"TableName") + 1
DoCmd.RunCommand acCmdSaveRecord
End If

replace CheckboxName and TableName with the correct names of those objects,
of course. with multiple users there is always the risk of more than one
person attempting to assign an incremental number at the same time. the
SaveRecord action will result in a "unique index" error for the "second"
user if this happens, so you'll need to add error handling code to rerun the
DMax() function and Save action.

hth

 

[Rick Brandt]

Help needed please

I have a table that includes a field named 'Report Number'....Not all
records will require a Report Number and also the numbers must follow on and
not have duplicates. Therefore, I would like the users to be able to view a
record and if required, tick a box on the record which automatically inserts
the next available number onto that particular record.

The only way I know is to ask the user to manually enter the next available
number (but this can cause errors etc)

Any idea's please?

In the Click event of your button...

If IsNull(Me![Report Number]) Then
Me![Report Number] = Nz(DMax("[Report Number]", "TableName"), 0) + 1
Me.Dirty = False
End If

There is a risk here that multiple users would pull the same value. To minimize
that threat, I included a line that saves the record as soon as the value is
assigned. The If-Then block assures that existing numbers don't get
over-written.

 

[tina]

reading Rick's post, i see that i forgot to address the "overwrite" issue,
as well as the "first number" issue. here's amended code that includes both
his suggestion and mine, as

If IsNull(Me!ReportNumber) And _
Me!CheckboxName = True Then
Me!ReportNumber = Nz(DMax("ReportNumber", _
"TableName"), 0) + 1
DoCmd.RunCommand acCmdSaveRecord
End If

hth

 

[Guest]

Do i still need to include:

Me.Dirty = False ?

 

[Rick Brandt]

Do i still need to include:

Me.Dirty = False ?

Me.Dirty = False is just another way to save the record. If you are using...

DoCmd.RunCommand acCmdSaveRecord

....then you don't need it.

 

[tina]

Rick, i've seen both used in posted code, and i have used both myself in my
databases. i've always wondered if there are specific reasons to use one
over the other - in given situations, perhaps? tia, tina

 

[Rick Brandt]

Rick, i've seen both used in posted code, and i have used both myself in my
databases. i've always wondered if there are specific reasons to use one
over the other - in given situations, perhaps? tia, tina

My preference is to set the Dirty property to False. The DoCmd.RunCommand
acCmdSaveRecord command actually will save whatever form currently has focus
which in some circumstances might not be the form executing the code and
therefore not the form intended. Setting Dirty to false does not have that
problem.

The downside of setting Dirty to False is that if the record cannot be saved the
error message is not as intuitive as with DoCmd.RunCommand acCmdSaveRecord. The
latter would give an error that pretty clearly describes a failure to save the
record. With the Dirty property you get a message along the lines of "Failed to
set property". Not a big deal as long as you are aware of it.