# LEARN MS-ACCESS TIPS AND TRICKS

Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

### Introduction.

How to find number of work-days (excluding Saturdays and Sundays) from a date-range in Microsoft Access?

The logic is simple, first find out how many whole weeks are there in the date range. Multiplying whole-weeks by 5 gives the number of work-days from whole weeks.  From the remaining days find and exclude Saturdays and Sundays, if any. Add the remaining days to the total work-days.

DateDiff(), DateAdd() functions are used for calculations and the Format() function gets day-of-the-week in three character form to find Saturday and Sunday to exclude from the remaining days.

Find the VBA Code segments for the above steps below, and full VBA Work_Days() Function Code at the end of this Article.

1. Find number of Whole Weeks between Begin-Date and End-Date:
`WholeWeeks = DateDiff("w", BeginDate, EndDate)`

WholeWeeks * 5 (7 - Saturdays & Sundays) will give the number of working days in whole weeks. Now, all that remains to find is how many working days left in the remaining days, if any?

2. Find the date after the whole week days:
`DateCnt = DateAdd("ww", WholeWeeks, BeginDate)`
3. Find number of work days in the remaining days by checking and excluding Saturdays and Sundays:
```Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
Loop
```
4. Calculate the Total Workdays:
`Work_Days = Wholeweeks * 5 + EndDays`

### The Whole Calculation in Work_Days Function.

The full VBA Code of the Work_Days() Function is given below:

```Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

On Error GoTo Err_Work_Days

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
'Number of whole weeks
WholeWeeks = DateDiff("w", BegDate, EndDate)
'Next date after whole weeks of 7 days each
EndDays = 0 'to count number of days except Saturday & Sunday

Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
Loop
'Calculate total work days and return the result
Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates.

If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description
End If

End Function
```

The above VBA Code was taken from Microsoft Access Help Document.

Share:

### Introduction.

We design Access Forms which fits into the existing Application Window Width (to display/edit records), or design popup Forms with specific size without borders or scroll bars (can be moved out of the Application Window area too) or Modal type form (popup type forms with it's Modal property value set to True) that must be closed, after taking suggested action on it, before you are able to work with other forms.

This type of form opens one-over-the-other (when more than one form is open) on the Application Window. You must enable Overlapping Windows by selecting Office Button - - > Access Options - - > Current Database - - > Document Window Options - - > Overlapping Windows to open the forms in this way, otherwise they will be opened in tabbed style in the Application Window.

### Pop-Up Forms.

Popup Forms will open on the exact location of the Application Window from where you have saved it during design time. If you need more details on this subject visit this Article Link: Positioning popup Forms.

We can open a Microsoft Access Form and move it to a particular location of the Application Window in resized state, if necessary, with MoveSize Action of Docmd Object.

### The MoveSize Action.

Here, we will learn the usage of MoveSize Action of the DoCmd Object in VBA.

View the Youtube Demo Video given below for reference. Select 720p HD Option from the Settings for better quality viewing.

Demo Video

When Supplier Code is selected on the Supplier List Form, the related Product List is displayed above the Supplier Form to the right of the main form. The width of the Product List Form is not changed but the height is changed depending on the number of items on the form.

We need two tables, two Queries and the Supplier List Form from the Northwind.accdb sample database to build this trick. You need to design a Form for the Product List. A Demo Database is given at the end of this Article to download and try it out, right away.

The list of Tables, Queries and Forms required, to build this database, are given below.

Tables:

• Suppliers
• Products
• Queries:

• Suppliers Extended
• ProductListQ
• SQL Code:

```SELECT Products.[Supplier IDs], Right([Product Name],Len([product name])-17) AS Product, Products.[List Price], Products.[Quantity Per Unit]
FROM Products
WHERE (((Products.[Supplier IDs].Value)=[forms]![Supplier List]![id]));
```

Forms:

• Supplier List
• Product List

Copy and Paste the following VBA Code into the Supplier List Form's VBA Module and save the Form:

```Private Sub Company_Click()
Dim frm As Form, ProductForm As String, items As Integer
Dim mainFormHeight As Integer
Dim intHeader As Integer, intFooter As Integer
Dim intH As Integer, frmchild As Form, oneInchTwips As Integer

On Error GoTo Company_Click_Err

ProductForm = "Product List"
oneInchTwips = 1440 'Form's internal value conversion factor

mainFormHeight = Me.WindowHeight

For Each frm In Forms
If frm.Name = ProductForm Then
DoCmd.Close acForm, ProductForm
Exit For
End If
Next
DoCmd.OpenForm ProductForm
Forms(ProductForm).Refresh
items = DCount("*", "ProductListQ")

Set frmchild = Forms(ProductForm)
'Calc the required height of the chid-form
'based on number of items for selected supplier
intFooter = frmchild.Section(acFooter).Height
'0.272 inch - product item row height
intH = intHeader + items * 0.272 * oneInchTwips + intFooter
intH = intH + oneInchTwips '- one inch margin from bottom
'Move and resize the height of the child form
'4.275 inches to the right from left of the Application Window
'1.25 inches - arbitrary value taken for bottom margin
DoCmd.MoveSize 4.275 * oneInchTwips, mainFormHeight - intH, , (items * 0.272 + 1.25) * oneInchTwips

Company_Click_Exit:
Exit Sub

Company_Click_Err:
MsgBox Err & ": " & Err.Description, , "Company_Click()"
Resume Company_Click_Exit

End Sub

Private Sub Form_Current()
Me.Refresh
End Sub
```

Note: Don't forget to change Overlapping Windows option in Access Option settings mentioned in paragraph two from top.

1. Open Supplier List Form.
2. Click on Supplier ID Field (with Company column heading) of any record, to open and display the selected Supplier related Product List, in Resized Product List Form and Moved to its specified location.

Share:

### Introduction.

We have the UCase() Function in Access to convert all letters in a string of Text into upper-case letters(UPPER() Function in Excel).

```? UCase("LeARn mS-access Tips aNd tRicKs")

Result: LEARN MS-ACCESS TIPS AND TRICKS
```

LCase() Function (LOWER() in Excel) of Access converts all the Text in a string into Lower-Case letters.

```? LCase("LeARn mS-access Tips aNd tRicKs")

Result: learn ms-access tips and tricks
```

PROPER() Built-in Function of Excel converts first letter of each word in a string of text into upper-case and rest of the text into Lower-case.

I don’t say there is no Function in Access to do that job, but it's name is not PROPER()

Microsoft Access Function StrConv() can do what PROPER() Function does in Excel.

It's usage is as given below:

```? StrConv("LeARn mS-access Tips aNd tRicKs",3)

Result: Learn Ms-access Tips And Tricks
```

### STRCONV() Function

STRCONV() Function needs two parameters:

1. The Text value to be converted
2. The conversion type parameter - an integer value.

Here, parameter value 3 stands for Proper-case conversion.

I know what you are thinking; can we do other conversions also with this Function?

Yes, STRCONV(Text,1) for UCase() Function

and STRCONV(Text,2) for LCase() Function.

There are other usages for the STRCONV() function, with different parameters. If you are interested to explore further then type STRCONV in the search control of Microsoft Access Help Documents and get the details.

If you think that the name of the function is difficult to memorize, requirement of a second parameter is also too much work then we can go by the Excel way and define a PROPER() Function in Access and use it wherever you want.

### Here is the VBA Code:

```Public Function PROPER(ByVal strText As String) As String
PROPER = StrConv(strText, 3)
End Function
```

Copy and Paste the above VBA Code into a Global VBA Module of your Project. If you have a Library Database then copy and paste the Code into it, so that you don't have to duplicate it in all your other Projects.

I will not be surprised if you name the function as PCase() in line with the other text conversion functions LCase() and UCase().

Share:

### Introduction.

Beginners of Microsoft Access can get confused with the usage of RUNSQL Action in Macro and DoCmd.RUNSQL method of Visual Basic for Applications (VBA). Whether you run this Action in Macro or in VBA you must provide SQL Statement of an Action-Query or Data-Definition Query Types only. If you are not sure which are these types of queries then refer the following list:

### Action Query Type.

Action Query Types
Query Type Statement
Append INSERT INTO
Delete DELETE
Make-Table SELECT ... INTO
Update UPDATE

### Data Definition Queries.

Data-Definition Query Types
Query Type Statement
Create a table CREATE TABLE
Alter a table ALTER TABLE
Delete a table DROP TABLE
Create an Index CREATE INDEX
Delete an Index DROP INDEX

Using SQL Statement of any other Query type in RUNSQL Action will end up in errors. In Macro the length of an SQL statement can be maximum 256 characters or less.

The DoCmd.RUNSQL method of VBA can execute an SQL statement with a maximum length of 32768 characters or less.

Note: You are not allowed to give an existing Query Name as parameter to this command. But, in VBA you can load the SQL Statement of a predefined query into a String Variable and use it as parameter to the DoCmd.RUNSQL command.

#### Example-1:

```Public Function DelRecs()
'Existing Delete Query’s SQL is used in this example
Dim db As Database, qryDef As QueryDef
Dim strSQL As String

'Read and save the SQL statement from 'Query48'
'and load into strSQL string variable
Set db = CurrentDb
Set qryDef = db.QueryDefs("Query48")
strSQL = qryDef.SQL

'Execute the SQL statement after
'disabling warning messages
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Using DoCmd.OpenQuery Command to run a Delete Query
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query48", acViewNormal
DoCmd.SetWarnings True

End Function

```

The RUNSQL Action of Macro Modifies/Deletes information of several records in one go. Before executing the Action Query Microsoft Access gives out a warning message, appropriate to the action specified, and waits for the User's response. The User must respond to proceed/cancel the action specified.

Once the procedure is perfected, by test running the Action Query several times, we can ask Microsoft Access to turn OFF the Warning Messages during execution of RUNSQL Action. After the Query running step, give control back to MS-Access by turning ON the Warning Message detection. From that point onwards MS-Access will be watching for the unexpected errors in the System and warns the User, as and when it happens.

The SetWarnings Action in Macro and DoCmd.SetWarnings Method in VBA are used for this purpose, when data processing for report involves one or more action query steps are placed in a Macro. Check the image of a Macro, given below in design view, with the SetWarnings settings, before and after the RUNSQL Action in the Macro. The first Action in the Macro is Setwarnings with the Parameter value NO to turn off the Warning Messages, when the RUNSQL Action executes in the next step. The SetWarnings Action with the Parameter value YES turns ON the Warning Messages in step three, so that MS-Access can take care of future unexpected System Errors of any kind, as and when it happens.

#### Example-2:

Create a Table with the Data-definition SQL

Warning:Double-check that you are not using any existing table-name for this sample run.

```Public Function DataDefQuery()
Dim strSQL As String

strSQL = "CREATE TABLE Books  (Title TEXT(50) NOT NULL, Author TEXT(50) NOT NULL, PublishDate DATE, Price CURRENCY)"

DoCmd.RunSQL strSQL

End Function
```

The OpenQuery Action in Macro and DoCmd.OpenQuery Method of VBA uses the name of a predefined Query (of any type) to open them in any of the following Views:

• Design View
• Datasheet View
• Print Preview
• Pivot Table
• Pivot Chart

Share:

### Introduction.

Hyperlinks in Microsoft Access is an excellent tool to open internal or external objects very easily, without writing macros or VBA Code. Hyperlink Address and Hyperlink SubAddress properties are available on Label controls, Command Buttons and Image controls.

If you have not yet tried anything with these properties of the above controls then let us go straight into a demo run and learn the usage of both these properties.

### The Sample Trial Run.

1. Create a new Blank Form.
2. Insert a Label control on the Form.
3. While the label control is still in selected state display its Property Sheet (F4).

• Hyperlink Address : for opening external objects, like Word Documents, Excel Worksheets, .PDF files etc.
• Hyperlink SubAddress : for opening internal objects, like Forms, Reports, Tables, Queries and Macros.

4. Find the Hyperlink SubAddress property and write the following text into it:
`Report <Your Report Name>`

Replace <Your Report Name> text with one of your own report name (without the < > brackets). The correct format for writing value into this property is <Object Type> i.e. Form, Report, Table, Query or Macro followed by a space, followed by the actual Object name.

5. Change the Caption Property value of the Label to Open Report.
6. Save the Form, with the Label control's Hyperlink SubAddress value set with the above changes.
7. Open the Form in normal view and click on the label control.

You will see now your report is open in Print Preview.

### Opening Excel/Word File.

Now, let us try to open a MS-Word Document using the other property, Hyperlink Address setting.

1. Open your Form and create a second Label control on it.
2. Display the property sheet of the label control.
3. Change the Caption property value to Open WordDoc.
4. Set the Hyperlink Address property value with the full pathname of a Word Document on your computer, like C:\My Documents\Letter.doc
5. Save the Form with the changes.
6. Open the Form in normal view and click on the second label to open the Document in MS-Word.

Note: You can open Web pages by setting the Hyperlink Address property value to a web address: say https://www.msaccesstips.com

If your external files, which you plan to open in Access, are all in one location C:\My Documents\ then you don’t need to duplicate it in every control but to specify the Path (C:\My Documents\) at one place: Hyperlink Base and only need to write the document name (like Letter.doc or any other file on that location) in the Hyperlink Address property.

Let us try that:

1. Click on the Office Button
and highlight Manage to display Database options (Access 2007). In Access 2003 and earlier versions, you will find this option in Tools Menu.
2. Select Database Properties.
3. Select Summary Tab.
4. Type C:\My Documents\ in the Hyperlink Base control (see the image given above)  and click OK to save it.
5. Open your Form and remove the C:\My Documents\ text typed in for earlier example, leaving the Letter.doc file name intact.
6. Save the Form and click on the label control to open the Word Document.
7. You may try to open any other document you have in that location, with only the file name change on the Label control.

#### Opening Objects from another Microsoft Access Database.

If you have followed me so far with the above sample exercises, then with few changes we can do it.

1. First, remove the text from Hyperlink Base control ('C:\My Documents\') and leave the control empty.
2. Create another Label control on your Form.
3. Change the Caption Property value to Ext. Database.
5. Set the Hyperlink SubAddress property value to Report myReport. Change the report name to match yours.
6. Save the Form with the change.
7. Open the Form in normal view and click on the label control.

Your external database will open first and will show your report in print preview.

Note: If your database is secured then it will prompt for UserID and Password. You may try to open other objects: Form, Query, Macro & Table.

Share:

### Introduction.

During installation of Microsoft Access 2003 or earlier versions, the NorthWind.mdb sample database also will be installed on your computer automatically. The sample database is very helpful for those who are starting with their self learning practices of Microsoft Access.

Those who are migrating from Microsoft Excel Worksheets and tables are created in line with what they have practiced in Worksheets, find themselves faced with lot of issues. In fact, Excel itself have database management support, even though they are not as flexible as Microsoft Access. Excel Users can browse for database basics in Excel Help documents to set up a database in Excel and learn simple data organizing & retrieval methods, like sorting, searching, filtering and so on. This not only helps to learn database basics but also will help to organize Excel worksheet/database in such a way that they can be easily linked/imported into Microsoft Access, if need arises at a later stage.

Coming back to the sample database creation in Microsoft Access 2007, it doesn't create a sample database (Northwind.accdb) during installation of MS-Office in your computer. But, you can create one yourself from the sample Templates provided. It contains all the tutorial materials you need to learn Microsoft Access.

### How to Create NorthWind.accdb Database.

1. Open Microsoft Access 2007

You will find several Database Template Categories displayed at the left panel and Featuring Category is selected by default. In the middle window, the template  Blank Database displayed and other templates designed for specific purposes.

2. On the left panel find the Sample Template category and select it.
3. Click on the Northwind 2007 Template.

The database will be saved in the active folder by default. You may change the folder by clicking on the folder icon to the right of the database file name.

4. Click Create Button to create the sample database in your preferred folder specified.

Always use the sample database as a reference point for all your issues regarding Table designing, setting up Relationships, Queries, Forms, Reports and Macros designing. Do few trial and error practices on the specific task you are trying to accomplish, with whatever knowledge and ideas you have. You will get better insight into the task you are trying to design and you can find solutions in most cases yourself. If you are not able to do it then with the trial-and-error background you will be in a better position to explain your specific issues and seek help from other sources.

### Access Users Forums.

You can search this Website for topics you are interested in or post your questions and get help from experts in Microsoft Access Users' Forums on Internet. Links to some of the popular Forum Websites are given below:

Share:

### Introduction

Your Microsoft Access Application development process is complete and ready to install for the User. At this stage you may design a small form known as Microsoft Access About Form for your Project. About form will display the customized Logo and the name of the Project, Version Number, Copy Rights information and other details, which you may wish to include on the form.

Sample About Form image of Windows Live writer Application is given below as an example:

Designing an Access About Form is easy. You need two Label controls, to display the Application Name & Current Version Number, a Text-box to display the Copy Rights information. If you have designed a Logo, to give a unique identity to your Application, then that also can be included on the About Form.

### Designing a Simple Access About Form.

Let us design such a small About Form, to know what it takes to create one with a simple Logo. Our sample About Form in design view is given below for reference.

I have designed a simple logo in MS-Word, captured a screenshot of it, modified in MS-Paint and saved as a .bmp file.

1. Open your Microsoft Access Application.
2. Create a new Blank Form.
3. Set the Width Property value of the Form to 3.93"
4. Click on the Detail Section of the Form to select it.
5. Set the Height property value to 1.53".
6. Insert an Access Image control from the Tool-Box at the left side of the Form and select the Project Logo image from your computer. You may select the  the Picture Property of the Image control and click on the build (. . .) button to browse and select the logo image from your computer, if you wish to change the image later.

#### The Image Properties.

7. Change the Image property values as given below:
• Picture Tiling : No
• Size Mode : Zoom
• Picture Alignment : Center
• Picture type : Embedded

8. #### Few Access Label & Text Controls.

Insert a Label control to the right of the logo and top of the Form, re-size it to make it wide enough to write the Application name in bold letters.
9. Write the name of your Project in the Caption property, change the font size big enough to your liking, make it bold and align the text to the center.
10. Create another Label control below the first one, with the same width of the first label, and write the Version number of your Project, make it bold and align the text to the center.
11. Insert a Textbox below the earlier labels, change it's width as wide as the top labels.
12. Copy and paste the following expression into the Control Source property of the textbox.
```="Copyright " & Chr\$(169) & Year(Date()) & " All Rights Reserved".
```
13. Change the following Property values of Text-Box as given below:
• Border Style : Transparent
• Text Align : Center
• Enabled : No
• Locked : Yes
• Tab Stop : No

#### MS-Access Command Button.

14. Create a Command Button below the textbox and position it to the center horizontally.
15. Make the following changes to the Command Button:
• Change the Name property value to cmdOK.
• Change the Caption property value to OK.
• Click on the Event Tab of the property sheet.
• Select the On Click property and select [Event Procedure] from the drop-down list.
• Click on the Build (...) button to open the VBA Module with the empty procedure start and end lines.
• Copy the middle line of the Code given below and paste the line in the middle of the start and end lines of the VBA procedure (Private Sub cmdOK_Click() . . . End Sub). Or copy all the three lines and paste it, overwriting the existing lines in the VBA Module.
• ```Private Sub cmdOK_Click()
DoCmd.Close
End Sub
```

When the Access User clicks on the Command Button the above Code will run and the About Form will be closed.

17. Open the About Form in normal View and see how it looks on the Access Application Window.

### The Property Value Changes.

As you can see the Access About Form needs some changes to make it's appearance like a real About Form. Let us do that to give it the final touches.

18. Make the following changes in the Form's Property Values as shown below:
• Pop Up : Yes
• Modal : Yes
• Default View : Form View
• Allow Form View : Yes
• Auto Center : Yes
• Auto Resize : Yes
• Fit to Screen : No
• Border Style : Dialog
• Record Selectors : No
• Dividing Lines : No
• Scroll Bars : Neither
• Control Box : Yes
• Close Button : No
• Min Max Button : None
19. Save the Form after the above changes.

### View The Application About Form in Normal View.

20. Open the About form in Normal View.

Sample Image of the completed About Form in normal view is given below.

You must add an Option in your Customized Menu of your Project to enable the User to open the Access Application About Form, if he/she wish to do so. Alternatively, you may add a Command Button on the Main Form of your Project to do that.

Share:

### Introduction

Last week we have tried out an interesting method of writing a range of excel data range directly into a Microsoft Access Table. Each row of cells are transferred into the table as a single record. If you have not come across that article then you may find it here.

This is an equally interesting method.  The selected Worksheet contents are appended to the Access Table in it's specified columns in the SQL.

Sample image of the Worksheet data is given below:

#### The VBA Code

```Private Sub CommandButton1_Click()
On Error GoTo CommandButton1_Click_Error
'Create Database connection Object
'Access Database must be in the same location of the Worksheet
dbpath = Application.ActiveWorkbook.Path & "\Database4XL.accdb"
'Get Workbook Full Pathname
dbWb = Application.ActiveWorkbook.FullName
'Get Active worksheet name
dbWs = Application.ActiveSheet.Name
'Create Data Target Connection string to open a session for data transfer
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath
'Datasheet name to add at the end of the Workbook Name, to complete the
'FROM clause of the SQL String.
dsh = "[" & dbWs & "\$]"
'Open session
cn.Open scn

'Append Query SQL String
ssql = "INSERT INTO Table1 ([Desc], [Qrtr1], [Qrtr2], [Qrtr3], [Qrtr4]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

'Run SQL
cn.Execute ssql

MsgBox "Data Added to " & dbpath & " Successfully."

CommandButton1_Click_Exit:
Exit Sub

CommandButton1_Click_Error:
MsgBox Err & " : " & Err.Description, , "CommandButton1_Click()"
Resume CommandButton1_Click_Exit

End Sub
```
##### Courtesy:
The above VBA Code was taken from a Forum Post of www.mrexcel.com/Forum and modified to run on the same sample data presented in the earlier Article published last week.

The Code on the Worksheet VBA Module is run from the Command Button1 Click Event Procedure.

Share:

### Introduction.

We can Import data from external data sources; from another Access database, dBase, Foxpro, Excel and so on. We can Export data from Access into these Applications as well.

Following is a list of topics I have published earlier; either on importing, exporting or working with external data sources from Microsoft Access:

Today, we will explore how to add a range of excel cells data directly into Access Table, by running VBA Code from within Excel?

#### The Algorithm of the program is as given below:

1. Create an Access Application Object and open it.
2. Open the target database within the Access Application.
3. Keep the Access Application window hidden.
4. Open the target table from the Database.
5. Take the count of Rows from one of the Excel data column.
6. Open a repeating loop to write the excel data one row at a time, from second row onwards.
7. Repeat the writing action till all the rows are transferred to the Access Table.
8. Close the table, database and quit MS-Access Application.

The Excel VBA Code is Run by clicking a Command Button on Excel Sheet. Sample image of the Excel Sheet with data and Command Button is given below:

Target Access Table Structure image is given below:

### The Excel VBA Code

```Sub Button1_Click()
Dim objAcc As Object
Dim recSet As Object
Dim DataRow As Long, EndRow As Long

On Error GoTo Button1_Click_Err

'Create Access Application Object
Set objAcc = CreateObject("Access.Application")
'Open Database in Microsoft Access window
objAcc.OpenCurrentDatabase "F:\mdbs\Database4XL.accdb", True
'Keep Access application window hidden
objAcc.Visible = False

'Open Access Table to add records from Excel
Set recSet = objAcc.CurrentDb.OpenRecordset("Table1")
'Take actual row counts of data for transfer
EndRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

With recSet
For DataRow = 2 To EndRow
![Desc] = Sheet1.Range("A" & DataRow).Value
![Qrtr1] = Sheet1.Cells.Range("B" & DataRow).Value
![Qrtr2] = Sheet1.Cells.Range("C" & DataRow).Value
![Qrtr3] = Sheet1.Cells.Range("D" & DataRow).Value
![Qrtr4] = Sheet1.Cells.Range("E" & DataRow).Value
.Update
Next
End With
recSet.Close
objAcc.Quit
Set objAcc = Nothing

Button1_Click_Exit:
Exit Sub

Button1_Click_Err:
MsgBox Err & " : " & Err.Description, , "Button1_Click()"
Resume Button1_Click_Exit

End Sub
```
##### Courtesy:
The non-functional raw VBA Code was presented by a User in www.mrexcel.com/forum/microsoft-access, modified by me to make it functional and was originally submitted there.

The first field of the table is an ID field with data type AutoNumber. ID field value is automatically generated when data is inserted into the other fields.

Share:

### Introduction.

You have spent a lot of time designing Custom Menus and Toolbars in your .MDB Database. You have upgraded to Microsoft Access 2007 Version. You opened your .MDB database in the new version of MS-Access. The customized menus and toolbars you have designed for your application are disappeared.

Your Custom Menus and Tool Bars are not gone anywhere, all you have to do is to find them.

Custom Menus and Toolbars are safely kept inside the Add-in Menu of Microsoft Access2007. Click on the Add-in Menu to find all of them there.  Check the sample image given below for reference.

Have you noticed the group name Custom Toolbars appearing at the bottom of the Add-in Menu?

But, Access 2007 Menu Bars also appearing on the top.  You want to turn off the default Access2007 Menus and Toolbars from the top and replace it with your Custom Menus and Toolbars.

### Access Option Changes.

Do the following to get that done:

1. Click on the Office Button (Top left corner).
2. Select Access Options.
3. Select Current Database.
4. Move the scrollbar down to find the Ribbon and Toolbar Options as shown on the image given above.
5. Select your Custom Menu Bar name from the drop-down list, replacing the text (Default).
6. Select your Shortcut Menu Bar name from the drop-down list replacing the text (Default).
7. Remove the check-marks from the next three options:
• Allow Built-in Toolbars.
8. Click OK to save changes and come out of Access Options.

9. One more step to go:

10. Close the database and open it again to register the changes.

Now, your Menu Bars and Tool Bars are in complete control of your Application they will look like the image given above.

You want to restore Access 2007 Menus and Toolbars, then open .MDB file by holding the Shift-Key, to prevent Auto-Macro from running and to get control of the database window.  Go through Steps-1 to Step-9 and reverse what you did earlier.

Share:

### Introduction.

How to bring sub-report summary value to the main report and use it in calculations?

But, first we need a ready made report for our project, without the calculations part on it.

First, take a look at the following images:

1. Print Preview of the finished report.
2. Original Report Preview.
3. Changes made on the Report in Design View, to get the result shown on the first image above..

In short, our task is to add the sub-report category total at the Footer Section of the Sales by Category sub-Report. Get that summary value of each category to the main report header.  Calculate percentage of each category of products sales value on Grand-Total Sales Value (or Pcercentage = Category Sales Value / Total Sales value of all Categories * 100).  We can do this by adding few text boxes on both reports and writing few expressions in them.

### The Sub-Report Changes.

2. Open Sales by Category Sub-report in design view.
3. Right-click on the Report Footer bar and select Properties to display the Property Sheet.
4. Select the Height Property and change the property value to 0.33” or 0.838 cm.
5. Select the Text Tool from the Toolbar above and draw a text box on the Report Footer area below ProductSale column.
6. Write the expression =Sum([ProductSales]) in the Control Source property of the text box. Change the Name property value to SubTotal.
7. Modify the Caption property value of child label to read as Sub-Total.
8. Save and close the Sales by Category Subreport.

### The Main-Report Changes.

9. Open Sales by Category main report in Design View.
10. Create a Text Box, on the Header Section of the Report, to the right of the report heading.
11. While the text box is in selected state display the property sheet (F4).
12. Write the expression =Sum([ProductSales]) in the Control Source property. Change the Name property value to TotalSales. Change the Caption property value of the child label to Total Sales.

Note: The above expression will calculate the Total Product Sales value of all Categories on the main report. The same expression on the sub-report will calculate sales value of current category of Product (example: Beverages). Now we have the product sales value of a particular category as SubTotal on the Sub-Report and the Total Sales values of all categories of products as TotalSales. In the next step we can find the percentage of a particular category of product’s sales on total sales of all categories. The simple formula can be Percentage=SubTotal/TotalSales*100.  But, SubTotal is on the Sub-Report and we cannot directly address it as SubTotal on it's parent report or Main report (without specifying where it can be located in the main report having one or more child reports or sub-reports).

13. Create a text box below the Category Header bar to the right of the Category Name heading, move the text box  to the right and position it below the Total Sales calculation control on the Report Header.
14. Right-click on the Control Source Property of the text box and select Build option, from the displayed list, to open the expression builder control.
• click on the = symbol to insert it into the expression editor window.
• Double-click on the + symbol at the left side of the Reports option to expand and show other options.
• Double-click on Sales by Category main report to display the Sub-Report's name.
• Click on Sales by Category Subreport to display it's control names in the next column.
• Find the Subtotal control in the list and double-click on it.

The Subtotal control reference of the sub-report: [Sales by Category Subreport].[Report]![SubTotal], is inserted into the expression builder. If we know how to write this reference correctly in to the Control Source property manually then we can do that without going through this route. But, once you go through this procedure a few times and study Microsoft Access’s addressing method you will know how to write them on Reports or Forms manually.

• Click OK to come back to the Control Source property of the text box with the reference of the subtotal control and to write the rest of the expression to calculate the percentage.
• Type /[TotalSales] to the end of the subtotal reference. Have you noticed the slash at the left side of the expression snippet?
• Select Percent from the drop-down list of the Format property. With this settings we don’t have to write *100 part in the expression.
• Change the Caption of the child label to Category %. Change the Font-size of the Text box and child label to 12 points and make Font-weight Bold.

### Report Sample Print Preview

Our Report is almost finished, but we need little more change and that comes next after we preview the progress of our work so far

15. Save the changes we have made on the Report and open it in Print Preview, to see the result of the changes we have done on the report so far.

The Report should look like the image given below.

16. Move the Report to the next page.

The Total Sales value on the Report Header Section is not appearing in the second page.

This will not appear on the report from second page onwards along with the Report Heading. Report Header section values will only print on the first page of the report and Report Footer will print only on the last page of the report.  But, we want the Heading and the expression written on the Header Section to appear on every page of our Report. Whatever we insert on Page Header Section will appear on every page. We can transfer the Report Heading to Page Header Section but not the Calculation Text box. The SUM(), Count() and other aggregate functions will not work on the Page Header Section, but we want the value from the Total Sales control to appear on the Page Header Section as well. We will do the following changes to keep the report heading and Total Sales values on all pages of the report.

### The Final Changes.

• Open the report in design view.
• Drag the Category Name header bar down to get enough space on the Page Header Section, to cut and paste the Report Heading there.
• High-light report heading and the report date controls (leave alone the Total Sales textbox), cut and paste them into the Page Header Section.
• Select the Total Sales text box, copy and paste it on the Page Header Section,  move and position it below the text control on the Report Header Section.
• Write the expression =[TotalSales] (the Name of the total sales calculation text box on the header section) in the Control Source property (over-writing the existing expression) of the copied text box.

This will display the value from Header Section text box, where the Total Sales value is calculated.

• Select the Header Section text control along with the child label and set their Visible Property value to No, to keep it hidden when the report is Previewed or printed.
• Save the report with the changes.

### Print Preview the Report.

• Open Sales by Category report in Print Preview, move the pages forward and check the headings and category percentage values.
17. I am sure next time you want to do something like this you can do it in few minutes.

Share:

### Introduction.

Hyperlinks are everywhere in Internet. We have them in Microsoft Access too. It can be used to open Objects (Tables, Forms, Reports or Queries) by clicking on a  hyperlink, without writing a single line of VBA Code or Macro. Hyperlinks can be created in Tables or Forms. You can create menus to open Forms, Reports etc. You can open external files, like Word, Worksheets, .PDF, text and others with the use of Hyperlinks in Microsoft Access.

Hyperlinks in Access can be created or edited manually by entering it's link segments in proper order, in table field or on Form controls. Normally, editing can be done through a dialog box, displayed when right-clicked on the hyperlink control.

1. Display Text : the text that appears in a field or control, indicating the hidden link's purpose
2. Address : external file's path-name
3. Sub-Address : Internal Object Name, to open Form, Report etc.
4. Screen-tip : the text displays as a tool-tip.

#### Hyperlink Syntax is as follows:

Hyperlink Data type field can store a maximum of 2048 characters.

You can find more details and usage of Hyperlinks in the earlier Post: Open Forms with Hyperlinks in Listbox.

Let us get into the manual editing of Hyperlink topic:

We will create a sample table with an Hyperlink field to try out the manual editing exercise.

1. create a Table with a single field and data type Hyperlink.
2. Save the table with a name.
3. Open the table in datasheet view.

If you know how to enter Hyperlink data into the field manually, you may do so, by following the hyperlink syntax shown below.

Example: My Excel File#C:\\\My Documents\Workbook1.xls##Click

4. After entering the sample hyperlink in the field, open the table in datasheet view.

You will see that the hyperlink field is active and the only text showing is the Display Text (My Excel File) part of the hyperlink information.

5. Move the mouse-pointer over the field, it changes the Mouse-Pointer to a hyperlink pointer (a Hand with index finger).

If you click on the field to edit the hyperlink information, it will only open the document specified in the second segment.

If you right-click on the field the shortcut menu, which carries the hyperlink editing options in a dialog box is displayed.  You may key-in values at appropriate controls to change the hyperlink values.  You cannot key-in values into the hyperlink field directly.

#### Simple Trick to Edit Hyperlink Manually.

But, we can manually edit the field with a simple trick.

6. Close the Table for now.
• Click on Office Button.
• Select Access Options.
• Find the Behavior Entering Field options.
• Select Go to end of field radio button.
• Click OK to close the Access Options control.
7. Open the Table after completing the above steps.

You can see that the insertion point is positioned at the end of the field value, exposing the hyperlink information. Move the insertion point back to the location where you need editing the field.

If you have more than one record to edit then change them and reset Behavior Entering Field option to Select Entire Field.

TIP:

If you would like to show the full Pathname of the File as display text then copy the second segment hyperlink value and paste it in the display text position also.

### Stand-alone Label Controls Have Hyperlink Properties.

The Label control on Form can be used as a hyperlink control.  The label control have Hyperlink Address & Hyperlink Sub-Address properties, use Caption property to set the Display Text value of hyperlink. Set the Special Effect property value to Raised to make it look like a Command Button.

Share:

### Introduction.

If you are new to Microsoft Access Report designing, then this simple Alphabetized Customer List preparation Tutorial will give you enough insight into what it takes to design a simple report.

We will need the following few steps to complete our task:

1. Prepare Customers' source data in a SELECT Query for the report.
2. Open a new report in Design View.
3. Insert the SELECT Query name into the Record Source Property of the Report.
4. Use Data Grouping and Sorting option of the Report to organize and display A, B, C  etc. in the Group Header.
5. Design the Report.
6. Preview the Report.

### A Sample Report.

Sample alphabetized list of customers Report Preview is given below:

### Designing A Report.

#### Get Some Sample Data.

1. Click on External Data Menu.
2. Click on Access Tool button to display the Import control dialog box, to specify the Source and destination of data.
3. Click on Browse... button to locate the Northwind.accdb sample database, Select the file and click Open.

The selected file pathname is inserted into the File Name control on the dialog box.

The first option is already selected as default, to import one or more required Access Objects from the selected Access database.

4. Click OK to open the selected Access Database and expose it's Tables, Queries, Reports etc.
5. Click on the Tables Tab, select the Customers table and click OK to import the selected table.

Next step is to create a SELECT Query using Customers table as Source.

6. Click on the Create menu and select Query Design from the Other group.
7. Click Close button to close the Show Table dialog box, without selecting any object from the displayed list.
8. You will find the SQL View option at the extreme left of the Tool bar and select it to display the Query's SQL editing window.

You will find the SQL statement SELECT; in the window.

9. Copy the SELECT Query Code given below and paste it into the SQL window, over-writing the existing SELECT; statement.
```SELECT Left([First Name],1) AS Alpha, [First Name] & " " & [Last Name] AS CName
FROM Customers
ORDER BY Left([First Name],1), [First Name] & " " & [Last Name];
```

As you can see in the above SQL string we have only two columns of data. First column have only one character in all rows, the left-most character taken from the First Name of the customer. The first column name on the query is given as Alpha.

We have used MS-Access built-in string function Left() for this purpose. There are other useful functions in this category, like Right(), Mid() and others.

Second Column of data is the name of the Customer, with First Name, Last Name joined together, separated with a space. The second column's name is given as CName. When you use an expression to create the query column data it is better to give a simple name to the column so that it is easy to memorize and use it on the Report or Form.  If you don’t give a suitable name then MS-Access will give names like Expr1, Expr2 (Expression 1, Expression 2 and so on) by default.

In the ORDER BY clause of the query both the columns are sorted in Ascending (A-Z) Order, first on Alpha column and then on CName column.

10. Save the Query with the name Customer ListQ.
11. Open the Customer ListQ in data sheet view and check the data.

Sample image of what we are going to create is given below for reference:

Let us design the Report

1. Select Report Design from the Create menu.

An empty Report is open in Design View with it’s Property Sheet. The first priority is to define our Customer ListQ Query as the Record Source of our report. If the property sheet is not displayed then click on the Property Sheet tool bar button to display it.

2. Select the Data Tab on the Property Sheet.
3. Click on the Record Source property and click on the drop-down list at the right-end of the property.
4. Find Customer ListQ Query (use the slider, if necessary) and select it from the drop-down list, to insert it into the Record Source property.
5. Click on Group & Sort Toolbar button from Group & Totals Group under Design Menu, if it is not already in selected state, to display the Group and Sort controls under the Footer of the Report.
6. Click on the Add a Group control displayed in the Group, Sort and Total shown below the empty report.
7. Click on the Alpha column name displayed from the Query columns list.

You can see the Alpha Group Header is now appearing between Page Header and Detail Sections of the empty report.

We must sort the customer names appearing under a particular character group (say A,B,C... group) so that they will appear in proper order as well.

Note: We have sorted the data in the Query by defining ORDER BY clause, but we must define Grouping and Sorting on the Report also to make them appear in proper order on the Report as well.

8. Click on Add a Sort control and select CName from the list.

Now, let us create the Heading of the Report, Group Heading (A, B, C... etc.) and customer names list to appear under each group.

9. Click on Label control to select it and draw a rectangle, wide enough to write the heading text "CUSTOMER LIST", select Bold and Italic formatting styles and change the font size to 16.
10. Select the Text Box control and draw a text box on the Alpha Header Section of the report.
11. Click on the Data Tab on the Property Sheet and select Alpha from the Control Source drop-down list. Change the font-style to Bold and character size to 16.
12. Select the child lable of the text box and delete it.
13. Create another text box in the Detail Section of the Report, below the Alpha Header control.
14. Select CName column name from the drop-down list in the Control Source property under Data Tab on the property sheet of the text control.
15. Reduce the Detail Sectoin height by dragging the Page-Footer section bar up to touch the text box's edge.
16. Save the report with the name: Customer List.

#### Print Preview the Report.

17. Open the Customer List report in print preview and see how it looks.

If the Heading, Group heading and customer list are not properly aligned to the left in your report, as shown in the first image on the top, try to align all the controls to the left.

Share:

### Introduction.

Over a period of time the Master Table of our database can grow into large volume, having thousands or millions of records. Maximum logical size of an Ms-Access Database is  only 2GB.  Everyday Repairing and Compacting may not improve the performance of the database. Processing time of reports or running time of Queries will  keep on increasing.

Majority of old records in the main table may not involve in daily or monthly querying or report preparing processes.  But, old records may involve in year-end processes, like history analysis, next year business target setting and so on.

Normally, old records of master table are set with a flag indicating as archived and retained in the master table itself.  Active records are filtered from the master table for reports or querying to monitor the current activities of business functions.  The filtering and sorting process may take more time, every time, due to large volume of data in the main table.

### Maintaining Data of Earlier Period.

Removing the old data from the main table and keeping them in a separate database will improve the active database’s performance.  The old data in archive.accdb (a name for reference) can be easily made available to the Front-End database for year-end processes.

You don't need to link and keep the archived table to the Front-End database permanently. You can combine the archived data with the active master table in a Union Query, by using direct references to the archived table, rather than keeping it linked to the FE database.  The Union Query having combined data of both the tables can be used as source for year-end processes.  You can find more details on Union Queries here.

But first, let us see, how we can safely transfer the old data from the master table (tblMaster)  to a new archive database: archive.accdb. Here, we assume that we have a Front-End, Back-End configuration of databases.

### The Prelude of our Action Plan.

We need the following steps to complete the process:

1. Create a new Access Database: archive.accdb in location C:\mdbs or in a location of your preference.

Note:If you are connected to a common Network Server then create a folder there/use an existing folder, where you have access-rights, and save archive.accdb there. The database will be backed-up regularly by network administrators and will be safe for recovery, if need arises. Click on the link to learn more about Database Daily Backup.

2. Close archive.accdb database.
3. Open the Back-End Database.
4. Transfer only the Structure of tblMaster into archive.accdb
5. Create a SELECT Query on tblMaster with appropriate criterion to select the old data.
6. Open the Select Query in Datasheet View, take the total record count and note it down.
7. Change the SELECT query into an Append Query.
8. Save and run Append Query to transfer the selected data directly to the archive.accdb database into table tblMaster.
9. Close BE database and open archive.accdb database.
10. Open tblMaster in archive database and check the count of records matches with the count taken earlier. If not, investigate the cause and redo from step 5 onwards, after deleting the wrong data in tblMaster of archive.accdb.
11. Close the archive.accdb and open BE database.
12. Create a Delete Query using tblMaster with the same Criteria you have used in the Append Query.
13. Open the Delete Query in Datasheet View and take the count of records and ensure that it matches with the count you have taken earlier.
14. Run the Delete Query to remove the records from tblMaster table from BE database.
15. Run Repair and Compact option to reduce the database size.
16. Close BE database and open FE database.

### Linking Old Data to Front-End (FE) Database.

17. Create a Union Query to combine data from tblMaster in BE and from tblMaster in Archive.accdb.

### Let us Execute the above Defined Plan.

Step-1 to step-3 are self explanatory.

In step 4: Right-click on tblMaster to display a shortcut menu.

• High-light Export option and select Access Database from the displayed menu.
• Click on Browse button and select the archive.accdb database and click Save to come back to the Export dialog box.
• Click OK to open up the Export Options dialog box.
• Select Definition Only option to transfer the tblMaster Table Structure into Archive.accdb.

Step-5: sample select Query Code:

```SELECT tblMaster.*
FROM tblMaster
WHERE (((tblMaster.mstDate)<Dateserial(1981,1,1)));
```

The above criteria will select all the records of 1980 and earlier period.

Step-6 is self explanatory.

Step-7: Open the Query, created in step-5, in design view.

• Click on the Append Query button on the Tool bar.
• Select tblMaster from the Table Name drop-down control on the dialog box.
• Select Another Database Radio Button.
• Click on Browse… Command Button to find the archive.accdb database, select it and click OK to come back to the dialog box in Query Design View.
• Click OK on the dialog box to change the Select Query to Append Query. Sample append query SQL is given below for reference.
```INSERT INTO tblMaster IN 'C:\mdbs\archive.accdb'
SELECT tblMaster.*
FROM tblMaster
WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));
```
• Open Append Query in Datasheet View and check the record count with the count you have taken earlier.
• If both count matches then save the Append Query.

Step-8: Right-Click on the Append Query and select Open to run the Query to extract selected data from tblMaster table and to append them in the archive.accdb tblMaster table.

• Click Yes Command Button, on the warning message control, to reconfirm the action.

Step-9 to Step-11: Self explanatory.

Step-12:  Sample Delete Query SQL is given below:

```DELETE tblMaster.*, tblMaster.mstDate
FROM tblMaster
WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));
```

Step-13 to Step-16: Self explanatory.

Step-17: Sample Union Query SQL is given below:

```SELECT tblMaster.*
FROM tblMaster
UNION ALL SELECT tblMaster.*
FROM tblMaster in 'C:\mdbs\archive.accdb';
```

Save the Union Query with the name tblMasterQ. Use tblMasterQ as Source Data for all year-end processes or wherever you need all the data together.  For other purposes your database will run faster.

You can continue to transfer data, when they become old, into the archive.accdb and delete them from the BE database.  No other change required anywhere.

Share:

## Translate

Delivered by FeedBurner

## Featured Post

### Word Mail-Merge with Ms-Access Table

Introduction. Form Letters. Form Letters are prepared from a standard Microsoft Word template merged with addressee related information fill...