On Form_Load() check if table is empty

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I want to check if a specified table is empty. And when it's empty to Close
the form.

I tried the following, where the CurrentDb.Execute part is not totally ok I
guess..

Private Sub Form_Load()
Dim Query As String
Query = CurrentDb.Execute("SELECT * FROM TableName")
If (Query = Null) Then DoCmd.Close
End Sub

Anybody an idea?
 
This example uses the Open event (because it can be cancelled) but you can
certainly modify it to use the Load event if you have a particular reason to
use that event.

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngRecords As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Count(*) AS TheCount FROM TestTable")
lngRecords = rst.Fields("TheCount")
rst.Close

If lngRecords = 0 Then
MsgBox "No data."
Cancel = True
End If

End Sub
 
hi,

Jochem said:
I want to check if a specified table is empty. And when it's empty to Close
the form.
I tried the following, where the CurrentDb.Execute part is not totally ok I
guess..
Dim Query As String
Query = CurrentDb.Execute("SELECT * FROM TableName")
If (Query = Null) Then DoCmd.Close
Click to expand...
The .Execute returns an ADODB.Recordset.
Anybody an idea?
Click to expand...
Single line:

If CurrentDb.Execute("SELECT Count(*) FROM Table").Fields.Item(0).Value
= 0 Then DoCmd.Close


mfG
--> stefan <--
 
Thanks both you guys, it works great now!

Perhaps you can also tell me how I can put a validation rule on a textbox or
a combobox based on a query. The input must be similar as the output of a
query.

Thanks in advance, you guys are a great and professional help!
 
hi,

Stefan said:
The .Execute returns an ADODB.Recordset. Nonsens.

Single line:

If CurrentDb.Execute("SELECT Count(*) FROM Table").Fields.Item(0).Value
= 0 Then DoCmd.Close
Click to expand...
Must be CurrentProject.Connection.Execute()...

mfG
--> stefan <--
 
Back
Top