Command button to generate number

[Guest]

Users need to click a command button that generates a [Job_Sheet] number for
an Event record. This number should then display in the [Job_Sheet] bound
form field.

The number needs to increment by 1, starting from 18,500. I think DMax() is
part of the solution, but I also need to...

.... prevent accidental deletion or changes to the number displayed
.... hide any 'default value', so that the field appears blank until a
[Job_Sheet] number is generated.
.... prevent further clicks to the command button generating a new number

Not all Event records will have [Job_Sheet] numbers.

Thank you for your help. (I have limited programming skills)

(Sorry if this question appears as a repeat posting - I had a server error
message on my first attempt to post)

 

[Guest]

--Users need to click a command button that generates a [Job_Sheet] number for
--an Event record. This number should then display in the [Job_Sheet] bound
--form field.
--The number needs to increment by 1, starting from 18,500. I think DMax()
is
--part of the solution, but I also need to...


Don't use a button, you will have a problem if two people will click the
button on the same time before the record is updated so they both will have
the same number. Use the before update event of the form instead, and create
a check box in the form, that the user will select if they want the Job_Sheet
to be updated.
And then on the before update event use the code

If Me.[CheckBoxName] = True Then
Me.[Job_Sheet] = Nz(DMax("[Job_Sheet]" , "[TableNAme]"),18499)+1
End If
*********************************
.... prevent accidental deletion or changes to the number displayed

Set the field Lock property to Yes
Also, with the first option they wont see the value until they close the form

*********************************

 

[Guest]

Here is something I got from one of the great MVP's Ken Snell
You can do it in a form that is bound to the table (or bound to an updatable
query that includes that table).

One uses the DMax domain function to get the maximum value for a field, and
then adds 1 to that value:

NextValue = Nz(DMax("FieldName", "TableName"), 0) + 1

 

[Mr. B]

Users need to click a command button that generates a [Job_Sheet] number for
an Event record. This number should then display in the [Job_Sheet] bound
form field.

The number needs to increment by 1, starting from 18,500. I think DMax() is
part of the solution, but I also need to...

... prevent accidental deletion or changes to the number displayed
... hide any 'default value', so that the field appears blank until a
[Job_Sheet] number is generated.
... prevent further clicks to the command button generating a new number

Not all Event records will have [Job_Sheet] numbers.

Thank you for your help. (I have limited programming skills)

(Sorry if this question appears as a repeat posting - I had a server error
message on my first attempt to post)

You are correct in that you can generate the incrementing number using
DMax. You are also correct in that you can do this with the use of a
command button.

Using the On Click event of your command button, you can use code
like:

If Me.NameOfJob_StreetControl > 0 then
with Me.NameOfJob_StreetControl
.value = DMax("Job_Street","TableName")
.locked = true
End With
Me.SomeOtherControl.SetFocus
Me.NameOfCommandButton.Enabled = False
End If

You would also then need to evaluate the Job_Street value each time
the user access any record to determine if the is an existing value in
the Job_Street and if there is, lock the field and disable the command
button so the user cannot generate another incremented number. You
could use code like the following in the On Current event of your
form:

If Me.NameOfJob_StreetControl > 0 then
Me.NameOfJob_StreetControl.locked = True
Me.NameOfCommandButton.Enabled = False
Else
Me.NameOfJob_StreetControl.locked = False
Me.NameOfCommandButton.Enabled = True
End If

Just be sure to add the actual names of your controls when adapting
the code.

HTH

Mr B

 

[Arvin Meyer [MVP]]

OK, lets say that we can't use a command button without disabling it after
each first click and re-enabling after the record is generated. That still
leaves the problem of the user forgetting to push the button. So it is
better to automatically generate the number in response to some event. Using
the BeforeUpdate event of the form, we can do that (untested aircode):

Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.txtJobSheetNumber & vbNullString)=0 Then
If MsgBox("Do you want a job sheet number", vbYesNo) = vbYes Then
Me.txtJobSheetNumber = DMax("JobSheetNumber", "MyTable") + 1
Cancel = True
End If
End If
End Sub

 

[Guest]

Thank you everyone for your suggestions. I will try them out shortly and
respond again.

 

[Guest]

Thank you.
I created a bound check box field [JobSheetCheck] (unbound didn't work)

Then placed the following in the BeforeUpdate event on the form ...

If Me.[JobSheetCheck] = True Then
Me.[Job_Sheet] = Nz(DMax("[Job_Sheet]" , "[tblEvents]"),18499)+1
End If

I also locked the [Job_Sheet] field

Result: An empty field until [JobSheetCheck] is ticked and the record saved.
The number cannot be deleted by the form user, and even if you remove the
tick from [JobSheetCheck] the number remains in the record.

That's exactly the outcome I wanted. Thank you everyone.