Question VBA for beginners?

[kferree]

I’ve been working on a process by which our team would enter each event they attend/speak at into a form on a navigation pane. While the underlying database is quite simple and can already accept and store the data we need to collect on the collection form and various reports; it is not as user friendly as I would like to make it. The following changes would not only make it more user friendly, they would also make it more secure and avoid user error.

As the database stands today, I need to change quite a few things for the database to perform the functions the team would like to see.Following are those changes outlined.


· On the navigation pane, click on Marketing Intelligence form – enter data = Need button to enter data into the Events Detail All table as well as pull up the Travel Authorization (TA) report.
· TA report will be reworked to look like the World Travel Authorization Form.
· TA report will display the newly entered record.
· TA report will need a button to print the report. (User will need to print to pdf in order to email for approval then to wt).
· TA report will then need to clear out for the next record or allow itself to be overwritten by the next record.
· TA report will pull up the Marketing Intelligence form – the process can repeat.
· All other reports need to be exportable to Excel.


The problem is that I don't know VBA so I don't yet understand how difficult the process I'd like to set up is - I am just beginning to read the manual... any suggestions?



T

 

[Catalina]

I think the best suggestion is to buy a book about Access/VBA and work your way through it.
And of course there are many good sources on the Internet.

If you want help here you have to ask specific questions.

 

[kferree]

Thanks Catalina - do the steps I laid out seem logical for VBA? If so, I can start looking for the code for those steps...

 

[Steve C]

I've not bought a book on access. The books I can afford are too simple, they don't have the depth of information and the books that do are like impenetrable tomes and cost hundreds of Pounds.

There's plenty on the Net, get an overview there and post your specific questions on here.

 

[Pat Hartman]

None of the things you have listed requires more than a couple of lines of code and most require only one.

One thing you should start with is creating forms for all data entry. Users should NOT have access directly to tables or queries. Create a menu and use that to select forms for various actions.

For example, the code to print the current record from a form on a report is:

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If
docmd.OpenReport "yourreportname", acViewPreview,,"MyKey = " & Me.MyKey

The first statement saves the current record if it needs saving and the second opens the report in print preview and passes in the ID of the current record.

"Me." is the way to reference methods and properties of the active form or report.

Unless you are going to take a class, the simplest way to learn VBA is by example. Search your PC for the Northwind database. It is an install option when Access is installed. If you don't have it, you can download it from the MS download site. There are also a bunch of useful templates. Even if none is suitable, you should be able to examine them and find code snippets such as what I posted that will help you automate tasks.

 

[kferree]

Thanks Pat, it's been busy since I first posted my request "VBA for beginners?". I bought a book (okay a couple books) of which I've been feverishly reading, and received an extension on this project. Now I'm coming back to this forum with a specific question...

When a specific button on the navigation pane is clicked, it pulls up a form as the next tab - then a print button on the form leads to a print preview screen from which the email button can be chose from the ribbon. To streamline this process, I would like to see the print button on the form be an email button.

So, I think I can do this simple function in macros. I think my first step is to open the form from the navigation pane. Then print preview - then email form.

To go this route, I logged into the macro editor window - and began plotting my first move. I attached it for review. If I am correct in my logic so far - I am stumped at the screenshot I sent. I don't know what to do with the Filter Name and the Where Condition.

Do you (or anyone reading this reply) know what to do at this point? And, of course if my logic is flawed please pass along your opinion.

Regards -

 

[Frothingslosh]

Could you save a higher resolution image? That thing is tiny, and expanding it destroys what little eligibility it has.

If you don't mind learning VBA rather than macros, however, and are using Outlook for your email, there's a good article about email via Access here:

http://support.microsoft.com/kb/161088

You would need to modify it to fit your system's needs, of course, but it's a good starting point.

 

[jdraw]

I recommend you review the attached tutorial. It will help you with overall design.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[Pat Hartman]

If you decide to go the macro route (not recommended), you'll find help in the macro forum.