Blank rows in table. (1 Viewer)

[moi]

Hello all,

Just today when i opend and browse one of my table, i found out that some rows are blank.. Can someone pls help me, what causes this blank rows, and how to trap and avoid this to happen.

All my entry form are bound to a table.

Thank you..

 

[CJ_London]

No table should have completely blank rows- at the very least the primary key field should be populated. If it is not, it implies corruption

 

[GaP42]

Check your table definition - does it have a primary key? is it autonumber?
Look at the table data - is the PK field populated?
If not then seems some corruption has occurred.
Do you have any tables with records that reference this "corrupt" table? Are these relationships defined in the Relationship window - how are they defined? Do they have null foreign keys to records in your "corrupt" table? Are those relationships enforced or optional?

Experiment in your non-production / test copy how records are added and deleted in that table and the related tables (removing FK reference). Manually track a record. Inspect the "corrupt" table after each action.

 

[CJ_London]

confusingly the OP has started another thread to say this one? is to be ignored. Have deleted the other thread as on rereading - not clear of the requirement

 

[moi]

confusingly the OP has started another thread to say this one? is to be ignored. Have deleted the other thread as on rereading - not clear of the requirement

Hi CJ_London,

Yes i requested the other post to be ignored or deleted, as said it is accidentally posted and it is not completed yet, for some reason i cannot just edit it, so i created again, i am typing on my cellphone..

 

[Gasman]

To edit on Chrome & Android, you press the ... to the right of the Report link. (Bottom left of post)
Then you have the option to Edit or Delete.

Pretty much where they are on a PC, but not enough room I expect.

 

[Pat Hartman]

Can someone pls help me, what causes this blank rows, and how to trap and avoid this to happen.

Usually the problem is caused by you dirtying the form with code AND having no validation in the form's BeforeUpdate event. That allows bad data to be saved.

To resolve the problem.
1. ALWAYS include validation checks in the form's BeforeUpdate event. That way you can cancel the save if there is any invalid data or any data is missing.
2. Do NOT dirty records with code. Wait until the user dirties the record before running any code you have.

Here's an example from one of my forms.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.EventName & "" = "" Then
        MsgBox "Event Name is required.", vbOKOnly
        Cancel = True
        Me.txtEventName.SetFocus
        Exit Sub
    End If
    If Me.ActiveYN & "" = "" Then
        MsgBox "Active YN is required.", vbOKOnly
        Cancel = True
        Me.ActiveYN.SetFocus
        Exit Sub
    End If
    If Me.cboMailingListID & "" = "" Then
        MsgBox "Mailing List is required.  Add a new Mailing iist first if required.", vbOKOnly
        Cancel = True
        Me.cboMailingListID.SetFocus
        Exit Sub
    End If
End Sub

 

[The_Doc_Man]

IF you are not comfortable with code solutions yet, then see if it makes sense to require a particular field in that table to not be blank, empty, or null. There is a "Required" flag for each field. If you set some text field as "Required" but do not allow it to be a zero-length string AND don't define a default value, then whenever you do whatever it is that created the empty row, it will trigger an error and you will see what is causing this.

To implement this solution, however, first you must manually delete the empty rows and verify that all of the non-empty rows have values in whatever field you select for this purpose. Then you must assure that warnings are NOT disabled.

EDITED by The_Doc_Man to correct the comment.

Pat's suggestion is more technically correct, but I offer my idea as a stopgap measure until you are comfortable with code solutions.

 

[moi]

Thanks

IF you are not comfortable with code solutions yet, then see if it makes sense to require a particular field in that table to not be blank, empty, or null. There is a "Required" flag for each field. If you set some text field as "Required" but do not allow it to be a zero-length string AND don't define a default value, then whenever you do whatever it is that created the empty row, it will trigger an error and you will see what is causing this.

To implement this solution, however, first you must manually delete the empty rows and verify that none of the non-empty rows have values in whatever field you select for this purpose. Then you must assure that warnings are NOT disabled.

Pat's suggestion is more technically correct, but I offer my idea as a stopgap measure until you are comfortable with code solutions.

Thanks The-Doc-Man,
Atleast you pickup my level in terms of solving db problems, and you step down at beginners level in giving solution.. Beginners like me is always looking a demo on how to do things correctly.. Guru's / Expert's here should have that in mind..

 

[moi]

Usually the problem is caused by you dirtying the form with code AND having no validation in the form's BeforeUpdate event. That allows bad data to be saved.

To resolve the problem.
1. ALWAYS include validation checks in the form's BeforeUpdate event. That way you can cancel the save if there is any invalid data or any data is missing.
2. Do NOT dirty records with code. Wait until the user dirties the record before running any code you have.

