First record of form is BLANK

[bigal.nz]

Hi All,

I have got this horrible issue where when I open my form based on a record set, the first record is always a blank and it ends up entering a blank record into my database, even though the record set itself contains no blanks.

Here is the code:

'------------------------------------------------------------
' CmdInput_Click
' Retrieve All Records where Status is not set AND show on input form
' Show daily meeting
'------------------------------------------------------------
Private Sub CmdSAMInput_Click()
    
    Dim rs As Recordset
    Dim strSQL As String

    strSQL = "SELECT * FROM SAM WHERE SAMStatus IS NULL;"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        DoCmd.OpenForm "SAM Input", acNormal
        Forms![SAM Input].RecordSource = strSQL
        Forms![SAM Input].Requery
        Else
        MsgBox ("There are no new Recordset to show")
    End If

End Sub

How do I stop this behaviour?

Not that I do want to allow editing on this form, I just don't want it to keep showing a first blank record which ends up going into my database.

Cheers

-Al

 

[Ranman256]

Your opening SQL asks for a null record,and you don't have one.
Asking for nulls will show a null, so just show all records.
Don't use the null.

 

[bigal.nz]

Your opening SQL asks for a null record,and you don't have one.
Asking for nulls will show a null, so just show all records.
Don't use the null.

My SQL asks for records where SAMStatus is null. That is not the line however that is creating a blank record.

The blank record appears to be created on the line:

DoCmd.OpenForm "SAM Input", acNormal

I stepped through the code, and it is after the form opens there is no record set, so it shows a blank form, which then is entered into the DB.

I think this is the issue.

Regards,

-Al

 

[sneuberg]

The mystery here seems to be why rs.RecordCount > 0 is True even when the recordset is empty. If you zip your database and upload it I'll see if I can figure it out.

 

[bigal.nz]

The mystery here seems to be why rs.RecordCount > 0 is True even when the recordset is empty. If you zip your database and upload it I'll see if I can figure it out.

Hi

The recordset is not empty, and the form correctly displays, for example 3 records returned, but initally the form is blank until you navigate to the "next record" when you see the first of the 3 returned.

I assume this blank record the form "displays" is then entered as a record into the database.

Does that make sense? Its not a case of a empty recordset incorrectly testing as 0 record count.

I will see if I can upload a copy of DB but need to delete a lot of data lol

Cheers

-AL

 

[bigal.nz]

Whats even more strange is that I have copied the database to another PC for testing and when I open the form with the recordset on that computer it doesnt display a blank record first - it goes straight to the record with data in it.

Very very odd.

-Al

 

[sneuberg]

I wonder if something is corrupt. Have you tried compact and repair?

Also if it's not on a record in the record set could it be on the new record? How can't it be adding a blank records without a key violation or don't you have a primary key for the table?

 

[bigal.nz]

Thanks!

It looks like a compact and repair of the backend fixed it!

-Al