LEARN MS-ACCESS TIPS AND TRICKS: msaccess controls

Showing posts with label msaccess controls. Show all posts

Limit to List Combo Box

a.p.r. pillaiJune 28, 2010Combo Boxes, msaccess controls 5 comments

Introduction.

Combo Boxes on Tables/Forms are for inserting frequently used common values quickly and easily into Data Fields. The Source Data Values of the Combo Box come from Table, Query, or from Value List. The User is expected to click on the Drop Down control of the Combo Box to display the items and select the required one to insert into the data field. The User can type Values directly into the Source Control of the Combo Box too.

But, the first Property setting out of the following two Property settings of Combo Box allows the user to select items from the existing list only and prevents from typing invalid values directly into the Target Field:

Limit to List = Yes
On Not in List = [Event Procedure]

When the Limit to List Property Value is set to Yes, you must select/type values available in the Combo Box list only and other values keyed in manually are not accepted in the Target Field. You must add new items in the Source Table of the Combo Box Control first before they can be used for the Combo Box.

For example, assume that you have a Table with a list of Fruits (only two items on the list now: Apple and Cherry) and you are using this list in a Combo Box on the Sales Form. When the Limit to List Property Value is set to Yes; you will not be allowed to enter the value Orange into the Target Field of the Combo Box.

The On-Not-in-List Event.

When the On Not in List Property is set to an Event Procedure; it is executed when the user enters a new value (Orange) manually into the Control-Source Field of the Combo Box. We can write code in the Event Procedure to add the entered new value into the Combo Box Source Table directly (after taking confirmation from the User) and update the Combo Box on the Form.

This method can save time otherwise needed for opening and adding new items in the Combo Box source Table manually. Besides that adding new values manually in the Source Table will not automatically refresh the Combo Box contents.

Let us try this out using the above example items as Source Data.

Combo Box Row Source Table.

Create a new Table with a single Field Name: Fruit and select the Data Type Text.
Save the Table Structure and name it Fruitlist.
Open the Table in Datasheet View and key in Apple and Cherry as two records.
Close and Save the Table with the records.

Create another table with the following Structure:

**Table Structure**
Field Name	Data Type	Size
ID	AutoNumber
Description	Text	50
Quantity	Numeric	Long Integer
UnitPrice	Numeric	Double

Before saving the Structure click on the second Field Data Type (Text) Column to select it.
Click on the Lookup Tab on the Property Sheet below.

Combo Box Property Settings.
Click on the Display Control Property and select Combo Box from the drop-down control.
The Row Source Type Property Value will be Table/Query, if it is not, then select it from the drop-down control.
Click on the drop-down control of the Row Source Property and select the Table Fruit list from the displayed list of Tables.
Change Column Width Property and List Width Property Values to 1".
Change the Limit to List Property Value to Yes.
Save the Table Structure with the name Sales.
Open the Table in Datasheet View and add a new record with Apple, 100, and 1.5 in Description, Quantity, and UnitPrice Fields respectively.
Close and save the Table with the record.
Click on the Sales Table to select it and select Form from Insert Menu.
Create a Form using the Form Wizard in Column Format and save the Form with the name Sales.

Testing Settings.
Open the Sales Form in the normal view.

Since we have added the Combo Box on the Table Structure it already appears on the form.
Press Ctrl++ (or click on the New Record control on the Record Navigation control) to add a new blank record on the Form.
Click on the drop-down control of the Combo Box and you will find the list of fruits: Apple and Cherry in it.
But, you Key-in Orange into the Description field and press Enter Key.
You will be greeted with the following error message:
If you want to enter the value Orange on the Form, first you must add that item to the Fruit list Table.
Open the Fruit list Table, and add Orange as a new record and close the Table.

But, this action will not refresh the Combo Box contents automatically to add Orange to the list. You have to close the Sales form and open it again before you are able to select Orange from the list. Or you must add a Command Button on the Form and write Code for requery the Combo Box contents.

What we did manually in response to the above error message can be automated by writing a VBA Routine that can be run through the On Not in List Event Procedure. You don't need to close and open the Form to refresh the Combo Box contents either.

Add New Item through VBA

Open the Sales Form in Design View.
Click on the Description Field to select the Combo Box control.
Display the Property Sheet (View - -> Properties).
Find and click on the On Not in List Property.
Select Event Procedure from the drop-down list.
Click on the build button (. . .) To open the VBA Module.

Copy and paste the following Code into the Module overwriting the top and bottom Procedure lines already appearing in the Module:

Private Sub Description_NotInList(NewData As String, Response As Integer)
Dim strmsg As String, rst As Recordset, db As Database

If Response Then
    strmsg = "Entered Item not in List!" & vbCr & vbCr & "Add to List...?"
      If MsgBox(strmsg, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in List") = vbYes Then
       Set db = CurrentDb
       Set rst = db.OpenRecordset("FruitList", dbOpenDynaset)
       rst.AddNew
       rst![Fruit] = NewData
       rst.Update
       rst.Close
       Me.Description.Undo
       Me.Description.Requery
       Me![Description] = NewData
       Me.Refresh
    End If
    Response = 0
End If
End Sub

Save and Close the Sales Form.

Trial Run Program.
Open it in a normal view.
Now, type the name of any fruit that is not in the Combo Box list (say Peach) in the Description field.

You will be greeted with the following Message Box:
Click the Command Button with the Label, Yes to add the new item keyed in the Description Field into the Fruit List Table and refresh the Combo Box List automatically.
Now, click on the drop-down control of the Combo Box and you can see that the new item is added to the list and accepted in the Description Field as well.

Menus with Option Group Control

a.p.r. pillaiJune 10, 2010Forms, Menus and Toolbars, msaccess controls 2 comments

Introduction

We can create cascading Menus with Tab Control and Options Group Controls on Form. Several Menus can be arranged neatly one behind the other and allow the user to make the one he/she would like to see by selecting the Main Menu Option.

For example, the sample image given below shows the Main Menu with three Options, each representing a different category, and a Group Sub-Menu Options.

When the Data Files Option is selected in the Main Menu, the Sub-Menu at the right displays its corresponding Options. The User can click on any one of the options shown on the right to open and work with that file.

When the user selects the Option Reports in the Main Menu; Report Options will appear in the same place replacing the Data Files Options displayed earlier. The Main Menu Option Views will bring up its Sub-Menu Options replacing the earlier display. This way several Menus can be arranged and displayed in the same place with a magical touch and can be Programmed with VBA or Macros to run the detail options.

Simple Interface Design and Code

You don't need to work with any complicated VBA Programs except a few simple lines of Code and Macros. The design task is very simple and once you know the trick you can implement it anywhere in no time.

The sample Design image of the above Form is given below:

Open a new Form in the Design view.
Select the Option Group Control from the Toolbox and draw it near the left side of the Form in the Detail Section.
Enter the three Options (Data Files, Reports, and Views) pressing Tab Key in each step to advance to the next line in the Wizard.
Click Finish to create the Option Group Control with Radio Button Type Controls with the Keyed-in Values as Labels.
Change the Caption Value of the attached Child-Label as Main Menu and position it on top of the Options Group control as shown on the design above.
Click on the outer frame of the Options Group Control to select it and display its Property Sheet (View - -> Properties).
Change the Name Property Value to Frame0 and the Border color Property Value to 0.
Select the Tab Control from the Toolbox and draw a Tab Control to the right of the Options Group Control (check the design image above).
A Tab Control with two Pages will be created. We must insert one more Page into the Tab Control.
While the Tab Control is still in the selected state (if it is not, then click on the right of the Tab Pages) Right-Click on it to display the Shortcut Menu.
Select Insert Page from the Shortcut Menu to add another Page to the Tab Control.
While the Tab Control is still in the selected state display its Property Sheet.
Change the Name Property Value to TabCtl9.

NB: No dot (.) at the end of the name when you change it on the control.
Data Tables Menu.
Click on the First Page of the Tab Control to make it current.
Select Option Group Control from the Toolbox and draw it on the First Page of the Tab Control.
Enter the following Options (or Form Names of your own Tables in your Database) by pressing Tab Key after each option on the Wizard:
- Employees
- Orders
- Order Details
- Customers
- Products
Click Finish to complete and create an Option Group with Radio Button Style options.
Display the Property Sheet of the Options Group (View - ->Properties).
Change the following Property Values as shown below:
- Name = Frame1
- Default Value = 0
- Border Color = 0
Change the Caption of the Child-Label attached to the Options Group Control to Data Files, make its width as wide as the Option Group Control, and position it above, as shown in the design image above.

We must create two more Option Group Controls on the 2nd and 3rd Pages of the Tab Control with a different set of Options.
The Reports Menu.
Follow Step-13 to 19 to create Option Group Control on the 2nd Page of the Tab Control with the following options and name the Option Group Frame as Frame2 and the Child-Label Caption as Report List:
- Employee Address Book
- Employee Phone Book
- Invoice
- Monthly Report
- Quarterly Report
You may create Report Names from your own Database replacing the above List.
Data View Menu.
Create another Option Group Control on the 3rd Page of the Tab Control with the following options or create your own Options and name the Option Group as Frame3 and Child-Label Caption as View Options:
- View Inventory
- View Orders
- View Customers
- View Suppliers
Now, we have to write a few lines of VBA Code for the Main Menu Option Group to select the detailed Options Page of the Tab Control based on the menu selection. Even though Page Captions show something like Page10, Page11, and Page12 (this may be different on your design) each Page is indexed as 0, 1, and 2. If you want to select the second Page of the Tab Control to display the Report Options, then you must address the Tab Control Page2 in Code as TabCtl9.Pages(1).Setfocus.
We can select an individual Page of the Tab Control by clicking on it too. But, this manual action will not synchronize with the Main Menu selection. The items on the Option Group Menu also have the index numbers 1 to the number of items on the Menu (Report List options 1 to 5).
When the user clicks on one of the items on the Option Group Main Menu we can test its index number and make its corresponding detailed menu on the Tab Control Page current.
In the final refinement of the Menus, we will hide the Tab Pages of the Tab Control so that the Sub-Menus on them can be accessed only through the program, depending on the selection made on the Main Menu by the User.

Code for Main Menu.
First, let us write a small VBA Routine on the On Click Event Procedure of the Frame0 Option Group Control (Main Menu) to allow the user to select one of the options on it and display its corresponding detailed Sub-Menu on the Tab Control. By default 1st item (Data Files) on the Main Menu will be in the selected state and the Data Files list will be visible on the Sub-Menu.
Display the Code Module of the Form (View - -> Code) or click on the Module Icon on the Toolbar Button.

Copy and paste the following VBA Code into the Module:

Private Sub Frame0_Click()
Dim k
k = Me![Frame0]
Select Case k
    Case 1
        Me.TabCtl9.Pages(0).SetFocus
    Case 2
        Me.TabCtl9.Pages(1).SetFocus
    Case 3
        Me.TabCtl9.Pages(2).SetFocus
End Select

End Sub

Trial Run of Menu.

Save and close the Form with the name Main Switchboard.
Open the Main Switchboard in a normal view.
Click on the 2nd Option Reports on the Main Menu to display the Report List on the 2nd Page of the Tab Control.
Try selecting other options on the Main Menu and watch the sub-menu changes on the Tab Control Pages.

Forms Menu.

Now, we will write VBA Code like the above example to open Data File Forms, when the User select Options from the Sub-Menu.

Open the Main Switchboard in Design View.
Display the Code Module of the Form (View - ->Code).

Copy and Paste the following VBA Code into an empty area of the Module:

Private Sub Frame1_Click()
Dim f1
f1 = Me![Frame1]
Select Case f1
    Case 1
        DoCmd.OpenForm "Employees", acNormal
    Case 2
        DoCmd.OpenForm "Orders", acNormal
    Case 3
        DoCmd.OpenForm "Order Details", acNormal
    Case 4
        DoCmd.OpenForm "Customers", acNormal
    Case 5
        DoCmd.OpenForm "Products", acNormal
End Sub

Save and Close the Main Switchboard Form.

Macros for Report Menu.
For running the Report Options we will create a Macro and attach it to the Options Group Control (with the name Frame2) rather than using the VBA routine.
Select the Macro tab in the Database window and select New to open a new Macro in the design view.
You must display the Condition Column of the Macro by selecting the Toolbar Button with the Icon Image (or similar image) given below:
Write the following Macro lines, as shown in the image given below, with the appropriate Parameter Values at the bottom Property Sheet for opening each Report in Print Preview/Print:
Save the Macro with the name RptMac.

Attach Macro to Report Options.
Open the Main Switchboard Form.
Click on the 2nd Page of the Tab Control to display the Reports Option Group Menu.
Click on the outer frame of the Options Group Menu to select it.
Display the Property Sheet (View - ->Properties).
Find and click on the On Click Property to select it.
Click on the drop-down list at the right edge of the Property and select the RptMac name from the list to insert it into the On-Click Event Property.
NB: You may create another Macro/VBA Routine for the third menu and attach it to the Frame3 Option Group Menu, before doing the next step.
In the next step, we are going to remove the Pages of the Tab Control, so that the transition of the Tab Pages through Code gives a magical touch to the Sub-Menu as different Menus will appear in the same place inter-changeably.
You can further refine the Sub-Menus by changing the dimension and position, by changing the following values same on all the three Sub-Menus on the Tab Control pages.
- Top
- Left
- Width
- height
Click on the outer edge of the Tab Control (or click on the right side of the third page) to select it.
Display the Property Sheet (View - ->Properties).
Find the Style Property in the Property Sheet and change the value Tabs to None.
Save and close the Main Switchboard Form.
Open the Form in normal view and try out the Menu.

Microsoft Date Time Picker Control

a.p.r. pillaiFebruary 14, 2010Forms, msaccess controls 4 comments

Introduction

We have learned how to use Calendar Control for easy entry of Date Values into Fields on Form through the earlier Article: Animated Floating Calendar. We have used a single Calendar Control for several Date Fields by moving the Calendar Control automatically near the selected Date Field with unfolding animation.

We have used this method to save space on the Form otherwise we need to place several Calendar Controls linked to each date field on the Form.

Now, we have a better ActiveX Control Microsoft Date Time Picker - a Calendar Control that almost looks like a Combo Box on Form, and is very easy to use.

Learn its Simple Usage.

Let us get into a simple example to learn how to use this Calendar for a Date Field on a Form.

Import the following Objects from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database:
- Table: Employees
- Form: Employees
Open the Employees Form in Design View.
Select the Company Info Tab.
Select the ActiveX Control option from the Insert Menu.
Select Microsoft Date and Timer Picker Control from the displayed list and click OK to create a control on the Form.
Move the control near the HireDate Field and resize it as shown on the sample design of the Form given below:

Few Simple Rules of Date/Time Picker Control.
We must know a few simple rules that go with this Control to use it with Date Fields.
- We can set the Control Source Property of Date Time Picker to HireDate and remove the existing HireDate Field.
- The Hire Date field cannot be blank. When you make a record current with HireDate Field blank or attempt to add a new record the Date Time Picker will show an Error Message: Can't set the value to NULL when CheckBox Property=FALSE.
The Error message indicates that the HireDate field cannot be left blank or you cannot remove Date Value from a field and make it blank unless you set the CheckBox Property of the Date and Time Picker Control to Yes.
Important Settings.
So, first thing first, ensure that the Date Time Picker Control you have added to the Form is in the selected state or click to select it, display the Property Sheet (View - -> Properties or Alt+Enter) of the Calendar Control and change the Control Source Property Value to HireDate and change the CheckBox Property Value to Yes.
.
- When you move to a particular record the Calendar will automatically move to the date earlier recorded in the HireDate Field. If you want to change the existing HireDate then move the Calendar to the desired Year and Month and click on the required date.
- When you move to a record with HireDate Field value NULL the Check-Mark disappears and the HireDate field looks disabled, indicating that it is empty, but the field shows the date value of the last record accessed. If you click on the drop-down control of the Calendar to open it, then the date value, shown from the last accessed record will be entered into the HireDate field immediately, whether you want it or not. You can remove the Check Mark to empty the field or select the correct date from the Calendar to overwrite the wrong value.
- If you prefer to set Date by incrementing or decrementing individual segment (day/month/year) of the Date value, like you do with a Digital Clock, then you may change the UpDown Property value to Yes. This will change the Drop-down control of the Calendar to a Spin Button Control and the normal Calendar View will not be available.
- You may click on the Day Segment of the Date value and use the Spin Button Control of the Date Time Picker to increase or decrease the value to the desired level. Use the same method for Month and Year Values.
You may experiment with the Date Time Picker Control, keeping those points I have mentioned above, to understand the behavior of the Calendar control better.

Settings for Time Value.
If you want to enter Time rather than Date into a field from the Date Time Picker then change the Format Property Value to 2. This will automatically change the UpDown Control to Yes and the Spin Button control appears in place of the drop-down control, these can be used to set each segment of hh:mm:ss AM/PM of the Time Value individually as explained above.

Web Browsing within Access Form

a.p.r. pillaiDecember 28, 2009Forms, msaccess controls 2 comments

Introduction

Browsing the World Wide Web is nothing new to us. But, how about organizing the Web Addresses of frequently visiting Websites in a Table and browsing the Web from within an Access Form?

Not only Internet Sites, but you can also browse the Intranet Website within your Local Area Network (Corporate LAN) too. All you have to do is to create an Access Form with a Microsoft Web Browser Control and a few lines of VBA Code.

For example, the following VBA Code will open the Gmail Web Site http://www.gmail.com/ automatically when you open the Form with the Web Browser Control, as shown in the above image.:

Simple Website Opening Code

Private Sub Form_Load()
Dim strURL As String

strURL = "http://www.gmail.com/"
Me.WebBrowser0.Navigate strURL
End Sub

Designing a Form for Web ActiveX Control

Don't know how to do it? Try the following:

Open a new Form in Design View.
Click somewhere on the Detail Section of the Form to select that area.
Select the ActiveX Control from Insert Menu.
Look for the name Microsoft Web Browser in the displayed list of ActiveX Controls and select it.
Click OK to insert a Web Browser Control on the Form.
While the Control is still in the selected state drag the right-bottom-corner sizing control to make it big enough on the Form so that you can view the Web Pages properly.
Display the Property Sheet of the Browser Control (View - ->Properties).
Change the Name Property Value to WebBrowserO to match with the name used in the above VBA Code.
Select Code from View Menu to display the VBA Code Module of the Form.
Copy and Paste the above Code into the Module.
Save and Close the Form with the name myWebBrowser.

Demo Run of the Form
Connect your PC to the Internet.
Open myWebBrowser Form in normal View.
Wait for a few seconds to load the Web Page into the Control and to display.

We have used the Website Address directly in the Code. But, if you create a table with the list of all the Website addresses that you visit very often then with a Combo Box on the Form we can select the Web address to go to that site quickly. We need to make a few changes to the above code to make it flexible.

Table with Frequently Visiting Web Addresses

Create a Table with a single field with the following details:
Table Name: WebSites
Field Name: Web Address Data Type: Text Field Size: 255
Save the Table Structure and open it in Datasheet View.
Add a few records with Web URLs that you visit frequently, and save and close the Table.
Make a copy of the Form myWebBrowser and name the copy as myWebBrowser2.
Open myWebBrowser2 in Design View.
Display the Form Header Section (View - -> Form Header/Footer), if it is not already Visible.
Expand the Form Header Section with enough height to create a Combo Box Control.
Create a Combo Box using the Web Sites Table.
While the Combo Box is still in the selected state display the Property Sheet (View - -> Properties).
Change the following Property Values of the Combo Box:
- Name : cboWeb
- Width : 5"
- Default Value: "http://www.gmail.com/" or any other Website Address Value you prefer.
Select the child label attached to the Combo box and change the Caption Property Value to Web Address:
Display the Code Module of the Form (View - -> Code).

Copy and Paste the following Code into the Module, replacing the existing lines.

Form Module Code

Option Compare Database
Option Explicit

Dim WebObj As WebBrowser
Dim varURL As Variant

Private Sub cboWeb_Click()
     GotoSite
End Sub

Private Sub cboWebLostFocus()
     GotoSite
End Sub

Private Sub Form_Load()
     Set WebObj = Me.WebBrowser0.Object
     GotoSite
End Sub

Private Function GotoSite()
    varURL = Me!cboWeb
If Len(Nz(varURL, "")) = 0 Then
    Exit Function
End If

WebObj.Navigate varURL

End Function

Save the Form and open it in Normal View.
The website address that you have inserted into the Default Value Property of the Combo Box will open up in the Browser Control.
Select one of the other website addresses you have added to the Table from the Combo Box.
The Browser Window will open the new Website.

If we open other Web Pages by clicking on the Links from the displayed page, then we can navigate (go Back or Forward) between pages by adding a few more lines of code.

Review of the VBA Code.

But, first, let us have a look at the above code to see what is happening there. We have declared a Web Browser Object Variable and a Variant Variable at the Global declaration area of the Module.

Created a separate Function GotoSite() to respond to different Events (Actions) on the Form without duplicating the code everywhere.

For example: when we open the Form the GotoSite() Function opens the Default Value URL, which we set in the Combo Box Property, through the Form_Load() Event Procedure.

When you select a URL from the Combo Box the cboWeb_Click() Event Procedure calls this Function to open the selected Web Page.

If you type a URL in the Web Address control cboWeb and move the Cursor out of the control the Lost_Focus() Event Procedure runs the Function to open the URL you typed in the Address Control.

We will create a few Command Buttons on the Header of the Form, as shown to the right of the Combo box Control on the above design, and write Subroutines with some simple Browser Object Commands to navigate between Web Pages, which we may open from the displayed Web Pages.

Web Navigation Controls

Open myWebBrowser2 Form in Design View.
Display the Toolbox (if it is not visible then select Toolbox from View Menu.
Create five Command Buttons to the right of the Combo Box.
Display the Property Sheet of the first Command Button and change the Name Property and Caption Property Values to Home.
Similarly, change the Name and Caption Property Values of the other Command Buttons with the values given below.
- Back
- Forward
- Refresh
- Stop
Display the Code Module of the Form (View - ->Code), copy and paste the following code into the VBA Module, below the existing Code lines.

Private Sub Home_Click()
On Error Resume Next
    WebObj.GoHome
End Sub

Private Sub Back_Click()
On Error Resume Next
    WebObj.GoBack
End Sub

Private Sub Forward_Click()
On Error Resume Next
    WebObj.GoForward
End Sub

Private Sub Refresh_Click()
On Error Resume Next
    WebObj.Refresh
End Sub

Private Sub Stop_Click()
On Error Resume Next
     WebObj.Stop
End Sub

The On Error Resume Next Statement prevents the Subroutines from running into Error when there are no Web Pages to Navigate to.

To test these Buttons you must click on a few links on the displayed Web Page to open other Pages from the same website and then use these buttons to navigate between those opened pages.

The Home Button will open the Default Home page you set in the Internet Explorer's Tools - -> Internet Options. . . Home Page - -> Address Control, not the default value you set in the Combo Box.

Form Bookmarks And Data Editing-3

a.p.r. pillaiNovember 09, 2009Forms, msaccess controls No comments

Continued on Form Bookmarks.

This is the continuation of our discussion on the usage of Form Bookmarks to revisit the records which we have already visited earlier. The links to the earlier Articles are given below:

The discussion on Bookmarks is not complete without touching on the subject of Searching and Finding records and showing them as current-record on the Form using RecordsetClone.

In our earlier examples, we have used the Find Control (Ctrl+F or Edit - ->Find. . .) to search for a record and when found save its Bookmark in an Array Variable in Memory for later use.

This time we will use a different method to find the record and bring it to the Form with the use of the Bookmark of the RecordsetClone of the Form.

For this method, we will use an Unbound Text Box to enter the search key value and a Command Button to click and find the record. To keep the VBA Code simple this time we will use the Customers table rather than the Order Details table because the Order Details Table has several records with the same OrderIDs.

If we use the Order Details table then we have to combine the ProductID with OrderID to form a unique value to retrieve a specific record among several records with the same OrderIDs. This method we have already used in the earlier example is to select and display all the records we have retrieved and edited.

Review of Bookmarks.

As I have pointed out in the earlier Articles when we open a Form with a Table, Query, or SQL Statement each record on the Form is marked by MS-Access with a unique identification tag known as Bookmark. We can create a copy of this Recordset into memory (RecordsetClone) and work with it independently. Using the RecordsetClone, with the Form Bookmark attached to each record, we can find the required record with VBA Code using the search Key Value. Once we find the target record in memory we can read that record's Bookmark and insert it into the Form's Bookmark Property to make that record current on the Form.

But remember, you cannot read the Form's Bookmark Property Value and insert it into the RecordsetClone to find the same record in memory.

The Ease of Usage.

From the User's Point of View, all she has to do is to enter the Search Key-Value (CustomerID) into the Unbound Text Box and click the Command Button next to it to find that record and bring it up on the Form.

Look at the sample VBA Code given below that runs on the Command Button Click (with the Name cmdFind) after setting the Search Key-Value (CustomerID) in an Unbound Text Box with the name xFind.

Private Sub cmdFind_Click() 
Dim m_Find, rst As Recordset
m_Find = Me![xFind]
Set rst = Me.RecordsetClone
rst.FindFirst "CustomerID = '" & [m_Find] & "'"
If Not rst.NoMatch Then 
     Me.Bookmark = rst.Bookmark 
End If

End Sub

The line that reads Set rst = Me.RecordsetClone copies the Form's Recordset into the Recordset Object Variable rst in Memory and the next line runs the FindFirst method of the Recordset Object to search and find the record with the given CustomerID Value.

In the next three lines, we are testing whether the rst.FindFirst method was successful in finding the record or not. If found then the Bookmark of the current record in the Recordset is transferred to the Bookmark Property of the Form to make that record Current on the Form.

There is an article on this method posted a few months back with the Title: Animating Label on Search Success. You may visit that Page to copy the complete VBA Code of the Sub-Routine given above and try them out.

You must import the Customers Table and Customers Form from C:\Program Files\Microsoft Office\Office11\Northwind.mdb sample Database and modify the Form to add a Text Box and a Command Button at the Footer of the Form.

When the rst.FindFirst method finds the record and makes it current; a Label at the bottom of the Unbound Text Box will flash a few times with a message indicating that the search operation was successful and that record is made Current on the Form. If the search operation failed then the Label will flash a few times with the message: Sorry, not found.

This method added to the above program gives the User a quick indication of whether the search was successful or not. To go to the Page to try out the Program Click here.

Earlier Post Link References:

Form Bookmarks And Data Editing-2

a.p.r. pillaiNovember 02, 2009Forms, msaccess controls No comments

Continued from Bookmarks and Data Editing

In the first part of this Article, we were using the saved Bookmarks to revisit the earlier visited records one by one to take a second look, if it became necessary, to ascertain the accuracy of edited information.

The Function myBookMarks() that we have created for this purpose can be added with one more Option, (along with 1=Save Bookmark, 2=retrieve Bookmarks, 3=initialize Bookmark List) to display all the edited records together in Datasheet View.

But, this method has some side effects, and one must be aware of it to implement some workaround methods in such situations. Here, we will try that with the Order Details Table.

In the last example, we have used the Bookmark Index Number and OrderID number values as a guide to cross-check with the retrieved record.

Several Products can be ordered under the same Purchase Order and all Products under the same Order will bear the same Order IDs too. If OrderIDs are alone used in a Query Criteria to retrieve the records, then all records with the same Order IDs will be displayed, irrespective of which record among them we have visited earlier.

There were no such issues when we were using Bookmarks of each record to find them again and Order IDs were used only as a guide to cross-check the retrieved record's identity.

But here, we are trying to use the Order Id Values saved in the Combo Box List as Criteria in a Query to retrieve all the edited records in one go.

This problem we can overcome if some other unique value, if available, is used in the Combo Box list. Or use one or more field values combined to form a unique value for each record and save it on the Combo Box List along with the Bookmark Index Number. This is what we are going to do now with the 4th Option of myBookMarks() Function.

Unique ID Value(s) as Key.

We will use OrderID with ProductID combined Values and save them in the Combo Box List. The same Product Code will not appear twice under the same Purchase Order. This will ensure that the values saved in the Combo Box are unique.

The idea behind this new method is to create a Dynamic Query using the Values saved in the Combo Box list and open the Query with all the edited records from the Order Detail Table with one click.

In the fourth Option of the Function myBookMarks(), we will build an SQL String using the Values saved in the Combo box as Criteria and modify the SQL string of a SELECT query to retrieve the records. We have to create another Command Button near the << Reset Button to run this Option so that the User can click on it to retrieve all the edited records and display them in Datasheet View at his will.

But, first, let us write the Code Segment that implements this particular Option. We need a few Objects and Variable declarations in the declaration section of the Function.

Dim db as Database, QryDef as Querydef
Dim strSql as String, strSqltmp as String, strCriteria as a String
.
.
.
Select Case ActionCode
.
.
.
Case 1
.
Case 2
.
Case 3
.
Case 4

strSqltmp = "SELECT [Order Details].* "
strSqltmp = strSqltmp & "FROM [Order Details] "
strSqltmp = strSqltmp & "WHERE ((([OrderID]" & "&" & Chr$(34)
strSqltmp = strSqltmp & "-" & Chr$(34) & "&" & "[ProductID]) In ('"

strCriteria = ""
For j = 0 To ArrayIndex -1
   If Len(strCriteria) = 0 Then
   strCriteria = ctrlCombo.Column(1, j)
Else
   strCriteria = strCriteria & "','" & ctrlCombo.Column(1, j)
End If

Next

strCriteria = strCriteria & "')));"

Set db = CurrentDb
Set Qrydef = db.QueryDefs("OrderDetails_Bookmarks")
strSql = strSqltmp & strCriteria
Qrydef.SQL = strSql
db.QueryDefs.Refresh
DoCmd.OpenQuery "OrderDetails_Bookmarks", acViewNormal
End Select

We are creating part of the SQL string that remains constant in the strSqltmp. Extracting the Combo Box 2nd Column Values (combined values of OrderID and ProductID separated with a hyphen character) and building the Criteria part of the Query in the String Variable strCriteria within the For. . .Next Loop. Finally, we are redefining the SQL of the OrderDetails_BookMarks Query before opening it with the extracted Records.

The Combo Box Columns have Zero-based Index Numbers and the second Column's Index number is 1. So the statement strCriteria = strCriteria & "-,'" & ctrlCombo.Column(1, j) takes the second column value OrderID and PrductID combined String value for criteria.

Modified VBA Code.

The modified Code of the myBookMarks() Function with the above Option is given below.

You may Copy the Code and Paste it into the Standard Module, replacing the earlier Code or rename the earlier Function and save this Code separately.

Public Const ArrayRange As Integer = 25
Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer

Public Function myBookMarks(ByVal ActionCode As Integer, ByVal cboBoxName As String, Optional ByVal RecordKeyValue) As String
'-----------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : October-2009
'URL    : www.msaccesstips.com
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------------
'Action Code : 1 - Save Bookmark in Memory
'            : 2 - Retrieve Bookmark and make the record current
'            : 3 - Initialize Bookmark List and ComboBox contents
'            : 4 - Filter Records and display in Datasheet View
'-----------------------------------------------------------------
Dim ctrlCombo As ComboBox, actvForm As Form, bkmk As String
Dim j As Integer, msg As String, bkmkchk As Variant
Dim strRowSource As String, strRStmp As String, matchflag As Integer
Dim msgButton As Integer

Dim db As Database, Qrydef As QueryDef
Dim strSql As String, strSqltmp As String, strCriteria As String

'On Error GoTo myBookMarks_Err

If ActionCode < 1 Or ActionCode > 4 Then
   msg = "Invalid Action Code : " & ActionCode & vbCr & vbCr
   msg = msg & "Valid Values : 1 to 4"
   MsgBox msg, , "myBookMarks"
   Exit Function
End If

Set actvForm = Screen.ActiveForm
Set ctrlCombo = actvForm.Controls(cboBoxName)
Select Case ActionCode
    Case 1
        bkmk = actvForm.Bookmark
        'check for existence of same bookmark in Array
        matchflag = -1
        For j = 1 To ArrayIndex
           matchflag = StrComp(bkmk, bookmarklist(j), vbBinaryCompare)
           If matchflag = 0 Then
               Exit For
           End If
        Next
        If matchflag = 0 Then
           msg = "Bookmark of " & RecordKeyValue & vbCr & vbCr
           msg = msg  & quot;Already Exists."
           MsgBox msg, , "myBookMarks()"
           Exit Function
        End If
        'Save Bookmark in Array
        ArrayIndex = ArrayIndex + 1
        If ArrayIndex > ArrayRange Then
          ArrayIndex = ArrayRange
          MsgBox "Boookmark List Full.", , "myBookMarks()"
          Exit Function
        End If
        bookmarklist(ArrayIndex) = bkmk

        GoSub FormatCombo

        ctrlCombo.RowSource = strRowSource
        ctrlCombo.Requery
    Case 2
        'Retrieve saved Bookmark and make the record current
        j = ctrlCombo.Value
        actvForm.Bookmark = bookmarklist(j)
    Case 3
        'Erase all Bookmarks from Array and
        'Delete the Combobox contents
        msg = "Erase Current Bookmark List...?"
        msgButton = vbYesNo + vbDefaultButton2 + vbQuestion
        If MsgBox(msg, msgButton, "myBookMarks()") = vbNo Then
            Exit Function
        End If
        For j = 1 To ArrayRange
           bookmarklist(j) = ""
        Next
        ctrlCombo.Value = Null
        ctrlCombo.RowSource = ""
        ArrayIndex = 0
    Case 4
        strSqltmp = "SELECT [Order Details].* "
        strSqltmp = strSqltmp & "FROM [Order Details] "
        strSqltmp = strSqltmp & "WHERE ((([OrderID]" & "&" & Chr$(34)
        strSqltmp = strSqltmp & "-" & Chr$(34) & "&" & "[ProductID]) In ('"
        strCriteria = ""
        For j = 0 To ArrayIndex - 1
            If Len(strCriteria) = 0 Then
                strCriteria = ctrlCombo.Column(1, j)
            Else
                strCriteria = strCriteria & "','" & ctrlCombo.Column(1, j)
            End If
        Next
        strCriteria = strCriteria & "')));"
 
       Set db = CurrentDb
        Set Qrydef = db.QueryDefs("OrderDetails_Bookmarks")
        strSql = strSqltmp & strCriteria
        Qrydef.SQL = strSql
        db.QueryDefs.Refresh
        DoCmd.OpenQuery "OrderDetails_Bookmarks", acViewNormal
End Select

myBookMarks_Exit:
Exit Function

FormatCombo:
'format current Bookmark serial number
'and OrderID to display in Combo Box
strRStmp = Chr$(34) & Format(ArrayIndex, "00") & Chr$(34) & ";"
strRStmp = strRStmp & Chr$(34) & RecordKeyValue & Chr$(34)

'get current combobox contents
strRowSource = ctrlCombo.RowSource

'Add the current Bookmark serial number
'and OrderID to the List in Combo Box
If Len(strRowSource) = 0 Then
     strRowSource = strRStmp
Else
     strRowSource = strRowSource & ";" & strRStmp
End If
Return

myBookMarks_Err:
MsgBox Err.Description, , "myBookMarks()"
Resume myBookMarks_Exit
End Function

A Select Query and some Changes in the Form.

You can try out this Option with a few changes to the Form that we created earlier (the Form in the design view is given below) by creating another Command Button and a simple SELECT Query.

First, Create a SELECT Query with the following SQL String and save it with the name OrderDetails_Bookmarks:
```
SELECT [Order Details].* FROM [Order Details];
```
Open the Form Order Details and create a Command Button next to the < Command Button as shown on the Form Design image given below:
Click on the Command Button to select it and display the Property Sheet (View - - > Properties)
Change the Name Property Value to cmdShow and the Caption Property Value to View Records.
Select the On Click Property, select Event Procedure from the drop-down list, and click on the Build (. . .) Button to open the Form's Code Module with the following empty skeleton of Sub-Routine:
```
Private Sub cmdShow_Click()

         End Sub
```
Write the following line in the middle of the Sub-Routine as shown below:
```
Private Sub cmdShow_Click()
    myBookMarks 4, "cboBMList"
End Sub
```
Perform a Trial Run
Save and Close the Order Details Form and open it in Normal View.
Double-Click on the Record Selector of a few records on the Form to add the Bookmark List in the Combo Box.
Click on the drop-down control of the Combo Box to ensure that the selected Item Codes are added to the Combo Box List.
Click on the View Records Command Button to open the Query OrderDetails_Bookmarks in Datasheet View with the records that match with the Combo Box Values.

Check the sample image of the Query result overlapping the Form, displaying all the records that belong to the Combo Box List Values.

The Product Field displays the Product Description rather than the Product Code that appears in the Bookmark Combo Box on the Main Form. The Display Width of the Combo Box in the Product Field is set to 0" to hide the Product Code in the Data View. But when you select an item from this Combo Box the Product Code is stored in the Order Details Table, because that is the Bound Column to the Table. When you double-click on the Record Selector the stored value of ProductID is taken rather than the Product Description, to combine OrderID Value and update the Combo Box List.

Want to find out how to open a Form with the last record that you were working on in the earlier session? Click here.

Want to find out how to use Combo Boxes and List Boxes in different ways? Visit the following Links:

Form Bookmarks and Data Editing

a.p.r. pillaiOctober 26, 2009Forms, msaccess controls 1 comment

Introduction

You want to edit information on 25 records on the Form randomly. You will open the main data editing Form. Search for the record by entering the Key Value to find (say Employee Code or OrderID Value) in the Find (Ctrl+F) control and edit the record when found on the Form. This procedure is repeated for all 25 records for the first time because you don't have any other choice but to find and edit the information.

But, the information you have changed is very critical and any mistakes in them may lead to serious issues. Mistakes can easily creep in when you go through the finding and editing procedure in a hurry. It is important that you should have a second look at each record to verify and ensure the accuracy of changes.

Going through the same procedure to find all those 25 records again by displaying the Find control, keying in the Key Values, and clicking on the Find button to reach the required record is not as enjoyable as it did for the first time.

But, if you can reach all those distant records one by one, in the same order of editing, without going through the above cumbersome procedure, then it will be a great relief, to finish the work faster.

Why I said in the same order during editing because you are holding the source document changes in the same order of your first visit to the records.

We will develop a trick with the Form's Bookmarks to make this kind of work easier for our Application Users.

FORM BOOKMARKS.

When you open a Form attached to a Table, Query, or SQL Statement each record on the Form is marked by MS-Access with a unique identifying tag known as Bookmark (a two Byte string Value). This is happening every time you open the Form with the above record sources. The Bookmarks are valid only in the current session of the Form and not stored in Tables.

We can read the Bookmark of any record from the Form's Bookmark Property, when the Record is Current on the Form, and store it in Variables in memory. Bookmarks which saved this way can be used again to go back quickly to the same record we visited earlier.

I have created a Function with the name myBookMarks() for this purpose and you can implement this method on any Form that has a Recordset attached to it by creating a Combo Box and a Command Button and four lines of code in the VBA Code Module of the Form to run the Function.

You will definitely get a pat on the back from the User of your MS-Access Application for implementing this simple feature. So, let us start with our sample Project.

SAMPLE PROJECT.

Open your VBA Editing Window (Alt+F11).

Create a new Standard Module (Insert - -> Module), Copy and Paste the following Code of myBookMarks() Function into the Module and Save it:

Option Compare Database
Option Explicit

Public Const ArrayRange As Integer = 25
Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer

Public Function myBookMarks(ByVal ActionCode As Integer, ByVal cboBoxName As String, Optional ByVal RecordKeyValue) As String
'-----------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : October-2009
'URL    : www.msaccesstips.com
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------------
'Action Code : 1 - Save Bookmark in Memory
'            : 2 - Retrieve Bookmark and make the record current
'            : 3 - Initialize Bookmark List and ComboBox contents
'-----------------------------------------------------------------
Dim ctrlCombo As ComboBox, actvForm As Form, bkmk As String
Dim j As Integer, msg As String, bkmkchk As Variant
Dim strRowSource As String, strRStmp As String, matchflag As Integer
Dim msgButton As Integer

On Error GoTo myBookMarks_Err

If ActionCode < 1 Or ActionCode > 3 Then
   msg = "Invalid Action Code : " & ActionCode & vbCr & vbCr
   msg = msg & "Valid Values : 1,2 or 3"
   MsgBox msg, , "myBookMarks()"
   Exit Function
End If

Set actvForm = Screen.ActiveForm
Set ctrlCombo = actvForm.Controls(cboBoxName)
Select Case ActionCode
    Case 1
        bkmk = actvForm.Bookmark
        'check for existence of same bookmark in Array
        matchflag = -1
        For j = 1 To ArrayIndex
           matchflag = StrComp(bkmk, bookmarklist(j), vbBinaryCompare)
           If matchflag = 0 Then
               Exit For
           End If
        Next
        If matchflag = 0 Then
           msg = "Bookmark of " & RecordKeyValue & vbCr & vbCr
           msg = msg & "Already Exists. "
           MsgBox msg, , "myBookMarks()"
           Exit Function
        End If
        'Save Bookmark in Array
        ArrayIndex = ArrayIndex + 1
        If ArrayIndex > ArrayRange Then
          ArrayIndex = ArrayRange
          MsgBox "Boookmark List Full. ", , "myBookMarks()"
          Exit Function
        End If
        bookmarklist(ArrayIndex) = bkmk

        GoSub FormatCombo

        ctrlCombo.RowSource = strRowSource
        ctrlCombo.Requery
    Case 2
        'Retrieve saved Bookmark and make the record current
        j = ctrlCombo.Value
        actvForm.Bookmark = bookmarklist(j)
    Case 3
        'Erase all Bookmarks from Array and
        'Delete the Combobox contents
        msg = "Erase Current Bookmark List...? "
        msgButton = vbYesNo + vbDefaultButton2 + vbQuestion
        If MsgBox(msg, msgButton, "myBookMarks()") = vbNo Then
            Exit Function
        End If
        For j = 1 To ArrayRange
           bookmarklist(j) = ""
        Next
        ctrlCombo.Value = Null
        ctrlCombo.RowSource = ""
        ArrayIndex = 0
End Select

myBookMarks_Exit:
Exit Function

FormatCombo:
'format current Bookmark serial number
'and OrderID to display in Combo Box
strRStmp = Chr$(34) & Format(ArrayIndex, "00") & Chr$(34) & ";"
strRStmp = strRStmp & Chr$(34) & RecordKeyValue & Chr$(34)

'get current combobox contents
strRowSource = ctrlCombo.RowSource

'Add the current Bookmark serial number
'and OrderID to the List in Combo Box
If Len(strRowSource) = 0 Then
     strRowSource = strRStmp
Else
     strRowSource = strRowSource & ";" & strRStmp
End If
Return

myBookMarks_Err:
MsgBox Err.Description, , "myBookMarks()"
Resume myBookMarks_Exit
End Function

Import the following Tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample Database:
- Order Details
- Products
NB: We are not using the second table directly, but the Order Details table references the Products Table for its Product Description.

Demo Form Design
Click on the Order Details table to select it.
Select Form from Insert Menu and select AutoForm: Tabular from the displayed list.
MS-Access creates a Tabular Form quickly. Save the Form with the name Order Details.
A sample image of a Tabular Form in Design View is given below.
Open Order Details Form in Design View.
Expand the Form Header area and move the Field Headings down for enough space to create a Combo Box and a Command Button as shown on the sample design above.
Display the ToolBox (View - ->ToolBox), if it is not already visible.
If the Control Wizards Tool Button is already in the selected state, then click on it to de-select it.
Select the Combo Box Tool from the ToolBox and draw a Combo Box in the Header Section of the Form as shown on the design above.
While the Combo Box is still in the selected state; select Properties from the View menu to display the Property Sheet of the Combo Box.
Change the following Property Values as shown below:
- Name = cboBMList
- Row Source Type = Value List
- Column Count = 2
- Column Widths = .5";1"
- Bound Column = 1
- List Rows = 8
- List Width = 1.5"
Change the Caption of the Child Label, attached to the Combo Box, to Bookmark List:.
Create a Command Button on the right side of the Combo Box.
Display the Property Sheet of the Command Button.
Change the following Property Values as shown below:
- Name = cmdReset
- Caption = << Reset
Form Class Module VBA Code.
Display the Code Module of the Form (View - -> Code).

Copy and Paste the following VBA Code into the Module; Save and Close the Form:

Option Compare Database

Private Sub Form_DblClick(Cancel As Integer)
    'Save Current Record's Bookmark in memory
    myBookMarks 1, "cboBMList", Me![OrderID]
End Sub

Private Sub cboBMList_Click()
    'Retrieve the bookmark using the
    'index number from the Combobox List
    'and make the respective record current
    myBookMarks 2, "cboBMList"
End Sub

Private Sub cmdReset_Click()
    'Initialize Bookmarks and Combobox contents
    myBookMarks 3, "cboBMList"
End Sub

Private Sub Form_Unload(Cancel As Integer)    
'Remove all Bookmarks from Memory
    myBookMarks 3, "cboBMList"
End Sub

Perform a Trial Run

Open the Order Details Form in Normal View.
Double-Click on one of the record selectors on the left side of the Form.
Click on the ComboBox Drop-down control to check whether the OrderID value of the Record that you have double-clicked is added into the Combo Box List with a sequence number in the first column or not.
Make a few more double-click on different Record selectors up or down in the form you like.
Check the Combo Box contents again to ensure that all these record references are added to the Combo Box with running serial numbers.

Now, let us check whether we can jump quickly to one of these records visited earlier by using the saved Bookmarks List appearing in the Combo Box.
Click on the drop-down control of the Combo Box, to display the list of Bookmarks and click on one of the items from the list.
The selected Order Id record will become the Current Record on the Form. Even if there are several records with the same OrderID it will correctly pick the record that you visited earlier because we are using Bookmark and not the Find method with the OrderID Value to find the record. If OrderID was used, then it will stop at the first record with the same OrderID numbers, not on the same record you visited earlier.
You may try out other items appearing on the list for now. You may implement this method on Forms with Column Format too.

Important Points to Note.
Here, I would like to remind you that we are saving the List of Bookmarks in the BookMarkList Array in myBookMarks() Function in the Standard Module. The ComboBox list items are added from the bookmark list from the Array. We have dimensioned the Array to hold a total of 25 elements and not all of them are filled in. The index number of the array with bookmarks filled in is added in the first column of the Combobox.
Check the following declarations of the Function in the Global area of the Module:
Public Const ArrayRange As Integer = 25
Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer
You can change the value 25 to a higher or lower desired value according to your specific needs.
Let us continue by adding a few more distant record bookmarks to the existing list.
Scroll down the vertical scrollbar of the Form and double-click on the Record-Selectors for a few more records from the distant area of the Recordset.
Now, try to reach any of these Bookmarks we have added to the list by selecting them one by one from the Combo Box List.
Isn't it very easy to revisit all those records a second time?
If you want to erase all those Bookmarks from the BookmarkList Array in memory click on the << Reset Command Button. After this, you can create a fresh list of Bookmarks.

The OrderID Field Value added to the Combo Box along with the Index Number of the Bookmark Array can be used to cross-check with the retrieved record value to ensure correctness.

Review of VBA Code

Let us look at the Sub-Routines we have copied into the Form Module and check what they are doing.

Private Sub Form_DblClick(Cancel As Integer)

    myBookMarks 1, "cboBMList", Me![OrderID]

End Sub

When you double-click on the Record Selector of a record the above Sub-Routine calls the main Function myBookmarks() with the following parameters:

Action Code: 1 - indicates to fetch the Current Bookmark (a two-byte string value consists of displayable/non-displayable characters) from the Active Form and save it in Memory in BookMarkList Array after incrementing the Array index number in Variable ArrayIndex. The Action Code is tested in the Select Case. . .End Select segment in the myBookMarks() Function. The Bookmark value itself is not displayed anywhere.

Combo Box Name: "cboBMList" - to display the Index Number of the BookmarkList Array in the Combo Box. The Name of the Control is enough to reference it on the Active Form.

Record Field Value: OrderID - to display the Record Field Value in the Combo Box along with the BookMarkList Array Index number. You can use any Field Value of your Table so far as it serves the purpose of checking the correctness of the record retrieved using the Bookmark.

The third parameter of myBookMarks() Function is defined as Optional and is omitted while calling the Functions to retrieve the Bookmark or to erase the Bookmarks List in the following three Sub-Routines respectively:

Private Sub cboBMList_Click()
  myBookMarks 2, "cboBMList"

End Sub

Private Sub cmdReset_Click()
  myBookMarks 3, "cboBMList"

End Sub

'Erases the Bookmarks when the Form is closed

Private Sub Form_Unload(Cancel As Integer)

  'Remove all Bookmarks from Memory

  myBookMarks 3, "cboBMList"

  End Sub

NB: Since the main Function myBookMarks() references the Active Form you can implement this method on any Form without directly passing any Form Name to the Function.

Saving, retrieving, and using Bookmarks for finding records is valid only for the current Form session.
Re-querying the Form's contents (not Refreshing) re-creates the Bookmarks for the Recordset on the form and earlier saved Bookmarks may not be valid after that. You must create a fresh Bookmark List to use correctly.
This method will not work on Forms attached to external Data Sources, linked to Microsoft Access, which doesn't support bookmarks.

Filter Function Output In Listbox-2

a.p.r. pillaiSeptember 25, 2009Functions, List Boxes, msaccess controls 2 comments

Introduction

Last week we saw the usage of the Filter() Function with a simple example and I hope you understood how it works. We have assigned constant values of the Source Array elements directly, to keep the VBA Code as simple as possible.

We can filter data on Forms by setting Criteria on the Filter Property of Forms. Similarly, we can use conditions in Queries to filter information from Tables as well.

But, the Filter() Function gives a unique way of filtering data from an Array of information loaded from Tables or Queries and creates output quickly based on matching or non-matching options.

Let us try out the Filter() Function in an Address Book Application to quickly find persons or places that match the specified search text and display them in a List Box. We will use Names and Addresses from the Employees Table of Northwind.mdb sample database for our experiment.

Following is the User Interface design that we planned to create and explains how the user will interact with it to display information quickly on the Form.

We will design a Form with a List Box, a Text Box Control, a Check-Box Control, and a Command Button for our experiment. An image of such a Form in Design View is given below:

When the Form is open in normal view the List Box and Text Box Controls will be empty. The User can enter the word ALL in the Text Box Control and click the Command Button to display the Name and Addresses of all Employees in the List Box.

The User can enter a word or phrase, like part of a Name or Address, that can match anywhere within the Name and Address text, and click on the Command Button to filter out the matching items and to display them in the List Box.

If the Matching Cases Check-Box is in the selected state, then the Filter action will select records that match with the search text given in the Text Box Control otherwise it will select all records that do not match with the search text.

To provide the User with the above facility, we need two Subroutines on the Form's Code Module and a User Defined Function in the Standard Module of the Database to use the Filter() Function.

When the User opens the above Form the first Sub-Routine is run from the Form_Load() Event Procedure to read the data (First Name, Last Name & Address) from the Employees Table, join all the three field values in a single row of text, and load them into a Singly Dimensioned Array Variable in Memory. This data will remain in memory till the User closes the Form.

The second Sub-Routine is run when the user clicks on the Command Button to extract information from the Source Array with the help of the Filter() function, based on the search text entered into the Text Box Control.

The Filter() Function will extract the entries that match with the search text in the Text Box Control, from the Source Array Variable and save the output into the target variable xTarget. All we have to do is to take these values, format them, and insert them as Row Source Property Value to display them in the List Box.

The Address Book Project.

Let us prepare for the Address Book's Quick Find Project.

The Design Task

Import Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
Open a new Form in Design View.
Select the List Box Control from the ToolBox and draw a List Box as shown on the design above.
While the List Box is in the selected state display the Property Sheet (View - - > Properties) and changes the following Property Values as given below:
- Name = AddBook
- Row Source Type = Value List
- Width = 4.5"
- Height = 1.75"
- Font Name = Courier New
- Font Size = 10
Position the Child Label attached to the List Box above and change the Caption value to Address Book.
Draw a Text Box below the List Box. Change the Name Property value of the TextBox to xFind. Position the Child Label above the Text Box and change the Caption value to Search Text/ALL.
Create a Check-Box Control to the right of the Text Box. Change the Name Property of the Check-Box to MatchFlag. Change the Default Value Property to True. Change the Caption value of the child label of Matching Cases.
Create a Command Button to the right of the Check-Box control. Change the Name Property Value of the Command Button to cmdFilter and the Caption Property Value to Filter.
NB: Ensure that the Name Property Values of the above controls are given exactly as I have mentioned above. This is important because we are referencing these names in Programs.
Display the Code Module of the Form (View - - >Code).

Copy and paste the following Code into the Form Module:

The VBA Code

Option Compare Database
Option Explicit
Dim xSource() As Variant

Private Sub Form_Load()
Dim db As Database, rst As Recordset, J As Integer
Dim FName As String * 12, LName As String * 12, Add As String * 20

'Take the count of records
J = DCount("*", "Employees")

'redimension the array for number of records
ReDim xSource(J) As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenDynaset)
'load the name and addresses into the array
J = 0
Do Until rst.EOF
   FName = rst![FirstName]
   LName = rst![LastName]
   Add = rst![Address]
  xSource(J) = FName & LName & Add
rst.MoveNext
J = J + 1
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Private Sub cmdFilter_Click()
Dim x_Find As String, xlist As String, xTarget As Variant
Dim x_MatchFlag As Boolean, J As Integer

Me.Refresh
x_Find = Nz(Me![xFind], "")
x_MatchFlag = Nz(Me![MatchFlag], 0)

'if no search criteria then exit
If Len(x_Find) = 0 Then
  Exit Sub
End If
    'initialize list box
    xlist = ""
    Me.AddBook.RowSource = xlist
    Me.AddBook.Requery

If UCase(x_Find) = "ALL" Then
    'Take all values from the Source Array
    'Format it as listbox items
    For J = 0 To UBound(xSource())
      xlist = xlist & xSource(J) & ";"
    Next
Else    'Call the Filter Function
    xTarget = GetFiltered(xSource(), x_Find, x_MatchFlag)
    'format the returned values as list box items
    If Len(xTarget(0)) > 0 Then
        For J = 0 To UBound(xTarget)
            xlist = xlist & xTarget(J) & ";"
        Next
    End If
End If
    'remove the semicolon from
    'the end of the list box Value List
    If Len(xlist) > 0 Then
        xlist = Left(xlist, Len(xlist) - 1)
    End If
    'insert the list item string
    'and refresh the list box
    Me.AddBook.RowSource = xlist
    Me.AddBook.Requery

End Sub

Save the Form with the name Filter Form.
Copy and paste the following Function into a Standard Module and save the Module:
```
Public Function GetFiltered(ByRef SourceArray() As Variant, ByVal xFilterText As Variant, ByVal FilterType As Boolean) As Variant
    GetFiltered = Filter(SourceArray, xFilterText, FilterType)
End Function
```
Filter() Function and Filter Property of the Form.
We cannot use the Filter() Function in the Form Module because the function name clashes with the Form Property Filter.
We have inserted the Filter() Function in the Standard Module enveloped in User Defined Function GetFiltered() with the necessary Parameters so that we can call it from the Form Module. The first parameter to the Function is passed By Reference so that it can use the Source Array values directly.
The Demo Runs
Open the Filter Form in normal View.
Enter the word ALL (in this case the Matching Cases flag has no effect) in the Text Box control and Click on the Filter Command Button.
This action will display the Name and Addresses of all Employees from the xSource() Array loaded from the Employees Table.
Enter the text Ave in the Text Box and see that the Matching Cases check box is in the selected state.
Click on the Command Button.
This time you will find that only two Employee (Nancy & Laura) names and addresses are filtered and the word Ave is appearing in their Address Lines.
Remove the check-mark from the Matching Cases check box and click on the Filter Command Button again.

Now, all items except the lines with the word Ave are listed in the List Box.

If you go through the Programs that we have copied into the Form Module you can see that we have declared the xSource() Array Variable in the Global area of the Module so that we can use the data in both Sub-Routines in the Form Module.

In the Form_Load() Event Procedure, we have declared three Variables as fixed-length String Type (see the declaration line given below).

Dim FName As String * 12, LName As String * 12, Add As String * 20

When we read employee Name and Addresses into these Variables the values will be left-justified inside the Variable and the balance area of the declared size will be space filled to the right. This method will space out items at a fixed distance from each other and properly align them when displayed.

It is important that we use a Fixed Width Font, like Courier New, for the List Box display and we set this in Step-4 above.

If you click the Filter Command Button when the TextBox is empty, then the program terminates, otherwise, it calls the GetFiltered() Function and passes the parameter values.

The output Values are returned in the xTarget Array and the next steps format the Value List and display them in the List Box.

Introduction.

The On-Not-in-List Event.

Combo Box Row Source Table.

Combo Box Property Settings.

Testing Settings.

Add New Item through VBA

Trial Run Program.

Introduction

Simple Interface Design and Code

Data Tables Menu.

The Reports Menu.

Data View Menu.

Code for Main Menu.

Trial Run of Menu.

Forms Menu.

Macros for Report Menu.

Attach Macro to Report Options.

Introduction

Learn its Simple Usage.

Few Simple Rules of Date/Time Picker Control.

Important Settings.

Settings for Time Value.

Introduction

Simple Website Opening Code

Designing a Form for Web ActiveX Control

Demo Run of the Form

Table with Frequently Visiting Web Addresses

Form Module Code

Review of the VBA Code.

Web Navigation Controls

Continued on Form Bookmarks.

Review of Bookmarks.

The Ease of Usage.

Earlier Post Link References:

Continued from Bookmarks and Data Editing

Unique ID Value(s) as Key.

Modified VBA Code.

A Select Query and some Changes in the Form.

Perform a Trial Run

Introduction

FORM BOOKMARKS.

SAMPLE PROJECT.

Demo Form Design

Form Class Module VBA Code.

Perform a Trial Run

Important Points to Note.

Review of VBA Code

Introduction

The Address Book Project.

The Design Task

The VBA Code

Filter() Function and Filter Property of the Form.

The Demo Runs

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter