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.

This article will offer a simple solution of how to let the person know what is needed from them on Microsoft Access data entry forms and where it is needed.

Last week I was asked for a way to give the person an on screen message while on Microsoft Access data entry forms without it requiring them to dismiss it by clicking the OK button on a message box. My reply was to have a label change it’s caption property from nothing to “The desired message…” then back to nothing using the form’s built in timer and some OnTimer event code. This article using some code similar to the last posted article.  It addresses two areas of Microsoft Access data entry forms assistance. The first is a visual clue as to where that skinny little cursor is blinking and second, what the user needs to know about the current form control that has cursor focus. The first area was addressed by changing the background color to a light shade of blue as the visual clue. The second area was addressed by having a custom message appear in a label at the top of the form. The messages are stored in the Tag property of each control that requires a message to be displayed by Microsoft Access. Data entry forms might have a message that simply lets the data entry person know that this is a required field or it might supply more about the type of information that should be entered in this field. This message is turned off when the user move to the next control on the form.

Below is a short video demonstrating how this works.

This green colored code below is all you need at the form level. It is placed in the form’s On Open Event.

Private Sub Form_Open(Cancel As Integer)
Me.SetFocus
Call SetEventHandlers(Me)
End Sub

Each form control has a tag property where you can type in the message you want to appear in the ‘lblControlMsg’ label that you will need to place on your form. If you need help finding the tag property see this video.

Place code below in a code module:

Public Sub SetEventHandlers(frm As Form)

‘Runing this routine from the OnOpen event of any form using
‘Call SetValidatorEventHandlers(Me)
‘will replace any OnGotFocus and OnLostFocus event code
‘with a call to the Highlighter function.
On Error GoTo errline
Dim ctl As Control
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
If ctl.Visible = True Then
ctl.OnGotFocus = “=Highlighter(true)”
ctl.OnLostFocus = “=Highlighter(false)”
End If
Case Else
End Select
Next
exitline:
Exit Sub
errline:
Select Case Err.Number
Case Else
MsgBox “Error ” & Err.Number & Chr(10) & Err.Description
GoTo exitline
End Select

End Sub

Public Function Highlighter(TurnOn As Boolean)

On Error GoTo errline
Select Case Screen.ActiveControl.ControlType
Case acTextBox, acComboBox, acListBox
If TurnOn Then
Screen.ActiveControl.BackColor = 16773857
‘requires that the name of the message control be labeled ‘lblControlMsg’
Screen.ActiveForm![lblControlMsg].Caption = Screen.ActiveControl.Tag
Else
Screen.ActiveControl.BackColor = -2147483643
Screen.ActiveForm![lblControlMsg].Caption = “”
End If
Case Else
End Select
exitline:
Exit Function
errline:
Select Case Err.Number
‘Form window not visible yet to see Screen.ActiveControl
‘(Only needed when setting event handlers by code)
Case 2475
Resume exitline
Case Else
MsgBox “Error ” & Err.Number & Chr(10) & Err.Description
GoTo exitline
Resume Next
End Select
End Function

Let me know how this worked for you by entering your comments and suggestions.

How to create Microsoft Access email is a common request. I received the following question a while back and it presented a great opportunity to show how useful Microsoft Access recordsets can be. Microsoft Access recordsets allow you to move through a set of records and use the data contained in them to perform such tasks as emailing, running reports or queries, analyzing data, etc.

The request was: “At the moment I am building a database for … I have a table holding information of the product, user, email address and balance field. I would like to create an automatic email alert to any user who’s balance is under £10 at anytime. I am guessing the solution is probably fairly simple but am really struggling. I have no idea how to do it so any help would be greatly appreciated.”

Here is the solution that I offered:

You can send the email by various methods but to use the built in Access email command that does not require Microsoft Outlook or access to an SMTP mail server just use the DoCmd.SendObject acSendNoObject (default). The acSendNoObject argument allows you to just send email message without sending an Access object such as a Report or Query object.