Here's an example from one of my forms.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.EventName & "" = "" Then
        MsgBox "Event Name is required.", vbOKOnly
        Cancel = True
        Me.txtEventName.SetFocus
        Exit Sub
    End If
    If Me.ActiveYN & "" = "" Then
        MsgBox "Active YN is required.", vbOKOnly
        Cancel = True
        Me.ActiveYN.SetFocus
        Exit Sub
    End If
    If Me.cboMailingListID & "" = "" Then
        MsgBox "Mailing List is required.  Add a new Mailing iist first if required.", vbOKOnly
        Cancel = True
        Me.cboMailingListID.SetFocus
        Exit Sub
    End If
End Sub

Thanks pat, i will see if i can implement your solution.. I am at beginners level, and have that difficulty in using code.. That is why i am always looking a demo every where..

 

[Gasman]

Beginners like me is always looking a demo on how to do things correctly.. Guru's / Expert's here should have that in mind..

If you get everything handed to you on a plate, you will learn nothing. You will become what I call an 'Oliver' from the film Oliver Twist, where he says 'Please sir can I have some more'
We do have users here who ask for A. They get the code, then they come back and ask for b, get the code for that. then come back for C and so on. They exist on other sites as well. Hell we have one person who has been using Access for over 19 years and he is a regular.

They have learnt nothng. People cannot even be bothered to Google these days.
Someone will ask "How do I...." and I do not know the answer, but a quick Google gets me the answer. :-(

So, put some effort into solving your own issues. You will be suprised how satisfying it is when you do and you solve it yourself.

I rarely write code for people, as it will take me almost as long as it would for you.
I do not remember the syntax, and rely on intellisense to guide me, but I will tell you the steps to take or offer a link that you can use to do what you want. It will not do it out of the box, that is hardly ever going to happen, but it will show you the way.

 

[CJ_London]

.. Beginners like me is always looking a demo on how to do things correctly.. Guru's / Expert's here should have that in mind..

But you are not looking, you are asking to provide

 

[moi]

If you get everything handed to you on a plate, you will learn nothing. You will become what I call an 'Oliver' from the film Oliver Twist, where he says 'Please sir can I have some more'
We do have users here who ask for A. They get the code, then they come back and ask for b, get the code for that. then come back for C and so on. They exist on other sites as well. Hell we have one person who has been using Access for over 19 years and he is a regular.

They have learnt nothng. People cannot even be bothered to Google these days.
Someone will ask "How do I...." and I do not know the answer, but a quick Google gets me the answer. :-(

So, put some effort into solving your own issues. You will be suprised how satisfying it is when you do and you solve it yourself.

I rarely write code for people, as it will take me almost as long as it would for you.
I do not remember the syntax, and rely on intellisense to guide me, but I will tell you the steps to take or offer a link that you can use to do what you want. It will not do it out of the box, that is hardly ever going to happen, but it will show you the way.

Ofcourse i did my own research also sir..

 

[moi]

But you are not looking, you are asking to provide

Thank you for telling me that sir.. Atleast the next time i come to your page i will know how to write..

 

[ebs17]

browse one of my table

If this is possible for you and others, the contents of records can also be removed without using forms. If it wasn't you, it was a third party.

The best, because comprehensive, protection would be to add it at table level, @The_Doc_Man has already described this. In addition, you would then have to prevent access to the tables and specifically access to the definition of the tables.

 

[moi]

If this is possible for you and others, the contents of records can also be removed without using forms. If it wasn't you, it was a third party.

The best, because comprehensive, protection would be to add it at table level, @The_Doc_Man has already described this. In addition, you would then have to prevent access to the tables and specifically access to the definition of the tables.

Thank you, am trying to digest all your recommendations/suggestions..

 

[CJ_London]

Ofcourse i did my own research also sir..

next time i come to your page i will know how to write..

Perhaps in the future you can show some of your research - example code you found or developed that doesn't work the way you want and why - err descriptions etc, links to some of the sites your research turns up and why they don't provide the result you want. At the moment, you just ask a question with no background to the actual requirement such as table design and relationships, screenshots of the forms you are using etc.

You don't even have the courtesy to respond to suggestions made e.g. by me and GaP42 (posts 2 & 3). We are16 posts in and we still don't know.

 

[Pat Hartman]

I also rarely write custom code but I do create samples that I am happy to share. I try very hard to use best practices and consistency so you can see what a working app actually looks like and how it handles validation in particular. Here are two "complete" applications so you can see how things work together across an app.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

Address Book

Address Book with alphabet buttons, a sort option, and a different filter option as well as two reports.

www.access-programmers.co.uk