Open a form with a sub-Form

[mloucel]

Hello Gurus..

I have a form that when opened Opens a Sub Form containing some useful data for the user, I really as ADMIN do not need that data, and interferes when I try to perform maintenance on the DB, for some reason even when I close the Form something is left Open and the laccdb file is never deleted, I did a reasearch even in this forums but nothing I did worked, I found some great code:

        With CurrentData
        
            ' "Tables"
            For Each aob In .AllTables
                If aob.IsLoaded Then
                    DoCmd.Close acTable, aob.Name, acSaveYes
                End If
            Next aob
                
            ' "Queries"
            For Each aob In .AllQueries
                If aob.IsLoaded Then
                    DoCmd.Close acQuery, aob.Name, acSaveYes
                End If
            Next aob
                                    
        End With

But damn thing never closes, but I noticed that when I go to Design View, the damn thing closes, so I had an Idea, create 2 Forms 1 with the Original form for the users and 1 for me the ADMIN, that did not have the sub-form, and it works fine.

BUT.

What if I can open the original form and before it opens, ask:

Is this the superadmin
if Yes
then Do Not Open the Sub-Form and Hide it
else
Continue Normally
end if

Is this possible? and if so, what type of VBA Code should I use?

Thanks..

 

[theDBguy]

That should be possible but can't tell you specifics without knowing details of your setup. Can you post a sample db?

 

[mloucel]

That should be possible but can't tell you specifics without knowing details of your setup. Can you post a sample db?

sure, let me do clean version without all the tables and bells and whistles as soon as I get to the office.
thanks.

 

[isladogs]

You can choose how a form loads (with or without subform) using open args

 

[arnelgp]

an .laccdb file will only got removed if all users of your apps is not using it.
.laccdb files are temporary lock files used for multi-user access and concurrency control in Microsoft Access databases using the .accdb format.

 

[mloucel]

an .laccdb file will only got removed if all users of your apps is not using it.
.laccdb files are temporary lock files used for multi-user access and concurrency control in Microsoft Access databases using the .accdb format.

Thanks @arnelgp , but this is not even production DB, this is my test DB, I split the DB, but that's it, and I am the only one with access to the folder that contains the DB.
Once I open the menu it access a form created by a Qry, when I go to menu that allows me to compact the DB, I close the Menu, that should release the Sub-Form, it doesn't, so I found a way to go 1 by one on all the tables and close them if open, same with any Form, I posted my code on #1.
Still no matter the laccdb does not go away, I've been testing, and when I actually go into design view, the damn thing goes away, so I end up creating a Menu without the Sub, and that one works like charm, no open Sub, No tables opened, no queries, it just works.
But having 2 forms seemed dumb, hence the idea of somehow opening the Menu but if the superadmin [ME] logs it won't open the sub-form, so that I do not have that problem, when doing the backup.

Anyhow, I'll try to get rid of all the bells and whistles and post my DB, maybe it will be easy this way.

Thanks Arnel.

 

[mloucel]

You can choose how a form loads (with or without subform) using open args

Colin, you're a genius in my book, but I do not have any idea ow can I accomplish that, nevertheless, I'll google and try.

Thank you sir.

 

[DHookom]

Do you have any way of automatically determining “superadmin [ME] “? There are function available that identify the user logged into Windows.

 

[mloucel]

Do you have any way of automatically determining “superadmin [ME] “? There are function available that identify the user logged into Windows.

Yes, all of our computers are marked WS001, 002, etc, so no luck there, my DB has a users Table, and the SuperAdmin [Me] is hard coded, so that no one can eliminate it, but there are 2 passwords, SA with password for SA, and SA with a password, that gives SA minus a few tasks.
I use tempvars.

 

[DHookom]

You could use your Tempvars in code to set the source object of the subform control and maybe set its Visible property.

 

[mloucel]

That should be possible but can't tell you specifics without knowing details of your setup. Can you post a sample db?

Took me a little while to clean up the db enough so I can submit only the essentials, there's 2 zip files, FE and BE, I had them on a folder called:

c:\apps

DISCLAIMER:

For the ones that will open the db, I AM A NEWBIE, do not expect a top notch, top class code, I did what I have learned so far, I understand that all of you have more experience, I just want to learn, all your suggestions are welcome.

there are 2 menus, MenuF is the one with problems not closing the BE, instead of the login I added a form with a button to open the menu, to mimic my login.
I would like to know how to open MenuF without the sub-form so that I can perform my compact routine.

In my Login I have a specific routine that works when the superadmin is login, so I will have to add the routine to stop the sub-form from opening on MenuF when the superadmin is logged.

And I will have to kick out everyone from the db prior of course.

Any Help will be appreciated.

 

[isladogs]

I've done a simple approach using OpenArgs as mentioned back in post #4
It could be improved further by changing the form height but I've not done that
I've not examined any of your other code or made any other changes
Both FE & BE in the same zip file

Hope that helps

BTW: One good reason for unsplitting files is to simplify uploading cut down databases

 

[mloucel]

I've done a simple approach using OpenArgs as mentioned back in post #4
It could be improved further by changing the form height but I've not done that
I've not examined any of your other code or made any other changes
Both FE & BE in the same zip file

Hope that helps

BTW: One good reason for unsplitting files is to simplify uploading cut down databases

Yes!!!

it worked, I did a change to my code where after the compact is done I ask the superadmin if he wants to open the MenuF in Full view or Restricted View, Finally I was able to perform the Backup and Compact and Repair, successfully.

Thank you, Colin.