Set Form RecordSource from In-Memory Table Recordset

[Letra]

First of all, sorry my poor english and the way I explain things newbie.
After spend some hours and reading thousands of code lines, I decided to request for some help.

I'm making an hierarchy administration menu and depending the user level, it will have its own menu with different permissions for each menu item.
When setting the user menu item permissions, as database administrator user, the idea is, select user from a list and in other list, set its permissions for all its menu items.

Example: User XYZ selected with Manager Level

Description | Active | Permissions (Insert, List, Read, Update, Delete ...)
Menu item 1 [_] [_] [_] [_] [_] [_] [_]
Menu item 2 [x] [_] [x] [x] [_] [_] [_]
Menu item 3 [_] [_] [_] [_] [_] [_] [_]
Menu item 4 [x] [x] [x] [x] [x] [x] [x]
Menu item 5 [_] [_] [_] [_] [_] [_] [_]
Menu item 6 [x] [_] [x] [x] [x] [_] [_]
Menu item 7 [_] [_] [_] [_] [_] [_] [_]

After select all the pretended permissions, insert it into a table.


To do this, I select data from several tables, grab it and after create an in-memory table the data will be placed and then made a recordset of it.
After all I clone the DAO.Recordset

Now comes the problem, this line do nothing. Even an error.
Parent!frm_Main_A_UsersMenu_MenuList.Form.RecordSo urce = Virtual_Rs_Clone

I have tryed ***.Recordset = Virtual_Rs_Clone
and nothing has happened.

I leave here a part of the database. I have made some changes but the main code is there.
To test is, open the form: frm_Main_First2Open

Can anyone help me with this.

Regards, Letra


Dim UMRU_Db As Database
Set UMRU_Db = CurrentDb

Dim UMRU_AppMenu_Rs As DAO.Recordset
Dim UMRU_UserMenu_Rs As DAO.Recordset

Select Case UCase(Me.sub_UsersMenu_fld_Level.Value)
Case "ROOT"
Set UMRU_AppMenu_Rs = UMRU_Db.OpenRecordset("SELECT tbl_AppMenu.* FROM tbl_AppMenu WHERE tbl_AppMenu.fld_LevelAcronym='R' OR tbl_AppMenu.fld_LevelAcronym='A' OR tbl_AppMenu.fld_LevelAcronym='G' OR tbl_AppMenu.fld_LevelAcronym='U' ORDER BY tbl_AppMenu.fld_LevelAcronym;")
Case "ADMINISTRAtOR"
Set UMRU_AppMenu_Rs = UMRU_Db.OpenRecordset("SELECT tbl_AppMenu.* FROM tbl_AppMenu WHERE tbl_AppMenu.fld_LevelAcronym='A' OR tbl_AppMenu.fld_LevelAcronym='G' OR tbl_AppMenu.fld_LevelAcronym='U' ORDER BY tbl_AppMenu.fld_LevelAcronym;")
Case "MANAGER"
Set UMRU_AppMenu_Rs = UMRU_Db.OpenRecordset("SELECT tbl_AppMenu.* FROM tbl_AppMenu WHERE tbl_AppMenu.fld_LevelAcronym='G' OR tbl_AppMenu.fld_LevelAcronym='U' ORDER BY tbl_AppMenu.fld_LevelAcronym;")
Case "USER"
Set UMRU_AppMenu_Rs = UMRU_Db.OpenRecordset("SELECT tbl_AppMenu.* FROM tbl_AppMenu WHERE tbl_AppMenu.fld_LevelAcronym='U' ORDER BY tbl_AppMenu.fld_LevelAcronym;")
Case Else
Set UMRU_AppMenu_Rs = UMRU_Db.OpenRecordset("SELECT tbl_AppMenu.* FROM tbl_AppMenu WHERE tbl_AppMenu.fld_LevelAcronym='NONE' ORDER BY tbl_AppMenu.fld_LevelAcronym;")
End Select
Set UMRU_UserMenu_Rs = UMRU_Db.OpenRecordset("SELECT tbl_UsersMenu.* FROM tbl_UsersMenu WHERE tbl_UsersMenu.fld_UserName=" & Me.sub_UsersMenu_fld_UserName.Value & ";")

If Not (UMRU_AppMenu_Rs.EOF And UMRU_AppMenu_Rs.BOF) And Not (UMRU_UserMenu_Rs.EOF And UMRU_UserMenu_Rs.BOF) Then
UMRU_AppMenu_Rs.MoveFirst
UMRU_UserMenu_Rs.MoveFirst




'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'TEMPORARY TABLE CREATION
Dim Virtual_Rs As DAO.Recordset
Dim Virtual_Rs_Clone As DAO.Recordset

