News Item From: Microsoft Access Team Blog | January 15, 2010

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 ... Read more at Microsoft Access Team Blog –

Great list of helpful websites are contained in this well deserved recognition.

We all owe our gratitude to these fine professionals who supply so much help.

My thanks goes out to all who made this list and many more who have shared their knowledge on their Access sites.

I was involved with the Microsoft Access 2010 first non-public beta.  A non-disclosure agreement prevented me from discussing details but now there is a public version available.  This should not be placed on a production machine and also not on a machine having earlier versions of Access you need to use.  This version has the ability to publish an Access application to the web using “Sharepoint Access Services”.  I have heard from various sources that any time from March to June 2010 will be the first release.

Below are several references* so you can find out more about what is being released.

• *New features of Access 2010 – http://www.microsoft.com/office/2010/en/access/default.aspx
  

• *Sharepoint 2010 and Access 2010. http://www.youtube.com/watch?v=Dq-tDuPfgZc
  

• *Microsoft Office Access 2010 http://www.youtube.com/watch?v=P8cheNlRlh4
  

• *Microsoft Access 2010 Macro Designer http://www.youtube.com/watch?v=6zvbK9×8rGw
  

• *Design navigation UI with Access 2010 http://www.youtube.com/watch?v=vkMzkhVfK6Y
  

• *Access 2010 Layouts http://www.youtube.com/watch?v=1wtgk2nosJE
  

• *Access 2010 data macros http://www.youtube.com/watch?v=J3G0ivjpy9k
  

• *Microsoft office 2010- Access http://www.youtube.com/watch?v=qUsrAJ6B_D4

If you are planning to use the new Access Web Forms, you will want to learn the new macros that will be needed.  Any forms that will run in an internet browser, after publishing to Sharepoint, will not use VBA code and will only use Macros. These forms and reports will also require the form and report “Layouts” that you now find in Access 2007.

The new data macros and lookups will allow much of your business logic to reside on the back end data side where you can make changes inside a single central location.

If you simply want your end users to work in the Access program but have the data reside on a Sharepoint server, you may use all the forms and reports and VBA code you have developed, but still have the great advantage of using the internet as your “extremely wide area network” for sharing your Microsoft Access database data.  In this case, just publishing your Access tables to an internet Sharepoint server will do the trick.

Microsoft Online Services:  http://www.microsoft.com/online/support.mspx will have this hosting available next year.  Currently they are only charging about $6.00 a month for single person licenses for their other Sharepoint services now currently available.  We can only hope it will be that inexpensive when they offer “Access Sharepoint Services”.

Bob Heifler

This Microsoft Access security log on solution will work with all versions of Access

Four steps to create this solution:

Step One
Create a new table called “tblUsers”
Add these fields:
Field Name                   Data Type

UserName                    Text
Password                     Text
Administrator_Rights    Yes/No

Make this a hidden table and do not show hidden table in your Access program (See the video below on how to do this.)
http://www.youtube.com/watch?v=kJjQaAWHmG8

Step Two

Create a new module and paste this code into it:

Public user As Variant
Public pw As Variant

Public Function getuser()
getuser = user
End Function

Public Function getpw()
getpw = pw
End Function

Step Three
Create a new query based on this SQL statement:

SELECT tblUsers.UserName, tblUsers.Password, tblUsers.Administrator_Rights
FROM tblUsers
WHERE (((tblUsers.UserName)=getUser()) AND ((tblUsers.Password)=getPW()));

The above is done by going to the SQL View of the query and pasting in the above SQL statement.

Name the query “qryRS”

Step Four

Create a form “frmLogOn”

Add a text box control and call it “txtUserName”
Place this code in it’s After Update event:
user = txtUserName

Add a text box control and call it “txtPassword”
Place this code in it’s After Update event:
pw = txtPassword

Add a button and place this code in it’s Click event:

On Error GoTo errline
If IsNull(Me.txtUserName) Then
MsgBox “Please enter a User Name.”, vbInformation, “Missing User Name”
Exit Sub
End If

