Copy Paste Looping 1

[Jean9]

I have created a listbox containing all the tables in the database. Each table stores permissions for users for different applications. The adminstrator would like to copy user's permissions to add a new user. Once the user is selected that the administrator wants to use to copy permissions, the list is narrowed down to just those tables that the user exists in. I want to be able to loop through the list and for each selected table, copy the column values and append the column values to the same table but with the new user's userid. Is there a way to do this without running an insert statement that selects all columns individually save the key userid column? Every table has different columns in it. I didn't want to have to create sql for each individual table. I just want to have a single routine that, for each selected value from the list, searches for the CopyUser userid in that record and adds a row for the new user using the CopyUser's record's values.

Any suggestions?
Thanks,
J9

 

[TheAceMan1]

How are ya Jean9 . . .

A starting point . . . copy/paste the following routine to the code module of the form:

[blue]Public Sub XferPermiss(srcID As Long, desID As Long)
   Dim db As DAO.Database, rstSrc As DAO.Recordset, rstDes As DAO.Recordset
   Dim Lbx As ListBox, x As Integer
   
   Set db = CurrentDb
   Set Lbx = Me![[purple][B][I]ListboxName[/I][/B][/purple]]
      
   For x = 0 To Lbx.ListCount - 1
      Set rstSrc = db.OpenRecordset(Lbx.Column(0, x), dbReadOnly)
      Set rstDes = db.OpenRecordset(Lbx.Column(0, x), dbOpenDynaset)
      rstSrc.FindFirst "[UserID] = " & srcID
      
      [purple]rstDes.AddNew[/purple]
      
      For x = 0 To rstSrc.Fields.Count - 1
         If rstSrc.Fields(x).Name = "UserID" Then
            [b]rstDes.Fields(x) = desID[/b]
         Else
            [b]rstDes.Fields(x) = rstSrc.Fields(x)[/b]
         End If
      Next
      
      [purple]rstDes.Update[/purple]
      Set rstDes = Nothing
      Set rstSrc = Nothing
   Next
   
   Set Lbx = Nothing
   Set db = Nothing
   
End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MichaelRed]

I am slow, old and confused.

I am 'uderstanding' that there is a 'database' which stores all of the data for multiple (un-related?) applications. In the Database, each table includes the set of usernames, the applications and the permissions set for that user?

If this (mis?)-understanding is true. it seems like a nightmare ripe for trouble, so please explain it somewhat.



MichaelRed

 

[TheAceMan1]

Howdy MichaelRed!

Good to see your still here!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Jean9]

Worked perfectly! Thank you.