Just so there can be a date stamp of when the email was sent, first add a new date field called Email_Sent_Date to your table. Next create a query called ‘Email_Reminder_RecordSet’, that has the name and email address of users having a balance under 10 pounds and optionally who have not received an email in the past month or what ever time seems prudent. You will use the new field ‘Email_Sent_Date’ for this purpose and this will allow the following code to enter the date that the email was sent. The code on the on click event of your button will open a recordset on this query and loop through it’s records to send the emails. Here is the code:

‘This code is using DAO (Data Access Objects) for recordset control so be sure DAO is showing under Tools menu Under References in your code module as a ‘referenced code library or select DAO 3.6 to add it if it is not showing already.

If you copy and paste code from this article, Please replace all double quotes that look curly with standard straight looking double quotes.  These “Web Page Smart Quotes”, that have a curved look to them, will not compile in your code editor unless they are replaced with your standard keyboard double quotes.

Here is the code to paste into the OnClick event of the button you will use to trigger this emailing code:

Dim rs as dao.recordset
set rs = currentdb.openrecordset(“Email_Reminder_RecordSet”)
with rs
if .eof and .bof then ‘IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE (No Records found for this query.)
Msgbox “ No emails will be sent because there are no records from the query ‘Email_Reminder_RecordSet’ “
else
do until .eof ‘DO UNTIL END OF RECORDSET FILE

‘THE NEXT LINE SENDS THE EMAIL MESSAGE
DoCmd.SendObject acSendNoObject, , , ![Email Address Field], , , “Low Balance Alert”, “Hello ” & ![First Name Field] & _
“, ” & Chr(10) & “You ballance is under 10 pounds. Please…”, False

.edit
![Email_Sent_Date] = now()
.update
.movenext ‘THIS MOVES TO THE NEXT RECORD IN THE RECORDSET
loop

End If
end with

‘CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

Please let me know how this works for you by leaving a reply / comment below.

Data validation checks that data are valid, sensible, and reasonable before they are processed. Microsoft Access validation can involve a time consuming choir if one had to do it for each control or ‘access field’, on each of your Access forms. Several have authored various approaches to automate Access validation for you. No single approach will work for all situations. Your form might allow a user to move off the record before Microsoft Access validation code is triggered. If you try to prevent the user from doing so, you will need to create additional design changes and add more complexity to your form.

I chose to take a conservative approach to Access validation, which works for most situations, but with a few more lines of code, will even prevent the user from leaving an unvalidated record no mater where he mouses to.

The user is notified if a form control failed validation, told why it failed, the control is highlighted briefly, and cursor focus is moved back to the failing control. Oh yes, it doesn’t try to validate form controls that come later, in the tab order, than where the user currently is. A single line of programming code is used on the OnOpen event of your form for this Microsoft Access validation of form data. The user can be prevented from navigating off the form record, adding a new record, or closing the form if validation fails, this requires an additional line of code on the appropriate form close, add new record and navigation buttons.

An Access developer just needs to enter a few characters into the ‘TAG’ property of a form’s control. The ‘TAG’ control property stores extra information about a control. The videos below will show you how to find this property in the ‘Property Sheet’ of the control you want to validate and will demonstrate the Access validation of a form using the code found from a link at the end of this article which moves you into the article containing the full programming code. There is no programming involved unless you wish to expand on the supplied code’s functionality.

Access Form Data Validation Demonstration Video:

from youtube

http://www.youtube.com/watch?v=otCMjARPiJY

You may get your example demostration file containing all code for Access 97, 2000, 2002, 2003, 2007 – Get File #51

This is where you will find all the Microsoft Access validation code in next article:

http://dailyaccesstips.com/code-for-microsoft-access-validation-of-data/

How to locate the Access Form Control ‘Tag’ Property for Access 97, 2000, 2002XP, 2003 video:

from youtube
http://www.youtube.com/watch?v=16HVabt2gM4

How to locate the Access Form Control ‘Tag’ Property for 2007 video:

from youtube
http://www.youtube.com/watch?v=hViGDmDU3P4

The following code subroutine called ‘SetValidatorEventHandlers’ is place in the OnOpen Event of every form you want Microsoft Access validation of form data. It will add the call to the Validator function from OnGotFocus Events of all visible text box, combo boxes, and listboxes on your form. If you already are planning to place other code in the OnGotFocus Events of these controls on your form, then you will want to reference the custom validation function call ‘Validator’ in the OnGotFocus subroutine for each control that you will want to trigger this code.

Place in the ‘Tag’ property of each control you want to validate the following symbols and characters:

• *n – The form control may not be left empty.
• *d – The control must contain a valid date.
• *+ – The control must have an amount greater than zero.
• *@ -The control must be a valid email address.

Other types of validation may be added by you as needed. You may may combine these pairs of characters if needed.

If you do not wish to refer to the form’s controls actual name in your validation message to the user, you may specify a preferred alternative name by adding this to the end of the tag property.
~My Preferred Control Name
Replace ‘My Preferred Control Name’ with the control name you want the user to see.

Here’s the code:

Public Sub SetValidatorEventHandlers(frm As Form)

‘Runing this routine from the OnOpen event of any form

‘using Call SetValidatorEventHandlers(Me)
‘will replace any OnGotFocus event code with a call
‘to the Validator function.
Public Sub SetValidatorEventHandlers(frm As Form)
‘Runing this routine from the OnOpen event of any form
‘using Call SetValidatorEventHandlers(Me)
‘will replace any OnGotFocus event code with a call
‘to the Validator function.
On Error GoTo errline
Dim ctl As Control, tagstr As String
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
If ctl.Visible = True Then
ctl.OnGotFocus = “=Validator()”
End If
Case acCommandButton
If ctl.Visible = True And Len(ctl.Tag) > 0 Then
Select Case ctl.Tag
Case “*Close”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*Close” & Chr(34) & “)”
Case “*First”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*First” & Chr(34) & “)”
Case “*Previous”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*Previous” & Chr(34) & “)”
Case “*Next”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*Next” & Chr(34) & “)”
Case “*Last”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*Last” & Chr(34) & “)”
Case “*New”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*New” & Chr(34) & “)”
Case Else
End Select
End If

Case Else
End Select
Next
exitline:
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: ” & Chr(10) & “Error Number: ” & Err.Number & Chr(10) & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”
GoTo exitline
Resume Next
End Select
End Sub

Public Function CommandButtonCodeActiveForm(CommandTagAction as String)

On Error GoTo errline
If Validator() = True Then
Select Case CommandTagAction
Case “*Close”
DoCmd.Close acForm, Screen.ActiveForm.Name
Case “*First”
DoCmd.GoToRecord , , acFirst
Case “*Previous”
DoCmd.GoToRecord , , acPrevious
Case “*Next”
DoCmd.GoToRecord , , acNext
Case “*Last”
DoCmd.GoToRecord , , acLast
Case “*New”
DoCmd.GoToRecord , , acNewRec
Case Else
End Select
End If
exitline:
Exit Function
errline:
Select Case Err.Number
Case 2105 ‘Impossible navigation attempt
Resume Next
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: ” _
& Chr(10) & “Error Number: ” & Err.Number & Chr(10) & Err.Description, vbCritical, _
“Please write this error down and note what you were doing at the time.”
GoTo exitline
End Select
End Function

Public Function Validator() As Boolean

On Error GoTo errline
Dim TabOrderedControls As New Collection
Dim ControlNames() As String
Dim i As Long
Dim ctl As Control
Dim CurrentControlTabIndex As Long
Dim strFailedCtlName As String
Dim bolFailedValidation As Boolean
Dim strErrorMsg As String
‘Is Form ready to be validated by this microsoft access validation code
If Screen.ActiveForm.NewRecord And Screen.ActiveForm.Dirty = False Then
Exit Function
End If
ReDim ControlNames(Screen.ActiveForm.Controls.Count)
‘Save any pending edits
If Screen.ActiveForm.Dirty Then
Screen.ActiveForm.Dirty = False
End If
‘Where are we in the tab order

‘If pressing a command button, consider we are to validate all tab order controls (Not just ones earlier than tab index of the command button.)
If Screen.ActiveControl.ControlType = acCommandButton Then
CurrentControlTabIndex = 999
Else
CurrentControlTabIndex = Screen.ActiveControl.TabIndex
End If

For Each ctl In Screen.ActiveForm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
‘Set yellow backround to white, if previously set to yellow
ctl.BackColor = -2147483643
‘Populate the ‘ControlNames’ array by control tab order
If ctl.TabIndex < CurrentControlTabIndex Then
ControlNames(ctl.TabIndex) = ctl.Name
End If
Case Else
’skip
End Select
Next
‘Populate collection ‘TabOrderedControls’ using ControlNames array.
For i = 0 To UBound(ControlNames)
If ControlNames(i) <> “” Then
TabOrderedControls.Add Screen.ActiveForm.Controls(ControlNames(i))
End If
Next i
‘Start validation
For Each ctl In TabOrderedControls
”Test for null (empty) on form control
If InStr(1, ctl.Tag, “*n”, 1) > 0 Then
If IsNull(ctl.Value) Then
strErrorMsg = ” needs to be filled in.”
bolFailedValidation = True
GoTo ValidatorFalure
End If
End If
‘Test for a valid date
If InStr(1, ctl.Tag, “*d”, 1) > 0 Then
If Not IsDate(ctl.Value) Then
strErrorMsg = ” must contain a valid date.”
bolFailedValidation = True
GoTo ValidatorFalure
End If
End If
‘Test for a value greater than zero
If InStr(1, ctl.Tag, “*+”, 1) > 0 Then
If Not (ctl.Value) > 0 Then
strErrorMsg = ” amount must be greater than zero.”
bolFailedValidation = True
GoTo ValidatorFalure
End If
End If
‘Test for a valid email address symbol
If InStr(1, ctl.Tag, “*@”, 1) > 0 Then
If Not InStr(1, ctl.Value, “@”, 1) > 0 Then
strErrorMsg = ” amount must be greater than zero.”
bolFailedValidation = True
GoTo ValidatorFalure
End If
End If
‘You may add additional validation here:
ValidatorFalure:
If bolFailedValidation = True Then
ctl.BackColor = 65535 ‘yellow
‘~ The tilde means there is an alternative preferred control name
‘After any tilde ~ there should be no other validation * asterisk symbols
If Not InStr(1, ctl.Tag, “~”, 0) = 0 Then
strFailedCtlName = Trim(Mid(ctl.Tag, InStr(1, ctl.Tag, “~”, vbBinaryCompare) + 1)) ‘Retreive prefered control name if different than real control name.
End If
If Len(strFailedCtlName) = 0 Then
MsgBox “The ” & ctl.Name & strErrorMsg, vbInformation, ctl.Name & ” missing value…”
Else
MsgBox “The ” & strFailedCtlName & strErrorMsg, vbInformation, strFailedCtlName & ” missing value…”
End If
ctl.SetFocus
If ctl.ControlType = acComboBox Then ‘If combo box, drop down the pick list.
ctl.Dropdown
End If
Validator = False
Exit Function
End If
Next ctl
Validator = True
exitline:
If Not TabOrderedControls Is Nothing Then
Set TabOrderedControls = Nothing
End If
Exit Function
errline:
Select Case Err.Number
‘Form window not visible yet to see Screen.ActiveControl
‘(Only needed when setting event handlers by code
‘using the ‘SetValidatorEventHandlers’ function.)
Case 2475
Resume exitline
Case Else
MsgBox “There was an error in the program. Please notify database administrator of the following error: ” _
& Chr(10) & “Error Number: ” & Err.Number & Chr(10) & Err.Description, vbCritical, _
“Please write this error down and note what you were doing at the time.”
GoTo exitline
End Select
End Function

Here is the demonstration video tutorial:

from youtube

First let’s start with the definition of a split MS Access Database:

A Microsoft Database that is split into a front-end database that contains the application (queries, forms, reports, macros, and modules) that are linked to MS Access tables stored in a back end shared database file containing only the data tables.

You will split MS Access Database when moving an application to a multi-user environment rather that have everyone using a single file on a shared network drive.  Doing otherwise puts your data at risk of corruption.  However, as you will find out in the next article, even a single user database will benefit from being split.

When you split a database, creating a front end and a back end, the back end Microsoft Database file is stored in a shared folder on a network drive and you link Microsoft Access data to MS Access front-end file. The front-end database file is distributed to each user’s desktop.  You would link Microsoft Access front-end to MS Access back-end, just prior to distribution. Using this design, each user has a copy of Microsoft Database program installed on their workstation machine along with their front-end application database file. Each user interacts with the data by using a local copy of the front-end Microsoft Database file. This reduces network traffic since the front-end does not need to be sent over the network path for each use, and allows the front-end database to contain settings or temporary data specific to the individual user. This split database design also allows development of the application, the front-end, to be totally independent of the data.  This allows for front-end to be easily replaced without affecting the data in the back end database. You will at that time link Microsoft Access front end file to MS Access back end file using the Linked Table Manager or some code to do it. The Microsoft Database program has two built-in utilities (Database Splitter and Linked Table Manager) to aid in your linking to MS Access data.  Look-up tables that rarely change their data are also possible items for the front-end. However, if you have tables with fields that look-up to other tables, those tables should be in the same back end database. Otherwise, if you open that source database and that table, it’s look-ups will not appear and this can cause data entry and data integrity problems.

The next article in this series will go over the risks when you do not split MS Access Database or link Microsoft Access front-end file to MS Access back end file.  The pros and cons of using a wizard to do the split for you will be discussed along with how to manually accomplish this procedure when the wizard would not do the job adequately.  At the end of the series we will discover code written by an Access MVP Microsoft Most Valuable Professional by the name of Dev Ashish. This code ensures that the data linking is working each time the program starts.  You will also be directed to a Microsoft KB Knowledge Base Article to further illustrate a manual method of splitting and linking your database.

In the next article you find out why it’s vital to Link Microsoft Access Application to MS Access back-end data file even if you only have one person using the database.

Continue to part 2: http://www.dailyaccesstips.com/why-link-microsoft-access-application-to-ms-access-backend-data-file-part-2-of-4/

Here are the main benefits of a split database. (See Split Database definition in Part 1)

Data Integrity:

Sharing a single database file over the network, rather than split MS Access Database, has been found by Microsoft to be the number one cause of database corruption.

A common cause of corruption is a dropped network connection when writing to MS Access back-end file.  When multiple users open a single Access database file on the file server, the chance that a network connection being dropped when one of them is writing data file to MS Access back-end is much higher than when each of these users opens his own copy of the front-end Access database file.

If a user, using an MS Access link to data, encounters a problem and the database closes unexpectedly, any database file corruption is usually limited to the copy of the front-end database that the user had open.  Because the user will link Microsoft Access application file to MS Access back-end file database by using linked tables, the back-end database file is much less likely to lead to MS Access data corruption.

Improved Performance:

The performance of the database will greatly improve when you link Microsoft Access application file to MS Access back-end file containing only Access data tables. The reason is that only the data is sent across the network.

