May 31 2010

Access MVP recognition

Category: Office 2007 professional — admin @ 8:46 pm

Today’s guest writer is Ric Lewis from the Access PM team.

Here at the beginning of the year we wanted to take the opportunity to highlight our MVPs.  These are folks whose tireless efforts produce the vibrant Access community, which is a large part of what makes this such a great product.  Many of them have answered thousands upon thousands of questions in newsgroups and on forums.  Office 2007 professionaland Office 2007 ultimate are so powerfull.Many of them have been involved in Access for more than 15 years.  As a product team, we’re particularly grateful for their efforts, and we look forward to the chance to spend some time with them and recognize them at next month’s MVP Summit.

Walter Niesz  (Website) Author of multiple How To and FAQ on Utter Access.  Walter is the unquestioned master of the pictorial tutorial.

Scott McDaniel (WebSite)  Genius three times over on Experts Exchange, making him not only one of the top Access Experts, but a perennial top 25 across the entire site. IS Windows 7 Professional better than Windows 7 Ultimate ?

Oli S (Website) A ribbon master and all around Access Freak, with over 30,000 answered questions to his credit on UtterAccess and other fourms.

Larry Linson (Website) (N. Texas Access SIG) Enjoying 16 years as leader of the Access SIG in North Texas, Larry is a prolific contributor to Access newsgroups, contributing an article each month to the North Texas PC News.  He’s currently collaborating on a book with other Access MVPs.             

George Hepworth (Website)      “Grover Park George” is an Admin at Utter Access, as well as an author and blogger.  He is known for focusing on newer users, helping to make sure their first experience in Access is a good one.

QuickBooks 2010 is so Helpful! Bob Larson (Website) You can’t throw a rock without hitting some Access Forum where Bob is posting.  He’s established a strong presence on nearly a dozen different Access forums, most recently on Microsoft Answers.          

Gord Hubbell (Website) Gord is webmaster of what is arguably the heart of the Access community, UtterAccess.com.  Gord’s passion and commitment to developing the Access community have borne fruits to everyone from first-timers to those of us who develop the product.

Emilio Sancha (Website) (Spanish Forums) Emilio had already been playing with Access for nine years when he discovered the existence of the Microsoft Newsgroups and was opened up to a new world of social Access.  He is a regular contributor the forums and newsgroups.

Roger Carlson (Website) Owner of two major repositories of Access wisdom, Roger has been contributing on Access-L, UtterAccess and MS Access newsgroups for years.

John Viescas (Website) John is a prolific writer, authoring five Access books, and co-authoring another.  He has been an Access MVP for 16 years and running.                   

Andrew Couch (Upsizing site) (AUG Community) Andrew is director of the UK Access User Group (www.ukaugcommunity.co.uk) and a regular speaker at UK Community Events. He specializes in migrating Access applications to SQL Server and .net.  He is also the technical developer behind the popular migration toolset MUST.

Leo (theDBGuy)  An UtterAccess.com VIP who has been giving sage advice to Access users all over the world with their database questions.

John Mishefske (Website)  John Mishefske provides samples that can be downloaded from his Tigeronomy Software site that contain full source code as well as code and Jet SQL examples for common problems faced by Access and VBA programmers.  

Garry Robinson (Website) (Smart Access) Garry earned his Access MVP stripes writing for the very popular Smart Access magazine from 1998-2006 and when that closed down he moved on to Database Journal.  He has since bought the rights to the Smart Access archives and published their content online.          

Thomas Möller  (Website) (German SIG) Thomas is a jack-of-all-trades, posting in forums, running the oldest German Access SIG, authoring for Access im Unternehmen and lecturing at the German Access developers conference AEK.

Glenn Lloyd (Website) Glenn Lloyd is an independent Access Developer, Office Applications trainer, and Forums Administrator at UtterAccess.com. He publishes a blog for Office application fans and users at www.officetipsandmethods.com.               

Tom van Stiphout (Contact) Active in public newsgroups as well as on his Access security blog.  Tom recently contributed to an Access book in response to a challenge from Steve Ballmer in last year’s conference.

Graham Seach (Contact) Co-author of the canonical Access 2003 VBA Programmer’s Reference.  Graham is active in newsgroups and as a speaker.  He is currently working with an Australian university on various ways to include Access in the curriculum, including developing a BS degree program in Workgroup Systems (essentially a degree in Access development).

Cinzia Pagani (Website) (Twitter) Cinzia is active in the Italian Access NG and Social Forum.  He also serves as one of the webmasters of RIO, an Italian language site for FAQs and article on Office development.

Miriam Bizup  Miriam is a Zone Advisor for Access topic areas on Experts-Exchange.  She also contributes to articles as a Page Editor for the database and programming areas, acting as a moderator for site-wide support issues.

Gina Whipp (Website) The largest thread on the MVP forums in the last month has been the thread welcoming Gina to the MVP group.  We’re glad to finally have her insight and efforts recognized in this setting.               

Crystal Long (Website)  Crystal has produced numerous Access learning materials, from her free “Access Basics” book, to her video tutorials on her own YouTube channel (http://www.youtube.com/user/LearnAccessByCrystal).  She is also a frequent contributor on multiple Access and Excel forums.

Alex Dybenko Active in MS Access newsgroups for years and on his Access blog (http://AccessBlog.net).

Joe Anderson (Website) He has been working with Access since 1.0.  He’s in the Microsoft Access Hall of Fame on Experts-Exchange.com, as well as owner and CEO of All Data Corp.

May 31 2010

Use the IsClient() expression to conditionally run VBA from Web forms

Category: Office 2007 professional — admin @ 8:45 pm

Today’s guest writer is Kerry Westphal the PM for the macro designer and browser logic.

Here is Kerry’s introduction on The Access Show on Channel 9:

We expect many advanced Access Services applications will have client and web objects—we call these hybrid applications. Hybrid applications are interesting because you can create client forms that leverage the full power of link tables, advanced queries, rich forms/reports, and VBA. See this previous post for more background on Web and client objects. Microsoft Office 2007 is powerfull.The IsClient() expression allows you to conditionally branch your logic depending on whether the form running inside Access or the browser. In this way you can have one button that runs a different macro based on the context it is called from. This allows you to take advantage of the full power of VBA and macros when the Web form is in the rich client while also using the same form to the browser. It makes application development much easier as you don’t have to create the same form twice (one for the Web and another for the client) to have different logic running based on the environment. I’m going to show three different examples how you can use this functionality in hybrid applications:

  1. Open client verses server forms based on context.
  2. Call a client macro from a button.
  3. Call a VBA function from a button.

Open client/server form example

Using isClient(), you can open a different form based on whether the form is running in the client or browser.  Windows 7 is also my love!

 The design experience for Web objects and the compatibility checker allows you to open client and Web forms alike from your Web form. In the following example, you can drill through to a different contact detail form from the button of a contact ID depending on the environment. This is especially useful since the details form on the client takes advantage of functionalities that are unsupported on the Web details form. QuickBooks 2010 is so Helpful!

image

Call client macro

The following example below is an example of extending the 2010 Contacts template to Save a Contact to Outlook using the IsClient() expression to run a client macro from a Web form.

clip_image002

The Save to Outlook button allows you to quickly save contacts to your Outlook address book from your Access database.

clip_image004

clip_image006

In the OnClick event for the Add Contact from Outlook button, check to see if the app is running in the Client and if so, then run the client macro AddFromOutlook that can run the client-only macro SaveToOutlook to open the dialog to save a new Outlook contact. Otherwise, when the button is clicked on the Web, throw a MessageBox saying that the functionality is unsupported. Dreamweaver CS4 is very easy-to-use!

Run VBA from a Web form

You can also run VBA from the Web form using the same syntax. The example below extends the Projects 2010 template and adds a task to Outlook every time a new task is added.

In the Save & Close button on the Task Details form, add logic to check if the form is running on the client. If so, then set up TempVars with the values from the form so it can be passed to the VBA function. We will call the VBA function from the onLoad event of a hidden form to get the VBA to add the task to Outlook to run. If the form is running on the Web, this code will not get called.

clip_image008

The onClick event on the Save & Close button opens a client form that triggers the RunCode call in the Onload event to run the AddOutlookTask code.
 image

Hopefully, this post gives you some ideas about how you can combine Web objects with client objects and call VBA code from inside Web forms if the form is run in the Access client.

Enjoy!

Edited 1/19/2010 — This expression is not available in beta 2. It will be available in RTM. Sorry for any confusion.

May 31 2010

Access 2010: Design more maintainable apps with calculated columns

Category: Microsoft Office 2007 — admin @ 8:43 pm

When I talk to developers about why they love Access, the most frequent response is always about fast they can build applications that track data. This saves time and money. Calculated columns are a new feature in Access 2010 that is designed to help end users and developers build applications faster, and are easier to maintain.Ms office 2007 is so Charismatic.

Let me start with a couple scenarios from the Access 2007 templates. In the Contacts database, there are two fairly complicated expressions that conditionally show either the name of the person or the company, depending on which is blank:

FileAs: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]), IIf(IsNull([FirstName]),[LastName],[LastName] & “, ” & [FirstName]))

