Acces dbase: automatic asking for the row? (and more)

[Roelof]

1. I made a relation between visio and my acces dbase. I want visio to
lookup the values in the database and that the user can select the values
from a dropdownlist. I managed to get the fields from my table in a shape.
so far so good. But when i drop this shape from a stencil on a page it
doesn't automaticly ask for the values. How can this be done? Do i need to
make some customfields anywhere or ?

2. After that i want to make sure the user can't select a value he already
used form the database. Does anyone have an idea to get that working?

3. We are making a lot of effort to make our project come to life. Therefor
when we use this acces-coupling do we need to distribute this acces-db with
our project or will it be packed inside the template we create?

4. Shapestudio is new and poorly documented, when will there be some
documentation/manuals?

 

[Al Edlund]

You raise some interesting questions regarding you solution, specifically
what are you coding as (standalone, web based, addin).

1. I made a relation between visio and my acces dbase. I want visio to
lookup the values in the database and that the user can select the values
from a dropdownlist. I managed to get the fields from my table in a shape.
so far so good. But when i drop this shape from a stencil on a page it
doesn't automaticly ask for the values. How can this be done? Do i need to
make some customfields anywhere or ?

custom properties support an "ask on drop" property, so you might consider
that as an option

2. After that i want to make sure the user can't select a value he already
used form the database. Does anyone have an idea to get that working?

are you reading data from the database as a reader (i.e a stream coming
back) or on a transaction
basis (where the record can be locked, also locking away other users). You
can of course do a scan
of pages/shapes within a document to see if a selected value has already
been chosen before deciding
to apply it to a shape.

3. We are making a lot of effort to make our project come to life.
Therefor when we use this acces-coupling do we need to distribute this
acces-db with our project or will it be packed inside the template we
create?

it will not be in the visio template so you will have to develop a plan for
distributing
the database (and possibly a runtime of the data engine as well).

4. Shapestudio is new and poorly documented, when will there be some
documentation/manuals?

there have been two good articles relative to shape studio published on
msdn, of course this
is still a work in progress.

Al

 

[David Parker [Visio MVP]]

If you are using the Database Wizard to connect to an Access table/view,
then you can define upto 5 fields that uniquely define a row, and have each
field prompted for, in a drop down list, by adding the Select On Drop event
for the master.

 

[Roelof]

Thanks for your inputs.

We are "developing" (trial&error based ;-) ) a solution where our customers
can design a special industrial solution and after that has a well
documented project with one single buttonpress.

The following is just an example and not what we build but reflects the way
our solution needs to function:
- a customer wants to design his wooden blocks
- all possible options for the blocks are in a database
- based on which options he chooses the amount wood needed changes
- when he chooses 45 different options he needs more wood then when he
chooses 16 options
- after selecting al options we know the amount of options, which options
and the amount of wood needed
- when the customer selects a specified report he gets all options printed
in code, the productionsteps he has added be draging and dropping options
and all figures for wood and productiontime

This is just an example, but as you may see whe need to interact with the
database, make calculations based on database-cells and "translate" options
into code.

Our first goal is to make this Visio-solution some kind of package that
customers can install at their PC.

 

[Gijs]

We have developed a vba code that will fill an combobox with data from a
database. Only we have one problem, only the horizontal data are shown in
the combobox. What we want is a combobox that will show the list of the
vertical data. We found an example on the internet that transposes the data,
but that vba code is made in excel, and doesn't work in Visio. Does anybody
know a way to show the vertical data?

The code we have is as follow:

Private Sub CommandButton1_Click()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String, stConn As String, stSQL As String
Dim vaData As Variant
Dim vadataT As Variant
Dim k As Long

'In order to increase the performance.
With Application
.EventsEnabled = False
.ScreenUpdating = False
End With
'Instantiate the Connectionobject.
Set cnt = New ADODB.Connection

'Path to and the name of the database.
stDB = ThisDocument.Path & "\" & "database.mdb"

'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"

'Create the SQL-statement.
stSQL = "SELECT * FROM list"

With cnt
.CursorLocation = adUseClient 'Necesary for creating disconnected
recordset.
.Open stConn 'Open connection.
'Instantiate the Recordsetobject and execute the SQL-state.
Set rst = .Execute(stSQL)
End With

With rst
Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count
'Populate the array with the whole recordset.
vaData = .GetRows
vaData.Transpose = True

End With
'Close the connection.
cnt.Close

'Manipulate the Combobox's properties and show the form.
With UserForm1
With .ComboBox2
.Clear
.BoundColumn = k
.List = vaData
.ListIndex = -1
End With

End With

'Restore the settings.
With Application
.EventsEnabled = True
.ScreenUpdating = True
End With

'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing

End Sub

 

[Roelof]

You should try a while not EOF (end of file) statement like :

If Not (easyDB.Recordset.EOF And easyDB.Recordset.BOF) Then
'fill the listview with contact records
Do Until easyDB.Recordset.EOF
Set itmCurrentRow = lvwAddressBook.ListItems.Add()
itmCurrentRow.Key =
DefaultField(easyDB.Recordset!Company_ID) & "K" 'set index Key
itmCurrentRow.Text = DefaultField(easyDB.Recordset!Name)
itmCurrentRow.SubItems(1) =
DefaultField(easyDB.Recordset!Address)
itmCurrentRow.SubItems(2) =
DefaultField(easyDB.Recordset!City)
itmCurrentRow.SubItems(3) =
DefaultField(easyDB.Recordset!State)
itmCurrentRow.SubItems(4) =
DefaultField(easyDB.Recordset!Zip)
easyDB.Recordset.MoveNext
Loop