Please read Masters of Access VBA (1 Viewer)

[sipi41]

I have a question. On the right hand, you can organize items by custom groups, let's say:

Transactions
Sales
Distribution
etc
etc
etc

If I have created a table, report, or form, and I know this form belongs to SALES, I can take the item with the mouse, drag it over the group and it become part of it, now, how do I do that programmatically...??

 

[LPurvis]

Doug Steele covers drag and drop emulation in Access in an article from... a while ago.
http://www.accessmvp.com/DJSteele/SmartAccess.html

You'll need to invest some time and effort - it's not at all inherent functionality.

Cheers

 

[sipi41]

wrong!

 

[LPurvis]

Seriously? That's your response?

 

[boblarson]

wrong!

Not a good response to ANYONE who is helping. But especially bad when responding to an Access MVP who knows a TON more than even I do about Access and the inner workings of Access.

A better response would have been something like, "That doesn't appear to answer my question. I was not wanting drag and drop functionality but instead just a way to add my object to the group in the database window."

 

[sipi41]

Does anybody know how to move an item from uncategorized group to any other group on the bar at the left??

 

[datAdrenaline]

Ok ... it seems there is a disconnect between what you are asking and the response you recieved. That much is obvious. With that, it would seem, you should take the time to look at your question, as well as the response and see how YOU the questioner can explain your goal more clearly in order to provide more detail to the volunteers at this site (or any other site you visit). For example, you talk about "on the right" in the first post, but "on the left" in a subseqent post. Remember, YOU are the one responsible for clearly expressing your issue, and in this case you have not done a very good job of expressing what you need.

----

Now, on to your issue. ----
Please correct me if I am wrong, but you are talking about categorizing (grouping) your Database Objects in the Database Window/Navigation Pane correct? In other words, this has nothing to do with DATA, it has to do with organization of your Access Objects into NavPane/Database Window Categories/Groups -- programmatically.

With that being the case, my first question is WHY? Organizing your objects would fall under the "development" end of creating a database application. Your users should not be creating database objects that need to be placed in a category/group. You, as the developer, should be providing all the objects that are required to run your application. The users interaction with the NavPane/Database Window is generally discouraged among developers who disribute Access applications.

But ... if you are using A2007/A2010, you can manipulate the data in the tables that govern the categorization and grouping:

MSysNavPaneObjectIDs (List of your objects, PK is a field named Id)
MSysNavPaneGroups (List of your custom groups - the names of them, PK is a field named Id)
MSysNavPaneGroupCategories (List of the names of the categories that contain the name of your custom groups, PK is a field named Id)
MSysNavPaneGroupToObjects (The junction table that links your database objects to the custom category, PK is a field named Id)

The hierarchy is this (note that these are 'virtual' relationships, so there is no Referential Integrity with cascading update/delete):
MSysNavPaneGroupCategories (a domain table)
MSysNavPaneGroups (Field GroupGroupCategoryId is FK to MSysNavPaneGroupCategories.Id; Field ObjectId is a FK to MSysNavPaneObjectIDs.Id -- but this is really not relavent in custom groups that you are trying to manage programatically)
MSysNavPageObjectIDs (a domain table, the data is a sub-set of what you find in MSysObjects)
MSysNavPaneGroupToObjects (Field GroupId is FK to MSysNavPaneGroups.Id; Field ObjectId is FK to MSysNavPaneObjectIDs.Id)

----

Please note that these tables are maintained by the Access UI and while you have the power to change the data in it, I would caution you because you also have the ability to screw a lot of stuff up. So, if you mess with this data, then only mess with the records that relate to your customizations and not the information that Access generates.

 

[sipi41]

I take ur advise, thank u!

mmm... regarding the response... mmm... i did not understand anything... any examples?

At leaset it gives me an idea... Thank you!

 

[datAdrenaline]

What version of Access are you using?

 

[sipi41]

Access 2007

 

[datAdrenaline]

I don't have an example of modifying the data in the MSysNavPane tables since I subscribe to the belief that you really should not have a need to do so.

Can you review with us why you would like this task automated? Not that you have to justify it, but often time people think they need to proceed one way, when there is a different, more accepted path to provide users with the same thing.

 

[sipi41]

Yes, thank you!

I have a button that when pressed, will look into other databases for data, to do that, instead of importing all data, I dynamically linked those tables, so it create some tables... is something similar to:

History Southwest Area
History West Area
History Sub-north area
etc
etc
etc

It will create the tables on the UN-categorized group, so, my idea is to create a group called HISTORY ONLY, and put those tables there, but I don't know how

 

[datAdrenaline]

So assuming that you have a custom group named "History Only", you will want to go into the table MSysNavPaneGroups and find the row that equates to your custom group name. You will want to remember the Id column value. We'll be storing that value in a VBA variable that we'll call lngGroupId (you will have to declare it appropriately in your code). Now comes the code that we're going to shoe horn into your existing code ...

You do have code that links your tables right? So, after you create the linked table object, you will want to have to add something like this ...

    'Set the GroupId of the group you want to place the table in
    lngGroupId = <some fixed number that you find by looking at the table>
 
    'Get the ObjectId of the newly added Linked Table object.
    lngObjectId = Nz(DLookup("Id", "MSysObjects", "[Name] = '" & strLinkedTablename & "' And [Type] In (1,4,6)"), 0)
 
    'Get the position of the newly added linked table object.
    lngPosition = Nz(DMax("Position", "MSysNavPaneGroupToObjects", "GroupId = " & lngGroupId), 0) + 1
 
    'Insert the new linked table object as a member of the custom group.
    If lngObjectId <> 0 Then
 
        With CurrentDb
 
            'Add the newly added Linked Table object to MSysNavPaneObjectIDs (if needed).
            If Nz(DLookup("Id", "MSysNavPaneObjectIDs", "Id = " & lngObjectId), 0) = 0 Then
                strSQL = "INSERT INTO MSysNavPaneObjectIDs (Id, [Name], [Type])" & _
                         " SELECT Id, [Name], [Type] FROM MSysObjects WHERE Id = " & lngObjectId
             
                .Execute strSQL, dbFailOnError
            End If
 
            'Add the new linked table object as a member of the custom group.
            strSQL = "INSERT INTO MSysNavPaneGroupToObjects (Flags, GroupID, Icon, ObjectID, Position)" & _
                     " VALUES (0," & lngGroupId & ",0," & lngObjectId & "," & lngPostion & ")"
 
            .Execute strSQL, dbFailOnError
 
        End With
 
    End If

Notes:
- This is COMPLETELY AIR CODE!!! ... I have not done any testing on it at all!
- I assume that you will declare all variables I used appropriately according to the prefix of the variable name.

----

Hope that helps!