Daily Access Tips

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.