Does not display

[Thompyt]

I have VBA on a form:

If IsNull(CR_No) And DLast("Action_Complete", "Change Request") = True Then
        Me.CR_No = DMax("CR_No", "Change Request") + 1
       Me.Sub_Num = 0
   End If
If IsNull(CR_No) And DLast("Action_Complete", "Change Request") = False Then
        Me.CR_No = DMax("CR_No", "Change Request")
        Me.Sub_Num = DLast("Sub_No", "Change Request") + 1
    End If

It does not display anything when I go next from the last record. It should add +1 on either control (CR_No or Sub_No) depending on the status of the last record in the database on Action_Complete field.

[June7]

I would not trust DLast to retrieve value from what you expect to be the 'last' record.

What event is this code in?

Have you step debugged? Refer to link at bottom of my post for debugging guidelines.

[Thompyt]

June,
You caught me just as I was going to edit my first post and put it in there. Its under Private Sub Form_Current()

How would I go to find the last record then and compare it to that?

[June7]

DLookup("Action_Complete", "[Change Request]", "CR_No=" & DMax("CR_No", "[Change Request]"))

Change Request has a space so better enclose in [].

And this assumes the CR_No is the unique record identifier for the table and always increasing for each new record.

[Thompyt]

I think it has something to do with this code:

If IsNull([Change Requested]) And IsNull(Rationale) Then
DoCmd.RunCommand acCmdUndo
End If

This comes after the first two If then statements in the Private Sub Form_Current() it works if I comment the 3 lines out.

You code wouldn't work on your assumption.
I have CRs such as
11.0
11.1
11.2
11.3
12.0....... Its always changing. That's why I have a CR_ID to keep each record uniquely identified.

[June7]

Then use CR_ID in the expression. Same assumption.

Those CR numbers appear to be unique and increasing.

[Thompyt]

ThThey are unique at this point, but there will be one field change, and the sequence starts over. The original request was to keep the DB intact even though the current events end and a new series of events start. When that starts the identifying field of the event changes and the CR’s start back at 1.0. Myself I’d prefer making a baseline dB and copying that to a new name for each event. Then it would be a sequential CR.

Your code where "CR_ID=" I found needs to be in the contol line on the form itself, but doesn't work. I get a -1 displayed and will rewrite previous numbers.

[Missinglinq]

To be perfectly frank, I'm having a problem trying to follow what you're trying to do, here, but there are a couple or three things you need to know:

1. June7 is absolutely right about DLast; in spite of its name, it returns a random value for a given Field, not the last value entered for the given Field, nor for its value in the 'last' Record entered!
2. You line of code DoCmd.RunCommand acCmdUndo makes no sense in the Form_Current event. This event fires when you first move to a Record...before anything else can be done. Since it fires before you can enter or edit data, there is nothing to Undo!
3. It looks as though you're trying to enter data for existing Records, where that piece of data is missing; if this is true, you probably need to do this task using an Update Query, rather than physically navigating through the Records in your Form.

Linq ;0)>

[Thompyt]

1. I understand about DLast not being a good idea.
2. I tried it in Form_After Update (Cancel as an Integer) - Not a very good outcome. Although I think the Access gods were against me this weekend.
3. I am trying the following:

I have a form where I have CR_No, Sub_No, Level, Soft_Level,Change Requested, rationale, AO_Vote, O6_Vote, Final_Vote, Action_Complete as some of the major fields.

We get a series of events (requests) from outside customers. These are called Change requests. To keep track of who what where and when, we assign them a CR Number. Some of these requests have underlying changes, thus we use the Sub_no to denote there. CR_No + Sub_No makes it a unique identifier.

This identifier is also "sectioned off" from others by having a unique "Exercise number" (which changes bi-annually).
Note: Here I think June7s code would work, instead of what I was thinking before.

The form [Board Change Request] automatically opens to a new record by selecting the button on the start form.
The user wants to go through the records using the previous button, or to the next record with the next button. They can also find the needed CR with the find button.

What is intended is to have the user open the form and the next CR_No & Sub-No is automatically entered on the basis of a field [Action_Complete].

Action_Complete = True (meaning the last sequenced CR has been closed) the form will open up with the next Higher CR_No and Sub-No would = 0.

Action_Complete = False (meaning the last sequenced CR is open) the form will open up with the same CR_No and Sub-No would add 1 to the previous Sub_No assigned to that CR.

Examples:
Settings: Action Complete = True, CR_ID = 15, Last CR_No = 123, Sub_No = 1
This would give you the value: CR_ID =16, CR_No =124, Sub_No = 0

Settings: Action Complete = True, CR_ID = 15, Last CR_No = 123, Sub_No = 1
This would give you the value: CR_ID =16, CR_No =123, Sub_No = 2

Since the user wants to go back to older records, the previous record CR_No & Sub_No cannot be automatically changed. Manual input by the user is allowed.

This is required since not all CR’s are voted closed sequentially, but requires further input from other sources (Questions must be answered).

Without controls on CR_No & Sub_No the user selecting the Next button on the new record a second blank record is automatically made. In order to avoid this and having hundreds of blank records in the dB, I was using DoCmd.RunCommand acCmdUndo when the form closed and two major text fields [Change requested] & [Rationale] were null.
Additionally if there is a possibility of where there is no data added, the form would not advance to the next record set. Maybe on Form_Update where [Change Requested], [Rationale] and some other fields, just to be sure, equaled Null, then the next button could be disabled.

If there is a more simplistic way of achieving this, I’d be appreciative of it.

DLookup("Action_Complete", "[Change Request]", "CR_No=" & DMax("CR_No", "[Change Request]"))

I don’t think that this handles the CR_No & Sub_No, only CR_No.

[June7]

2. Form_AfterUpdate event does not have a Cancel argument. Did you mean BeforeUpdate?

If you need to consider two fields as unique identifier (compound key), that does complicate the lookup.

[Thompyt]

Form_BeforeUpdate does not work with DoCmd.RunCommand acCmdUndo errors out and generates a record. I am also thinking that DMax may not work if you have several NIEs. It would pick up the highest CR all the time for previous NIE's. Is there some type of Filter where it would equal the current NIE?

So far my way of if then statements work, but the AcCmdUndo doesn't work.

[June7]

Don't think should need acCmdUndo.

Reivew https://msdn.microsoft.com/en-us/lib.../ff822421.aspx

[Thompyt]

Thanks June7,

this code seems to work and a pop-up message gives the user the option to cancel or continue if they select the close button. It tells the user that the operation cannot be done if the next button is selected.

Unfortunately I cannot go to the previous or first record without having something in the [Change Requested] and Rationale fields.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Change_Requested) And IsNull(Rationale) Then
       Cancel = True
    End If
End Sub