On Form_Load() check if table is empty

J

Jochem Davids

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?
 
B

Brendan Reynolds

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
 
S

Stefan Hoffmann

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 <--
 
J

Jochem Davids

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!
 
S

Stefan Hoffmann

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 <--
 
Top