With DBEngine
.BeginTrans
With .Workspaces(0)(0)
On Error Resume Next
.Execute "CREATE TABLE Virtual_Table (vrt_ControlName TEXT, vrt_MenuItem TEXT, vrt_MenuItem_Active BIT, vrt_MI_Insert BIT, vrt_MI_List BIT, vrt_MI_Read BIT, vrt_MI_Update BIT, vrt_MI_Delete BIT, vrt_Manipulate BIT, vrt_MI_Execute BIT, vrt_MI_Backups BIT);"
Set Virtual_Rs = .OpenRecordset("Virtual_Table", dbOpenTable)
With Virtual_Rs
Do While Not UMRU_AppMenu_Rs.EOF
.AddNew
.Fields("vrt_ControlName") = UMRU_AppMenu_Rs("fld_ControlName")
.Fields("vrt_MenuItem") = UMRU_AppMenu_Rs("fld_MenuItem")
.Fields("vrt_MenuItem_Active") = UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName"))

If InStr(1, UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName") & "_Rights"), "I", 0) > 0 Then
.Fields("vrt_MI_Insert") = True
Else
.Fields("vrt_MI_Insert") = False
End If
If InStr(1, UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName") & "_Rights"), "L", 0) > 0 Then
.Fields("vrt_MI_List") = True
Else
.Fields("vrt_MI_List") = False
End If
If InStr(1, UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName") & "_Rights"), "R", 0) > 0 Then
.Fields("vrt_MI_Read") = True
Else
.Fields("vrt_MI_Read") = False
End If
If InStr(1, UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName") & "_Rights"), "U", 0) > 0 Then
.Fields("vrt_MI_Update") = True
Else
.Fields("vrt_MI_Update") = False
End If
If InStr(1, UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName") & "_Rights"), "D", 0) > 0 Then
.Fields("vrt_MI_Delete") = True
Else
.Fields("vrt_MI_Delete") = False
End If
If InStr(1, UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName") & "_Rights"), "M", 0) > 0 Then
.Fields("vrt_MI_Manipulate") = True
Else
.Fields("vrt_MI_Manipulate") = False
End If
If InStr(1, UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName") & "_Rights"), "X", 0) > 0 Then
.Fields("vrt_MI_Execute") = True
Else
.Fields("vrt_MI_Execute") = False
End If
If InStr(1, UMRU_UserMenu_Rs("fld_" & UMRU_AppMenu_Rs("fld_ControlName") & "_Rights"), "M", 0) > 0 Then
.Fields("vrt_MI_Backups") = True
Else
.Fields("vrt_MI_Backups") = False
End If
.Update

UMRU_AppMenu_Rs.MoveNext
Loop

Set Virtual_Rs_Clone = .Clone
.Close
End With

'Debug.Print Virtual_Rs_Clone
'Do While Not Virtual_Rs_Clone.EOF
' Debug.Print "> " & Virtual_Rs_Clone("vrt_ControlName") & " - " & Virtual_Rs_Clone("vrt_MenuItem") & " - " & Virtual_Rs_Clone("vrt_MenuItem_Active")
' Virtual_Rs_Clone.MoveNext
'Loop

End With
End With
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||


Else
'Sem registos para mostrar
End If

'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'FILL FORM RECORDSOURCE WITH VIRTUAL RECORDSET

Virtual_Rs_Clone.MoveLast
Virtual_Rs_Clone.MoveFirst




Parent!frm_Main_A_UsersMenu_MenuList.Form.RecordSo urce = Virtual_Rs_Clone 'SELECT * FROM Virtual_Table (Full Virtual_Rs_Clone Recordset)

'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||
'||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||||||||||||||||||||||



UMRU_AppMenu_Rs.Close
Set UMRU_AppMenu_Rs = Nothing
UMRU_UserMenu_Rs.Close
Set UMRU_UserMenu_Rs = Nothing



https://drive.google.com/open?id=19M...Ns2EfyNyWnDQMu
File: Virtual_Table.accdb

[June7]

Usual approach is to set the RecordSource property with an SQL statement. Why do you need a temporary virtual table?

In future, please post lengthy code between CODE tags to retain indentation and readability. Also, forum will drop some characters or convert some strings to smileys if not within CODE tags.

Also, many here will not download files from external locations. A 2mb zip file can be attached to post. Follow instructions at bottom of my post.

[pbaldy]

Also, many here will not download files from external locations. A 2mb zip file can be attached to post. Follow instructions at bottom of my post.

It was already attached.

[Letra]

The virtual table is used to create a dinamyc menu.
I've tryed to use an SQL statement on RecordSource property, but it gives me an error that table doesn't exist.
If I use a phisical table, it will take longer to process the request. At home, it works, almost fine, but at work (where database will be used), the computers are too slow and it takes too long.

[June7]

You say the physical table works at home but is too slow at work?

Review https://www.databasejournal.com/feat...Recordsets.htm

It states to set RecordSet property, not RecordSource. Example:

Set Me.Recordset = rs