If IsNull(Me.txtPassword) Then
MsgBox “Please enter a Password for the user name ” & Me.txtUserName & “.”, vbInformation, “Missing User Name”
Exit Sub
End If

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(“qryRS”)
With rs
If .EOF And .BOF Then
MsgBox “Your log on information was incorrect.  Try again.”, vbCritical, “Failed Log On”
GoTo exitline
Else
If !Administrator_Rights = False Then
DoCmd.OpenForm “frmMenu”
DoCmd.Close acForm, “frmLogOn”
GoTo exitline
End If
End If
End With

If chbxEditTable = -1 Then
DoCmd.OpenTable “tblUsers”, acViewNormal
DoCmd.Close acForm, “frmLogOn”
Else
DoCmd.OpenForm “frmMenu”
DoCmd.Close acForm, “frmLogOn”
End If

exitline:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

Exit Sub
errline:

Select Case Err.Number
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: Error Number: ” & Err.Number & “  ” & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”

GoTo exitline
End Select

A demononstration of this solution is here:
http://www.youtube.com/watch?v=0RwU1roBJ6I

Hide Table Video:
http://www.youtube.com/watch?v=kJjQaAWHmG8

from youtube
Log On Demo:
http://www.youtube.com/watch?v=0RwU1roBJ6I

from youtube

Bonus article from Smart Access

Even some experienced Access developers shy away from writing SQL directly. That’s a shame, because, unless you’re willing to write SQL, you can’t use subqueries, which are a powerful tool for solving some especially thorny data retrieval problems. In this article, Mike Gunderloy introduces subqueries and shows how you can use them in Access.

http://www.vb123.com/smart/go.asp?rfdby=myap

Peter Vogel discusses Null Fields

I recall having numerous problems with this area until I got some help.  All Access developers who are not sure of the potential NULL problems and their solutions should read this article. It will save you several headaches!

Nulls give most database developers a lot of trouble. One example is that you can’t compare Nulls to anything and another is that a numeric field with Null in it won’t be less than, equal to, or greater than any other number. Yes Null fields are a difficult concept but you need to understand them to be a database expert.

Find the full article here.

Enjoy the articles,

Bob Heifler

In this article, Doug Steele demonstrates a useful formula to use with date calculations.

If I’ve got a date, can I determine the date of the Monday for the week that date is in? For instance, I’ve got a date of 17 November 2004 (which is a Wednesday), and I want to identify that the Monday of that week is 15 November 2004.

Find the full article here.

There is  another good article for you from Cindy Meister at above link when you click on “Access Answers: Date Tricks” in the link above.

Sometimes, as an Access developer you need a more powerful reporting tool than Access’s internal reporting engine. Microsoft Word can give you the power that you need. Cindy Meister’s article covers the topics that every Access developer needs to know about Microsoft Word.

Enjoy the articles,

Bob Heifler

Time to get some reporting basics in another classic article by Microsoft Access Chris Weber and Frank Sutherland

If you think that reports are boring, Chris Weber and Frank Sutherland are going to show you just how wrong you can be. Topics discussed include Reports on separate pages, showing group headers more clearly, memos reporting issues, overlapping sub-reports and report performance. They also throw in a tip to solve a common problem and show how to improve report performance.

Full Article Reprint Here.

Enjoy the article!

Bob Heifler

List box magic is found in another classic article by Microsoft Access guru Doug Steele

In this article, Doug Steele looks at various ways of using the List Box control including Multi-Select , moving items between lists, using non table record sources and showing all tables in a database in a list box. Full Article Reprint Here.

List Box object has an ItemsSelected collection which provides a means to access data in the selected rows.  The code that Doug shows you will allow you to pull all the items that were selected on the list box.

Enjoy the article!

Bob Heifler

Article Summary of DATA MODELING FOR THE ACCESS NEWCOMER, PART 2 by Glenn Lloyd:

Last month, Glenn Lloyd started outlining his approach to data modeling. This month, he finishes his discussion of the rules that ensure that your database will actually work after you’ve built it.

