Previous value + 1, with a twist

[Joshua61679]

So I have a text box "Sample" on a form "TEST Dent" that is bound to a table "TEST Dent". I'd like for the default value of this box to be the previous record + 1. I do have the field in the table set to Number, if that makes a difference.


However, I need it editable, and occasionally that value will be manually changed to the same value as the previous record, so autonumbering is out. Also, the numbering will often start over, so finding the highest value and adding 1 is also out.

I've done some searching and haven't been able to find a solution. Is this even possible? Thanks in advance for any help!

 

[dhookom]

You could set the default value of the text box to an expression using DMax(). This may take some modification if you have multiple people entering data into the table.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Joshua61679 . . .

A starting point. The code below goes in the [blue]code module[/blue] of the form.

[blue]Public Function defSample()
   Dim rst As DAO.Recordset
   
   Set rst = Me.RecordsetClone
   
   If rst.RecordCount > 0 Then
      rst.MoveLast
      defSample = rst!Sample + 1
   Else
      defSample = 1
   End If
   
   Set rst = Nothing
      
End Function[/blue]

And the [blue]Default Value[/blue] property of [purple]Sample[/purple] becomes:

[blue] =defSample()[/blue]

You'll see the default on the add new record line.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Joshua61679]

Thanks so much for the help.

dhookom, I do have multiple users that need to be able to use the database and with the starting over of the numbering in the records, I coudn't figure out how to get the DMAX() to be useful. Probably someone smarter than me could come up with something.

TheAceMan1, your code seemed to work in principle, but seemed to have some problems on when to set and apply the default for a new record. Sometimes it worked, sometimes it didn't. But, you gave me the thought to go back to trying to set the default value, which I had given up on earlier.

I had at one point tried set the default value of "Sample" to "[forms]![TEST Dent]![sample] + 1" on "Lost Focus" This, of course, caused the problem of if you just tabbed through the field, rather than manually setting it, it would change it's value on you, and you'd constantly high. Since my goal wsa to be able to usually just tabb through the field, this didn't work for what I needed. However, I came up with the idea to use the last control box in the record (I know, I didn't think to mention that there were other control boxes, and someone probably would have come up with this before me if I had, sorry) to set the default value of "Sample" to increase by 1. I does mean that every time you open the form, you have to type in the first value for sample, but that works for my needs.

So to close this out, I ended up with the ridiculously simple code of

Private Sub Rating_LostFocus()

    Forms![TEST Dent]!Sample.DefaultValue = Forms![TEST Dent]!Sample + 1

End Sub

Thanks again to both of you for getting my thinking headed in the right direction!

-Josh

 

[dhookom]

What happens if you tab in and out of Rating several times. Won't you keep incrementing the value? Take a look at Rogers Access Library autonumber Sample. His second example traps for issues with multiple users.

Duane
Hook'D on Access
MS Access MVP

 

[Joshua61679]

Duane,
If you tab in and out of Rating (which is the last control on the form) before you input anything in Sample (the first control on the form), then yes, you drive the value up. However, I can't convieve of any reason to do that intentionally. Also, because I need the ability for the user to be able to edit the Sample field manually anyways, if they happened to do that by accident, it's a simple fix for them, and then they are back on track for the next record, unless they do it again. I know it's best to always assume that if a user can break something, they will, but since I've got the manual editing option still available, I think I'm comfortable with the results I'm getting now.

Thanks again!

-Josh

 

[missinglinq]

I can't convieve (sic) of any reason to do that intentionally.

If you can conceive of it...one day a user will do it, and you need to ensure that doing it won't lead to an error in your data. Likewise, assuming that anyone doing something this illogical would then correct it, because the means of correction is available, is a mistake!

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[TheAceMan1]

Joshua61679 . . .

I can't help but go along with [blue]missinglinq[/blue]. So I came up with the following. The Idea is that you only want to update [blue]Sample[/blue] for a new record. Sample in [blue]previously saved records[/blue] should remain untouched. To accomplish this I used the Forms [blue]On Dirty[/blue] event to update sample. The event triggers when you [blue]edit a record[/blue] and we update sample directly (same code I gave only modified). Add a little detection to tell if were editing a new record and its done. So

[ol][li]Clear or remout out the [blue]Default Value[/blue] property of [blue]Sample[/blue].[/li]
[li]Clear or remout any other code meant to update Sample.[/li]
[li]Replace the code I gave with the following:

[blue]Public Function defAmt() As Long
   Dim rst As DAO.Recordset
   
   Set rst = Me.RecordsetClone
   
   If Me.NewRecord And rst.RecordCount > 0 Then
      rst.MoveLast
      Me.Sample = rst!Sample + 1
   Else
      Me.Sample = 1
   End If
   
   Set rst = Nothing
      
End Function[/blue]

[/li]
[li]For the forms On Dirty event (VBA, not thr property line) copy/paste the following:

[blue]  Call defAmt[/blue]

[/li][/ol]

Edit a new record and Sample get updated on 1st key. If the user is not satisified they can Esc out or what ever they wish. All is tested and works well.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

I wouldn't rely on using a recordsetclone since there may be a filter on the form's record source. I would use DMax() against the entire table and then using Roger's sample with multiple users:

This form illustrates how to use DMax is used to obtain the next number for ProductID similar to how the autonumber works.
=DMax("ProductID","Product")+1
in the default value of ProductID.

In order to make this work in a multi-user environment, when you try to save the record, if the primary key is duplicated (because someone else has already used the number), an error will occur and it will go back and get another number.

See the OnError Event of the form and the IncrementNumber function for relevant code.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

From this threads originating post:

[blue] I need it editable, and occasionally that value will be manually changed to the same value as the previous record, so [purple]autonumbering is out[/purple]. Also, the numbering will often start over, [purple]so finding the highest value and adding 1 is also out[/purple].[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

TheAceMan,
If you download and examine the solution I suggested, it sets the default value and would allow editing after the fact. If the field isn't a primary or unique index the Form_Error won't execute.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

I downloaded and reviewed the sample from [blue]Rogers Library[/blue]. Its a great autonumber scheme for multiple user environments. However the index of the[blue]Sample[/blue] field is this thread has to be at best [purple]Yes (Duplicates OK)[/purple]. Of course this means no pk

So the question is, [blue]how to handle intended duplicates in multiuser? ...[/blue]



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

TheAceMan,
I think we both don't care for undefined values. With numbers like this, there should be rules that would determine the values and not leave much to users in terms of editing ;-)

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

Roger That!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]