Delete empty table (1 Viewer)

[lhanes72]

Problem: How do I delete an empty table by identifying a field in the table as null.

DoCmd.OpenTable "TableName"
If Field Is Null Then
DoCmd.Close acTable, "TableName"
DoCmd.DeleteObject acTable, "TableName"
Else
DoCmd.Close acTable, "TableName"
DoCmd.OpenForm "FormName"
End If

 

[boblarson]

Okay, your question just set off alarms in my head. Why is your table empty and what is its purpose for being there and why would you need code to delete it unless this is an ongoing matter?

 

[lhanes72]

The table is a temporary table created by a make-table query where the user inputs parameters. If the parameters do not exist in that query (say the user inputs "a" when they should input 1-10) the make-table is empty. If the table is empty, the macro progress is halted with its deletion.

 

[boblarson]

I would suggest making the table ONCE and then using a delete query to empty it and then an append query to add records as needed.

 

[lhanes72]

I guess what I'm really trying to get at is, does Is Null and Is Not Null work in vba?

 

[boblarson]

Well, in VBA you would use:

IsNull(TheItemToBeTested)

 

[lhanes72]

I can't understand why vba does not recognize the fields as null when the table is empty.

 

[boblarson]

I can't understand why vba does not recognize the fields as null when the table is empty.

Because you aren't checking the contents of the table. You would need something like this:

If DCount("*", "TableNameHere") = 0 Then

 

[lhanes72]

That's exactly what I was trying to get it to do! I'm sorry it took that long to get around to the underlying question/problem. Thanks so much for your help. I can see this coming in handy more than this one time.