He emphasizes “that good data modeling isn’t a ‘nice-to-have feature.’ It’s essential to success.”

Get your free full article reprint here under “Latest Link”:Full Article

Why not get hundreds of great Microsoft Access articles written by the leaders in this field.   All past issues of “Smart Access”, fully indexed for searching, are now available to you.

Since you are a Daily Access Tips / My Access Program subscriber, don’t forget to take your Coupon Code.

Enjoy!

Bob Heifler

Time to cover some basics.  Grab a drink and snack while you receive your first gold coin from the treasure chest of ‘Smart Access’ republished articles:

Article Summary of DATA MODELING FOR THE ACCESS NEWCOMER, PART 1 by Glenn Lloyd:

Thorough, thoughtful, and accurate data modeling should be the starting point of detailed database design.  But a surprising number of developers have little or no understanding of data modeling and shy away from what sounds like a non-profitable and time-consuming task.

Glenn Lloyd looks at the typical design pitfalls that trap Access beginners and shows the basic techniques that ensure success.

He discusses database design, where to start, relational tables  in relationship to application requirements, types of relationships, data modeling to represent the real world entities, and Normalization rules.

Get your free full article reprint here:  http://www.vb123.com/kb/200511_gldatamodeling.htm

Don’t forget to take your discount coupon (On the right side of screen).

Place your comments in a reply below:

Bob Heifler

Michael Haag offers a tip this week on filling in the city and state after entering a zip code on a data entry MS Access form. Data is stored in a ‘Zipcodes’ table that contains 3 states for the New York area with a Dlookup function to retrieve the city and State.

Michael states “I think one of the main advantages is: The information doesn’t have to be stored in the “Customer” table, so it doesn’t take up space.” for Microsoft Access display. I think the main advantages are speed of data entry and elimination of spelling errors during data entry. MS Access allows for a Dlookup fuction to bring back data from a table or query.

He places in the control source property of the ‘City’ text box this:

=Dlookup(“[city]“,”zipcodes”,”zipcodes= ‘” & [Forms]![frmcustomer]![zipcode] & ” ‘ “)

He places in the control source property of the ‘State’ text box this:

=Dlookup(“[State]“,”zipcodes”,”zipcodes= ‘” & [Forms]![frmcustomer]![zipcode] & ” ‘ “)

An alternate approach would use the ‘After Update’ method of the ‘Zipcode’ form control with this code:

Private Sub ZipCode_AfterUpdate()

If IsNull(Me.City) Then

Me.City = Dlookup(“[city]“, “zipcodes”, “zipcodes= ‘” & [ZipCode] & ” ‘ “)

End If

If IsNull(Me.State) Then

Me.State = Dlookup(“[State]“, “zipcodes”, “zipcodes= ‘” & [ZipCode] & ” ‘ “)

End If

End Sub

This second solution would have the City and State form controls bound to the Customer table or the code could be placed in the On Current Event of the form to handle unbound controls for the City and State Microsoft Access display of data.

Both approaches still require the maintenance of the Zipcode table. Perhaps an easy to maintain solution would involve changing the Zipcode form control to a combo box which is feed by a Group By query (See below) that is based on all prior Zip codes, Cities, and States entered in the Customer table (Not limited to list.) This has the advantage of no maintenance of a Zipcode table and speed of data entry, however it can potentially have two or more Zip, City, State combinations of misspelled City or State names.

The combo box SQL would be:

SELECT Customer.ZipCode, Customer.City, Customer.State

FROM Customer

GROUP BY Customer.ZipCode, Customer.City, Customer.State;

The after update for the combo box would be changed to:

Private Sub ZipCode_AfterUpdate()

If IsNull(Me.City) Then

Me.City = ZipCode.column(1)

End If

If IsNull(Me.State) Then

Me.State = ZipCode.column(2)

End If

End Sub

Try out one of these solutions and please enter your feedback in the comments of this blog posting (Below).

Thank you Michael for your tip and article suggestion.