[X]My Assistant

[bakersburg9]

I have a form with a button to exit (quit) my database after dropping a table - but if the database is opened later, and the table hasn't been re-instated - there will be an error that the table does not exist - is there a way I can deal with this through error handling ? I wouldn't know where to begin....

CODE

cmdExitDatabase_Click()

DoCmd.SetWarnings False  ' Don't want error if table doesn't exist

'Delete 'Invoice Approvals' table - when data is exported from Sharepoint, want it to go in a table named "Invoice Approvals"
MsgBox "Warning! 'Invoice Approvals' table will be deleted upon exit - if you are finished, that's ok", vbYesNoCancel

DoCmd.DeleteObject acTable, "Invoice Approvals"

DoCmd.Quit

DoCmd.SetWarnings True

Any help would be greatly appreciated....

[RJD]

Hi: Rather than use error handling for this, you could include UA's own TableExists function from the Wiki and apply it like this ...

Public Function DeleteTable()

If TableExists("Invoice Approval") Then

DoCmd.DeleteObject acTable, "Invoice Approval"

End If

End Function

...adding back the other code items you are using as well ...

I would also suggest (although it does not affect this solution) that you eliminate the space from the object name ... just saying ...

HTH
Joe

[bakersburg9]

Great idea ! How do I set up the TableExists function ? it says function not defined - as for the spaces, yeah, it just comes out that way, and the cost/ben. doesn't work out to take out the space, because someone else is going to be administering this db

[RJD]

Hi again: you can copy the function from the Function library here at UA and put it in a standard module. I have that already done, but am not in my office right now.

I'll check back when in my home office and see if I need to post the demo for you.

HTH
Joe

from phone

[bakersburg9]

Hey, thanks, Joe !!

[RJD]

You didn't say if you needed it, but here's my demo that includes the TableExists function just in case ...

HTH
Joe

Attached File(s)

 CheckTable.zip ( 20.32K )Number of downloads: 5

 

[bakersburg9]

Joe,
Thanks so much for that !!!! Works GREAT !!! I knew I'd get there ....

Steve

[RJD]

You are very welcome, Steve. Happy to assist.

Regards
Joe

[bakersburg9]

I spoke too soon - now it's erring - all I did was change two of the module names - now I'm getting an error:

The expression you entered has a function name that Microsoft Access Can't Find

What??? Why would it err just because I changed the module names ?

See attached db - I have it set up for a form (the only form) to open on Startup - when I click the "Exit Database" button, there's code behind the button to run the DeleteTable() function, which calls the TableExists function you shared with me - it's supposed to DROP the "Invoice Approvals" table if that table exists - not send me a Nastygram :-( grrr... I've attached the db, if you want to look....


EDIT:
Found this: "Note: You can't call a Function procedure from a macro if the function name is the same as the module name.
... well, butter my butt and call me toast.... WHO KNEW ???? - Never mind.....
This post has been edited by bakersburg9: Jan 4 2018, 06:26 PM

Attached File(s)

 Invoice_Approvals.zip ( 58.06K )Number of downloads: 2

 

[RJD]

Hi Steve: Glad you got that sorted out! Right, it's a naming issue ...

Joe