Access Item Genie demo for Organizing / Tracking your Items

[MajP]

This demo was asked by @Mike Krailo if using an Acess Treeview could you do some of the functions of the Utility

ItemGenie - Inventory Made Easy

Cataloging, Finding, and Listing of Things, Made Easy. Perfect for home inventory and small businesses. Try ItemGenie for free today.

itemgenie.com

He and I have been working together to make this a useable utility and demonstrate a lot of functionality using a Treeview.

Some background here

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Can someone point to the latest database with the latest features mentioned in this thread? There seems to be a lot of conversation and additional code, but I haven't found the authoritative file with latest info. MajP, you've done a lot of great work on this !!

www.access-programmers.co.uk

This is a work in progress but demos a lot of capability.

You can add, edit, sort, drag and drop locations and items in those locations. You can choose different icons for the different types of locations (box, room, generic location, bag, etc.) You can add images for each item. There are features for bulk move of items and bulk edit. There is a Summary data showing roll up information.

When you click on a location you see details of the location
When you click on an item you see details on the item
Buttons on the left will reflect the same functions on the right click menu.

You click on a location from the tree you can then edit the Location properties and you see the items in the location.

With you select an "Item" in the tree you can see the details on that item, edit, or delete the item.

The menu on left is duplicated on the right click making some features easier to access.

There are still a lot of features to add and code to fix, but it is a 95% working solution.
If interested please provide additional features you may want demonstrated. I will continue to flush this out and post the most current version here.

So that people can use the database, but still get code updates I split the front end from the backend. This way you can simply link to your backend database and only need to grab the updated front end. When you first start or get a new front end you will be prompted to link to the backend tables. Do not edit the sub folder names, for your images, icons bitmaps, and backend folder. They need to exist in the same folder as the Front end.

 

[Mike Krailo]

I added some location icons that look much better now. There is now an Attic, Bedroom, Kitchen, Bathroom, Driveway, Garage, and a Shelf icon. Added frmIcon form so it can be used to add new icon names to the table using the Edit List from the edit location form.

Also added an extra field ItemValue to track the current estimated value of an item. The table doesn't sync up with changes made to the tree yet, but it's a start.

This of course will require the ItemValue field be a mandatory field so the calculations work. Same with the count, it should default to 1 and never be empty.

 

[GK in the UK]

Additional feature suggestion: Drag and drop is a pain if the target node is out of the tree view control window. You might want to consider doing what I did which is to allow a selection of items on checks, across any level, then drop them on a single highlighted target node from the context menu. You can scroll the view to find the target without losing the selection. You have to enable check boxes but I find it's a much faster way to reorganise the tree, even if it's a single checked node to a target that's out of view. The code is mostly there to do this - I built a collection of nodes using TVW.GetCheckedDescendants, looped the collection, looked up the record and updated the parent. If all that happened without a rollback, updated the nodes.

 

[Mike Krailo]

Just did some visual rearranging of the forms to accommodate a slightly larger font size of 12 and icon size was changed to 20x20 instead of 16x16. Small change, but it makes it easier on my older eye's. I also added some more data to the tables to display a few of the new icons. The only other change was with the add/edit location form so that the OK and Cancel button were in the details section so the tab stop would move to the OK button.

Drag and drop is a pain if the target node is out of the tree view control window.

Are you saying there is a complication when the width of the node longer and requires scrolling to get to it? You want to select individual items and move them all to a single new location? That's a great idea. We're just getting started on this so I'm sure that is doable. Maybe instead of always showing the checkboxes, you can select a button that shows checkboxes enabling that functionality.

 

[GK in the UK]

It's not the width of the nodes, it's how much of the depth of the treeview is displayed in the control. It doesn't auto-scroll so if your target is out of reach you can't do it, you end up faffing about trying to make both nodes visible. I see that FMS have some code that enables auto-scrolling but I haven't examined it, I like the multi selection better anyway. Yes you could enable the checkboxes if you want to do that.

I already built a treeview with the concept of locations, which I call groups, and items, which are individual records. I see there is a comment in Sub tvw_TreeviewNodeDragDropSub about figuring out how to handle invalid node drops. Was this developed? In my case, I have groups and items in the same table, whereas ItemGenie has two tables. If I add a group node which is determined by the record type in the table, I prefix it with a shifted space. An item node is prefixed with a regular space. They're both visible at the start of the node text but look the same of course, but my code colours group nodes in blue. You could use something like this to determine if a node drop is valid.

Anyway here's my code to move a batch of checked nodes. The query and recordset code would need to fit your table structure. In my case the parent/child relationships and sort order are saved in an separate table, I can build as many treeview structures as I like on the same set of records (which is a different use case to ItemGenie)

Private Sub DoMoveCheckedNodes(nod As Node, NewParentNode As Node)
On Error GoTo Err_Handler

  Dim ws As DAO.Workspace   ' current workspace (for transaction)
  Dim db As DAO.Database    ' inside the transaction
  Dim rs As DAO.Recordset
  Dim bInTrans As Boolean   ' flag that transaction is active

  Dim myDescendants As Collection
  Dim myNode As Node
  Dim lngNodeID As Long
  Dim lngNewParentID As Long
  Dim strKey As String

  ' we pass this in as the node to build the descendants from,
  ' the root but we could pass in any branch root instead
  If Not nod Is Nothing Then

      ' make sure the target lngParentNode is a group record with a leading shifted space
      If TVW.SelectedNode Is Nothing Or Not fIsGroupNode(NewParentNode) Then
          MsgBox "A group node must be selected to move the checked records to", vbInformation, gstrAppTitle
      Else
          ' initialize database object inside a transaction
          Set ws = DBEngine(0)
          ws.BeginTrans
          bInTrans = True
          Set db = ws(0)
          
          ' select every row from tblTvParents for treeview TvID
          With db.QueryDefs("q_tv_AllRecordsOfTreeView")
              .Parameters("TvID") = Me.cmbTV
              .Parameters("TreeSet") = Me.fraTreeSet
              Set rs = .OpenRecordset
          End With
          
          lngNewParentID = TVW.getNodePK(NewParentNode)
          ' build a collection of all checked descendants across all groups
          Set myDescendants = TVW.GetCheckedDescendants(False, nod)
          For Each myNode In myDescendants
              With myNode
                  lngNodeID = TVW.getNodePK(myNode)
                  rs.FindFirst ("tvpChildID=" & lngNodeID)
                  If Not rs.NoMatch Then
                      rs.Edit
                          rs!tvpParentID = lngNewParentID       ' assign the checked record to the new parent
                      rs.Update
                  Else
                      MsgBox "DoMoveCheckedNodes: tvpChildID not found: " & lngNodeID, vbCritical, gstrAppTitle
                      GoTo Exit_Handler                         ' rollback and quit
                  End If
              End With
          Next
          
          ws.CommitTrans
          bInTrans = False
          
          ' if we're still here no error was generated, now migrate the treeview nodes in the control
          strKey = RecID & CStr(lngNewParentID)
          For Each myNode In myDescendants
              Me.TVW.Nodes.Remove myNode.Index                  ' remove the node at it's current position
              Set nod = Me.TVW.TreeView.Nodes.Add(strKey, tvwChild, myNode.key, myNode.Text)
                  nod.Tag = RecID
              If fIsGroupNode(nod) Then nod.ForeColor = vbBlue
          Next
      End If
  End If

Exit_Handler:
  On Error Resume Next
  Set db = Nothing
  Set rs = Nothing
  If bInTrans Then                  ' rollback if the transaction is active
      ws.Rollback
      MsgBox "Move failed, retry", vbCritical, gstrAppTitle
  End If
  Set ws = Nothing
Exit Sub
Err_Handler:
  Select Case Err.Number
      Case 35600:   Resume Next     ' index out of bounds
      Case Else: Call LogError(Err.Number, Err.Description, Me.Name & ":DoMoveCheckedNodes", , ShowErrors)
  End Select
  Resume Exit_Handler
End Sub

 

[Mike Krailo]

It's not the width of the nodes, it's how much of the depth of the treeview is displayed in the control. It doesn't auto-scroll so if your target is out of reach you can't do it, you end up faffing about trying to make both nodes visible. I see that FMS have some code that enables auto-scrolling but I haven't examined it, I like the multi selection better anyway. Yes you could enable the checkboxes if you want to do that.

Thanks for the clarification, that certainly makes sense but in that case I probably would not want to use drag and drop and go for a Move Items button instead. Then you just select the new location and and do the move that way. It makes no sense to use drag and drop if the place you are dragging to is a long ways away (scroll wise).

The multi selection idea is a good one for sure.

 

[MajP]

I added some location icons that look much better now. There is now an Attic, Bedroom, Kitchen, Bathroom, Driveway, Garage, and a Shelf icon. Added frmIcon form so it can be used to add new icon names to the table using the Edit List from the edit location form.

I automated this. If you have your bitmaps in the folder then no need to do anything.
Prior to loading the tree
1. It loops the icon folder and any bmps are added to tblIcons. I include the file name too so that I can display the icon in the form now.
2. Any icons listed in the table but not in the folder are removed from the table
3. It check that any icons in use have a matching one in the folder. If not it alerts you to add the icon. and closes the form.

I see there is a comment in Sub tvw_TreeviewNodeDragDropSub about figuring out how to handle invalid node drops. Was this developed?

I cannot easily check the drag and drop easily before hand without hardwiring the Class module. Since I want this code to be generic to any tree I am hesitant to hardwire it. So instead I alert the user after the fact and move it back to where it came from.

There are already some features to move an item without drag and drop but I will add some more. You can change the location from the pull down in the Item Edit form and it will move it.

Drag and drop is a pain if the target node is out of the tree view control window. You might want to consider doing what I did which is to allow a selection of items on checks, across any level, then drop them on a single highlighted target node from the context menu.

This is a legit problem. I do not know if there is anyway to get it to auto scroll, making the drag and drop not real useful for big trees. Not sure if anyone has a workaround for that besides an alternate interface.

Can you show or explain this interface? After you make your selections, how are you "dropping" on a single highlighted target node? Is that a click. I assume it is not a drag. Or is that just a click on "context menu"? What is the "context menu".

 

[GK in the UK]

Context menu is right-click menu. My tree is for financial accounting. I build the node totals into the node text and each item node is summed into all it's parent (group) nodes. There is a lot going on which is outside the scope of the tree view, but as a result of the summing up which all happens in queries on the fly I display the tree in one of two modes: the normal view which shows the totals, and a maintenance mode which enables the checkboxes and excludes the totals. It wouldn't make sense to move a node in the normal view because the parent totals would be wrong.

In maintenance mode you just check as many nodes as require moving, then navigate to the group node that you want to drop them on. The selection isn't lost so you can expand branches or manually scroll until you find the group. Right-click and select 'Move Checked Records Here', which runs the code I posted, the table updates are done, original nodes deleted, new nodes added. If the new node is a group (leading shifted space) it's set blue.

Then when you're done with re-organising the tree you click rebuild and the tree displays the new arrangement with the new group totals.

Maintenance mode, about to drop 2 selected nodes onto the selected group:

 

[Mike Krailo]

So maintenance mode essentially adds the check boxes to the tree and allows you to right click the target node and select Move Checked Records Here. That's a neat and tidy right click menu.

I automated this. If you have your bitmaps in the folder then no need to do anything.
Prior to loading the tree
1. It loops the icon folder and any bmps are added to tblIcons. I include the file name too so that I can display the icon in the form now.
2. Any icons listed in the table but not in the folder are removed from the table
3. It check that any icons in use have a matching one in the folder. If not it alerts you to add the icon. and closes the form.

I assume this is in a version I had not seen yet? I put the reworked bitmap files into the folder and they did not show up in the dropdown until I added them to the table manually or via the form I created for it.

 

[MajP]

I put V3 in the initial post

Access Item Genie demo for Organizing / Tracking your Items

This demo was asked by @Mike Krailo if using an Acess Treeview could you do some of the functions of the Utility https://itemgenie.com/ Some background here...

www.access-programmers.co.uk

I added both buttons and right click controls to do this button is in lower left. When all done I can work on aesthetics of the form buttons.

when you select "Check Nodes to Move" this comes up and the tree changes to include checks.

Then you can select all items to move. If you select a sub location to move, then all items below it move. There is no requirement to check the child items as well. If you check and item and not its parent location then only the items move not the whole branch. Once complete the checkboxes go away.

 

[Mike Krailo]

That's strange, I tested that out and didn't have any errors. The problem I'm struggling with is doing a simple add item to the current location. So if I right click on the Garage it doesn't display the full right click menu to be able to add an item. Sometimes it works and sometimes it doesn't and you just get the expand/collapse tree options only. If you expand the whole tree and right click on each location, the full right click menu will not show on every node.

 

[GK in the UK]

It may work if you click in a different place along the node. Try changing the multiplicands in Private Sub mTVW_MouseUp in the class module. It may depend on your monitor. I changed y=y*15 to y=y*12 but I still occasionally get the wrong menu appearing.

More info: https://stackoverflow.com/questions/5860529/creating-a-mouse-over-effect-on-a-vb-treeview-node

 

[arnelgp]

Sometimes it works and sometimes it doesn't and you just get the expand/collapse tree options only.

Genies only grant you 3 wishes. I think you already used them all

 

[MajP]

The link kind of explains the issue. This is a monitor issue and you need to put in some "Kentucky Windage" so that when you right click it knows if you are on or off a node.
For me the answer is 15 and it works.

Private Sub mTVW_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
  Dim NodeHit As Node
  If Button = 2 Then
    'For some reason you multiply X and Y by 15.  Saw this on Stack Overflow
    'https://stackoverflow.com/questions/5860529/creating-a-mouse-over-effect-on-a-vb-treeview-node
    x = x * 15
    y = y * 15
    Set NodeHit = mTVW.HitTest(x, y)
    If Not NodeHit Is Nothing Then
      RaiseEvent RightClickOnNode(NodeHit)
    Else
      RaiseEvent RightClickOffNode
    End If
  End If
End Sub

I found this out by trial and error. Since the menu should do different things if you are on a node or off a node. WIthout this adjustment it was doing the opposite. If I clicked on a node it would return no node, and when I clicked off of a node it would say I was on a node.

In order to figure this out go into these methods in the form

'Because it creates two types of command bars depending on on or off node.
'Should see if this can be done once and then pick the correct bar.

Private Sub tvw_RightClickOffNode()
  createCommandBarPopUpNoNode Me, Me.tvw
End Sub

Private Sub tvw_RightClickOnNode(RightClickedNode As Node)
  RightClickedNode.Selected = True
  createCommandBarPopUpNode Me, Me.tvw
End Sub

'Because it creates two types of command bars depending on on or off node.
'Should see if this can be done once and then pick the correct bar.
Private Sub tvw_RightClickOffNode()
  msgbox "Clicked off of a node"
End Sub

Private Sub tvw_RightClickOnNode(RightClickedNode As Node)
  if not RightClickedNode is Nothing Then
    Msgbox "right click on node " & rightClickNode.text
  else
    Msgbox "Right Click on node"
  end if
End Sub

Now you can play with the values until you get the correct message. It might be as simple as removing all adjustments, but I have no idea how you pick an adjustment.
In this application I can probably expand the off node menu. The problem is if you are not returning a current node when right clicking then something like "Move items to selected node" is going to move items to the wrong node. So you need to ensure the right click is returning a proper node.

Maybe there is a way to move a dot to show where the X and Y are being returned. I have not tried that.

 

[Mike Krailo]

It may work if you click in a different place along the node. Try changing the multiplicands in Private Sub mTVW_MouseUp in the class module. It may depend on your monitor. I changed y=y*15 to y=y*12 but I still occasionally get the wrong menu appearing.

More info: https://stackoverflow.com/questions/5860529/creating-a-mouse-over-effect-on-a-vb-treeview-node

So I have to do a TVW calibration because of my particular monitor? I'm playing around with those values now. I see now the Y value is too short on my monitor. The X value seems to be OK. I was initially trying to right click the node name and not the icon so that part wasn't intuitive either. It is much more intuitive if the highlighted node name is the place to right click to get the menu. Wonder if there is a way to make the menu work off of the name instead of the icon? The original item genie way which is much more intuitive because it has both a pencil icon and the plus icon for editing or adding a node. Works perfectly on any monitor.

