Counting Records in a Table

[tmort]

I have a data entry form that I want to go to a new record on opening. It works except in the instance when there are no records at all. I would like to do something like if recordcount >0 then go to new record.

If I'm on the right track I need to set the recordset to something in the second line below, but, I'm unclear on the syntax.

To just go to a new record when the form is opened I use:


Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[compliance sample] = False"
Me.FilterOn = True
DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

However, this won't work if there are no existing records.

I'm thinking something like:

Private Sub Form_Open(Cancel As Integer)
Set rst = qdf.OpenRecordset
Me.Filter = "[compliance sample] = False"
Me.FilterOn = True
If rst.RecordCount > 0 Then DoCmd.RunCommand acCmdRecordsGoToNew
End Sub

It doesn't like the second line and I really don't know VBA very well.


Can anyone help?

Thanks in advance

 

[llkhoutx]

Count the records in your form with the form recordset clone.

dim rs as dao.recordset
set rs =me.recordsetclone
if rs.eof then
'you have no records, do whatever
else
rs.movelast
'rs.recordcount gives then gives the number of records
end if
rs.close
set rs=nothing

 

[jgc31]

An alternative approach is to trap "error 3021 No current record" and use this to add a new record to the recordset.

 

[tmort]

Thanks,

I changed it to:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[compliance sample] = False"
Me.FilterOn = True
Dim rs As dao.Recordset
Set rs = Me.RecordsetClone
If rs.EOF Then
Else
DoCmd.RunCommand acCmdRecordsGoToNew
rs.Close
Set rs = Nothing
End If
End Sub

 

[llkhoutx]

Me.Filter = "[compliance sample] = False"

is incorrect. It should be:

Me.Filter = "[compliance sample] =" & False