ContactName: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]), IIf(IsNull([FirstName]),[LastName],[FirstName] & ” ” & [LastName]))

IS Windows 7 Professional better than Windows 7 Ultimate ?

The Sales Pipeline database has a similar scenario with a different expression that calculates the value of the potential opportunity:

ForecastValue: [EstRevenue]*[Probability]

In these two scenarios it is highly likely that if you are going to use data from either of the tables, you will also need the calculated fields.

In the templates, we created a base query that did a SELECT * and included the expression columns. Unfortunately, I see many poorly designed databases in which an expression is repeated throughout queries, forms and reports. When the expression requires modification, the database owner must look for the many places it may have been used so all the occurrences can be changed.  With calculated fields, there is only one place to edit, so maintenance is faster and easier.

The main benefit of calculated columns is encapsulation, which leads to cleaner database design. It allows database developers to define the most common fields at the table level. This makes applications easier to maintain as inevitable bugs creep up in expressions (at least if you write expressions like I do :-) ). The developer can easily track the expression back to one place—the table.

Note: data macros are another example of a feature that allows developers to encapsulate logic in the table rather than behind form events. Dreamweaver CS4 is very easy-to-use!

Calculated columns are easy to create. Open the table in browse view. From the ribbon, select Table Tools | Fields | Add & Delete | More Fields | Calculated Field or choose the Calculated field type in table design view. Either way, the expression builder will help create the expression.

image

Another benefit calculated columns provide is consistency with SharePoint. Microsoft has made a big investment to make SharePoint a platform for collaboration and data applications. Calculated columns were introduced in SharePoint 2003 as a “calc and store” model, where values are only recalculated when dependencies change. This model is a performance optimization that makes it faster for the server to query against these values, especially when users sort and filter on the calculated field.

The calc and store model cannot be used for volatile expressions such as Date() and Now() because the stored result would only be accurate at the moment it is initially calculated. Functions that reference outside the row of the table, such as DSum, DCount and DLookup are also prohibited because it would be costly from a performance perspective to detect when dependencies have changed. These types of calculated fields are better left in base queries.

Calculated fields can save development time, reduce errors, and increase performance.  This results in better-designed applications.

Enjoy!

May 31 2010

Power Tip: Making forms easier to use and maintain

Category: Microsoft Office 2007 — admin @ 8:42 pm

Reader Štefan Masič provides today’s Power Tip.

In today’s post I would like to share with you some tips about form design that I’m using consistently in all applications I develop. Maybe you can get some benefit from my approach to form design.

As we all know, analysis and preparation of user needs are essential in building all elements of an application. This approach results in forms that  are easy to use, are uniform throughout the whole application, and are easy to maintain.Office 2007 professionaland Office 2007 ultimate are so powerfull.

Here are some design needs which must be satisfied on every Access form:

  1. The name of a form must be easily visible.
  2. The name of a form must be an expression of its functionality.
  3. User must easily identify all mandatory (required) fields.
  4. All mandatory fields should be visible on the first page of a form.
  5. All fields with a default value, auto number functionality, or read only property should be designed with a different background color.

It is common to put the name of a form into the window’s title with the Forms!FormName.Caption property. I prefer to use the Form Header Section with the label called lblFormName. This label can be larger and is therefore much more visible.

1

Figure 1: Title of the presented form is larger and more visible than the window’s title.

In my applications all mandatory fields have bold labels with the additional foreground color, named mandatoryLabelColor. All mandatory fields are surrounded with the border color, called mandatoryBorderColor.

Form showing different colors of labels, borders, and backgrounds

Picture 2: Mandatory fields are visible on the first view.

All mandatory fields are positioned on the first page. All other non-mandatory fields are positioned after first page break. With this design, the user can immediately determine which fields are required. All other fields are below them and on a second page, which is immediately visible with moving of the scrollbar. I prefer designing a form with page breaks and using the scrollbar for easy moving between pages. From my experience, this works better than a Tab control on forms with many fields.

All fields with default values, fields with the auto number functionality or read-only fields should have a different background color. For example: I use a yellow background color, called specialBackgroundColor. Additionally, all those fields have the AutoTab property set to No.

As mentioned above I use three colors, mandatoryLabelColor, mandatoryBorderColor, specialBackgroundColor. For all those colors I always use company colors or ask the customer to pick  specific colors.

In my examples the following colors were used:

  • color of mandatory labels: Pantone 341 C (CMYK 100, 0, 70, 30 or RGB 0, 117, 84)
  • borders of mandatory fields: Pantone 166 C (CMYK 0, 60, 100, 0 or RGB 239, 124, 0)
  • background color of special fields: Pantone 109 C (CMYK 0, 10, 95, 0 or RGB 255, 222, 0)

