Computer Learning Zone CLZ Access Excel Word Windows

Knowing how to think empowers you far beyond those who know only what to think.

-Neil deGrasse Tyson
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   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




The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
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
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/24/2024 2:52:42 PM. PLT: 0s