Modules & VBA :: Recursive Search And Populate Results In Tree View Control
May 28, 2015
I need some VBA coding to do the following tasks
Table 1: two columns - Child Tag and Parent Tag. Parent Tags can also be in Child Tag column. In other words, a parent can have multiple levels of children.
Table 2: one column - Backup Tag.
I'd like to have a form with a combo box, pick a Parent Tag, the search all its child tags and compare each Child Tag found with records in Table 2 to see if there is a match. Then populate all results in a tree view control.
A visual example :
Parent Tag
...Child Tag 1 - Back up tag found
......Child Tag 11
......Child Tag 12 - Back up tag found
...Child Tag 2
......Child Tag 21
...Child Tag 3
......Child Tag 31
.........Child Tag 311 - Back up tag found
I have a form with a Treeview in. I have it populated from a self-referencing database using the following code.
Code: Private Sub Form_Load() Const strTableQueryName = "SELECT * FROM tblHierarchy ORDER BY tblHierarchy.Function_Parent;" Dim db As DAO.Database, rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, dbReadOnly)
[Code] ....
The database this is referencing is about 30000 lines and it takes ~4 minutes to populate this way. I know Treeview isn't really supposed to be used in this way however it's what is required.
Now I have come up with the theory that I will populate each node with children as its clicked to be expanded.
This problem is driving me crazy.... :mad: I have main form which contains treeview control. TreeView contains list of all users that have been added. I add users in pop up form. But when I hit Update button in pop up form the treeview dosen`t want to update. I can make it work only if I close main form and open it again when I hit Update button on pop up form.
If IsLoaded("frmOsnovnoOkno") Then DoCmd.Close acForm, "frmOsnovnoOkno" DoCmd.OpenForm "frmOsnovnoOkno" DoCmd.Hourglass False End If
I dont want to close main form ("frmOsnovnoOkno") and open it again I just want to update treeview without closing and opening main form. Please help me.. :)
Hi, I'm just wondering if it's possible to display the result of a query on a subform which does not have a relationship with any other table.
What I'm doing is creating a search form where a query is built from the users input (through combo boxes and text boxes) of what table, attribute and condition they want to search from. When they press the search button, I want the result to be displayed in a datasheet below the selection criteria
The first thing I thought of using to display information with was a subform, but I think the purpose of having the subform is so that it can be linked with a form. So, is there another control or way of displaying results. The results can come from any table.
I am not sure if this is the correct forum to post this question but I am trying to create a tree view library reference database for several pdf documents I need to index, and would be happy if there is a sample available to download.
I am not sure if this is the correct forum to post this question but I am trying to create a tree view library reference database for several pdf documents I need to index, and would be happy if there is a sample available to download.
I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.
The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.This is the code for the search:
Code:
Private Sub cmdAssetSearch_Click() Dim rs As Object If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then MsgBox "Please type in an asset number to search for.", vbOKOnly Me.TextAsset.SetFocus
[code]....
I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.
I have a list of staff that have a conflict of interest with a particular entity. As a result, these staff are not allowed to interview these entities.
I have a query that matches all staff with their respective entities that they have a conflict of interest with (CoI) and that is functioning correctly.
When the form loads to add an interview, there is a listbox that pulls all the people who are not allowed to do an interview with that particular entity. That is also working correctly.
I have a subform, that is a continuous form, which will allow the user to add staff, one at a time, via a drop down box. These people are stored in their own table with a FK Id to the interview table. This also works correctly.
How to filter the combobox on the subform to exclude the people in the listbox.
Here is what I have tried, loosely based on what I have found on Google and researching here. I am 100% sure it is not working correctly, but what I am missing.
The query the listbox is based on has 3 colums, the ID, the Name, and the business contract number.
Code: Private Sub Form_Load() Dim strSource As String Dim i As Integer For i = 0 To Me.lstCoI.ListCount - 1
[Code] ....
In the immediate window, I get the following result:
SELECT [staff] FROM lutStaff WHERE Staff <> name1 SELECT [staff] FROM lutStaff WHERE Staff <> name2 SELECT [staff] FROM lutStaff WHERE Staff <> name3 SELECT [staff] FROM lutStaff WHERE Staff <> name4 SELECT [staff] FROM lutStaff WHERE Staff <> name5 SELECT [staff] FROM lutStaff WHERE Staff <> name6
The issue is that the box is not filtering all the names out of the list it is built on. It is only filtering out the last name.
Obviously I need to save the results for comparison, but I am at a loss on how to do that.
My company is finally using my accounts package (alongside our original package until we can be certain there are no bugs).
Anyway just adding nice to have features at the moment.
I've got a form that has 5 combo boxes where the user can select 5 customers. Then a button so they can graph the amount of money generated by each customer and compare them.
I've just made a query to return the top 5 highest grossing customers.
What I what to do now it feed the query results into the combobox values when I press a new button on my form.
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
I have a form titled 'New/Edit/Search Contractor Employee" that provides controls to fields within a table titled "Contractors". In this table, I have a Hyperlink field that will store the location of the files related to individual employees. On this form, I have a Control that (at the moment) the user can copy a Hyperlink and enter into this, to populate the field. This will then allow the user to click on this Hyperlink to open up the individual folder location to drag and drop whatever files they want associated to this individual record.
I have created a Command Button to automatically create a unique folder for this record, VBA below:
Private Sub Command168_Click() strPath = "V:IntProdTrans&OpsTERMINALSDriver and Contractor Database 2014ContIndPW" & "" & txtCompanyID If Len(Dir(strPath, vbDirectory)) = 0 Then MkDir strPath End If strPath = "V:IntProdTrans&OpsTERMINALSDriver and Contractor Database 2014ContIndPW" & "" & txtCompanyID & "" & txtFirstName & " " & txtLastName If Len(Dir(strPath, vbDirectory)) = 0 Then MkDir strPath End If End Sub
What I'm trying to work out now is how to automatically enter this new folder location into the Control, which is then viewable and can be clicked on. I managed to do this (can't for the life of me remember how) but it entered the location as text and I wasn't able to click.
The Hyperlink control is titled 'Induction Paperwork' relates to a Hyperlink field.
I'm trying to tie a from into the results from and AD Query. I'm not that experienced with doing this kind of thing so I may be going about this the wrong way. Anyway here's what I've got so far:
Code: Private Sub Form_Open(Cancel As Integer) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = New ADODB.Connection With cn .ConnectionString = "Provider=ADSDSOObject;Trusted_Connection=yes;"
[Code] .....
When stepping through the code, it all goes well until it hits the line that actually connects it to the form "Set Me.Recordset = rs".
When this line is executed, Access crashes and attempts to restart.
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
I have a search form to run a query to produce matching records from a few tables. However there are many fields so I was wondering if I could make something like a split form to view the results from the query in one window.
I know you can make queries produce tables and you can have split forms linked to table information. I wasn't sure if you could/ how you would run a query and produce a table with a split form.
Ok, i have tried and searched but could not find a good start to a solution for my problem. I have a calendar form, which shows the dates and under it text labels. Now whenever something is present in my table (tblKalender) on those dates, i want it to show in the text labels under the correct date. Now, i have the results through a query, but i dont want to link the query to the form. Only thing that i have trouble with now is getting the information i need to show to those labels. I have written some code (early stages) which i want to show me the results for testing, later on i can fill the fields. I keep getting an error saying not enough parameters, expected is 1.
Can anybody take a look at the code or help me with this? My query for day one is called qerKal1 and the fields i need to get back are the hour and the task. The query gets it's criteria from the label above that day (lbDag1), which shows the date.
I have this code in a module and when the form is opened it runs the fucntion.
I have a set of data in a database that has a field "parent id" (the id of another row in the database) that I want to track back to where the parent id = 0
Now there could be any number of iterations needed to get through to this result so i cant just run a query once...
I could write a vb script to do the queries and build up an array of the results, but I am wanting to access this data through crystal reports so dont think that option will work.
Ideally what I want to do is create a query that will return the list of data as a result set that I can access through crystal.
Is this possible at all?
If it is, please can someone advise how this could be done?
I have an access database that is being filled in with different client names. I also have a form that when filled in does a mail merge to word so that letters can be sent out to people with their personal information. I want to know how to search in the database and when I click on the appropriate client it will put the information into the form. I have Office 2010 products and am using windows 7. Let me know if you need more information and I can supply it. The other option is that I create a seperate search form and when I search for something it will pull up multiple results in a grid and then from there I can click on the client that I want and send it to the 1st form.
Sooo, I finally have the chance to tear apart some of the horrible structures that live in one of my databases.
Currently it Tracks systems access levels across the company, and to do this we create Roles that are loosely based on the company structure and comprise of 4 constituent parts in the name and a bunch of stuff that govern the access level of accounts in that role on the system in question.
To handle this I have 4, interrelated, Tables called role 1, role 2 and so on which contain simply the descriptor of the role part that they contain, so that [Role 1] might contain "Finance", [role 2] might contain "payroll", [role 3] "contrator payments", [role 4] "payments administrator".
Role 1 is related to role2,3,4 and so on up the chain and each individual role table is related to the "master" Role definition which contains the access level information for the system in question.
I'm hoping at this point that everyone currently looks similar to :eek:
If not, let me add that A role can currently contain either [role 1],[role 2][role 3] and a placeholder "#no level 4#" or can contain a "proper" descriptor in [Role 4].
Because of the design, we currently have 3000+ "no level 4#"s held in [Role 4] (wheres the slap head smiley when you need it?)
Now I've been looking at a number of ways of trying to Normalise and improve this part of the DB, the obvious solution, because role 1-4 tables are purely descriptors is to just combine all of those into one "role" table, stick a junction table between it and the Role Definition table and be done with it. However this still leaves several problems, we're still, sort of, hardcoded to 4 levels within the database itself (ok so we can just add another column if we need more) and a few other obvious failings.
Still with me?
So I've started to look into the possiblity of using a recursive relationship on what is still, in effect, the Junction table between the descriptors and the Role Definition.
At a basic level I now have 3 tables:
Role ---- RoleID - PK Description - varchar
Roleconfig ---------- ConfigID - PK ParentconfigID - int RoleID - int
Roledefinition ------------- ID RoleconfigID
ParentconfigID relates to ConfigID within RoleConfig
At the moment, this structure, again at a basic level, now appears to work. However the variable elements within a role looked like a potential problem. Finding element one is simple, the [partentconfigID] is NULL. Finding the Top element when you've got 4 is simple, [configID] doesn't appear in [parentconfigID].
Where the fun starts is trying to control the recursion where you've got role1,role2, role3 being a valid role description and a role4 added to it also being a valid role description. Now as far as I can see there are two options to handle this.
1) Create in Roleconfig an entry (ok, entries) for role1,2,3 and use that as your 3 element role description. Create new entries containing the same information for your 1,2,3,4 role element. Less than ideal for, I hope, obvious reasons, we're still basically duplicating information and it is also difficult to build your role description in a query because you don't know how many elements will comprise that description.
2) Add a "valid" boolean column to roleconfig so that you can reuse your existing 1,2,3 and simply tag role 3 as 'valid', then add a role4 element and also tag that as 'valid'. The main downside to this is similar to the last one above, you know that valid means it is a top level description, but you still don't know how many elements there are and outputting a list containing
Finance-Payroll-ContractorPayments-PaymentAdmin AND Finance-Payroll-ContractorPayments
As valid roles still looks like it requires some jiggery-Pokery
I still have some concerns about controlling the recursion and ensuring that roledefinition can only relate back to a valid top level role which looks like it will require some careful planning. It's necessary to create a validation rule so that parentconfigID cannot be the configID for example, and I'll need to ensure that Roledefinition cannot relate to a roleconfig that isn't the last element in the chain.
We already "shoehorn" what are effectively 5+ element role descriptions into this structure, using recursion like this, I believe, eliminates the need for future Database changes if the front end code is amended to handle it. Which I guess is where the "discussion" part of the thread title comes in.
Sorry for the length of the thread, but this is melting my brain at the moment and it's not something that seems to come up very often so thought it might be interesting.
I am trying to write a product-row material cost program. Every product consists of row materials. When I sum up cost of row materials of each product I can find cost of products. But when the row material of the a product is a row material again the my solution does not work.
I have another table that has costs of row materials.
My query computes the cost of p1 but does not compute the cost of p2 since not knowing the cost of p1. I need a recursive structure that can compute cost of p2.
Hi guys, I know this has been discussed before, but I cannot seem to find any threads regarding this issue.
So, I'm using the tab control on my form, but every time I open my form the view scrolls down and I cannot see the actual tabs. Is there any way to correct this?
Code: Outfits: ID | Top Color ID | Bottom Color ID 1 2 1 2 3 4
Colors: ID | Name 1 Red 2 Blue 3 Green 4 Orange
I'd like to have a query in design view to have the following result
Code: Outfit ID | Top Color Name | Bottom Color Name 1 Blue Red 2 Green Orange
In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name".