UPDATE: I just change the X value to 10 as well and now it works on my monitor perfectly to include the name of the node. So now I can right click the displayed name for every node and get the right click menu. Awesome.

It's a visual alignment, but you would think the visible height of the tree minus the height of the total height of the visible area shown is something that can be calculated on the fly. What if the formatting of the tree view is manipulated? Does that again require a change in the Y value to make everything line up?

BTW, on my monitor (Gigabyte M28U) a Y value of 10 made everything work perfectly. Lowering the Y value increases the size of the node hit zone in the Y direction. The key of course is to focus in on the last node in the chain and try to right click that node. If you get the expand/collapse tree only menu, the zone is too small in height and the Y multiplier needs to be reduced until that bottom node triggers the expected right click menu. There has to be a way to calculate this based on monitor resolution. There are api's to get that info and then make the adjustment accordingly in code.

 

[Mike Krailo]

I just tried increasing the icon size just to see if this caused any issue with the Node Hit zones, and it had no adverse effect whatsoever. I thought it might shrink the hit zone, but it did not. That's a good thing. So it appears to be a monitor resolution issue only.

On another issue, I see that comes up when a root location node is created is that the table Parent_ID field is incorrectly populated with a value of Zero instead of just leaving the value Null as it should. See the next snapshot of the newly added Kitchen node. This will cause the node to vanish the next time the tree is read in from the table.

UPDATE: It appears to only happen from the right click menu and not the command button. I think it's the Before Insert event, but not positive. I ran out of time to troubleshoot. Got to go to work.

 

[MajP]

That was a mistake on my part. The fix is to update these methods in in frmAddEditItems

Private Sub Form_Load()
  If (Me.OpenArgs & "") <> "" And Not Me.NewRecord Then
    If CLng(Me.OpenArgs) <> 0 Then Me.Location_ID_FK = CLng(Me.OpenArgs)
  End If
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
  If Me.NewRecord And Not IsNull(Me.OpenArgs) Then
    If CLng(Me.OpenArgs) <> 0 Then Me.Location_ID_FK = CLng(Me.OpenArgs)
  End If
End Sub

However I am not even sure if that form_load method does anything anymore. I do not know if I ever pass a location ID into the form for an existing item to change the location. That may be leftover. But this fix will keep it from creating the problem even if it actually does something.

However I think I will mod the right click menu to get rid of the intermediate form. It would mirror the buttons.
Add Root Location
Add Child Location
Add Unlocated Item
Add Located Item

Also if you click off of a node you would get the ability to add root locations and unlocated items.

 

[MajP]

I added V5 to the initial post. The right click menus for on and off a node are easier to use. And the new code is easier to manager.
I still need to add your additional item properties. Need to add the ability to delete a node from the tree and a method to move items to the root level (top level locations and unlocated items).

There is not fix for the monitor issues that I know how to do. Maybe someday I could build a tool to calibrate. You would pass in x and y values and it could put a dot on the control. I think this is doable based on some code that @isladogs presented on precise location. Then once you determine the correct x and y it could be saved in a configuration table.

 

[Mike Krailo]

Just for added info, my monitor at work has a resolution of 2560x1440 and the values of X=15 and Y=15 works perfectly on that one. A configuration table would work. We'll live with it for now. What happened to version 5? It disappeared on my end.

 

[Mike Krailo]

I'm posting some of my additions here, there are some things I have to have in there. The extra field for ItemValue is one of them because initial cost and current value will always change the total value of the inventory. The total shown at the top updates on delete or add of items. Just not sure how to delete nodes. All of V6 has been included. I see you organized the modules differently, that made it a bit more work to incorporate, but its all in there now.

Added a form based on tblResolution for selecting the resolution of your particular monitor and setup the class variables to use tempvars based off of the table. There is also a convenient combo on the form for selecting the default resolution. If we can get a few others to test out the app on different monitors and update the table to Verified for the given resolution, we can cover all the bases without further issue.

The only thing I couldn't do was re-initialize the tree after updating the default resolution. What's the command for that. Right now, I have to close and reopen the app.