Adding sequentially

[Thompyt]

I am adding extra to the Db and have run into the following



CR_No Default = DLast("CR_No","Change Request")

But I want to add +1 to the CR_No when another fieled = true,otherwise leave it as is.

I am trying to use the following as code in VBA to try and do that:

Private Sub CR_Number_AfterUpdate()
If Me.Action_Complete = DLast("Action_Complete", "Change Request") = Yes Then
Me.CR_Number = DLast("CR_No", "Change Request") + 1
Else
Me.CR_Number = DLast("CR_No", "Change Request")
End If
If Me.CR_Number <> DLast("CR_No", "Change Request") Then
Me.Sub_Num = 0
Else
Me.Sub_Num = DLast("Sub_No", "Change Request") + 1
End If
End Sub

The second If then works like I want it to. So if the first runs and meets the criteria, then it sould be the next CR_No + 1 with Sub_Num = 0. If I manually change it, Sub_Num resets to 0.

I want the value to show up in the form when iot opens to a new record. I am thinking I need to change the default value.

Suggestions?

[ssanfu]

DLast() is the wrong function to use.
From Help: "You can use the DLast function to return a random record from a particular field in a table or query when you simply need any value from that field. ."

In addition, you are missing the criteria argument.

I am confused what the code is supposed to do....
You are using the CR_Number_AfterUpdate event to change the number you already entered into the "CR_Number" control?

[June7]

Should not rely on First, Last, DFirst, DLast - use Max and Min, DMax and DMin instead.

[Thompyt]

I am trying to make it as easy for the user. I want it to look up the value in the last record and go off of that. This is for the user to make several subsets of the same number. or when the user types in a new number, it resets the sub to 0. Just how I want it to act.

What I am trying to do is make the "prime" number update +1 when the field in the previous record = true.

I'll have to look up Max/Min as I compare that to a minimum or maximum value as ranges. If Max puts me at the last record, thats sufficient.

I think the default value set in the form for the field is messing with the code.

[ssanfu]

I want it to look up the value in the last record

Define what you mean by "the last record".

Tables are unordered. The "last record" Now can be different from the "last record" 30 minutes from Now.
And the "last record" can/will change by changing the sort order and the criteria in a query.

[Thompyt]

Define what you mean by "the last record".

Tables are unordered. The "last record" Now can be different from the "last record" 30 minutes from Now.
And the "last record" can/will change by changing the sort order and the criteria in a query.

SNAFU,
The Last record is the record input.

For clarity also. Sub_No field default is Dlast("Sub_No", Change Request")+1

If I could get the last records number in that field without forcing the default in that field. I think it would help.

[Thompyt]

I changed it to DMax function.seems cleaner and safer if the user screws up. I still havent found the way to add +1 if another field is true/yes. Does VBA overide the default code on a textbox in a form?

I cannot use a Dmax/Dmin on the Sub_Num. It is a rotating value and resets with a new CR_No.

[June7]

If you are using expression to set CR_No, why have a DefaultValue?

Setting the Sub_Num could probably still be done with DMax, just requires more criteria.

[Thompyt]

If you are using expression to set CR_No, why have a DefaultValue?

Setting the Sub_Num could probably still be done with DMax, just requires more criteria.

I figured out how to display w/out Default value. I put the DMax in the Form Current. Now if I go back and forth it counts up the sub numbers. So I havent, gotten the complete hang and +1 count with the other field. It is a Yes/No checkbox. =True or =-1, or = Yes. Still playing with that.

[June7]

Be careful, you don't want the code to overwrite value if you move to an existing record. Use:

If IsNull(fieldname) Then DMax(...

[Thompyt]

Thanks,
I know you don't like Dlast but this is what I have come up with. It seems to work unless you close out the form on the new record, and reopen. The CR_No is blank. I think this has to do with the CR_No no longer being blank. With this I can go back to previous records without corrupting the data.

If DLast("Action_Complete", "Change Request") = True Then
If IsNull(CR_No) Or Me.CR_Num <> DMax("CR_No", "Change Request") Then
Me.CR_Num = DMax("CR_No", "Change Request") + 1
Me.Sub_Num = 0
Else
Me.CR_Num = DMax("CR_No", "Change Request")
Me.Sub_Num = DLast("Sub_No", "Change Request") + 1
End If
End If

[Thompyt]

Is there a way to stop the new record from saving if nothing is done? Each time I open the form to new record it initiates correctly, but when I close it, the record saves all blank except for defaulted and the CR_NO & Sub_ Num? It does the same when I use AcLast and select next.

[June7]

That might get tricky. Review https://www.accessforums.net/access/...ent-48322.html

[Thompyt]

I'm pretty sure I got it licked. A combination of:

Private Sub Form_Current()
Me.O6Vote.Enabled = False
Me.GOVote.Enabled = False
Me.FinalVote.Enabled = False
If DLast("Action_Complete", "Change Request") = True Then
If IsNull(CR_No) Then
Me.CR_Num = DMax("CR_No", "Change Request") + 1
Me.Sub_Num = 0
End If
End If
If DLast("Action_Complete", "Change Request") = False Then
If IsNull(CR_No) Then
Me.CR_Num = DMax("CR_No", "Change Request")
Me.Sub_Num = DLast("Sub_No", "Change Request") + 1
End If
End If
End Sub

And

Private Sub CR_Num_AfterUpdate()
If Me.CR_Num <> DMax("CR_No", "Change Request") Then
Me.Sub_Num = 0
Else
Me.CR_Num = DMax("CR_No", "Change Request")
Me.Sub_Num = DLast("Sub_No", "Change Request") + 1
End If
End Sub