In a shared database that is not split, the database objects themselves (tables, queries, forms, reports, macros and modules) must be sent across the network, not just the data. Because only the data is sent across the network, database record transactions such as record edits are completed faster. This reduces network traffic and that results in greater network speed.

Loading the entire application (forms, controls, code, etc) locally and using only an MS Access link to your data will make your application interface run faster because the form or report is sitting on the local workstation hard drive saving time retrieving it across the network.

Maintainability:

Because each user works with a local copy of the front-end database linked to MS Access back-end, each user can develop additional queries, forms, reports, and other database objects without affecting other users. They just need to ensure these changes get added to the next distributed version of the front-end.

Another advantage, individual users can customize some of the settings in Access without affecting other users’ settings. Similarly, distributing a new version of the front-end database will not disrupt access to the data that is stored in the back-end database. So it’s easy to maintain and update the program by just replacing the application file and link Microsoft Access front-end to MS Access back-end. Since the data is in a separate file, no data will be overwritten.

Greater Security:

Since you link Microsoft Access application file to MS Access back-end file located on a central server, different users may be assigned with different levels of permission. You may assign some read only permission to your database while assigning others read and write permissions. The server can be used to grant various permission levels for the MS Access link to the network folder. Your network or system administrator can set these for you.

Before you start splitting your database, you need to choose the best method.  Read the next article in this series to find out what’s best for you. http://www.dailyaccesstips.com/manually-link-microsoft-access-to-ms-access-data-part-3-of-4/

I am showing the manual methods first before the automated “Database Splitter” wizard method because it offers several advantages over the split MS Access Database wizard to Link Microsoft Access front-end to MS Access back-end.

The advantages of the manual approach are:

• It will result in smaller sizes for both front-end and back-end databases.
• It will not leave your back-end unsecured. A secure database split with the wizard will leave the back-end database file unsecured.
• Table relationships will not be lost.
  

If you want to link Microsoft Access front-end to MS Access back-end there are two processes you can use. You can use either of these methods on any version of Access program.

Here is the first manual method:

1. Copy the database.
2. Give it a name similar name but add the word program or ‘prg’ to the name. This will be your front-end database program file.
3. Delete the tables from this front-end. Do not delete any “lookup tables”, if their data is not going to change. Example of a good local table: A list of State abbreviations.
4. Rename to original file by adding the work data to it. This is your back-end file.
5. Delete all the queries, forms, reports, macros and modules from the back-end file.
6. In the front-end, use the Linked Table Manager to link to all the tables in the back-end or to get your MS Access link you may manually link to each table.
7. Compact both database files.

This first method will not lose any table relationships should you have any.

An alternative method is:

1. Create a new empty database and name it with the word data in the name. This will be your back-end file.
2. Import all of the tables into this back-end file.
3. Create a second new empty database and name it with the word program or ‘prg’ in the name. This will be your front-end file.
4. Import all of the queries, forms, reports, macros and modules into the front-end file.
5. Use the Linked Tables manager to link to all the tables in the back-end file or manually link to each table to get your MS Access link.
   

There is a Microsoft KB Knowledge Base Article on how to do this at: http://support.microsoft.com/kb/304932

The Microsoft Developer Network MSDN also has an article on this subject: http://msdn.microsoft.com/en-us/library/aa141493(office.10).aspx

As a side note, Microsoft has always allowed an upgraded newer version of your front-end to Link Microsoft Access to MS Access Data back-end that are in earlier versions. For example, you might upgrade your Access 2002 version to Access 2007 for some of your users getting a new computer, however you will not have to upgrade the back-end data tables to 2007.

Continue to the next article in this series: http://www.dailyaccesstips.com/use-wizard-to-link-microsoft-access-to-ms-access-data-part-4-of-4/

Each version of Microsoft Access has a built-in wizard to link Microsoft Access front-end file to MS Access back-end file in order to get your MS Access link between the two files.

You will find the wizard on the menu bar or ribbon at the top of the Access window. Here are the menu paths to get to your wizard.

