Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

This collection of functions covers all the normal rounding methods of just about any numeric value - at extreme precision.
3
LVL 60

Author Comment

by:Gustav Brock
Finally I have these assembled. Through the years bits and pieces were collected, but now I had the option for a rewrite.

/gustav

PS: There are minor flaws in the layout but the editor is somewhat strange so I couldn't get it completely straight.
0
LVL 68

Expert Comment

by:Jim Horn
Nicely laid out.  Voted Yes.
0
Background
What I'm presenting in this article is the result of 2 conditions in my work area:
  1. We have a SQL Server production environment but no development or test environment; and
  2. We have an MS Access front end using tables in SQL Server but we are not using any other components of SQL Server (i.e., we are not utilizing stored procedures, views, table-valued functions, etc.).
Without these 2 conditions, I would not present this article as a possible work around.  Even without the second condition, if you have a development or test environment, I would recommend using those environments instead of what is presented in the article.  Also, I acknowledge that there may be several other alternatives to what I'm presenting but recognize that's usually the case with any challenge we face.

Why Develop this VBA
I have created a MS Access database front end using SQL Server as the backend for data storage only.  Although eventually I'd like to run all my processes through stored procedures in SQL Server so that the front end platform could be changed, the volume of data is small enough to run my processes through MS Access VBA.  Also, my experience with SQL Server is progressing but not to the point of being able to quickly write the code in the time needed to complete this project.  So, I'll come back to writing the code later.

We issue the MS Access front end to members of our team through an …
0

Expert Comment

by:Paul Rudy
Good job. Quite useful to create an off line copy of a SQL back end database to used during a network or server outage.
0

Introduction:

Have you ever been given the advice NOT to use domain functions inside queries?  I have, and I have repeated that advice over and over again; why?  The main reason is that this process is inherently slow.

But what if you absolutely need that value in your dataset?  I recently ran into this problem, and no matter what I tried, I could not get a particular field into a dataset and keep the rest of the recordset updateable, without using a DLookup().  This was extremely frustrating for users of the application, because it took close to 30 seconds for the datasheet to finish "calculating" every time the form was loaded or refreshed (this with only about 450 records). 

There are two primary reasons for the slowness associated with including domain functions in queries:
1.  Every call to a domain function requires that a new recordset be created using the table you want to grab data from and using the critieria you provided.  This is the same thing that happens when you use a correlated subquery (sample below) in your queries.
SELECT A.ID, A.Description,
(SELECT B.SomeValue FROM B WHERE B.SomeID = A.ID) as SomeValue
FROM A

Open in new window

2.  When you use a domain function (DLookup, DMax, DMin, DSum) or a user defined function in your query, the query parser is unable to see inside the function, so it has no means of determining the optimal execution plan.  This is the main reason you don't want to use Domain functions (or UDF's that return data) in …
6
LVL 68

Expert Comment

by:Jim Horn
Nice job.  Voting Yes.
0
LVL 15

Administrative Comment

by:Eric AKA Netminder
Dale,

The Page Editors have determined that this article is worthy of Awarded status.

Congratulations!

ericpete
Page Editor
0

Preface:

When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and removed the user interface for creating commandbars. This resulted in a common misconception that all CommandBars have been deprecated (eliminated). The truth is that you can no longer create CommandBar MENUS starting with Office 2007. However, you can still create and use pop-up menus, which are sometimes referred to as "right-click" or "shortcut" menus, using VBA. This series is all about these types of menus; I will use these two terms interchangeably in the remainder of this article.
 

Introduction:

Part 1 of this series (Understanding and Using Commandbars) describes techniques for exploring and using the CommandBar object. Part 2 of the series (Creating your Own) provides a more detailed discussion of the CommandBar and CommandBar Control object models, and demonstrates how to create your own shortcut menus.

While working on a new application, I found a need to not only develop my own right-click menus, but to augment and replace several of the …
4
LVL 55

Author Comment

by:Dale Fye
Thanks.  This is the first article I've written using the new editor, and I like the flexibility.  I was a little disappointed in the ability to size text other than using the couple of "styles".
0
LVL 68

Expert Comment

by:Jim Horn
Excellent article.  Voted Yes.
0
Dates are popular criteria in queries.  Date ranges ("between #1/1/2015# and #1/31/2015#", for instance)  are useful when you want to see everything that happened in January of 2015.  But if you want to see what happened last month, you have to open the query and type in the range, or -- if you're using fields on a form to supply your query with criteria -- enter the dates before you run the query.  If you regularly run "LastMonthsSalesReport", or "ListAppointmentsNextMonth", changing the date range can get old real fast, and -- if you don't remember that February has 28 days except in leap year -- can be error prone.

Here's a way to use the DateSerial function to set date range criteria for last year or next month.

DateSerial accepts integers and using those integers returns a Date.  You can use literal values (DateSerial (2015, 3, 1) ) to return the first day of March, 2015, but using expressions to hand DateSerial integers is where the real power is. This article was written on March 20, 2015. Today, pasting ?Year(Date) into the Immediate pane and hitting  returns an integer -- 2015 -- that is the current year. The same kind of formatting can return integers for the current month and current day-of-the-month: ?Month(Date) returns 3; ?Day(Date) returns 20.

Pasting ?DateSerial( Year(date), Month(Date), 1) into the Immediate pane and hitting  returns the date of the first day of the current month. …
8
Oh no! Did you forget to:

1. Set up QuickBooks Payroll for Job Costing?
2. Check Payroll Items to Track Expenses By Job?
3. Assign Overhead to expenses not belonging to a particular job?

Not sure if you did or didn't do any of the above?

Find out quickly by running the QuickBooks Profit & Loss Standard report. Select a date macro that includes a lot of transactions. An entire year is best.

Next:

1. Set columns to Total Only and note the Net Profit at the bottom.
2. Set columns to Customer:Job and note the Net Profit at the bottom.

If the two Net Profits do not match, NOT all expenses were assigned to jobs. Quickly fix the features you forgot to avoid this dilemma going forward.

For past transactions, this article provides a work around.

Applications/Utilities required:

1. Microsoft Access
2. QOCBC
3. QuickBooks

Knowledge required:

1. Basic understanding of VBA
2. How to create a Microsoft Access form
3. How to use the Visual Basic Editor
4. How to run QuickBooks reports

You can simply create the form and copy the code below into it. If you want to understand the process so you can alter it to fit your needs, an explanation follows. Be warned, however, that it may seem confusing. A good many calculations and loops are required. The end result, however, is tidy and handy.

This method uses the QODBC sp_report ProfitAndLossStandard to:

 1. run the Profit & Loss Standard for ALL
 2.…
0
LVL 9

Author Comment

by:Annaliese Dell
Thank you! I hope it helps somebody.
0
Tab between several subforms keeping these in sync when browsing records, and even when adding and deleting records.
7

Expert Comment

by:SK Quah
Thank you very much for your sharing, Gustav - by following your precise instructions here, I was able to synchronize the scrolling of two sub-forms. My boss is very happy!  Without your codes, I will not be able to do it.

Best regards,
Siew Kheng
1
LVL 60

Author Comment

by:Gustav Brock
Thanks Siew, I'm glad to hear that.
0
This article explains how to consolidate QuickBoooks transaction types in a database so only one form or report can be used to view multiple transaction types. This article uses the tables:

     1.  InvoiceLine
     2.  EstimateLine
     3.  CreditMemoLine

The benefits include:

     1. smaller database
     2. less code
     3. less places to change code
     4. fewer forms and reports

To use this article, you need:
 
  1. Basic knowledge of SQL and VBA for applications
  2. QuickBooks
  3. Database application
  4. QODBC, an ODBC driver that lets you exchange information with QuickBooks using SQL. Yes, you can write to QuickBooks from your database using QODBC without learning the complicated QuickBooks SDK.
The first thing you need to know is how QuickBooks stores transactions. QuickBooks is a relational database.

Constant #1: Each of these transaction types has a parent table and a child table.

Example:
Parent                       Child
Invoice                      InvoiceLine
Estimate                     EstimateLine
CreditMemo                   CreditMemoLine

