Default maximum value + 1

Justns11

Registered User.
Local time
Yesterday, 23:06
Joined
Mar 24, 2009
Messages
20
Hello,
I currently have a form where I'd like the default value for one of the textboxes to be the maximum value in a field + 1. Basically, I want my primary key field to increase by 1 each time a record is inputted, just like the autonumber field would do. All I need is the vb code to put in. I've already got about 1200 records in the database so i think its it's too late to use the autonumber field. Any help would be greatly appreciated. Thanks alot. Access 07 by the way.
 
you do this like this:
Code:
me.textbox = dmax("field", "table")
use it on whatever event you want. Possibles are FORM LOAD() & ON CURRENT() (with an 'IF' statement checking for me.newrecord).
 
Here's a typical Auto-incrementing Number hack. The first code here would be for an IDNumber that is defined in the table as Text datatype. "Number" fields that aren't used for math really should be defined as Text.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim StartName as Long

If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
  Me.IDNumber = “1"
Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

Here's the same code for an IDNumber defined as Numerical:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
  Me.IDNumber = 1
 Else
   Me.IDNumber = DMax("[IDNumber]", "YourTableName") + 1
  End If
End If
End Sub
 
Thanks a lot for the help guys. I'm having another problem now. If you open the form and don't input any data into it and simply close it, then it creates a record with the stepID (PK) and the step# with no other information. I don't want it to do this, and it creates a problem because there will be gaps in the stepID's and the user will have to manually edit the datasheet...which I don't want. I've attached two screenshots. The first(SS1) is of the form and what it looks like when you open it. The second screen shot shows the datasheet and what the record that is created looks like. I've handled the error of just hitting next step by putting a msgbox in there and not allowing you to move to the next record if the part number is null, but I need some way to delete the record as the form is closed if the partno field is null. Here is what my entire form's code looks like now.

Code:
Option Compare Database

Private Sub done_Click()
DoCmd.Close
End Sub

Private Sub next_Click()
If IsNull(Me.PartNo.Value) Then
Dim box As String
box = MsgBox("You must enter a Part Number and Operation ID")
Else
DoCmd.GoToRecord , , acNewRec
End If
End Sub

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
  Me.StepID = 1
 Else
   Me.StepID = DMax("[StepID]", "ProductOperation") + 1
   Me.Step_Num = 1
  End If
End If
End Sub

Private Sub form_Afterinsert()
If Not IsNull(Me.Step_Num.Value) Then
  Step_Num.DefaultValue = Me.Step_Num.Value + 1
End If
If Not IsNull(Me.PartNo.Value) Then
  PartNo.DefaultValue = """" & Me.PartNo.Value & """"
End If
  If Not IsNull(Me.StepID.Value) Then
  StepID.DefaultValue = Me.StepID.Value + 1
End If
End Sub
If you have any questions please feel free to ask. Thank you in advance.
 

Attachments

  • ss1.JPG
    ss1.JPG
    84.6 KB · Views: 99
  • ss2.JPG
    ss2.JPG
    47.9 KB · Views: 101
Get rid of the assignment of the number in your form load event. Assign it at the Before Update event instead.
 
I did this and it fixed my original problem. But it doesn't fill in the default fields like I originally wanted. Here is my code now
Code:
Option Compare Database

Private Sub done_Click()
DoCmd.Close
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
  Me.StepID = 1
 Else
   Me.StepID = DMax("[StepID]", "ProductOperation") + 1
   Me.Step_Num = 1
  End If
End If
End Sub

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub next_Click()
If IsNull(Me.PartNo.Value) Then
Dim box As String
box = MsgBox("You must enter a part number to continue")
Else
If IsNull(Me.Operation_ID.Value) Then
Dim box2 As String
box2 = MsgBox("You must enter an operation id to continue")
Else
DoCmd.GoToRecord , , acNewRec
End If
End If
End Sub
Private Sub form_Afterinsert()
If Not IsNull(Me.Step_Num.Value) Then
  Step_Num.DefaultValue = Me.Step_Num.Value + 1
End If
If Not IsNull(Me.PartNo.Value) Then
  PartNo.DefaultValue = """" & Me.PartNo.Value & """"
End If
  If Not IsNull(Me.StepID.Value) Then
  StepID.DefaultValue = Me.StepID.Value + 1
  
    
End If
End Sub
 
I think if you look you'll see that it is filling in the field, it simply doing it as you move to another record or close the form, not while you're entering data in the new form..
 
missing I know exactly what your saying. But if I opened the form and closed it right away it actually creates a record in the table "ProductOperations", not just in the subtable like your saying. I tried showing this with the screenshots, but I guess that didn't work.
 

Users who are viewing this thread

Back
Top Bottom