Access 95-97 Tools |
Add-Ins | Database Splitter

Access 2000 – 2003 Tools |
Database Utilities | Database Splitter

Access 2007 Database Tools
| Move Data | Access Back-End

Here is how to use the Wizard in Access 2007:

Note: The wizard doesn’t copy the table relationships, if you have set any up.

1. On your computer, make a copy of the database that you want to split and place it on your local hard drive.
2. Open the copy that is on your local hard disk drive.
3. On the Database Tools tab, in the Move Data group, choose Access Database for the Database Splitter Wizard to start.
4. In the Create Back-end Database dialog box, give the back-end a name. I always place the word ‘data’ in the name.
5. To get your MS Access link for the correct back-end location, in the File Name box, you enter the path to the network in front of the file name. If the network location for the
   back-end database was \\server_fruit\share_folder_apple\ and the file name for the back-end database is MyDB_Data.accdb, you would enter \\server_fruit share_folder_apple\ MyDB_Data.accdb in the File Name box.

Note: It’s better to use the double slash UNC (UNC: Acronym for Universal Naming Convention or Uniform Naming Convention) rather than a mapped drive letter that might get unmapped.

After the database splitting procedure is finished, you can re-link the linked tables using Linked Table Manger to link Microsoft Access front-end file to MS Access back-end file.
Go to Database Tools from the Access Ribbon and select Linked Table Manager. In earlier Access versions, it’s under Tools, Database Utilities. Select the table names that you want to update and click on Ok button. If you ever want to link to a database located in another drive or folder, check the box ‘Always prompt’ for new location. Select the table names that you want to update and click on Ok button. If you ever want to link to a database located in another drive or folder, check the box ‘Always prompt’ for new location.

In Access 95-2003 do the following:

1. Open the database to be split on a local drive.
2. From the main menu, choose Tools then Database Utilities (Ad-Ins for Access 95 or 97) then Database Splitter.
3. The default file name for the back-end will show as DatabaseName_be.mdb (for Access 2003). The letters ‘be’ stands for back-end. I choose to use the word ‘data’ instead of the letters ‘be’.
4. Browse to the server and folder where the back-end file will be stored.
5. Now click on the Split button.
6. A message will appear when the split is complete.
7. Test the front-end database to confirm that the proper links have been created.
8. Now that you have your MS Access link, distribute the front-end database to your users, so that they can start to use the database.

Access MVP, Microsoft Most Valuable Professional, Dev Ashish has got some code that looks during program start up at each table in the database, and tries to find the data source for that table if the Connect property is populated. If the database specified for the linked table is not present, the code brings up a dialog window so that the user can select an alternative source. The code will then proceed to link Microsoft Access front-end file to MS Access back-end file.

Find Dev Ashish’s code here: http://www.mvps.org/access/tables/tbl0009.htm

Dev Ashish also references some code, for when one Access database has linked tables from different data sources (ODBC, Excel, FoxPro etc), that can create your MS Access link to all linked sources, regardless of their source type.

Here’s the code: http://www.mvps.org/access/tables/tbl0012.htm

Well that wraps up this series of articles.  Your comments and acknowledgments are always welcome.

Bob Heifler

Here are three questions that have been asked of me regarding how to pass a value from one place to another in MS Access:

1. I have multiple queries and need a form for a date to be entered once. Rather than have to enter the same dates for 16 queries. I would like to set them up with a temporary variable somehow so that each query could read the date and the user would only have to enter the date range once.
2. I have a query and want to establish criteria based on a combo box on a Microsoft Access form. When I leave the form’s combo box blank I want to include everything in the query output otherwise just the item in selected in the combo box.
3. What is the best way for me to get the value of a variable from a subroutine into another subroutine or a query?

I commonly get asked how to pass a value from one location in Microsoft Access to another. In VBA, Visual Basic for Applications, code a value that is passed or referenced later in code is called a variable. If it is referenced later out side of the form or code module were it was first given a value, then we need the variable value to be visible broadly throughout the application. This public variable (called “strFilter ” below), then needs to be placed in a code module that you create in Access. A simple function is then needed to retrieve the value of your public variable that you have declared in that code module you created. At the end of this article you will find a video tutorial demonstrating how to do this. Note that versions of Access 2007 or later have something called “tempvars” which serve this purpose with some additional benefits. You can find an article on this at http://www.vb123.com.au/toolbox/09_access/needforspeed.htm.

Create a new code module where you will declare your public variables and write a function to get them when they are needed.

Declare the public variable in a code module using this single line of code in the declaration area at the top of the code module you just created:

Public qrytesting as Variant

Note that the word “Public” allows this value to be visible from anywhere in the Microsoft Access programming.

Now create the public function in this code module to get the value of the public variable:

‘This function requires the name of the public variable it is to retrieve, in this case “strFilter”

Public Function GetPublicVariable(VariableName As String) As Variant
Select Case VariableName
Case “strFilter”
GetPublicVariable = strFilter
Case Else
MsgBox “You need Variable Name on GetPublicVariable function”
End Select
End Function

You set the public variable like this:
strFilter = [NameOfFormControl]
or
strFilter = some value

or

strFilter = another variable

This code is usually placed in the after update event of a form control.

You will retrieve the value of the set public variable with this line of code:

GetPublicVariable(“strFilter”)

GetPublicVariable(“strFilter”) can be used as the query criteria of the query above called “qrytesting”.

Regarding: “When I leave the form’s combo box blank” you could use this combination in the
Access query grid’s criteria cell:

Like GetPublicVariable(“strFilter”) & “*”
The Like operator will give you anything starting with the value that is currently being held
in the public variable call “strFilter”, however if nothing is stored there, a null value, then Microsoft Access wild card value, the *
asterisk, would be used for retrieving all values.

A simpler function to retrieve the public variable could have been written as seen below, but
it would not have been reusable for other public variables.

Public Function GetStrFilter() as Variant
GetStrFilter = strFilter
End Function

from youtube

I hope this has been useful.  Let me know.

Bob Heifler

How well your computer consultant understands your business computer requirements determine your total computer consulting costs by minimizing midstream changes, corrections and additions. Things not fully clarified can get altered or totally omitted, requiring more costly labor to correct or redesign.

Don’t leave it to chance.  Here is a checklist that covers the key areas to clarify for yourself and your computer consultant before discussion starts:

A list of the things you need to keep track of in your database and how they relate to each other is a good start but here is a more complete list to make sure nothing is falls between the cracks between you and your computer consultant.

1. What are your major categories of data and what specific data is stored under each category. Listing out your various business processes, and the steps required to accomplish them will help answer this.
2. How will want to filter your data to get a just what you need for a report or a screen view.
3. Any examples of reports or other output you may need or are currently using. (Microsoft Word can be used to illustrate a report design.)
4. Define what data validation or business rules are needed during data entry.
5. Any special requirements involving linking to or importing data from data sources outside of your business software.
6. Any financial accounting requirements or other legal / liability issues requiring additional data storage and output.
7. What kind of hardware system exists and is anyone supporting this.
8. Will data access require any remote access permissions?
9. The level of security access required for various areas of data viewing and editing among the individuals given permissions.
10. Current data entry media and formats currently being used if any exist.
11. Any data migration requirements from existing data sources, such as available formats form these sources

I can tell you that a consultant’s time might not always be billable on that first consultation, but he or she will appreciate the business owner who is prepared and can answer these basic questions.

I have found that it’s rare that a business owner is sufficiently prepared to start a discussion in this area unless these points have been thought out.

If you have had any experiences with computer business consultants that went sour or did not meet expectations, your comments and lessons learned are welcome.  We all need to learn more about this vital area and that includes me.

Bob Heifler

MyAccessProgram.com