Preventing a record from being deleted

[A10 Instructor]

I have a table with a blank record at the begining and populated records thereafter. I used the cmd button wizard to create a delete button for that table. Is there a way to prevent the blank record (record 1) from being deleted by the command button but still allow all other records to be deleted?

 

[trucktime]

Delete

You can test on one off the fields and prevent deleting the record when
it is empty.

In the On Click Event of the command button put something like this:

If Me.textfield = "" Then
MsgBox "Record cannot be deleted", vbOkOnly, ""
Cancel = True

Else
DoCmd.RunCommand acCmdDeleteRecord
End If

Replace 'textfield' with the actual name of the field

 

[neileg]

I'm curious as to why you would need a blank record?

 

[Pat Hartman]

The concept of a blank first record sound like a table design problem. You need to work on your relational database thinking. A table is not a flat file. Physical record order is meaningless. EVERY record of a table is like every other record. NO record is "different" in what it represents.

 

[A10 Instructor]

Re

I'm curious as to why you would need a blank record?

I have a several forms that use the same table. I created a combo box to allow the user to pick a particular record and the forms display all fields of that record. I use a blank record so that when the form is first opened, it won't show the first populated record.

If you know of a better way to handle this, I would appreciate any help.

Here is the code for the combo box:

Private Sub ComboP_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[last] = '" & Me![ComboP] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Pat Hartman]

Remove the RecordSource query name to make the form "unbound" and put it back after the form is open.

Private Sub ComboP_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Me.RecordSource = "YourQueryName"
Set rs = Me.Recordset.Clone
rs.FindFirst "[last] = '" & Me.ComboP & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[neileg]

Pat is, as usual, the shining light on this one, I'm just a glow in the dark! I'd have been tempted to create a table with a blank record in it, and use a union query to link this with your base data.

But then, as my status line says, I'm just a dirty hacker, not a proper programmer.

 

[A10 Instructor]

Re:

Remove the RecordSource query name to make the form "unbound" and put it back after the form is open.

Private Sub ComboP_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Me.RecordSource = "YourQueryName"
Set rs = Me.Recordset.Clone
rs.FindFirst "[last] = '" & Me.ComboP & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Pat,
Thank you for the advice. I did make the form "unbound" and use the code you posted. I do not understand the "...and put it back after the form is open." though. The text boxes that have the control source connected to each field in my table will appear with " #Name? " in them. Once I select a name from the combo box, everything works OK.

Can you possibly enlighten me further?

Thanks

 

[Pat Hartman]

I have a better suggestion.

1. Change the form's recordsource query so that its Where clause refers to the combo.
Select ....
From ...
Where SomeField = Forms!YourForm!cboSomeField;
2. In the AfterUpdate event of the combo, requery the form.
Me.Requery

With this method you can leave the form bound all the time. When the form is first opened, the value of the combo is null so the recordset for the form will be empty. The form may be completely blank except for the combo but this is fine. As soon as you choose something from the combo and requery the form, it will populate.

 

[A10 Instructor]

I have a better suggestion.

1. Change the form's recordsource query so that its Where clause refers to the combo.
Select ....
From ...
Where SomeField = Forms!YourForm!cboSomeField;
2. In the AfterUpdate event of the combo, requery the form.
Me.Requery

With this method you can leave the form bound all the time. When the form is first opened, the value of the combo is null so the recordset for the form will be empty. The form may be completely blank except for the combo but this is fine. As soon as you choose something from the combo and requery the form, it will populate.

Pat,

Thanks for all of your help and suggestions. This is exactly what I was looking for!

Again thanks for all your help.