In the segment of code below, I get an error Object Required on the "Set rsName" statement. Hopefully the code is clear enough to understand that the intent is to use the rsName object as the name of a DOA recordset name, of which there are four. I'm assuming there's something else required to make such a variable substitution?
Bill
Code:
Private Sub MigrateHdgs()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim rsHeadings As DAO.Recordset
Dim rsHd1 As DAO.Recordset
Dim rsHd2 As DAO.Recordset
Dim rsHD3 As DAO.Recordset
Dim rsHD4 As DAO.Recordset
Dim HDName As String
Dim tblName As String
Dim rsName As Object
Dim ndx As Integer
Dim JustSavedID As Integer
Set rsHeadings = DBEngine(0)(0).OpenRecordset("QHeadings") 'Sorted by HD1 names
strSQL = "SELECT * FROM tblHD1"
Set rsHd1 = DBEngine(0)(0).OpenRecordset(strSQL) 'Should be empty
strSQL = "SELECT * FROM tblHD2"
Set rsHd2 = DBEngine(0)(0).OpenRecordset(strSQL) 'Should be empty
strSQL = "SELECT * FROM tblHD3"
Set rsHD3 = DBEngine(0)(0).OpenRecordset(strSQL) 'Should be empty
strSQL = "SELECT * FROM tblHD4"
Set rsHD4 = DBEngine(0)(0).OpenRecordset(strSQL) 'Should be empty
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Loop through the headings and populate the HD1, HD2, HD3 and HD4 tables as appropriate.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
rsHeadings.MoveFirst
Do While Not rsHeadings.EOF
arr = Split(rsHeadings!folder, "/")
JustSavedID = 0
For ndx = 0 To UBound(arr)
HDName = arr(ndx)
tblName = "tblHD" & Trim(Str(ndx + 1))
Set rsName = "rsHD" & Trim(Str(ndx + 1))
rsName.AddNew
rsName!title = rsHeadings!Name
rsName!Type = ndx + 1
rsName!Fmt = ndx * 2
rsName!MbrID = Null
rsName!PtrURLs = Null
rsName!PtrNxtHD = Null
If JustSavedID > 0 Then _
rsName!MbrID = JustSavedID
rsName.Update
rsName.Bookmark = rsName.LastModified
JustSavedID = rsName!HdID
Next ndx
rsHeadings.MoveNext
Loop
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Close and release the Recordsets
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
rsHeadings.Close
Set rsHeadings = Nothing
rsHd1.Close
Set rsHd1 = Nothing
rsHd2.Close
Set rsHd2 = Nothing
rsHD3.Close
Set rsHD3 = Nothing
rsHD4.Close
Set rsHD4 = Nothing
End Sub