Populate combobox from Access db

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Populate combobox from Access db

Logged in as: Guest

arrSession:exec spGetSession 2,2,3583

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Populate combobox from Access db
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Populate combobox from Access db - 6/29/2005 10:15:31 PM

NigeJK

Posts: 16
Score: 0
Joined: 6/27/2005
From:
Status: offline

I am trying populate a combobox with values from an Access db. I dont get any values in my combo box. I have found many examples but all using the server which I want to avoid. Any ideas ?

Sub ActivityLoad()
Dim conn as ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source =C:\BMD\eDatabase.mdb"

Dim strsql as string
strsql = "SELECT DISTINCT Activity.Activity FROM Activity"
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.open strsql, conn

Do While Not rs.EOF
ComboBox.AddItem rs.Fields("Activity") ' Do an AddItem for each record
rs.MoveNext
Loop
rs.Close
conn.Close
End Sub

Post #: 1

Re: Populate combobox from Access db - 6/29/2005 11:14:58 PM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Do you get any error message?

My Access wants to have "conn.open" before "rs.open strsql, conn"

Replace "Combobox.AddItem ..." with "Debug.print rs.Fields("Activity")"
to check the sanity of Access accessing data

Is "ComboBox.AddItem" valid in *your* version of Access?

Check Settings of Combobox: "Daten|Herkunftstyp" (data|sourcetype?) should be "Wertliste"
(list of values?)

(in reply to NigeJK)

Post #: 2

Re: Populate combobox from Access db - 6/29/2005 11:50:02 PM

NigeJK

Posts: 16
Score: 0
Joined: 6/27/2005
From:
Status: offline

I dont get any error when run in IE but when in script editor I get a "class not defined: 'ADODB'" error for the line "set conn = new ADODB.connection"

I have had combobox.Additem running previously when it was running as server code.

The "Debug.print rs.Fields("Activity")" line did not produce anything.

(in reply to NigeJK)

Post #: 3

Re: Populate combobox from Access db - 6/30/2005 12:11:44 AM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

"Dim conn as ADODB.Connection / Dim Var As Type" is VBA code. As far as
I know IE can't execute VBA code. Don't you run your sample code under
Access?

Client script code to test filling of a ComboBox/Select could
look like this:

If you can run this (saved as "activity.html" or "*.hta"),
I promise to add the database related code.

(in reply to NigeJK)

Post #: 4

Re: Populate combobox from Access db - 6/30/2005 1:44:09 AM

NigeJK

Posts: 16
Score: 0
Joined: 6/27/2005
From:
Status: offline

I saved as an html file. When loaded into IE I had a page with 2 empty comboboxes and two buttons. The comboboxs were unpopulated and the buttons did nothing.

(in reply to NigeJK)

Post #: 5

Re: Populate combobox from Access db - 6/30/2005 1:58:20 AM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Shame on me:

(in reply to NigeJK)

Post #: 6

Re: Populate combobox from Access db - 6/30/2005 2:26:52 AM

NigeJK

Posts: 16
Score: 0
Joined: 6/27/2005
From:
Status: offline

Working now

(in reply to NigeJK)

Post #: 7

Re: Populate combobox from Access db - 6/30/2005 2:34:41 AM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

The promised Db-Code;

Sub fillDbCBX( oCBX )
Dim oDoc
Dim sValue
Dim oOpt

Dim sFSpec
Dim strsql ' as string
Dim conn   ' as ADODB.Connection
Dim rs     ' As ADODB.Recordset
Dim sTmp

sFSpec = "C:\BMD\eDatabase.mdb"
strsql = "SELECT DISTINCT Activity.Activity FROM Activity"

' sFSpec = "C:\wis\_vbs\0506\dev\forum\activ.mdb"
' strsql = "SELECT DISTINCT sActivity FROM Activity"

On Error Resume Next
Set conn = getCnMDB( sFSpec )
If conn Is Nothing Then MsgBox "Connection" : Exit Sub End If
Set rs   = GetSelectFRO( conn, strsql )
If rs Is Nothing Then MsgBox "Recordset": Exit Sub End If
On Error Goto 0

