Modules, Functions, Procedures, Headaches, Frowning and Brain Ache

[rlewis76]

Afternoon all,

Sorry to do this, i know the subject has been covered numerous times, I've searched for the threads, read the threads and understood entirely none of it.

So let me explain what I'm trying to do. I've created a simple database that splits out one table into several based on company name. Nice and simple, no problems. The next thing i wanted to do was export the newly created tables into an excel file with a new tab for each of the companies. But this produced lots of errors after using the macro builder which caused me to look for more answers. In the end I ditched the macro builder in favour of some VBA that was posted...I pasted this into a module...clicked run and it went through like a charm. Hurrah! Problem solved.

But no...didn't realise that you couldn't simply run a module...so I've tried attaching it to a macro using the builder...it just opens the module which isn't what I was hoping it would do.

There has been a lot said about applying procedures and functions and the like but the simple truth of the matter is i just don't 'get it'...

Query looks like this (strPath has been changed as it contained my company name, company names have been changed as they contain customer names)

Private Sub Command0_Click()
strPath = "desktopyaddayaddayadda\Remittance Database Test.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "company1", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "company2", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "company3", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "company4", strPath
End Sub

If someone can explain, like you would to a 6 year old or learning impaired chicken, how functions and procedures are used in a situation like this it would be very much appreciated. All i want to do is click on a form button and run the damn thing.

Thanks in advance,
Ross

 

[jdraw]

Please tell us WHAT you are trying to accomplish - not HOW you have done something.
Can you tell us about the database - tables and relationships?
Show the details of your query.
Why are you using Access and Excel? They really are different animals suited to different purposes.

 

[Mile-O]

I've created a simple database that splits out one table into several based on company name. Nice and simple, no problems. The next thing i wanted to do was export the newly created tables into an excel file with a new tab for each of the companies.

Goes against some basic database design, though. You'd have been better just using queries to select the relevant company info from your main table.

There has been a lot said about applying procedures and functions and the like but the simple truth of the matter is i just don't 'get it'...

Okay. Here's a quick overview.

Tables store data. They can have fields and rows.

Queries question or manipulate the data. You can get SELECT queries, as well as INSERT, DELETE, UPDATE, etc

Forms allow for manipulation of the data (i.e. data entry). Forms have controls. Each of these controls have numerous events attached to them (e.g. clicking a button triggers an event)

Reports present the data. Using your queries, you can make your data readable and fit for printing out.

Macros are basic actions to operate the database. They are pre-defined and limited in what they can do.

Modules are pages of code. In these you can write functions and subroutines. A function is some code that returns a value. A subroutine is some code that performs some action. You can notice them because they will be declared as a Function or Sub.

So, you don't just run a module. As I said, forms and their controls (textboxes, etc. - and this goes for reports, too) have events. You put the code in an event. Forms and Reports have their own module (an enclosed world called a Class Module; forget about class modules for now).

A standalone module has a wider scope than a form or report's module. In these modules you would put code you expect to use in multiple places in your database. It stops you putting the same code in every module.

Am I pitching at the right level?

 

[rlewis76]

Thanks both for your replies.

Mile-O you've cracked it there. While I'm quite happy plodding along with queries (all types I'm quite happy with), tables and simple macros i completely fall down when it comes to VBA.

It appears the problem is i was reading other posts on various forums and misunderstanding where the solution lay which is why my brain started dripping out of my ears. I created an event in a form and pasted the text in there and everything is now doing exactly what i wanted!

Thanks for the breakdown above, that really helped and is now bookmarked for future reference!

Many thanks