Help with Msgbox (1 Viewer)

isladogs · Nov 17, 2017

Gasman said:
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.

Good point Paul.

If the OP can successfully identify a way of checking there are no temp tables, the Delete button should either be disabled or hidden.
In which case the last bit of code becomes redundant.

lookforsmt · Nov 17, 2017

i am getting result 1.

isladogs · Nov 17, 2017

As I suspected

You need to change the DCount line to count temp tables & nothing else
One thing you could do is run this:

Code:

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

and see what it shows in the Immediate window
Then alter your DCount line to exclude that ....
I'm sure [C-I]* has been the issue from the start & I really don't know what it will find!

You've never actually said what your temp table naming convention is....!

Minty · Nov 17, 2017

The [C-I]* will find any table starting with the letters C through to I .
I'd suggest from the previous posts the Op actually wants something along the lines of

Like "C[0-9]*"

lookforsmt · Nov 17, 2017

Back to my project where i am importing 8 text files from shared folder, but sometimes if the user keeps 9 text files in shared folder instead of 8.
It will import all the 9 files and there by not run my sequence codes, which is why i am creating separate delete button for the user to just delete all the temp tables which start with either C01 or C51.

You may argue why not control the import of 8 txt files from the beginning so this situation does not arise. Well i will do that perhaps later as that might be little more tricky.

Apologies for not disclosing the temp table names
I have tried your 2nd approach and it gives me error message

Code:

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

and i am getting below error

Runtime error 3075, Syntax error (missing operator) in query expression

Minty · Nov 17, 2017

You cant Dlookup "*" as a field name, you would have to use DCount.
Also As Name and Type are reserved words try putting them in [] e.g.

Code:

DLookup("[Name]","MsysObjects","[Name] Like 'C[0-9]*' And [Type] =1")

This will only return the first one it finds as well.

lookforsmt · Nov 17, 2017

can i upload my db for better understanding

isladogs · Nov 17, 2017

Minty

You beat me once again
Apologies - its difficult using wildcards with DLookup

Scrap that idea & try this for the DCount

Code:

Debug.Print DCount("*", "MSysObjects", "[Name] Like ('CO1*' or 'C51*') And [Type] = 1")

or if all your TEMP tables are local, you just need this

Code:

Debug.Print DCount("*", "MSysObjects", "[Name] Like ('CO1*' or 'C51*')")

Yes you can upload it but I already understand what you want.
You just need the correct DCount line

isladogs · Nov 17, 2017

I meant to say that if you do want to use wildcards in a DLookup, you would do it like this:

Code:

Debug.Print Nz(DLookup("Name", "MSysObjects", "[Name] Like ('CO1*' or 'CS1*') And Type = 1"), "NONE")

Notice I've replaced the "*" with "Name" as it can only lookup values for a field name

If any tables matching that condition exist, only the first will be listed ....as Minty said previously.
If there aren't any, it will show NONE

lookforsmt · Nov 17, 2017

i am sorry all, i have tried and it still gives me the same result. I am uploading the db

FYI. The temp tables starting with "C" are on local drive and not linked tables.
i have added the temp tables in the db.

isladogs · Nov 17, 2017

Here you are.
All fixed

You hadn't updated the code in line with what I've been saying for the last 20 or so posts!

With a minor tweak to the code I suggested it now works perfectly.
You'll have to copy the temp tables back of course!

Recommend you add error handling to all your code - make it MUCH easier to fix errors

lookforsmt · Nov 17, 2017

Thanks ridders for this. I did put his code but must have missed something which i was not getting the result. Sorry for that.
I will add the error handler as suggested.

I want to thank you all helping me.

You guys are simply great. Thank you I will close the thread as Solved.

isladogs · Nov 17, 2017

lookforsmt said:
Thanks ridders for this. I did put his code but must have missed something which i was not getting the result. Sorry for that.
I will add the error handler as suggested.

I want to thank you all helping me.

You guys are simply great. Thank you I will close the thread as Solved.

You're welcome.
What you hadn't done was change the DCount line as instructed to replace the incorrect code based on [C-I] which didn't work.

lookforsmt · Nov 17, 2017

Noted, thanks

Help with Msgbox (1 Viewer)

isladogs

MVP / VIP

lookforsmt

Registered User.

isladogs

MVP / VIP

Minty

AWF VIP

lookforsmt

Registered User.

Minty

AWF VIP

lookforsmt

Registered User.

isladogs

MVP / VIP

isladogs

MVP / VIP

lookforsmt

Registered User.

Attachments

isladogs

MVP / VIP

Attachments

lookforsmt

Registered User.

isladogs

MVP / VIP

lookforsmt

Registered User.

Similar threads

Users who are viewing this thread