oCBX.Length = 0
Set oDoc    = oCBX.document
Do While Not rs.EOF
Set oOpt   = oDoc.createElement( "OPTION" )
sTmp       = getFieldValueString( rs, "Activity" )
oOpt.Text  = sTmp
oOpt.Value = sTmp
oCBX.Options.Add oOpt
rs.MoveNext
Loop
rs.Close
conn.Close

End Sub

Function getFieldValueString( oRS, sNaNu )
Dim sRVal
sRVal = oRS.Fields( sNaNu )
If IsNull( sRVal ) Then
sRVal = ""
End If
getFieldValueString = CStr( sRVal )
End Function

Function getCnMDB( sFSpec )
Dim oCN
Dim sTmp

sTmp    =   "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Persist Security Info=False;"      _
& "Data Source=" + sFSpec + ";"

On Error Resume Next
Set oCN = CreateObject( "ADODB.Connection" )
oCN.Open sTmp
If 0 <> Err.Number Then
stop
Set oCN = Nothing
End If
On Error GoTo 0
Set getCnMDB = oCN
End Function

Function getSelectFRO( oCN, sSQL )
Const adOpenForwardOnly =          0 ' 00000000
Const adLockReadOnly    =          1 ' 00000001
Const adCmdText         =          1 ' 00000001

Dim oRS

On Error Resume Next
Set oRS  = CreateObject( "ADODB.Recordset" )
oRS.Open sSQL, oCN, adOpenForwardOnly, adLockReadOnly, adCmdText
If 0 <> Err.Number Then
Set oRS = Nothing
End If
On Error GoTo 0
Set getSelectFRO = oRS
End Function

Changes in doActivity()

(in reply to NigeJK)

Post #: 8

Re: Populate combobox from Access db - 6/30/2005 3:01:54 AM

NigeJK

Posts: 16
Score: 0
Joined: 6/27/2005
From:
Status: offline

Many thanks...

I implemented the extra code, changed doActivity() and set sFspec & strsql to the correct versions. I loaded the page into IE and when I clicked the button I get the "Connection" error. I have had a look through but cannot see any problems. I have put my code here. Any ideas ?

<html>
<head>
<title>Activity</title>
<meta http-equiv = "content-script-type" content = "text/vbscript"/>
<script language = "VBScript" type = "text/vbscript" >
'<![CDATA[

Sub doActivity()
Dim aValues
Dim oCBX
aValues = Array( "one", "two", "three" )
Set oCBX = document.All( "id_cbxActivity" )
' fillCBX oCBX, aValues
fillDbCBX oCBX

End Sub

Sub fillCBX( oCBX, aValues )
Dim oDoc
Dim sValue
Dim oOpt
oCBX.Length = 0
Set oDoc = oCBX.document
For Each sValue In aValues
Set oOpt = oDoc.createElement( "OPTION" )
oOpt.Text = sValue
oOpt.Value = sValue
oCBX.Options.Add oOpt
Next
End Sub

Sub fillDbCBX( oCBX )
Dim oDoc
Dim sValue
Dim oOpt
Dim sFSpec
Dim strsql ' as string
Dim conn ' as ADODB.Connection
Dim rs ' As ADODB.Recordset
Dim sTmp

sFSpec = "C:\BMD\eDatabase.mdb"
strsql = "SELECT DISTINCT Activity.Activity FROM Activity"

' sFSpec = "C:\wis\_vbs\0506\dev\forum\activ.mdb"
' strsql = "SELECT DISTINCT sActivity FROM Activity"

On Error Resume Next
Set conn = getCnMDB( sFSpec )
If conn Is Nothing Then MsgBox "Connection" : Exit Sub End If
Set rs = GetSelectFRO( conn, strsql )
If rs Is Nothing Then MsgBox "Recordset": Exit Sub End If
On Error Goto 0

