In a continuous form have a Command Button (delete) hide at the last row (Data entry)

[jim_koug]

Hello, all, again.

I have a continuous Form with Data entry set to True. Its used to edit existing entries and also add new ones. I added a delete command at each row that works just fine, apart from the last row, as it is the new entry row.
My delete command is this:

Private Sub Command22_Click()
Dim Answer As Integer
Answer = MsgBox("Do you want to delete this Pill?", vbYesNo + vbExclamation + vbDefaultButton2, "Delete Confirmation")
If Answer = vbYes Then


DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True


Forms![EditPills].Form![Text253] = ""
Forms![EditPills].Form![Text18] = ""
Forms![EditPills].Form![PillsSearchQuery_subform].Requery
    End If


End Sub

I tried changing the visibility of the command button:

Private Sub Form_Load()
If Me.NewRecord Then
   Me.Command22.Visible = False
End If
End Sub

But that changes the visibility of all command buttons to false.

Is there a way to distinguish the data entry row to make the button at that row not visible? Maybe a way to tell it its the last row?
If not, is there a way when executing the delete command to tell it there's no record and not execute it?

Thanks again in advance.

[xps350]

You could prevent the code to executie by inserting a first line like:

If IsNull(me.Pill) Then Exit Sub

[jim_koug]

Thanks, Peter, for the reply, but i cant do that.
i found out that there are several ways to enter empty new entries in a continuous form. If you press any letter in the entry row, delete it by backspace and then move to another row it adds an empty record. If, instead of completly delete a record, you just delete the value with backspace it keeps the record with null values.
I cant force a textbox not to be null, as there are several textboxes in each each row, and just having one of those filled is ok.
As it is a query subform, i could just show only not null records, but that means the null records created by mistake will forever stay hidden, with no way to delete them (end user will not have access to the table) and just keep piling up.

It feels strange if theres no way, as i cant imagine i m the only one in the world with a data entry continuous form with a delete button ar each row...

If theres absolutely no way to hide that delete button at the entry row, the only thing i can imagine i can do is hide the null entries from the subform and have a code to delete null entries ar some point (like when the form loads).

[xps350]

I don't think it's a good idea to allow empty records to be stored. In my opinion, you should make at least some, if not all, fields mandatory in your table design. This way you also prevent pollution in your database.

[jim_koug]

As almost always, it was a PEBCAK...
I didnt add the field [AMKA] with the connection between form and subform in the subform. Even though not present, it still gets its value once that row/record is been populated.
So i dont have empty records, it was just the fields i put that was empty.
Now i can add that field [AMKA] as invisible in the subform, and use your code for Null on that.
Thanks again, Peter.

[Gasman]

Private Sub Command22_Click()

You might want to start giving your control more meaningful names?

As you project grows Command22 is going to mean nothing to you (or anyone else who might have to work on it).

[jim_koug]

(or anyone else who might have to work on it).

Someone else working on MY db??? I put so much effort in it, its MINE!!!

[Aussiebear]

Someone else working on MY db??? I put so much effort in it, its MINE!!!

In 12 months, even you will struggle to remember what "Command22" represents. Gasman's suggestion is spot on.