Consulting

Results 1 to 8 of 8

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

  1. #1

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

    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.

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

    If IsNull(me.Pill) Then Exit Sub
    Groeten,

    Peter

  3. #3
    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).

  4. #4
    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.
    Groeten,

    Peter

  5. #5
    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.

  6. #6
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location
    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).

  7. #7
    Quote Originally Posted by Gasman View Post
    (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!!!

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by jim_koug View Post
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •