graphical mapping

[abenitez77]

I want to be able to do graphical mapping the way ms access does in query design mode where you can link 2 fields from 2 different tables. Drag and drop if you will. I want to be able to do this with multiple columns in both list boxes. Has this been done by anyone before and how can i do this?

 

[MajP]

Can you explain this a little more? What is the purpose? I never tried this but I think it could be faked if you are going from a static (no scroll) listbox to another static listbox. I think you could find the approximate X,Y position based on the location of the listbox and the list index. Then you could draw a line between the two. Working with lines in access is not very intuitive because you have to manipulate slant, top, left, height, width. Adding scrolling would take this from just very complicated to extremely complicated. Access does not expose much information about how a form or list is scrolled. This would require a lot of windows API. Unless you are a very advance vba programmer, I would look for another alternative.

 

[abenitez77]

i want to map fields in 1 list box with the fields in another list box...the way it looks like in query editor where you can set relationships...dragging one field to another. I really don't want to set a realationship in tables but want to create a tie from one field to another in 2 different listboxes. So i would be doing this for multiple items in a list.

 

[MajP]

But what is the purpose of doing this? What is the purpose of showing lines between the two records. There may be a better simpler interface such as a treeview to give the user the same information/presentation.

 

[abenitez77]

I want to map fields(values) in 1 list to a second list that has fields(values) and then create a 3rd list that has the selected values from both lists. ie like you would when you do an import in DTS...where you map fields...I want to use this to create a dynamic sql query that uses the mapped fields. This would help me create a dynamic query for a table...

 

[MajP]

I played with this, and I can draw lines in between. But it is not scrollable. Once you scroll the lines are out of synch. Adding the ability to scroll makes this much,much more complicated. That may be ok since your lists are likely not that long. You would have to ensure the list is as big as the max records.

I would have three listboxes. Click in list and then click in the second. This creates a record in the third listbox. The third listbox has two columns Field1, Field 2. This sounds what you are doing, so do you really need the lines?

Here is a demo. It is rough, but enough to get you started if you want to pursue this. You have to click in the lists not drag.

http://www.4shared.com/document/X3w_Rhp0/TwoLists.html

The general idea with how to do the measurements.

Option Compare Database
Option Explicit
Private mLstOne As Access.ListBox
Private mLstTwo As Access.ListBox
Private mX1 As Long
Private mY1 As Long
Private mX2 As Long
Private mY2 As Long
Private mItem1 As Integer
Private mItem2 As Integer
Private mLineCount As Integer
Const mMaxLines = 5
Private Sub Form_Load()
  Set mLstOne = Me.lstOne
  Set mLstTwo = Me.lstTwo
  mLineCount = 1
End Sub
Public Property Get lstOneTop() As Long
  lstOneTop = mLstOne.Top
End Property
Public Property Get lstTwoTop() As Long
  lstTwoTop = mLstTwo.Top
End Property

Private Sub lstOne_Click()
  mItem1 = lstOne.ListIndex
End Sub
Private Sub lstOne_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  mX1 = X + lstOne.Left
  mY1 = Y + Me.lstOneTop

End Sub
Private Sub lstTwo_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  mX2 = X + Me.lstTwo.Left
  mY2 = Y + Me.lstTwoTop
  If mLineCount <= mMaxLines Then
    ConnectLine Me.Controls("ln" & mLineCount)
  Else
    MsgBox "I only added 5 lines. You can add more"
  End If
End Sub
Public Sub ConnectLine(ln As Access.Line)
  'MsgBox Me.ln1.Top
  Dim lnTop As Long
  Dim lnWidth As Long
  Dim lnHeight As Long
  ln.Visible = True
  ln.Left = lstOne.Left + lstOne.Width
  ln.Width = Me.lstTwo.Left - Me.lstOne.Left - Me.lstOne.Width
  If mY1 >= mY2 Then
    ln.Top = mY2
    ln.LineSlant = True
    ln.Height = mY1 - mY2
  Else
    ln.LineSlant = False
    ln.Top = mY1
    ln.Height = mY2 - mY1
  End If
  mLineCount = mLineCount + 1
End Sub

 

[abenitez77]

thanks...this is a good start.