Good morning. I am not getting an error thrown with my syntax, but I am not getting any data inserted into my table either so I know something is awry. I have used the Debug.Print to verify that my variables hold the appropriate values, and I have manually checked and verified that the Create Table statement is executing and the table is being created, so the issue must lie with my syntax in the recordset.
Can someone more proficient in the use of recordsets analyze my code and tell me what I need to change so it executes as desired?
Code:
'Variables declared at top of module
Public userid As String
Public username As String
Public userdescription As String
'Variables set in Public Sub
userid = "jj21863"
username = "James Jones"
userdescription = "normal user, no admin rights"
'Statement to create table
DoCmd.RunSQL ("Create Table [temp] (userid Text,username Text,userdescription Text)")
'Recordset to iterate the records and insert into the created table
Dim rsSelect As DAO.Recordset
Dim rsInsert As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rsSelect = db.OpenRecordset("Select [importeddata]." & userid & " FROM [importeddata]")
Set rsInsert = db.OpenRecordset("temp")
Do While Not rsSelect.EOF
rsInsert.AddNew
rsInsert!userid = rsSelect.Fields(" & chr(34) & userid & chr(34) & ")
rsInsert!username = ConcatRelated("&username&", "[importeddata]", "[userid] =" & rsSelect!userid)
rsInsert!userdescription = ConcatRelated("&userdescription&", "[importeddata]", "[userid] =" & rsSelect!userid)
rsInsert.Update
rsSelect.MoveNext
Loop