Open in new window

Constant #2: The unique identifier for these parent tables is TxnID.

Constant #3: The unique identifier for these child tables is [childtable]TxnLineID.

Examples:
 
Invoice: TxnID                 InvoiceLine: InvoiceLineTxnLineID
Estimate: TxnID                EstimateLine: EstimateLineTxnLineID
CreditMemo: TxnID              CreditMemoLine: CreditMemoLineTxnLineID

Open in new window

0
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
6

Expert Comment

by:Tech BUD
Here u have discussed about Metro framework...Can u tell me the soln n VBA????
0
LVL 60

Author Comment

by:Gustav Brock
It's not about the framework. It's about applying a message box and an input box in VBA (Microsoft Access) a design that mimics and matches that of Windows 8.x.
0
As with the article about a TreeView exchange with standard continous forms (which you can find here) this is again a possibility to use slider bars, spin buttons and scrollbars using standard Access controls that are bundled together using a VBA class module.

All the details can be found in the documentation below that is also included in the attached Zip file.

The demo database is in the A2007 ACCDB format, but there are no specialities except the use of transparent buttons (in the demo only, not necessary for the class) so this class can also be used in other versions of Access. Also included are some demo graphics for the graphic slider variants which you can use like you want.

Have fun in experimenting with the new kind of "control".

Christian

CCSliderV1-2.zip

 

CC Slider

Table of Contents
1 About CCSlider
1.1 Features
2 How to use
3 The demo forms
3.1 frmSliderExamples
3.2 frmSliderExamples_Graphic
3.3 frmSliderExamples_Continous
3.4 frmSliderExamples_Continous2
3.5 frmSliderExamples_Equalizer
 


1 About CCSlider

This is another attempt to replace a control that is part of the Microsoft Common Controls with native Access controls. This time it is the slider control I wanted to replace with this little project.

The MS slider control can be …
1
In the last few months I developed a little system to replace the Microsoft Common Controls TreeView control. This one uses only basic features of Access in a continous form so you can display a hierarchical table in a tree with nodes and subnodes, including the lines, like in an Explorer files and folder display. It is able to display additional columns like any normal continous form and is also updatable.

Feel free to use it anywhere you like as long as you don't sell it as your own work and you keep the remarks in the code. You're also free to change the code to fit your needs.

Have fun in experimenting with the new possibility of continous forms with trees.

Happy Christmas 2014,

Christian

CCTableTreeV2.zip

The documentation (also included in the attached zip file):

CC Table Tree



Table of Contents
1 About CCTableTree
2 Different Versions
2.1 DAO Internal
2.2 DAO External
2.3 ADO/ADP External
3 Table Tree Form
3.1 Details about the form
3.2 Events
3.3 Multi Tests
4 Configuration Editor
4.1 Default configuration
4.2 Configuration for all levels
4.3 Different configuration for each level
4.4 Editing the RTF fields
4.5 The configuration columns
4.5.1 Configuration Name
4.5.2 User ID
4.5.3 Assign Cfg. To Level
4.5.4 Handle Opened/Closed
4.5.5 Textfield Format
4.5.6 Vertical Line / Branch Line / Corner Formatted
4.5.7 Level Format
 


1 About CCTableTree

CCTableTree is a system of some …
2

Expert Comment

by:Member_2_7786493
Oops, you are right Christian.
Sorry for the misunderstanding.
It works super now.
:thumbs up:
0
LVL 26

Author Comment

by:Bitsqueezer
No problem, glad if I could help you and it's useful for you.
0

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were using spreadsheets. It seems they bought the software thinking it would help but found out it did not work like their business did, and was not flexible enough to accommodate them.

 

When asked if I could suggest new software that might help, I asked them to show me how they ran their business. I collected a copy of each piece of paper that they used.

 

On the floor of my office I laid out all the forms and reports in the way that their business flowed. Next I took an 8 1/2 by 11 engineers blue grid paper pad and started drawing and labeling boxes where data would be input. Each data collection form would be on its own page.

 

At this point I would take the paper forms back to the client and run through them as if they were looking at a computer screen. Going through this process helped me become better acquainted with the flow and relationships of the data.

 

Next using colored sticky dots, I would put dots on each box that seemed related. This gave the basic design for the tables. I started MS Access and I created all the tables. Then I createe queries for the forms and then designed the forms.

 

For the first several weeks, the tables were pretty straight forward but the queries were a little more intense. The forms and reports were the hardest because this is where you have to make things flow the way a human would do it.

 

Things are more complex now (I started in 1998 before the internet). Now there are thousands of sites with free to paid support. What used to take me a few weeks to figure out, I can now find a solution for in a day or two on the Internet. When I find a very good site, like Experts Exchange, I will usually sign up and pay the fee for support; the small amount they ask for in a year will most likely pay off in my first request for support.


The reason I wrote this is to encourage you to create something that works the way you do. Trying to accommodate yourself to the way some generic software wants you to perform will stymie you for the rest of your business life. The other reason to do this is, very few businesses stay the same day in and day out. When you create your own database you can change it as your business changes.


If you really do not feel you can do this, try to find someone in your local community to write the program for you or to assist you in getting started.


 If you are a young person looking for an adventurous life, walk into businesses in your local community and ask how their software is working for them. I even wrote a few early databases for free to cut my teeth so to speak and for the experience (one of them wound up being my largest client). Once word gets out that you can do the above for businesses in your community you will have a great career and life.  

 

15

Expert Comment

by:Law Simon
I have done much the same thing for clients in the engineering field.  Almost always they are running their business from spreadsheets.  The great advantage of spreadsheets is their ease of data input and flexibility.  I write Access databases that read the data from the spreadsheets and then analyse and report the data from Access.  The client can keep operating  while you are installing and debugging the system and the massive effort in writing user friendly data input forms is avoided.  Remote sites can email in their workbook daily.  By loading all the data from the workbook into Access, any backdated changes that the site has made will automatically be included.
The other interesting outcome I have noticed is that as my experience increased I made the system simpler, and as the system became simpler it also became more flexible.  The challenge is to find a simple way to code complex user requirements.
0

Expert Comment

by:jason97m
Great article, I am currently working for a client building their database in MS Access.  Hoping to find more customers in the next few months.
0

Introduction

When answering a recent question, the testing of my solution taught me something about the VB language and some limitations in Access queries. This article shows you how to get past the 29 parameter limit in the Choose() function -- A Better Choose -- and how the variable column capability can add flexibility to your Access queries.


The Problem

The problem we are trying to solve, or functionality we want to introduce, is the ability to get different field values based on the numeric value of another field in the row.  Although the original question referenced fields with numeric names (D1, D2, D3, etc.), it would be nice to be able to select fields with any names.  Of course, the simplest solution for sequential number-named columns would look like this:

Select ID, DLookup("D" & Value, "MyTable", "ID=" & ID) As ValueChoice
From Mytable

Open in new window

Original question: http:Q_28476429.html


What doesn't work

Before getting to the solution, it might help to understand what does not work and why.


Choose

There is a 29 parameter limit.  If you have more than 28 columns, you will not be able to use the Choose function.  Here is an example of the Choose() function maxed out.  If I added one more field, I will get the 3075 error message: Expression too complex in query expression.

SELECT Top 1 Choose(ID,C011,C042,C303,C084,C005,C016,C777,C008,C009,C010,
C111,C012,C013,C014,C015,C016,C017,C018,C019,C020,
C021,C022,C023,C024,C025,C026,C027,C028) AS ValueChoice
FROM BigTable;

Open in new window


Note: In any code example, assume that the lines are continued or that line continuation does not matter due to the environment (Access query run-time).


Switch

2
LVL 1

Expert Comment

by:isense
It was my original problem.  The database was a corporate database for one of my clients.  The database has a record for each month and within the record there is a field for each day of the month (31) and a total.
0
LVL 49

Author Comment

by:aikimark
@Dale

I posted the (much) simpler solution in the original question thread.  The fact that these fields were sequentially numbered facilitated the dynamic column name creation via simple concatenation inside of a DLookup() function.
0
Applies to:  2007, 2010, 2013 (desktop) - untested in earlier versions

Datasheets are so easy to work with and provide so much flexibility that I sometimes wonder why anyone would want to use a listbox, but I keep going back to those listboxes anyway. 

In this article I'm going to provide you with an incredibly easy way to save your datasheet column order and column width properties, for all of your users, for all of your datasheets(1), and yet ensure that those same settings will be present when you field the next version of your application.

Most developers will embed a datasheet subform within a main form, and add a variety of controls and features to the main form, while displaying data in the subform.  Embedded-Datasheet.jpgOne of the handy features of a datasheet is that users can move, resize, hide, and even freeze columns on the left side of the datasheet; they can configure the datasheet exactly the way they want and when they close the application, their settings are saved and restored the next time they open the application (at least sometimes they are). Unfortunately, the next time you distribute a new version of the application, their custom settings will be gone; they will have to go through the entire process of reconfiguring the datasheet the way they want it.

To avoid this headache,  I developed a couple of relatively simple procedures that store the datasheet column properties in a table each time the datasheet is closed, …
2
LVL 55

Author Comment

by:Dale Fye
Eric,

This is interesting, because when I went in to add the line about which versions it applies to, the code blocks were missing the code again.  There has to be some sort of bug in the editor, possibly when you add text above the code window.
0
LVL 15

Administrative Comment

by:Eric AKA Netminder
Dale,

Congratulations; your article has been published, and has been awarded EE-Approved status.

ericpete
Page Editor
0
I've detailed how to get many large images to print on an Access report in an article here, but what about the scenario where you'd like to preview the images, on a form, as a slideshow?

As it turns out, that can be done natively in Access, with good performance.  No need for any API code, or embedding something PowerPoint-ish, or any third party controls.  Just straight up MS Access, running on Windows Vista or later, using VBA code and referencing the built-in MS Windows Image Acquisition 2.0 Library (WIA).  If it was me -- and it is -- I'd like to be able to link to existing images and save the path to the files in the database (saving images to OLE fields or attachments is just evil and should be avoided in most cases.)  I wouldn't mind to be able to associate any number of images with a given record, and I wouldn't mind to be able to specify how many pixels wide and high the preview images should be.  So, I've done that.

When you open the sample, frmSelectImages opens.  It's got a textbox for the path to a folder, a multi-select list box to allow you to select some files from the folder for association and some textboxes for width, height and what job to associate those images with.  Here's the code
Option Compare Database
Option Explicit

Private Sub cmdAddSelected_Click()
Dim db As Database
Dim rs As Recordset
Dim MyItem 

Open in new window

5
LVL 15

Administrative Comment

by:Eric AKA Netminder
Nick67,

Congratulations; your article has been published, and has been awarded EE-Approved status as well.

I'm really beginning to like your narrative style...

ericpete
Page Editor
0

Expert Comment

by:James Boomer
Nick67,

I have downloaded your "Placing a high performance slideshow on an MS Access Form" database, and it performs the functions I am looking for very well. The only issue that I have had is that when I save the database, it prompts me to save in Access 2007. Once that happens, the VBA code stalls. I get the following message:

Run-time error '13':

Type mismatch

When I debug, the code stalls on

Set rs = db.OpenRecordset("Select * from tempFileNames where 1=2;", dbOpenDynaset, dbSeeChanges)

I have been looking for fixes, but have not been able to find any. I would appreciate any help you could provide. Please keep in mind I am a VERY novice VBA programmer.

Thank you,

Boomer
0
In my article "Printing many large images in MS Access reports" I demonstrated techniques to dynamically resize large images that Access has trouble rendering down to a size that Access can print. Which is all well and fine.

Right up until you have a really large image with fine detail (like say printing) on it that you need to be able to zoom in on when you render the report as a PDF.  And then, grinding the image down to 96 DPI doesn't work out quite so well.  And yes, I suppose you could break out good, old MSPaint (or Paint.NET or any other image editing software) and break your image down into a few segments, and add THOSE images to your report instead of the very large original.

Of course, that would get REALLY old if you had to do it repeatedly. It would be a lot nicer if Access, even if it can't render the original of the image you'd like, could do the scutwork of breaking the image down to chunks and displaying those. And, very fortunately, with some WIA code and some restructuring of the report, it can.  

And it would be REALLY good if you could have some flexibility in deciding, right at the time you add the picture's path to the database, how many segments you'd like to break the image into, how much overlap each of the segments should have, how big the control that displays them on the report should be, and how many DPI the …
4
LVL 15

Administrative Comment

by:Eric AKA Netminder
Nick67,

Congratulations! Your article has been published, and has been selected as EE-Approved.

ericpete
Page Editor
0
LVL 49

Expert Comment

by:aikimark
I was looking forward to this article, Nick.  Very nice.  Thank you.
0
Whenever you have the need to create a database bigger than something to manage your local stamp collection you will find out that the best frontend/backend solution to work with in Access is to use a Microsoft SQL Server (or other real database servers) as backend, simply because it's an active server in opposite to a passive Access backend file which can't do anything without a frontend working with this file (of course there are a lot more reasons to work with a database server but that's not the theme of this article).

Things which we all must do to work with a database are of course creating objects to work with, in the simplest form: creating some tables. When you create a small database this can be done quickly and so the SQL Server Management Studio is one good way to do that.

But when you create bigger databases of course the number of tables grows quickly and so you must always do the same things again and again: Open the table designer, create a long integer field, set a primary key on it, set identity insert on, create a timestamp field (in most cases necessary for Access as frontend), often create i.e. a name field for lookup tables and a comment field, set indexes as needed - and so on.
Can be very boring and time consuming the more tables you need to create. You can of course create template scripts in SQL Server Management Studio and insert the needed values, but I thought there must be a better way to enter some data quickly without directly working …
0
With the ubiquity of camera phones, you can document many, many things with images. When it comes time to commit those images to paper in the form of an MS Access report, you will quickly discover that Access doesn't really care to render and print an unlimited number of arbitrarily large images.

Depending on the size and number of the images, the version of Access, and the oomph of the machine doing the heavy lifting, the number of images you can get printed before Access doesn't print them, gets dog-slow, hangs or crashes can vary. But there is general unhappiness at some point. Fortunately, we can get good results if we know a few facts, follow a few best practices, and work with some VBA code.

First, the facts. MS Access prints images at the web-standard 96 dots per inch (DPI) for the most part. How do I know that? Experimentation. Put a big, unbound image control on a report (say 8" x 6") with a size mode of 'clip'. Now bind it to a small image to it (say 200 x 150 pixels). Print it and get out a ruler. 96 dots to the inch. That's the minimum.

Next, how does Access resize to that resolution? Back in the day, Access only dealt with .bmp files. I suspect that Access internally converts the image to a bmp and then resizes it. If you've ever converted a .jpg to a .bmp and watched memory usage in Task Manager, you know this isn't an easy operation and consumes vast quantities of RAM. Watch Access's RAM consumption when you are trying to spool up a report with lots of…
14

Expert Comment

by:kico222yilin
a  usefull article
0

Expert Comment

by:Jan Huijs
Nick, great article. Your article helped me solving an issue that has troubling my mind for quite some years! Thanks sooooo much! With many greetings here from Renkum, the Netherlands! Jan Huijs
0
Old Mission Point Lighthouse Buoy & Mackinac Bridge
An overview of migrating your Microsoft Access queries to SQL Server Transact-SQL (T-SQL) query language, and is intended to be a 101-level introduction.  The target audience is Access developers that are familiar with queries, but not very familiar with SQL Server.
22
LVL 68

Author Comment

by:Jim Horn
... and you can build queries in SQL, but it sounds like you're ultimately going to need some kind of front-end UI to call and execute those queries.  Go ahead and ask these questions in the Access and SQL zones, but please be upfront that you're doing this for a class, and make sure the questions are designed towards helping you understand the concepts and seeking advise, and not doing your homework for you.  

As you can imagine, we get a lot of lazy (multiple expletives deleted) around here that attempt to have experts flat-out do their homework for them.

Thanks for reading my article.  If it helped you, please hit the 'Yes' button next to 'Was this article helpful' between the end of the article and the beginning of the member comments.
0

Expert Comment

by:Evelyn Decker
Thanks for the information!  I know what you mean about people trying to get other people to do their homework and in my opinion they are doing themselves a dis-service which they will not learn a thing. Also if I have any questions as I go along I will post and I am sure I will have questions.

Thanks Again
Evelyn
0
Applies to: 2000, 2003, 2007, 2010, 2013 (desktop)

As stated in the first article,  Understanding CommandBars (Part 1), users of Windows applications expect to be able to right click their mouse button over a form or control and see a popup menu designed to provide options for the user based on where they are and what they are doing in the application.  The CommandBars built into Access do a pretty good job of meeting these needs, but custom applications require custom solutions and Access provides a robust way for you to create your own shortcut bars (at least for your desktop apps).

In that article, I briefly described how to identify the names of each of the Access CommandBars (also referred to as shortcut or popup menus) and the controls within each of those CommandBars.  In this article you will learn how to create your own shortcut menus, modify existing CommandBars, and implement custom shortcut menus within your applications.  The sample database contains all of the code used in the article.

If you are fortunate to have a copy of Access 2003 laying around, you can use the instructions in this link from the Microsoft Office web site to create your custom shortcut/popup  menus, and then import them into your application. …
20

Expert Comment

by:Gilmer Pajarez
Excellent Article. it really help me to clarify many questions.
0

Expert Comment

by:Patel Vijay
amezing..........
0
This article is about how to create your own variables in Microsoft Access that can be used anywhere and saved between user sessions, resulting in making life easier on users, make maintenance easier for developers, and frames up Super Awesome Developer Ninja Stuff!    

All code is in the attachment at the end of this article.
 

Summary


  • The Variables Table
  • How To Get And Set Variables
  • Use in Queries, Forms, and Reports
  • Damn Handy Stuff you can Implement Right Now
  • Super Awesome Developer Ninja Stuff!!
 

The Variables Table

Create a simple table named VARIABLES, with only four columns:

  • txt_name, short text-50, Indexed = Yes (Duplicates OK), which is the name of the variable.  Here I'm using ALL CAPS with underscores, which is just a style preference.
  • txt_value, Short Text-255, Indexed = Yes (Duplicates OK), which is the value, and long enough to hold the longest possible value, which is typically a file path.
  • txt_description, Short Text-255, which is developer love notes on the purpose of the variable.
  • id, integer (not AutoNumber), which is a numeric value to uniquely identify the variable.
VARIABLES table in design viewHere's a sample in datasheet view sorted by id.  

VARIABLES table in datasheet view


How to Get and Set Variables

Before we can write code, since we're accessing tables a reference will need to be set to the …
9
Tested in Access 2007 and 2010.  Untested, but should work in 2003 and 2013.

Background:
I recently had a client ask if there was a way they could sort the columns of a listbox.  In this particular application, we had developed a number of forms which allowed them to view issues (bad or missing data) within various tables in their database.  These issues arose because of their desire to enter some basic information about each of their oil/gas wells, and fill the rest in later.  What they found, however, was that it was difficult to keep track of which wells were missing information, so we developed a series of forms that allowed them to view only those wells with a specific issue and then quickly navigate to the offending records.  During this process, they also discovered that different users process information differently, so they asked me if each user could define their sort orders at run time.

Sorting a list:
Most developers have had an application where users needed to be able to sort by a particular column in a listbox.  To do this with a single column, they generally add some code to the Click event of one or more column headers.  This is a relatively simple process of modifying the RowSource property of the listbox by adding an Order By clause to the SQL string.  

In this instance I had multiple forms with list boxes containing multiple columns.  I felt I needed to create a more universal solution than the one mentioned above.  This …
4
LVL 55

Author Comment

by:Dale Fye
aikmark,

I don't have access to my machine with 2003 installed at the moment, and won't until tomorrow, but I see what you mean.  Very strange that it appears one way to you and not to me or mark_willis.
0
LVL 1

Expert Comment

by:MarvinM80
aikimark,

I'm getting a compile error in the code on the following line of the SortThis() routine:
FormOrList.OrderBy = TempVars("OrderBy") 

Open in new window

I love using TempVars instead of global variables. As for your compile error, I usually use the exclamation point notation, i.e. TempVars!OrderBy. I know that there are different acceptable formats, but I have found that sometimes changing the format will get through the compiler. That may only be worth 2 cents, but maybe it's worth a try.
0
If you've ever tried to use Access in an object-oriented way you probably know that you can instantiate not only standard class modules but also forms and reports as they are class modules with an additional "base functionality".

This can be used, for example, to open the same form again and again in different windows to display different records of a table. The user can also close these windows normally using the close button of the window or some close button supplied by the application. So far, so good.

But what happens to the (closed) form object, the instance, in the background? If you release all variables pointing to the instance by setting it to "Nothing" you would expect that the form will now be unloaded, like most other objects you have instantiated on your own.
Unfortunately that's not the case, or not immediately. Access has collections for it's own objects for any instance, the "Forms" collection in this case, which holds an object pointer of all opened forms until they are closed using "DoCmd.Close" or the [X]-button of the window - or if no variable points to an instance of an opened form which was opened using the object oriented way ("Set frm = New Form_MyForm").
Access will do that, sure - but not when you expect that, it removes the form from the "Forms" collection only if there is any time to do that, when nothing else happens (and, following the help, that includes moving the mouse). That means, if your form uses i.e. a Timer event the event will…
0
When you look through the list of available controls in Access you will sooner or later come to one of the most comfortable controls, the ComboBox.

It has the ability to show you a list of records with multiple columns without the need to switch to another form to find the right one. And one of the really best functions of it is that you are able to enter a text and with every added character the ComboBox searches for any matching value in the bound or display column so you don't need to enter the complete text and you also don't need to open the dropdown list and search for the value manually.

Little side remark about the display column of the ComboBox, well known by any Access expert but not by beginners: The bound column is the column which usually is the ID of the record you really want to save into the table but you don't want the user to enter it as the user wants to work with the textual entry in the ComboBox.

If you set the first column (usually the bound column) to the ID column you would hide it with a setting of 0 as width. The next VISIBLE column > 0 is ALWAYS used as the column for the textual AutoComplete function of the combobox. But if you have a combobox which consists of Lastname and Firstname fields you maybe want to enter AND display "Smith, John", but the columns in the ComboBox should show "LastName  |  FirstName" as single columns.

In this case you would use a trick: Let the query assemble the two fields as Display column like this:

Open in new window

2

Comparison of Microsoft Access, LightSwitch and Visual Studio Platforms for Database Developers

Microsoft Access started at the beginning of the Windows revolution 20+ years ago and became the most popular database of all time. More recently, additional technologies have become significant, so it behooves the Microsoft Access community to be aware of the trends and options.

Ultimately, it's about being able to create solutions that help you and/or your users accomplish their mission. Sometimes the user's platform is critical, sometimes, it's the data source, and other times it's the permissions you have to deploy a solution. A variety of platforms and options are available with benefits and limitations with each. Meanwhile, Microsoft Access is also evolving with their latest Access 2013 version offering new web based solutions.

Matrix Comparison

Click for the detailed, complete matrix comparison of Microsoft Access, LightSwitch, and Visual Studio .NET
Matrix Comparison of Microsoft Access, LightSwitch and Visual Studio .NETFrom simple to more sophisticated, Microsoft offers a wide range of options:

Microsoft Access

Desktop
Microsoft Access (desktop) lets you create database applications that can be run on a PC and easily shared across a network or passed to another person with a file transfer. Each user needs to have the right version of Access installed. Access databases can have data within itself…
3
LVL 68

Expert Comment

by:Jim Horn
Good read.  I've always wondered back in the Access 97-2007 days when Access would come up with a decent web-enabled solution.   The limitations listed here (Macros only, no Office automation with Excel-Outlook, and no file interoperability) appear to be serious limitations.
0

Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.