CmdButton, Listbox and MsgBox in a Form

[sebastico]

Hello

I'm working with Access 2003

In a form I have a button to display person names in a List Box, which is
working well. What I need is, if there are no records to show in the List Box
a MsgBox displays "No records in DB"

This is my code:
Private Sub CmdA_Click()
lsbAut.RowSource = "SELECT AuID, AutName FROM TAut WHERE Left(AutName, 1) =
'A';"
If DCount("AuID", "AutName", "Left(AutName, 1) = 'A'") = 0 Then
MsgBox "No records in DB"
End If
End Sub

The code works well without
If DCount("AuID", "AutName", "Left(AutName, 1) = 'A'") = 0 Then
MsgBox "No records in DB"
End If

How can I use the above code with the MsgBox?

In the same way I would like that when there are records in List Box vba
will displays the number of records found, something like XX Records in DB.

Your advice is highly appreciated

 

[John W. Vinson]

Hello

I'm working with Access 2003

In a form I have a button to display person names in a List Box, which is
working well. What I need is, if there are no records to show in the List Box
a MsgBox displays "No records in DB"

This is my code:
Private Sub CmdA_Click()
lsbAut.RowSource = "SELECT AuID, AutName FROM TAut WHERE Left(AutName, 1) =
'A';"
If DCount("AuID", "AutName", "Left(AutName, 1) = 'A'") = 0 Then
MsgBox "No records in DB"
End If
End Sub

The code works well without
If DCount("AuID", "AutName", "Left(AutName, 1) = 'A'") = 0 Then
MsgBox "No records in DB"
End If

How can I use the above code with the MsgBox?

In the same way I would like that when there are records in List Box vba
will displays the number of records found, something like XX Records in DB.

Your advice is highly appreciated

I'd use a Recordset:

Private Sub CmdA_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT AuID, AutName FROM TAut WHERE AutName LIKE 'A*' _
& " ORDER BY AutName;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me!lsbAut.RowSource = strSQL
Else
MsgBox "No records in DB"
End If
rs.Close
Set rs = Nothing
End Sub

Note that I'm using a criterion LIKE 'A*' - which will use an index on
AutName, which certainly should be there - rather than calling a function on
each row, and sorting the Autnames if any are found.

 

[sebastico]

John

The code works excellent. Thank you.

Following your code, I'm trying vba to displays the number of records
founded, see the comment in 3ed line, where XX means the total of records
founded.

If rs.RecordCount>0 Then
Me!lsdatt.RowSource=strSQL
MsgBox "XX Records in DB" ' How can I code this?
Else
MsgBox "No records in DB"
End If
rs.Close
Set rs = Nothing
End Sub

Many thanks

 

[John W. Vinson]

Following your code, I'm trying vba to displays the number of records
founded, see the comment in 3ed line, where XX means the total of records
founded.

You'll need to move to the last record of the recordset (to save time Opening
a recordset usually just returns the first record it finds; while the code
continues the rest of the records will be retrieved:

If rs.RecordCount>0 Then
Me!lsdatt.RowSource=strSQL
rs.MoveLast
MsgBox rs.RecordCount " Records in DB"
Else
MsgBox "No records in DB"
End If
rs.Close
Set rs = Nothing
End Sub

 

[sebastico]

John

Thank you very much. The code works great.