Delete record on close if no save??? 1

[chrisaroundtown]

Hi,

I have a form that is used for data entry. I don't want records with only half the information required. To combat this I have put in a check on my Submit button that will check each field to make sure it doesn't = "". If it does I throw up a messagebox.

I want to make sure every entry goes through this check so on the form I have a "Saved" field, when the submit button is clicked txtSaved = 1.

For the OnClose event of the form I want to say
If txtSaved <> 1 Then
MsgBox &quot;Your entry has not been saved and will be deleted&quot;, vbOkOnly,&quot;Not Saved&quot;
'Then Delete the record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Else
End If

I am getting the error &quot;The command or action SelectRecord isn't available now.&quot;

Any Ideas,

Chris

 

[scking]

In the form BeforeUpdate event check and then, if the record doesn't have the necessary information use Me.Undo to reset the record. Then you won't event need to delete a record. Besides, it sounds like you are trying to delete a record that hasn't yet been save and therefore doesn't exist. There is an additional benefit using this design technique. You can Cancel the save operation for one, and no user could bypass the code. If you use a submit button what's to keep a user from closing the form in one of the other ways; control box or Alt+F4?

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[chrisaroundtown]

I have tried but it doesn't work,

In the before update event I have
txtCallDate.SetFocus
If txtCallDate.Text <> &quot;&quot; Then
txtCallTime.SetFocus
If txtCallTime.Text <> &quot;&quot; Then
cmbCSRName.SetFocus
If cmbCSRName.Text <> &quot;&quot; Then
cmbSpokenToBy.SetFocus
If cmbSpokenToBy.Text <> &quot;&quot; Then
frmSickLate.SetFocus
If frmSickLate.Value <> 0 Then
txtSickLateDate.SetFocus
If txtSickLateDate.Text <> &quot;&quot; Then
txtRosteredStartTime.SetFocus
If RosteredStartTime.Value <> 0 Then
txtRosteredEndTime.SetFocus
If RosteredEndTime.Value <> RosteredStartTime.Value Then
txtETA.SetFocus
If txtETA.Value <> 0 Then
cmbReason.SetFocus
If cmbReason.Text <> &quot;&quot; Then
txtComments.SetFocus
If txtComments.Text <> &quot;&quot; Then


Else
Me.Undo
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

I also tried this after update but this doesn't work either, any ideas?

Thanks
Chris

 

[scking]

Simply cancel the update and let the user select the empty control with the mouse.

Also, you could use Me.IsDirty to determine whether the user has made any entries to the form rather than a txtSaved control.

'frm is a parameter
Dim i As Integer
If Me.IsDirty Then
Dim i As Integer
Dim ctl As Control
For i = 0 To frm.Controls.Count - 1
Set ctl = frm.Controls(i)
Select Case ctl.ControlType
Case acTextBox, acComboBox
If Len(ctl) = 0 Then
'Mark this control
End If
Case Else
' Whatever
End Select
End If
Next i
End If

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[chrisaroundtown]

Thanks,

Could you please check your code as I am getting an error, you have 2 If's and 3 End If's.

Thanks
Chris.

 

[Hap007]

Here is a sure fire way to solve this problem

1) Create a temporary table (Local in your Database) that your form links to.
2) Have the user enter data into this table.
3) When user presses save, you validate each field and then create a permanent entry in the live table.
4) Final step, remove the temporary record.
Note: Make sure the temporary table is empty when you start the program each time.

2 advantages.
1) You only add records that you first validate, period.
2) You do not lock the main table with your update code, accept when you do the actual add.

Hope this Helps,
Hap
Access Add-on developer

http://www.fcs-software.com

 

[chrisaroundtown]

Hap,

Do you know the line of code of would use to update the live table from the temporary table?

Also, what happens if 2 users are updating the table at the same time?

Thanks

 

[Hap007]

Chris,

It is not one line, you would need to open the live (target) table as a record set, and then move each entry field to the target field. When all is moved, then do the save.

This assumes you are comfortable using VB code.

Another method would be to just do the 'UnDo' Command at the time your form closes.

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,acMenuVer70

That way, if the user did not save the record, it would be undone. This is not as good as the other method, but it is one line.

Hope This Helps,
Hap


Access Add-on developer

http://www.fcs-software.com

 

[chrisaroundtown]

Hap,

Sorry I have not been using Vb for very long therefore I cannot come up with this.

Does anyone else have any other ideas?

Thanks
Chris.

 

[hymn]

Do you require all fields to have data in or just certain fields?
You could in the table in design view make the field /fields require data
Or is it certain data needs to be inputted?

 

[PeteJohnston]

If the validation is only checking that something has been entered, would it be enough to just put an entry in the ValidationRule property for the text or combo box? Putting the following code behind the cancel button gives the user a chance to return to editing the record before undoing the add/change/delete.

Private Sub btnCancel_Click()
   If Me.Dirty Then
      'if edit in progress give user chance to recover
      If MsgBox("Abandon changes to this record?", vbYesNo + vbQuestion, "Confirm Action") = vbYes Then
         Me.Undo
         DoCmd.Close
      End If
   Else
      DoCmd.Close
   End If
End Sub

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[chrisaroundtown]

Great,

I actually did both the required fields by hymn and the message box by Pete. Thanks Guys.

One other question, is there a way to change the message shown when a field doesn't meet the required entry, similar to the validation text?

Thanks
Chris.

 

[hymn]

If MsgBox(&quot;WHAT EVER YOU WANT TO PUT IN?&quot;, vbYesNo + vbQuestion, &quot;TITLE&quot = vbYes Then

Hope this helps

 

[PeteJohnston]

On the Properties page there is a Validation Text property just below the Validation Rule. Would this be enough? You may be able to modify it thru code before displaying it but I've never tried that.

It's a shame that Access doesn't seem to fire a _Validate event like VB. You can do all of your validation in there and put out different messages depending on what error.

If I'm wrong about _Validate can someone let me know? PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[chrisaroundtown]

I have tried but it looks like the validation text only works for the validation rule.

Is there the eqilivent for the required property?

Thanks
Chris.

 

[scking]

That is true that the validation text property of the table only is displayed when the validation rule fails. Also, if the required property is set to true/yes then any attempted add or edit to the record which fails to include a valid value will also fail. This information is also available by clicking the F1 button.

The snippet provided on Mar 11 will cause the add to fail. And if it has and extra 'End If' you can remove it and it should compile. We write these from memory, most of us, and don't have the luxury of a compiler to tell us if we had a typo. This would allow you to verify a class of controls just prior to updating the data in the table to make sure they have data before you get the rather ugly error from Access. You can also add acTextBox to the select statement to verify them along with the combo boxes.

Public Sub Form_BeforeUpdate(Cancel As Integer)
'frm is a parameter
Dim i As Integer
If Me.IsDirty Then
Dim i As Integer
Dim ctl As Control
For i = 0 To frm.Controls.Count - 1
Set ctl = frm.Controls(i)
Select Case ctl.ControlType
Case acTextBox, acComboBox
If Len(ctl) = 0 Then
'Mark this control
Cancel = True
MsgBox &quot;The '&quot; & ctl.Name & &quot;' control is not complete. Please enter a valid value.&quot;
Exit Sub
End If
Case Else
' Whatever
End Select
Next i
End If
End Sub

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------