Passing a selected value from a ListBox to VBA to update a Shape

[ringram]

I have linked several text shapes (Identified as 2) in a Visio 2003
drawing to an Access Database. I have written a short segment of VBA
code to update the Text fields based on the Primary Key field.

I have added a Listbox control (called ListBox1) and loaded it with
numbers 0 - 5. (I first attempted to link the Primary Key from the
Access Database to the Listbox and to display them; however, I could
never get it to work) From the Listbox control, I am able to select a
value that corresponds to the primary key and row within the Database.
To act on the selected listbox value I added a button control to
execute the Marco. My Code.


Private Sub CommandButton1_Click()
If ListBox1.Value >= 0 Then
example1
End If
End Sub

Private Sub UserForm_Initialize()
With ListBox1
..AddItem 0
..AddItem 1
..AddItem 2
..AddItem 3
..AddItem 4
..AddItem 5
End With
End Sub

'Marco

Sub example1()
ActiveWindow.DeselectAll
Dim UndoScopeID1 As Long
UndoScopeID1 = Application.BeginUndoScope("Custom Properties")
Dim vsoShape1 As Visio.Shape
Dim intPropRow1 As Integer
intPropRow1 = 0

Set vsoShape1 = Application.ActiveWindow.Page.Shapes.ItemFromID(2)
vsoShape1.CellsSRC(visSectionProp, intPropRow1,
visCustPropsValue).FormulaU = """ListBox1.Value"""
Application.Addons("Database Refresh").Run ""
End Sub


My problem.

From the Drawing I can select a number from the Listbox drop down and

then click on the Control button. The marco executes, however, I can't
past the value of the Listbox using = """ListBox1.Value""" to the Shape
to change the primary key and text information shown. (Loading a value
.... ...FormulaU = """3""" works - looks up primary key in Access
Database and loads the linked cell to the text) (The ListBox1 value is
updated to correspond to whatever selection is made from the ListBox
drop down.)

Also Executing I get the following error when Database Refresh runs.
"Cannot read all of the data from the linked record fields. Run the
database wizard and re-link the shape to correct the problem Error Data
ODBC Error 07001 [ODBC MSAcess Driver] Too Few Prarameters, Expected 1

How can I pass a value from the list box to the shape as an index to
the Access Database Primary Key?

Thank you,

Ray

 

[David Parker]

Shouldn't
= """ListBox1.Value"""
be
= "=""" & ListBox1.Value & """"
?

I have linked several text shapes (Identified as 2) in a Visio 2003
drawing to an Access Database. I have written a short segment of VBA
code to update the Text fields based on the Primary Key field.

I have added a Listbox control (called ListBox1) and loaded it with
numbers 0 - 5. (I first attempted to link the Primary Key from the
Access Database to the Listbox and to display them; however, I could
never get it to work) From the Listbox control, I am able to select a
value that corresponds to the primary key and row within the Database.
To act on the selected listbox value I added a button control to
execute the Marco. My Code.


Private Sub CommandButton1_Click()
If ListBox1.Value >= 0 Then
example1
End If
End Sub

Private Sub UserForm_Initialize()
With ListBox1
.AddItem 0
.AddItem 1
.AddItem 2
.AddItem 3
.AddItem 4
.AddItem 5
End With
End Sub

'Marco

Sub example1()
ActiveWindow.DeselectAll
Dim UndoScopeID1 As Long
UndoScopeID1 = Application.BeginUndoScope("Custom Properties")
Dim vsoShape1 As Visio.Shape
Dim intPropRow1 As Integer
intPropRow1 = 0

Set vsoShape1 = Application.ActiveWindow.Page.Shapes.ItemFromID(2)
vsoShape1.CellsSRC(visSectionProp, intPropRow1,
visCustPropsValue).FormulaU = """ListBox1.Value"""
Application.Addons("Database Refresh").Run ""
End Sub


My problem.

From the Drawing I can select a number from the Listbox drop down and

then click on the Control button. The marco executes, however, I can't
past the value of the Listbox using = """ListBox1.Value""" to the Shape
to change the primary key and text information shown. (Loading a value
... ...FormulaU = """3""" works - looks up primary key in Access
Database and loads the linked cell to the text) (The ListBox1 value is
updated to correspond to whatever selection is made from the ListBox
drop down.)

Also Executing I get the following error when Database Refresh runs.
"Cannot read all of the data from the linked record fields. Run the
database wizard and re-link the shape to correct the problem Error Data
ODBC Error 07001 [ODBC MSAcess Driver] Too Few Prarameters, Expected 1

How can I pass a value from the list box to the shape as an index to
the Access Database Primary Key?

Thank you,

Ray

 

[ringram]

David,

Many thanks. The format you suggested worked well. Now on the the
next issue.

I still get the ODBC Error when the Application.Addons("Database
Refresh").Run "" executes
but I'll try rebuilding the drawing from scatch once again and then add
the VBA segments.
Prior to added the ListBox and Control Button, the Macro worked fine
when the primary key was "hardcoded".
That is, when Addons("Database Refresh") executed the drawing would
update with the appropriate information
from the Database.

Ray

 

[ringram]

David,

Actually, I spoke to soon... it appears my value does get passed but
is missing something.

ID is the Primary Key field name in my access database table called
"ExampleData"

ODBC Error: 37000
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(`ExampleDATA`.`ID` = & ListBox1.Value
& )'.