Computer Learning Zone CLZ Access Excel Word Windows

Education is not the piling on of learning, information, data, facts, skills, or abilities... but is rather making visible what is hidden as a seed.

-Thomas Moore
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
Tips & Tricks

New Tips Added Weekly!
Click here to get on our Mailing List


Advanced - Fill a ComboBox

Fill a ComboBox from an Access Database

Connect to an Access Database and fill a combobox or SELECT control.

We are going to use the sample PCResale.NET Db from the Access Courses.

Firstly build a simple HTA like we have done previously.

	<!DOCTYPE html>
	<html lang="en">
	   <title>Connect to Access db</title>
			APPLICATIONNAME = "Fill ComboBox"
			ICON = "599CD.ico"

		<h1>Connect to Access Db and Fill a ComboBox</h1>

We are going to get a list of Customers and place it on the Form.

Add a placeholder SELECT with an ID we can hook into.

	<form name="PCResale" method="post" action="">
		<label for="Customer">Customer</label>	
		<select name="Customer" id="Customer">
			<option value="---Select A Customer---">---Select A Customer---</option>
			<script Language="VBScript">

We then need a script to connect to the Access database and display some information.

Create a Sub to do this. I'm going to call it FillCustomers().

Build up the variables we need, I like to Dim them and then Close them so I don't forget.

	<script language="vbscript">
	Sub FillCustomers()
		'Connection String
		Dim sConnect
		Dim objCmd
		Dim objRS
		'Clean Up and Close
		Set objRS = Nothing
	End Sub

We need a Connection String to talk to Access. In this case I'm connecting to an ".accdb" which is 2007+

It can include the following attributes.

      Data Source
	'Initialize Connection String to connect to an MS Access Database (.accdb)
	sConnect = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=PCResale.accdb;Persist Security Info=False"

If this were an ".mdb" we would need a slightly amended string.

	'Initialize Connection String to connect to an MS Access Database (.mdb)
	sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PCResale.mdb;Persist Security Info=False"

Notice that ACE has switched to JET and the version is 4.0 instead of 12.0

For the Source I'm using a relative reference as the DB is saved in the same folder as the HTA. You would need to put the full path if otherwise.

Next we can create a SQL statement which we can execute to return the Recordset of Customers.

	'Create a command object to execute Advancded SQL Statements
	Set objCmd = CreateObject("ADODB.Command")
	objCmd.ActiveConnection = sConnect    'Connect to database
	objCmd.CommandText = "SELECT CustomerID, FirstName FROM CustomerT;"

Now create a RecordSet

	' Create the actual recordset to be used in this script 
    ' and retrieve or display information from database
    Set objRS = CreateObject("ADODB.Recordset")
    Set objRS = objCmd.Execute

Once we have our Recordset we need to add it to the combo. Lets do this in a simple loop.

	While not objRS.EOF
		Set objOption = document.createElement("option")
		objOption.Text  = objRS("FirstName")
		objOption.Value = objRS("CustomerID")
		document.PCResale.Customers.Add objOption

There are two ways you can access a Field, either by it's index (objRS(0)) or name (objRS("FirstName")).

To fill this value on the page you could either add a button to press or you can use a Load event. I want it to show once the page has loaded.

Index: Index | Lesson:


Alex Hedley (Avatar) By: Alex Hedley

Click here to sign up for more FREE tips



Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn