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
Firstly build a simple HTA like we have done previously.
<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="">
<select name="Customer" id="Customer">
<option value="---Select A Customer---">---Select A Customer---</option>
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
Build up the variables we need, I like to Dim them and then Close them so I don't forget.
'Clean Up and Close
Set objRS = Nothing
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.
'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"
ACE has switched to
JET and the version is
4.0 instead of
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")
There are two ways you can access a Field, either by it's index (
objRS(0)) or name (
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.
By: Alex Hedley
to sign up for more Click here FREE tips