Increment a Text Box on a Form

[Adam McReynolds]

I need to create an auto number on a text box in a form. I created it for the user to enter their own repair ID but now they want an auto number and it is the primary key. I would prefer it to build an increment of 1 on the last record in the table. I have my current code updating 2 tables simultaneously. Any help would be awesome. Help me get this guy off my back! Here is my current code:

Private Sub btn_new_repair_Click()
If IsNull(Me.txt_new_repair) Or Me.txt_new_repair = "" Then
MsgBox "Please Enter a Repair ID"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If

If DCount("RepairID", "TBL_REPAIRS", "RepairID = '" & Me.txt_new_repair & "'") > 0 Then
MsgBox "That Repair ID is already Created. Please Enter a new Repair ID or Search for the Record below in the search field"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO TBL_REPAIRS (RepairID) Values ('" & Me!txt_new_repair & "');"
DoCmd.RunSQL "INSERT INTO TBL_REPAIRS_BILLING (RepairID) Values ('" & Me!txt_new_repair & "');"

DoCmd.OpenForm "FRM_APU_CUST_INFO", , , "RepairID = '" & Me.txt_new_repair & "'"
DoCmd.Close acForm, "FRM_MENU"
DoCmd.SetWarnings True
End Sub

 

[pr2-eugin]

Adam, you might want to look into a method called DMax + 1, by RainLover.. It is simple and easy.. I also see that you have used Cancel = True.. However that has no effect on your code as the Click() method does not have the capability to Cancel an action..

 

[Adam McReynolds]

Adam, you might want to look into a method called DMax + 1, by RainLover.. It is simple and easy.. I also see that you have used Cancel = True.. However that has no effect on your code as the Click() method does not have the capability to Cancel an action..

Thanks for the response. I added this to my code, any possible problems Here?:

Private Sub btn_new_repair_Click()
[B]Me.txt_new_repair = DMax("RepairID", "TBL_REPAIRS") + 1[/B]

If IsNull(Me.txt_new_repair) Or Me.txt_new_repair = "" Then
MsgBox "Please Enter a Repair ID"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If

If DCount("RepairID", "TBL_REPAIRS", "RepairID = '" & Me.txt_new_repair & "'") > 0 Then
MsgBox "That Repair ID is already Created. Please Enter a new Repair ID or Search for the Record below in the search field"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO TBL_REPAIRS (RepairID) Values ('" & Me!txt_new_repair & "');"
DoCmd.RunSQL "INSERT INTO TBL_REPAIRS_BILLING (RepairID) Values ('" & Me!txt_new_repair & "');"
DoCmd.OpenForm "FRM_APU_CUST_INFO", , , "RepairID = '" & Me.txt_new_repair & "'"
DoCmd.Close acForm, "FRM_MENU"
DoCmd.SetWarnings True
End Sub

I am a little nervous to apply it to the production DB before i know it isn't going to have a future problem that I am not seeing now.

Also, I didn't know that about cancel= True. Thanks for that. I just copied the code from my boss and have used it ever since.

 

[Solo712]

Thanks for the response. I added this to my code, any possible problems Here?:

Private Sub btn_new_repair_Click()
[B]Me.txt_new_repair = DMax("RepairID", "TBL_REPAIRS") + 1[/B]
 
If IsNull(Me.txt_new_repair) Or Me.txt_new_repair = "" Then
MsgBox "Please Enter a Repair ID"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If
 
If DCount("RepairID", "TBL_REPAIRS", "RepairID = '" & Me.txt_new_repair & "'") > 0 Then
MsgBox "That Repair ID is already Created. Please Enter a new Repair ID or Search for the Record below in the search field"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If
 
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO TBL_REPAIRS (RepairID) Values ('" & Me!txt_new_repair & "');"
DoCmd.RunSQL "INSERT INTO TBL_REPAIRS_BILLING (RepairID) Values ('" & Me!txt_new_repair & "');"
DoCmd.OpenForm "FRM_APU_CUST_INFO", , , "RepairID = '" & Me.txt_new_repair & "'"
DoCmd.Close acForm, "FRM_MENU"
DoCmd.SetWarnings True
End Sub

I am a little nervous to apply it to the production DB before i know it isn't going to have a future problem that I am not seeing now.
.

You can scratch the former code from the first function. All you really need is :

Private Sub btn_new_repair_Click()
[B]Me.txt_new_repair = DMax("RepairID", "TBL_REPAIRS") + 1[/B]
End Sub