If all the colors are determined during the analysis phase, and they’re not likely to change later,  I always use a solution that does not require any programming. Here are Access numbers for all three colors, which I put into the appropriate properties of the labels and fields:

  • mandatoryLabelColor = LabelName.ForeColor = 5534976 (for Pantone 341 C)
  • mandatoryBorderColor = FieldName.BorderColor = 31983 (for Pantone 166 C)
  • specialBackgroundColor = FieldName.BackColor = 57087 (for Pantone 109 C)

Using global variables for more flexibility

If I think the customer might prefer different colors at a later date, I use variables instead of constants. This solution requires creation of a new module, with declarations of three public constants and three public variables. See the example modGlobalConstantsAndVariables below.

Public constants can be used when colors are defined in an early phase and are not likely to be changed. Public variables can be used when it’s possible that the colors might be changed in the future.

Module modGlobalConstantsAndVariables looks like:

'------------------------------------------------------------
' Element: Module, 2010
' Description: Definition of global constants and variables.
' - mandatoryLabeleColor
' - mandatoryBorderColor
' - specialBacgroundColor
'' Purpose : Global constants, global variables.
'' Author: Stefan Masic, january 2010
' Information: con = Constant; R=Red; G=Green; B=Blue
' See also: -
' Changes:
' (dd.mm.yyyyy, Author) Text
'------------------------------------------------------------
'--------------------------------------------------------------------

' Public constants.
Public Const conMandatoryLabelColor = 5334976 ' R=0 G=117 B=84
Public Const conMandatoryBorderColor = 31983 ' R=239 G=124 B=0
Public Const conSpecialBackgroundColor = 57087 ' R=255 G=222 B=0
'--------------------------------------------------------------------
'--------------------------------------------------------------------

' Public variables.
Public lngMandatoryLabelColor As Long
Public lngMandatoryBorderColor As Long
Public lngSpecialBacgroundColor As Long

'--------------------------------------------------------------------
' End of module
'--------------------------------------------------------------------

The simplest programming solution requires creation of a new OnOpen event on each form, with the following code for every mandatory field and every special field:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Form_Open

Me.MandatoryLabelName.ForeColor = conMandatoryLabelColor
Me.MandatoryFieldName.BorderColor = conMandatoryBorderColor
Me.SpecialFiledName.BackColor = conSpecialBackgroundColor

Exit_ Form_Open

Exit Sub

Err_Form_Open

MsgBox Err.Description
Resume Exit_Form_Open

End Sub ‘ Form_Open

Storing color values in a table

When I’m creating a general purpose application where colors are likely to change, I use a special table, called tabDefaultApplicationValues. In its simplest definition, this table includes only one row and many fields. This table includes three fields MandatoryLabelColor, MandatoryBorderColor, and SpecialBackgroundColor. I also need function getDefaultApplicationValue(ValueName).

With this method, the simple procedure Form_OnOpen needs a little rearranging:

Me.MandatoryLabelName.ForeColor = lngMandatoryLabelColor
Me.MandatoryFieldName.BorderColor = lngMandatoryBorderColor
Me.SpecialFiledName.BackColor = lngSpecialBackgroundColor

Public variables can be filled during the loading process of Main form or Switchboard with the  following code:

lngMandatoryLabelColor = getDefaultApplicationValue(“MandatoryLabelColor”)
lngMandatoryBorderColor = getDefaultApplicationValue(“MandatoryBorderColor”)
lngSpecialBackgroundColor= getDefaultApplicationValue(“SpecialBackgroundColor”)

Here is a simple function you can use to retrieve the color values from the table:

'------------------------------------------------------------
' Element: Function, 2010
' Purpose: Get value from table tabDefaultApplicatonValues.
'
' Input: Default value field name (String)
' Output: Value (String)
'
' See also: tabDefaultApplicationValues
' Avtor: Stefan Masic, january 2010
' Remark:
' Changes:
'------------------------------------------------------------

Public Function getDefaultApplicationValue(strDefaultValueName As String) As String

On Error GoTo Err_getDefaultApplicationValue

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strDefaultValue As String
Dim strSqlStmt As String

' Check default value name.
If IsNull(strDefaultValueName) Then
  strDefaultValue = ""
Else
  ' Define current DB.
  Set dbs = DBEngine.Workspaces(0).Databases(0)
  strSqlStmt = "SELECT " & strDefaultValueName & " FROM tabDefaultApplicationValues"
  Set rst = dbs.OpenRecordset(strSqlStmt)
  ' Get value if record exists (rst.RecordCount>0).
  If rst.RecordCount > 0 Then
    ' Check NULL.
    If Not IsNull(rst.Fields(0).Value) Then
      strDefaultValue = rst.Fields(0).Value
    Else
      strDefaultValue = ""
    End If
  Else
    strDefaultValue = ""
  End If
  ' Free rst in dbs.
  rst.Close
  dbs.Close
End If ' IsNull

getDefaultApplicationValue = strDefaultValue

Exit_getDefaultApplicationValue:
Exit Function
Err_getDefaultApplicationValue:
MsgBox Err.Description & " (Error no.: " & Err.Number & ")"
Resume Exit_getDefaultApplicationValue

End Function ' getDefaultApplicationValue

If the solution requires personalization of default application values, or if the solution will be used by many companies, then this table will have two or more rows of default values for every configuration, and the function will have two parameters. Here are two examples:

getDefaultApplicationValue(UserId, ValueName).
getDefaultApplicationValue(CompanyId, ValueName).

The first described approach does not require any programming, and as such it is easier to manage the application over time and with new versions of Access. I recommend this approach.

May 31 2010

Tips to migrate legacy databases to SharePoint and the Web

Category: Office 2007 professional — admin @ 3:51 am

Today’s guest writer is Ela Yildizer Genc, a developer on the team who recently worked on Web Services, Backstage and Ribbon. 

Office 2007 professionaland Office 2007 ultimate are so powerfull.I have been recently migrated some legacy applications to the Web. I expect many Access users will want to migrate their existing applications to the Web, I thought it would be helpful share my experience and give you some useful resources.

There are some schema restrictions on web databases and if you want to bring your existing application to the Web, first you have to make your application web compatible. 

IS Windows 7 Professional better than Windows 7 Ultimate ? Here are the steps you can follow before you can publish your application to Access Services:

  1. Save your application in default database format (.accdb)
  2. Run Compatibility Checker to see if your application’s schema is web compatible.
  3. Open the Web Compatibility Issues Table. The errors in table are explanatory with which table, which control you have and there is also a link for each error where you can also get info about what to do.
  4. Fix the errors in Web Compatibility Issues Table and run Compatibility Checker until your application is Web compatible.

Now your data is web compatible and you can publish it to Access Services. 

QuickBooks 2010 is so Helpful! However, you can’t open your objects (Forms/Reports/Reports/Modules) other than tables in the browser since they are still client objects. Existing forms and reports will publish to the server and round trip to other Access clients that open the application but they will not render in the browser. You have to create new web objects if you want to open these objects in the browser. You will also need to set the Web Display Form which will be displayed in the first place when you open your database in the browser. To set your Web Display form go to the Backstage | Options | Current Database page and select a form from the Web Display Form dropdown menu.  Typically, you will want this form to be the main navigation form—here is a blog post that will get you started creating intuitive navigation UI. Dreamweaver CS4 is very easy-to-use!

Tips and Tricks

There is a pretty good white paper that talks about integration of Access Applications with Access Services. Before starting your schema changes, take a look at the “Migrating Legacy Data to Web Tables” section to see a list of common compatibility issues and their solutions. Knowing the schema changes you are expecting will definitely help you make right design changes in your application. Acrobat 9 is so useful!

Errors in Web Compatibility Issues Table are self-explanatory. While fixing an error, pay attention to element type, element name, control type, control name, property name in in Web Compatibility Issues Table. This will tell you where exactly your error is and the error description will tell you what is expected/incompatible with the web. There is also a link for each error where you can also get info about the solution. You can also take a look at Access 2010 Web compatibility checker to get more info about Web Compatibility Checker and more tricks about resolving issues.

Modules are published to the server and won’t affect the migration of the application to the web. You will need to recompile after saving your application in .accdb format.

Most common errors I faced and solutions:

  1. Composite indexes are not compatible with web databases. If you have a database with composite indexes, you have to create a new primary index with AutoNumber type and create a data macro to preserve the uniqueness of your fields making up the composite key. Here is blog post about how you can write data macros to work around the issue.
  2. Relationships that are not associated with a Web-compatible lookup are incompatible with the Web. You have to delete all relationships not based on lookups and create them using lookups.
  3. Some Number/Currency/Date&Time formats are not supported in Access Services and below are the supported formats for these fields.
  •  
    • If you have a Number field, make sure it is formatted as General Number/Standard/Percent.
    • If you have a Currency field, make sure it is formatted as Currency/Euro.
    • If you have a Date/Time field, make sure it is formatted as General Date/Short Date.

Depending on the complexity of your database, it is possible in a couple of hours you can make your database web compatible and start creating your Web forms and reports.

Enjoy!

May 31 2010

