delete user-selected table(s)

Ah! You beat me to it. But good for you.
 
thanks.
my main goal, obviously, is to get the code working.
but it's also fun to figure some things out myself.
(i'm not telling you to stop feeding me answers!) :)

now all i have to do is perform a check to make sure the user isn't trying to change the name of a table in the list to a name that's already being used.
i suppose a loop of some sort is necessary.
 
Last edited:
I'll give you a chance to figure that one out first. :)
 
alright. i could use a clue.
the blue text is the stuff that's supposed to do the checking for duplicate names.

Private Sub BtnRenameOK_Click()
If Me!LstTables.ItemsSelected.count > 0 And Len(Me.TxtRename > 0) Then
If Me!LstTables.ItemsSelected.count < 2 Then

Dim rowNum As Integer
Dim counter As Integer
rowNum = Me.LstTables.ListCount
For counter = 0 To rowNum Step 1
Dim varItem2 As Variant
For Each varItem2 In Me.LstTables.ItemsSelected
If Me.LstTables.ItemData(varItem2) = Me.TxtRename Then
MsgBox "Same name as another table."
GoTo BackEnd
End If
Next varItem2
Next counter

Dim varItem As Variant
For Each varItem In Me.LstTables.ItemsSelected
DoCmd.Rename Me.TxtRename, acTable, Me.LstTables.ItemData(varItem)
Next varItem
Me.TxtRename = ""
Me.LstTables.Requery
Else
MsgBox "You must select a single list in order to to rename it.", , "Cannot Rename"
End If
End If

BackEnd:

End Sub
 
Here's some code, courtesy of Access 2000 Developer's Handbook:


Public Function DoesTableExist(strTablename As String) As Boolean
Dim aob As AccessObject

On Error Resume Next
Set aob = CurrentData.AllTables(strTablename)
DoesTableExist = (Err.Number = 0)

End Function


Call the function from your code like this:
DoesTableExist("yourtablename")
It will return True or False
 
wow, that works nicely.
so much for my loopity-loop stuff.
thanks again, dcx693.
 
I knew I remembered a shorter way to do this. It might be slower, but here's a short formula for determining the existence of a table:
Nz(DLookup("[Type]","MSysObjects","[Name]= 'tablename'")=1,False)
Just replace tablename with the name of your table.

And I knew you sounded familiar. I got the idea from this thread that you and I were both in on.

The formula works by looking in the hidden MSysObjects table and looks for an object called tablename. It then returns the Type of the object found. It should be 1 if the table is found. Otherwise, it's Null. The formula then tries to set the value of the DLookup equal to 1. If it succeeds, it returns True, if not, it returns False.
 
I think I understand how the formula's supposed to be working, but I don't know about the part where you say that it tries to set 1 = 1 (and returns true) or Null = 1 (and returns false).
My point is that the formula isn't working.

If Nz(DLookup("[Type]", "MSysObjects", "[Name]= 'Me.TxtRename'") = 1, False) Then

...The formula's returning "False" every time. I have no idea why because "False" should only be the result of the NZ() function if the value of the variant is Null.
So the variant is Null every time.
So maybe the DLookup isn't working as expected.
Any idears?
 
Last edited:
The Dlookup is failing because of the way that you're referencing your table name in the formula. It's looking for a table called "Me.txtRename", it's not looking for the table that is referred to within that control.

Change the last parameter of the Dlookup slightly to something like this:

"[Name]= '" & Me.TxtRename & "'") = 1

I think that syntax should work. Try it and let me know.
 
Of course, I might have noticed that but I was trying to figure out how the Dlookup and NZ functions worked.
(It works, thanks.)
 

Users who are viewing this thread

Back
Top Bottom