Deleting Stuff!

[devolved_one]

Is it possible to delete all records in a database from code inside a form?? If so whats the code to do it??

Everytime I go into the Form I want it to clean out the database it is linked with.

Also what is the command to close a form.

Thanks.

 

[dennyryan]

You can do it in code or you could execute a delete query. I'll give you the command for executing a query in code:

Set qdf = dbClient.QueryDefs "ClearOutDailyTransactions")
qdf.Execute


Denny

 

[devolved_one]

hmm.. ok. I'm not familiar with Queries at all. Do I just through that statement into my code or do I create a query and if I create a query how do I do it?

 

[dennyryan]

You probably should spend time getting some level of familiarity with queries unless you want to write everything in VBA.

They are pretty easy to create but difficult to explain in writing. However, Microsoft has been thoughtful enough (such a nice company) to include most of that in the Help facility. I would suggest checking that resource out and then come back with any specific questions that you might not understand. It's really about a 10 minute exercise and well worth the time spent.

Denny

 

[devolved_one]

ok.. I was looking through the ms help on it and basically copied and pasted the code in an example and modified to work with my stuff.

I created a delete action query..

here's the names of my database (my naming conventions are shit! I'm lazy! )

database - db1
table - table1
query - query1
form - frmGetData

i tried what you sent me but I didn't like the "CleanOutDailyTransactions" or whatever so I deleted that then it said no object defined or something..

ms help says something like
Dim qdf as QueryDef
but then when you run it it says user defined type not defined..

so.. hmm.. I dunno.. any more suggestions??

 

[dennyryan]

Instead of ClearOutDailyTransactions (nice short name) you should put in your more discreet query name, i.e. query1.

Presumably that should work with the few paltry statements that I sent you.

Denny

 

[dennyryan]

My fault, I missed a parenthesis when I was editing the example, also you need some other statemenst as the Example in Help led you to believe sorry:

Dim dbClient As Database
Dim qdf As QueryDef
Set dbClient = CurrentDb
Set qdf = dbClient.QueryDefs("Query1")
qdf.Execute

Denny

 

[devolved_one]

hmm.. I dunno.. there most be something else missing.. with that last post if I copy and paste I get "Compile Error: User-defined type not defined" on the Dim dbClient as DataBase
and I'm assuming I'll get it on the Dim qdf as QueryDef too.. (seeing as I have before)
any other ideas?!?!..

thanks.,

 

[dennyryan]

Gee, this all started out loooking so easy (those are the only ones I touch).

The error message you're getting is mysterious to me. I assume that some of the people who live and breath VBA would be able to explain that problem and how to fix it.

You should be getting a dropdown list of items as soon as you type "Dim qdf as" and be able to select Querydef from the list but that's obviously not happening.

I'm of no use to you anymore. You may have to repost the question if no one else picks up on this.

Denny