How can auto delete sub form's blank row

[moke123]

You could try something along these lines. Undo changes to previous records or force completion of new records.

In the required fields of the subform place V8 in the tag property.

Code goes in the subform's before update event

Private Sub Form_BeforeUpdate(Cancel As Integer)


    Cancel = IsFrmInvalid(Me, "OrderID")


End Sub

Function IsFrmInvalid(frm As Form, PKeyName As String) As Boolean


    Dim ctl As Control
       
    For Each ctl In frm.Controls


        If ctl.Tag = "V8" Then
        
            If IsNull(ctl) Then
            
                If Not frm.NewRecord Then
            
                    IsFrmInvalid = False
                 
                    frm.Undo
            
                    MsgBox "Changes to " & PKeyName & " " & ctl.Parent(PKeyName) & " have been undone"
                    
                Else
                
                    IsFrmInvalid = True
                    
                    MsgBox PKeyName & " " & ctl.Parent(PKeyName) & " is Incomplete. Please complete record"
                    
                    ctl.SetFocus
                    
                End If
                                  
            End If


        End If
    Next


End Function

[mike60smart]

Well in that case you are allowed to sack the user.

[deepaksharma]

You could try something along these lines. Undo changes to previous records or force completion of new records.

thanks for replay
1.Combo box will not be created in the product ID because the product is unlimited.
2. It is impossible to edit the sub form in the program created by you. If a customer revises his order and cancels any item or meter, then how will it be deleted in the sub form.
3. Mike has suggested a good method in post 4 and it also works but I want that delete button in sub form should not be created but should be coded in before update so that as soon as the user moves from blank row other row blank row auto deleted.
Thanks for helping

[moke123]

then how will it be deleted in the sub form.

So it's not a user inadvertently deleting entries, you are trying to delete the record by deleting only the visible part of the record.
Use a delete button as previously suggested.

[deepaksharma]

With a delete button as previously suggested.

but I want that delete button in sub form should not be created but should be coded in before update so that as soon as the user moves from blank row other row blank row auto deleted.
Or all empty rows of the sub form will be automatically delete when the user saves the form.

[Welshgasman]

but I want that delete button in sub form should not be created but should be coded in before update so that as soon as the user moves from blank row other row blank row auto deleted.
Or all empty rows of the sub form will be automatically delete when the user saves the form.

All those 'empty rows' are actually records. except for the New row.

[deepaksharma]

All those 'empty rows' are actually records. except for the New row.

Ok, so how to put the code in them that if shed and meter are empty then that record "empty record" gets deleted.

[Welshgasman]

Ok, so how to put the code in them that if shed and meter are empty then that record "empty record" gets deleted.

I do not know how many times you need to be told.

You code the BeforeUpdate event of the form to not allow records to be saved without the required data. Did you even look at that link I posted? :-(
Then when you have that working, just create a query to get rid of the empty records.

STOP the empty records being saved first, else you will always be chasing your tail.

[moke123]

Personally I would never do this, but you seem determined to do it this way.
Use your own field names in the condition statement.

Private Sub Form_AfterUpdate()


    Dim rs As DAO.Recordset


    Set rs = Me.Recordset


    rs.MoveFirst


    Do Until rs.EOF


        If IsNull(rs!ProdID) And IsNull(rs!Qty) Then  'Use your own field names
 
            rs.Delete
 
        End If


        rs.MoveNext
    Loop


End Sub

[deepaksharma]

Personally I would never do this, but you seem determined to do it this way.
Use your own field names in the condition statement.

101 cannon salute to you.
It is working perfectly fine but I am facing two problems.

1. While inserting a new record, when the cursor is moved to the meter field of the second row by the tab key in the sub form, the view of the cursor changes (as if the insert key is on) and have to press an extra tab. Why is this so? What is happening and how can I fix it.

2. You have checked the black row by running a loop in the gray shade table to delete the black row. My guess is that when the amount of data in the gray shade table increases, this loop will take more time to run, which is likely to slow down the program. I was wondering if there is a sql query that will directly check the black row on the current record (main form offerid to sub form offerid) and delete it. What do you say.
thank for your help.