Don't save record if no data is input

[Thompyt]

How do you do this?



I have the Dmax +1 equation for numbering, but I don't want the form to save the record if there is no other data added. How do I accomplish this?

Thanks

[June7]

Fairly common topic.

Here is one discussion https://www.accessforums.net/access/...ent-48322.html

[Thompyt]

June7,
This isn't "deleting" and by using a button. i want the user to be able to open the form not do anything, then close it without writing a record.

[June7]

I think it is similar situation. From what I remember of my review of the issue, the record is initiated by code that sets a field value. The OPs solution was to allow the record to commit then to delete it. The discussion goes on to explore alternative of UNDO action.

What are you doing that causes record to be initiated? Just having the DefaultValue property set does not initiate a record. User or code must enter a value into a field.

Generating custom unique identifier in a multi-user database has risk. Until the value is committed to table, other users might generate the same ID.

If the value is committed and another user generates a record with the next ID but then the previous one is deleted, there will be a gap in the sequence.

[Missinglinq]

Use the DMax() + 1 in the Form_BeforeUpdate event to assign the Value to the Field. If no data is physically entered by the user, the Form_BeforeUpdate event doesn't run and no Record is created!

The other advantage to this approach, if this is to be used in a multi-user environment, is that you drastically reduce the chance of Records being entered, simultaneously, by multi-users, being assigned the same value. In a decade-and-a-half, using this strategy, I've never had two Records that had the same value assigned.

Linq ;0)>

[Thompyt]

Missling,
I like the idea of Form_BeforeUpdate (Cancel as Integer), but I get a null CR_No when I do this, instead of the DMax or Dmax +1 (depending on circumstances) when I use Form_Current. I am not sure that beforeUpdate will run each record though.

Private Sub Form_Load()
Me.O6Vote.Enabled = False
Me.GOVote.Enabled = False
Me.FinalVote.Enabled = False
Me.Soft_Level.Visible = False
DoCmd.GoToRecord , , acNewRec

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.O6Vote.Enabled = False
Me.GOVote.Enabled = False
Me.FinalVote.Enabled = False
If DLast("Action_Complete", "Change Request") = Yes 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") = No 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

[Thompyt]

June7 et al,
I took out all the Dmax coding by ' then put Me.CR_Num = DMAX("CR_No", Change Request") in the Sub Form_load and the Sub_Form_BeforeUpdate alternately. Why would I get a continuous blank in the CR_Num field on the form?

[June7]

Might look again at the thread referenced in post 2. It has new info about canceling a new record.

[Thompyt]

June7 if you noticed, I am not doing this under Sub_Form_Current, but Sub_Form_BeforeUpdate. It does not write a file to the table as I wanted. It will not display the DMax in the field when I open the form I cannot get it to show up on the field. I have tried the DMAX function under Form_Load and under Form_BeforeUpdate. The undo function is not required in this instance since I am not using the Form_Current.

Thanks

[June7]

The issue now is that the CR_Num field is not getting populated? Does the form BeforeUpdate event get triggered?

[Thompyt]

It looks like it. All the enabled=false fields stay disabled. I can change a record, go to the next record and in the new record the fields rermain disabled.

[June7]

That was a 'yes' to both questions?

If you want to provide db for analysis ...

[Thompyt]

NIE Change Request.zip
As requested

[June7]

Code in Cr_Num_AfterUpdate to populate CR_Num doesn't make sense.

A control's BeforeUdate/AfterUpdate events don't fire if control is populated programmatically. Only user data entry triggers those events.

Do you want user to see CR_Num populated? Consider:

Private Sub Form_Current()
    Me.O6Vote.Enabled = False
    Me.GOVote.Enabled = False
    Me.FinalVote.Enabled = False
    Me.Soft_Level.Visible = False
    If IsNull(CR_No) 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 Sub

'eliminate form BeforeUpdate or if user does not need to see CR_Num, use this event instead of Current
'eliminate CR_Num_AfterUpdate()

Private Sub Level_BeforeUpdate(Cancel As Integer)
Me.Soft_Level.Visible = (Me.Level = "Software")
End Sub

Dang - embedded macro! The Current event was running twice, weird. I changed the button macro to [Event Procedure] and code behaves much nicer.
Private Sub Board_request_Click()
DoCmd.OpenForm "Board Change Request", , , , acFormAdd
End Sub

[Thompyt]

Thanks, but that puts me back to square one. Why can't it be done in Sub Form_BeforeUpdate? With before update, it didn't count up continuous and make new records. It wouldn't go past the first new record. The only issue with BeforeUpdate was that I couldn't get it to display. The Sub_Num would display, making the issue weird.