I would prevent the user touching the field (set Enabled to False and Locked to True). And yes the routine is quite safe. Basically, it boils down to the probability of two users on two machines hitting the button inside a milisecond.

Best,
Jiri

 

[pr2-eugin]

I would always advice to make a backup, test how it works on the mock model, before making anything live.. The code can have some modifications, If the txt_new_repair, is assigned at the first line, the next If that follows is redundant, as it already has a value, so it will never be Null or empty.. Just incase we need to wrap up the possibilty of having Null.. So surround the DMax with Nz() function.. In my guess I thought RepairsID would be Number, so it should not be surrounded by single quotes.. The Number would check the Max number and increment one to it.. Possibilty of having a duplicate would be RARE.. having said that, if multiple users are to use the DB, then it is becomes a necessity to handle Duplicate ID.. But again this might be rare.. So we can take that away as well..

So if this Form is Bound to the table TBL_REPAIRS, then you would need only one INSERT Statement.. Technically the txt_new_repair would be bound to the Field in the table (??!)

Private Sub btn_new_repair_Click()
    Me.txt_new_repair = Nz(DMax("RepairID", "TBL_REPAIRS"), 0) + 1
    
    CurrentDB.Execute "INSERT INTO TBL_REPAIRS_BILLING (RepairID) Values (" & Me.txt_new_repair & ");"
    DoCmd.OpenForm "FRM_APU_CUST_INFO", , , "RepairID = '" & Me.txt_new_repair & "'"
    DoCmd.Close acForm, "FRM_MENU"
End Sub

So the above code is all that you might need.. Hope this helps..

EDIT: Well Solo712, has popped in too.. Good..

 

[Adam McReynolds]

I would always advice to make a backup, test how it works on the mock model, before making anything live.. The code can have some modifications, If the txt_new_repair, is assigned at the first line, the next If that follows is redundant, as it already has a value, so it will never be Null or empty.. Just incase we need to wrap up the possibilty of having Null.. So surround the DMax with Nz() function.. In my guess I thought RepairsID would be Number, so it should not be surrounded by single quotes.. The Number would check the Max number and increment one to it.. Possibilty of having a duplicate would be RARE.. having said that, if multiple users are to use the DB, then it is becomes a necessity to handle Duplicate ID.. But again this might be rare.. So we can take that away as well..

So if this Form is Bound to the table TBL_REPAIRS, then you would need only one INSERT Statement.. Technically the txt_new_repair would be bound to the Field in the table (??!)

Private Sub btn_new_repair_Click()
    Me.txt_new_repair = Nz(DMax("RepairID", "TBL_REPAIRS"), 0) + 1
    
    CurrentDB.Execute "INSERT INTO TBL_REPAIRS_BILLING (RepairID) Values (" & Me.txt_new_repair & ");"
    DoCmd.OpenForm "FRM_APU_CUST_INFO", , , "RepairID = '" & Me.txt_new_repair & "'"
    DoCmd.Close acForm, "FRM_MENU"
End Sub

So the above code is all that you might need.. Hope this helps..

EDIT: Well Solo712, has popped in too.. Good..

Thanks. I am taking out the check for null and adding the NZ function. Will this default to 0 if null and then add 1?

I am keeping the check for duplicates though, because the user fills out about 8 different forms that all connect through this RepairID and it is a primary key so if there is a chance for duplicate then at the end when they try to save and it would have to close w/o it. I figure it can't hurt and maybe can help.

And txt_new_repair is unbound. This form acts as a main menu where they begin the process so it is full of unbound text boxes and then when the user enters step 1. in the data entry process they then enter a bound form.

Thanks again for all the help. I love the icon too. My fav. Butters episode is where they dress him up like a dog and take him to the vet cause he has a ninja star in his eye and the kids don't want to get in trouble. Cheers!

 

[pr2-eugin]

Thanks. I am taking out the check for null and adding the NZ function. Will this default to 0 if null and then add 1?

Yes, if the value is Null it will default to 0, then add 1 to it..

I am keeping the check for duplicates though, because the user fills out about 8 different forms that all connect through this RepairID and it is a primary key so if there is a chance for duplicate then at the end when they try to save and it would have to close w/o it. I figure it can't hurt and maybe can help.

If this is the Primary Key, then it will not allow you to close without a Value nor if there is a duplicate.. However the chance of this happening is very rare..

Thanks again for all the help. I love the icon too. My fav. Butters episode is where they dress him up like a dog and take him to the vet cause he has a ninja star in his eye and the kids don't want to get in trouble. Cheers!

My favorite character is Butters.. I was actually watching the episode last night..