Help with Msgbox (1 Viewer)

[lookforsmt]

HI!

i have the below code where i want to delete the table from db, but before that it should prompt me yes or no. but 3 conditions
1) If user press no, then the msgbox says file not deleted
2) If user press yes, then the msgbox says file deleted

Upto here the code works fine

i want the code to further check if there is no tables available in db then
3) if the user press yes, then the msgbox says table not available

Below is my code which i am not able to put the 3 condition

Private Sub cmd_Delete_2_Click()
 
 Dim db As DAO.Database
 Dim tdf As DAO.TableDef

 Const cstrPrompt As String = _
      "Are you sure you want to delete the temp tables? Yes/No"
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
    Set db = CurrentDb
   
 For Each tdf In db.TableDefs

    If Not (tdf.Name Like "MSys*") And tdf.Name Like "[C-I]*" Then
       DoCmd.DeleteObject acTable, tdf.Name
    End If

 Next
    MsgBox (" Files deleted")
Else: MsgBox (" Files Not deleted")

 End If
End Sub

 

[Gasman]

I'd be checking whether the tables exist first?
If no tables, disable the button?
No point asking the user if they want to delete them when there are none to delete?

 

[arnelgp]

to check first if those tables are present, use dcount:

if dcount("*","MsysObjects","Name like '[C-I]*' And Type In (1,4,6)")<>0 then
your msgbox

else
msgbox "No temp table found"
end if
end sub

 

[Uncle Gizmo]

You may find this YouTube Video and Google presentation Helpful ...

Advanced Message Box

 

[isladogs]

Two things

1. What exactly are you checking for with the [C-I]* code?
2. Ariel's code suggested checking for linked tables as well as local tables.
However, as written the code will only delete local tables.
Those have Type=1 in MSysObjects table

 

[arnelgp]

on post #1 code he is deleting them also, regardless.

 

[lookforsmt]

thankyou for your advice and suggestions.

as the user will not be able to see the navigation pane he would not know if the temp tables exists.
So when the user clicks the delete button and then clicks "yes" the code would delete the tables if it exists and display popup message as " temp deleted"
Else it should display message "no temp tables for deletion"

Below code provided by arnelgp, i am don't know on which line should i place the code.

Request if you advice me from my code where will this be inserted.

thanks

 

[arnelgp]

Private Sub cmd_Delete_2_Click()
 
 Dim db As DAO.Database
 Dim tdf As DAO.TableDef

 Const cstrPrompt As String = _
      "Are you sure you want to delete the temp tables? Yes/No"

if dcount("*","MsysObjects","Name like '[C-I]*' And Type In (1,4,6)")<>0 then
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
    Set db = CurrentDb
   
 For Each tdf In db.TableDefs

    If Not (tdf.Name Like "MSys*") And tdf.Name Like "[C-I]*" Then
       DoCmd.DeleteObject acTable, tdf.Name
    End If

 Next
    MsgBox (" Files deleted")
Else: MsgBox (" Files Not deleted")

 End If
Else
Msgbox "No temp table exists. "
End If
End Sub

 

[lookforsmt]

Thanks arnelgp for the code

it works fine when there are temp tables available in db, and prompts message as expected.
But when there are no tables in db, it will Not display below msg

Msgbox "No temp table exists. "

 

[isladogs]

Replace the Else: with Else and then put the rest of that line on the following line

Also as I said in a previous reply, this can only delete local tables.
So replace
type In(1, 4, 6)
with
Type =1

Apologies for not using code tags. I'm on a tablet

 

[lookforsmt]

Thanks ridders but its was not clear what i should do.

 

[arnelgp]

remove the last Else together with msgbox.

Ridder: Refer to post #1 code, the delete code delete all regardless.

 

[isladogs]

Modified version of arnels post

Private Sub cmd_Delete_2_Click()
 
 Dim db As DAO.Database
 Dim tdf As DAO.TableDef

 Const cstrPrompt As String = _
      "Are you sure you want to delete the temp tables? Yes/No"

If dcount("*","MsysObjects","Name like '[C-I]*' [COLOR="red"]And Type =1[/COLOR]")<>0 then
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
       Set db = CurrentDb
   
        For Each tdf In db.TableDefs

        If Not (tdf.Name Like "MSys*") And tdf.Name Like "[C-I]*" Then
            DoCmd.DeleteObject acTable, tdf.Name
        End If

        Next
        MsgBox (" Files deleted")
     [COLOR="Red"]Else
         MsgBox (" Files Not deleted")[/COLOR]
     End If
Else
     Msgbox "No temp table exists. "
End If
End Sub

 

[isladogs]

Ridder: Refer to post #1 code, the delete code delete all regardless

No it won't.
The code needs to be modified if you want to DELETE a table IN A LINKED DB
All it will do is remove the link which is not the same thing at all

 

[lookforsmt]

Thanks ridders & arnelgp for the codes. but unfortunate both have the same result.

I think there is something more to add to this. Apologies if my explanation is not clear.

There are two situations:
A) when there is temp tables available in db
B) when there is No temp tables available.

Situation A:
When the user clicks delete button it prompts user whether he wants to action "Yes" or "No"
- If he clicks "No", then it will display a message "Files Not deleted" This will not delete the tables and works perfectly fine.
- If he clicks "Yes", then it will display a message "Files deleted" This will delete the tables and works perfectly fine.

Situation B"
When the user clicks delete button it should display popup message "No temp table found" and exit the routine

This is what i am trying to achieve.
Sorry for being pain

 

[Gasman]

I would still question why the user is being offered a delete button when there is nothing to delete?
Would also solve your problem as well.

 

[isladogs]

No, I understood that earlier.

For situation B, try the following
a) add a debug line before the first If

 Debug.Print dcount("*","MsysObjects","Name like '[C-I]*' And Type =1")

Does it show 0 in the immediate window when you run the code
If not, alter the DCount line until it does show 0
b) then step through the code using breakpoints and confirm it runs the line

Msgbox "No temp table exists. "

 

[lookforsmt]

i have query which displays all tables starting with C01 & C51

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "C01*" Or (MSysObjects.Name) Like "C51*"));

Since MSysObjects also has other table, is it the reason it does not show popup message, " files not found"

i may be wrong just guessing.

 

[lookforsmt]

i will try what you have posted in post #17 and share the outcome

 

[isladogs]

Back in post 5, I asked:

Two things

1. What exactly are you checking for with the [C-I]* code?
2. Ariel's code suggested checking for linked tables as well as local tables.
However, as written the code will only delete local tables.
Those have Type=1 in MSysObjects table

I suspect the [C-I]* code that you provided is incorrect

Since MSysObjects also has other table, is it the reason it does not show popup message, " files not found"

That's what I'm guessing - that the DCount value is NEVER zero
You need to find a condition which will correctly identify nothing but temp tables