UtterAccess extreme makeover with Access Wiki

Category: Office 2007 professional — admin @ 3:48 am

The extreme makeover of UtterAccess is underway! Access MVP and owner of UtterAccess, Gord Hubbell, has released a significant upgrade that improves the social tools available to the Access community. 

Office 2007 professionaland Office 2007 ultimate are so powerfull.UtterAccess is known for its friendly community that strives to give people great answers to questions. The site offers a forum for many Access experts that openly help people do amazing things. The community of quality people have even better help tools. 

IS Windows 7 Professional better than Windows 7 Ultimate ?  I’m personally excited about the long-term impact the site will continue to have in helping people create great databases, excel in their career, and build lasting friendships.

There are two parts to this upgrade—a new wiki and upgraded discussion forums. QuickBooks 2010 is so Helpful!

Access Wiki

For years, Access experts have created their own sites full of useful knowledge about how to maximize the benefit of Access. These sites are a significant piece of the overall value proposition of the Access product. If you build your app on Access, there are online resources and a community of experts to help get you realize your ideas. 

Dreamweaver CS4 is very easy-to-use!  A few favorite sites include Allen Browne’s Tips, www.accessjunkie.com, and http://www.mvps.org/access. Now there is a new site for free how-to content about Access.

image 

I expect the Access Wiki to grow as one of the best free source of Access learning materials.

What’s a wiki? A good read can be had here: http://en.wikipedia.org/wiki/Wiki. UtterAccess uses MediaWiki, the same framework as Wikipedia.

For me, a wiki is a living topic that (unlike static personal web sites) is always undergoing continuous improvement. Mistakes are quickly corrected and unclear sentences or topics reworked. If you have a more clear way of making a point—by all means change it. If a useful link provides better resources—add it. Making changes to the topics is incredibly easy. You just need to sign-in start editing. Here is a get started overview and a syntax quick reference once you have moved beyond basics.

Updates to the Wiki have already started to flow in. Here are a few topics that have already been created: Error handling, Relationships, Login security,  Record key, and Display related data. Take a few minutes and give it a read and update topics that leaves out an important point or correct grammar mistakes. As one of the Five pillars from Wikipedia says:

“Be bold in updating articles and do not worry about making mistakes. Your efforts do not need to be perfect; because prior versions are saved, no damage you do is irreparable.”

Contribute your insights to the Access Wiki today!

Discussion Forum Upgrade

For years, Gord has tuned and customized the forum software the drives UtterAccess. It has served the community well. When we talked about integrating MediaWiki, it was clear the biggest challenge was to integrate the two frameworks authentication systems and user experience. To bolt together the forum and MediaWiki was a significant rewrite. Gord discovered IP.Board and IPBWiki (by Peter De Decker in Belgium) provides a seamless user experience and shared authentication system between the forum and wiki. He describes the forum upgrade as going from Cessna to a Boeing 747.

It was an uneventful take off last night but expect a few turbulences as crew and users get use to the new cabin. 

Photoshop CS4 is so magic!  I expect members will quickly get use to the new user experience and enjoy discovering all the new features. Here are some of the personal profile improvements users will find:

  • Friends list – manage your friends for easier to send private messages, to see if your friends are currently online and display your friends on your profile
  • Profile comments – allow visitors to leave comments on your profile
  • Recent visitors – recent visitors are shown on your profile
  • Your content – show your recent posts / blog entries / gallery uploads on your profile
  • Personal statement – add a bio for others to read
  • Incredibly easy access to edit your location, age and IM details

image

Thanks Gord and Community Contributors

I can’t say enough how much we appreciate the work Gord and the UtterAccess forum administrators contribute to the community. The new wiki truly is (and will be) a collaborative contribution from many people.

It is amazes me how many people give so freely of their time and resources. UtterAccess isn’t the only place people turn for answers. There are other communities such as Access World Forums, Newsgroups, LinkedIn, Tek-Tips, stackoverflow, and Experts Exchange. The service provided by these communities is also appreciated by the millions. Thanks for all you do!

May 31 2010

Access 2010 BrowseTo DoCmd and macro action

Category: Office 2007 professional — admin @ 3:47 am

Today’s guest writer is Steve Greenberg, a Lead Program Manager on the team. Steve is responsible for form rendering and the new templates in Access 2010.  

Ms office 2007 is so Charismatic.Last week the team was privileged to host our MVPs at a conference here in Redmond. During our in-depth talks about Access 2010, some of these MVPs asked me about the new DoCmd and macro action called BrowseTo that we’ve introduced with Access 2010. I’ve written this post to demystify this strange new action.

In short, BrowseTo allows you to create web-style navigation between forms and reports.

We’ve already talked about the navigation control. The navigation control allows you to easily create tabbed navigation hosting sub-forms and sub-reports.

BrowseTo operates similarly but is much more flexible. BrowseTo is the navigation control’s big sister. It allows you to roll your own navigation or build custom UI on top of an existing navigation control. 

Office 2007 professionaland Office 2007 ultimate are so powerfull.Let’s show a simple example of BrowseTo in action.

Wizard

image

This wizard asks the user to step through a series of forms. The “Next” button disposes “Step 1” form and loads “Step 2” form in its place. IS Windows 7 Professional better than Windows 7 Ultimate ?   Here’s what the OnClick event of the Next button looks like:

clip_image001

Let’s take a look at each of the non-NULL parameters.

  • Object Type: “Form”. Alternatively, this could be “Report” if we were looking to load a report.
  • Object Name: “Step2”. This is the name of the form or report to load.
  • Data Mode: “Edit”. “Add” and “Read Only” are the other options. These are the same options available for the familiar OpenForm action.

BrowseTo operates by browsing from Step1 to Step2. It works just like a hyperlink.

The Address Book

Now let’s look at a more advanced example. Lois, one of the PMs on the team, designed the 2010 Contacts template around a main Address Book view of contacts. It lists all the contacts down the left side of the page. With one click, the contact’s details are presented in business card in the center of the page. Pretty cool!

image

How is this done? The list of contacts on the left side is a continuous form. The textbox that contains the name of the contact has an OnClick event. The OnClick event loads the Name Card form and filters to the appropriate contact.

clip_image004

Let’s take a look at the rest of BrowseTo’s parameters introduced by this example.

  • Where Condition: “[ID]=[TempVars]![tmpID]”. This is the WHERE condition to apply to the form or report we load.
  • Page: “”. Although not relevant for this example, BrowseTo can be used to drillthrough to a particular page of data in a continuous form.
  • Path to Subform Control: “Main.NavigationSubform>ContactCard.DS”.

The Path parameter is the tricky part. There are three techniques for using the Path parameter. Let me describe them before returning to the example.

  1. Just like a hyperlink: If BrowseTo is called from a “top-level form” (a form that is NOT within another subform) and if the Path parameter is left NULL, then BrowseTo operates just like a hyperlink. The current form is disposed, and the form specified in BrowseTo is loaded in its place. This is the technique used in the wizard example.
  2. Change my parent subform control’s SourceObject: If BrowseTo is called from a form that is hosted within a subform control and if the Path parameter is left NULL, then BrowseTo leaves the parent form in place and changes only the contents of the current subform control. The current form or report is disposed, and the form specified in BrowseTo is loaded in its place.
  3. Change the SourceObject of an arbitrary subform control: The Path parameter allows you to control the SourceObject of any subform that is currently visible in the application.

We utilize this third technique in Contacts. Path is set to “Main.NavigationSubform>ContactCard.DS” This means that Access is instructed to load the NameCard form inside the DS subform control of the ContactCard form. ContactCard, in turn, is loaded inside the NavigationSubform subform control of the Main form.

If the “>” character seems unusual, that’s because it has been introduced specifically for the Path parameter of BrowseTo. Its meaning is this: The form to the right of the “>” character is loaded into the subform control specified to its left.

When you’re developing a database using BrowseTo, you may occasionally run into this error message:

clip_image005

Here are some tips for resolving this error message:

  • The path must specify a series of Form.SubFormControl pairs. Each subform control specified must be a control in the form that precedes it in the path.
  • The pairs must be separated by the “>” character.
  • The first form in the path must be the form that is currently loaded directly in the Access window (or browser window if the application is running on the web.) This means that the Path parameter only allows you to change the contents of a currently-visible subform control.

If you’re using Access 2010, experiment with BrowseTo and give us feedback.

Enjoy!

May 31 2010

The secret decoder ring for Access Services URLs

Category: Office 2007 professional — admin @ 3:45 am

Today’s guest writer is Steve Greenberg, a Lead Program Manager on the team. Steve is responsible for form rendering and the new templates in Access 2010.  

Ms office 2007 are so Charismatic.One of the recurring topics on this blog has been the methods used to navigate a database. A few months ago, we introduced you to the Access 2010 navigation control. We also just recently introduced the BrowseTo macro action.

With Access Services, there is another method of navigating your application, and the good news is that you don’t have to do any work to get it. I’m talking of course, about the browser’s BACK button.

Since the early days of the web, the BACK button, and its less-used sibling, the FORWARD button, have been the most common method users have for navigating websites. However, they present challenges to web application developers. What does it mean to “go back” in an application? Unlike web-sites, applications are not inherently architected with a breadcrumb trail that keeps track of previous states. 

 

Office Professional 2007 and Office Ultimate 2007 are my favorite.

Here are some additional questions that pop-up once you start talking about running an application in a web browser:

  • What happens when you push the refresh button? Have you ever been frustrated by being sent to the homepage of a website and having to start your work over again?
  • Can you bookmark data you often use to easily return to it later?
  • Can you copy the contents of an address bar, send it to a friend, and have them quickly see the same information you’re seeing?

The answer to all these questions depends on whether the action taken in the web application updates the URL seen in the address bar. When the URL is updated, a new breadcrumb is added to the trail, and the new state of the application can be reconstructed even if the page is refreshed, accessed in the future or pulled up on a different machine.

Certain actions in Access Services update the URL, and certain actions do not. Certain information is reflected in the URL, and certain information is not. Knowing the details can help you optimize applications for the web from the outset.

Before we reveal the secrets of the Access Services URL decoder ring, there’s one concept to explain: At any point in time when using an Access Services application, there is one special form or report – the “key object”. Extensive information about the key object’s data and state is maintained in the URL. Because of space limitations in the URL, little information is maintained about any other currently-open objects. You might guess that the key object is the form or report that is loaded directly in the browser window. 

IS Windows 7 Professional better than Windows 7 Ultimate ? But many Access Services applications use a navigation control. For these applications, the form directly in the browser window has no data.

Windows 7 is also my love! As a result, if there are a series of forms and sub-forms, the key object is set to be the top-level form or report that is bound to data. With this information in hand, let’s look at a sample URL:

URL Example Explanation
http://<server>/<site>/default.aspx# http://myserver/mysite/default.aspx# You specify the server and site name when you publish.
Type=<Object Type>& Type=Form& “Form” or “Report”. The type of the key object.
Name=<Object Name>& Name=MyCF& The name of the key object.
Path=<Path to Sub Form Control>& Path=Navigation1.NavigationSubform%3ENavigation2.NavigationSubform& This is the path of forms and subforms that parent the key object. See the BrowseTo post for more information on Path.
Where=<Where Clause>& Where=%3DID%3C%3E2& The WHERE clause applied to the key object.
Page=<Page Number>& Page=2& The current page if the key object is a continuous form or current record number in a single record form.

If the current record is the New row, then it is set to *.
DataMode=<DataMode> DataMode=Edit The data mode of the key object.

 

Note that because parameters are URL-encoded, they look funny. (For example, instead of [, you see %3D).

Finally, here’s a list of actions that update the URL:

  • Using the BrowseTo or OpenForm to change the key object, its WHERE clause, page or data mode.  QuickBooks 2010 is so Helpful!
  • Using the built-in First, Previous, Next, Last and New buttons to change the current record or page.
  • Using GoToRecord to change the current record or page.
  • Using SetFilter to change the WHERE clause for the key object. Acrobat 9 is so useful!

And here’s a list of actions that do not update the URL:

  • Using OpenForm to open a modal pop-up dialog.
  • Any actions taken within a modal pop-up dialog.
  • Using SetOrderBy to change the order by.
  • Using the datasheet column headers to sort or filter.
  • Scrolling in the datasheet.
  • Changing the current tab in a tab control. 

Dreamweaver CS4 is very easy-to-use! With that information in hand, you can choose to create applications that interact predictably with the browser’s back, forward and refresh buttons.

 

 

Enjoy!

May 31 2010

Power Tips: become a guest blogger!

Category: Microsoft Office 2007 — admin @ 3:43 am

The Power Tips feature of this blog has been underway for about a year now, and we’d like to take this opportunity to say a big thank you to those of you who have sent in tips. Office 2007 professional and Office 2007 Ultimate are so powerfull. However… lately our queue of posts has emptied out, so especially if you’ve never done it before, now is the time to become a guest blogger and submit your tip. We depend on the blog community to keep the feature going.

A Power Tip can be a code sample, some SQL, an expression, a series of property settings, or some other Access trick that you have learned along the way, and that you think might be useful to others. 

Windows 7 is also my love!Now that many of you have Access 2010 in your hands, you can be among the first to submit Power Tips for some of the new features!

Power Tips are compiled and posted by Chris Downs and Mike Stowe, Access writers for Office Online and MSDN, respectively. 

QuickBooks 2010 is so Helpful! Just send your tips to us at accpower@microsoft.com. We’ll do some minimal editing and post it to the blog, and of course we’ll give you whatever attribution you prefer.

Photoshop CS4 is so magic!  As always, screen shots and video demos are welcome and encouraged. Thanks again, and hope to hear from you soon!