oCBX.Length = 0
Set oDoc = oCBX.document
Do While Not rs.EOF
Set oOpt = oDoc.createElement( "OPTION" )
sTmp = getFieldValueString( rs, "sActivity" )
oOpt.Text = sTmp
oOpt.Value = sTmp
oCBX.Options.Add oOpt
rs.MoveNext
Loop
rs.Close
conn.Close

End Sub

Function getFieldValueString( oRS, sNaNu )
Dim sRVal
sRVal = oRS.Fields( sNaNu )
If IsNull( sRVal ) Then
sRVal = ""
End If
getFieldValueString = CStr( sRVal )
End Function

Function getCnMDB( sFSpec )
Dim oCN
Dim sTmp

sTmp = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Persist Security Info=False;" _
& "Data Source=" & sFSpec & ";"

On Error Resume Next
Set oCN = CreateObject( "ADODB.Connection" )
oCN.Open sTmp
If 0 <> Err.Number Then
stop
Set oCN = Nothing
End If
On Error GoTo 0
Set getCnMDB = oCN
End Function

Function getSelectFRO( oCN, sSQL )
Const adOpenForwardOnly = 0 ' 00000000
Const adLockReadOnly = 1 ' 00000001
Const adCmdText = 1 ' 00000001

Dim oRS

On Error Resume Next
Set oRS = CreateObject( "ADODB.Recordset" )
oRS.Open sSQL, oCN, adOpenForwardOnly, adLockReadOnly, adCmdText
If 0 <> Err.Number Then
Set oRS = Nothing
End If
On Error GoTo 0
Set getSelectFRO = oRS
End Function

']]>

</script>
</head>
<body>
<form id = "frnActivity">
<input type = "BUTTON" onclick = "doActivity()" value = "Activity!">
<select id = "id_cbxActivity"
size = "1"
></select>
</form>
</body>
</html>

(in reply to NigeJK)

Post #: 9

Re: Populate combobox from Access db - 6/30/2005 3:12:37 AM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Put the "stop" at the beginning of function getCnMDB()

Single step; view "Err.Description"

Change
sTmp = getFieldValueString( rs, "sActivity" )
=>
sTmp = getFieldValueString( rs, "Activity" )

(Sorry about that)

(in reply to NigeJK)

Post #: 10

Re: Populate combobox from Access db - 6/30/2005 3:34:33 AM

NigeJK

Posts: 16
Score: 0
Joined: 6/27/2005
From:
Status: offline

I put the stop and "Msgbox Err.Description" on the Error trap in the "Function getCnMDB( sFSpec )" as follws :

On Error Resume Next
Msgbox Err.Description
Set oCN = CreateObject( "ADODB.Connection" )
oCN.Open sTmp
If 0 <> Err.Number Then
Msgbox Err.Description
Set oCN = Nothing

The first Msgbox came up as blank ?
The second came up as "Object Required"

(in reply to NigeJK)

Post #: 11

Re: Populate combobox from Access db - 6/30/2005 5:46:11 AM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Change

On Error Resume Next
Msgbox Err.Description
Set oCN = CreateObject( "ADODB.Connection" )
oCN.Open sTmp

To

On Error Resume Next
Set oCN = CreateObject( "ADODB.Connection" )
Msgbox Err.Description ' we expect CreateObject() to fail and want to see the error msg
oCN.Open sTmp

I suspect that your security settings for "local intranet"
disallow the use of ActiveX objects; try to change this to
"ask user for permission" (to keep at least some control).

If this is not possible, try to copy activity.html to *.hta.

(in reply to NigeJK)

Post #: 12

Re: Populate combobox from Access db - 6/30/2005 8:06:23 PM

NigeJK

Posts: 16
Score: 0
Joined: 6/27/2005
From:
Status: offline

Superb - I know have it doing what I want to do with a few tweaks to use object comboboxes, and event firing. Many thanks for your help. I guess it was just the secuity settings on the computer that caused the connection error.

Is there a way I can stop the ADO warning box that it is accessing a source from another domain ?

(in reply to NigeJK)

Post #: 13

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Populate combobox from Access db

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts