Unexplained Recordset Error 1

[Bonediggler1]

Hello--

I am getting the "method or data member not found" error on .[BLAH] with the code below. All table, field, etc names exist and are spelled correctly. I just used the same code in a different table without error. Any idea what the problem is???


Sub TEST()

Dim DBS As Database
Dim RST As Recordset

Dim X As Integer
Dim RTYPE

Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("tblTEST")

With RST
.MoveFirst

For X = 1 To RST.RecordCount

RTYPE = .[BLAH]

DBS.Execute "INSERT INTO TBLTEST SELECT '" & RTYPE & "' AS DATA "

.MoveNext

Next
End With


End Sub

 

[dhookom]

I would change to the following

Sub TEST()
[green]'be explicit with DAO[/green]
Dim DBS As DAO.Database
Dim RST As DAO.Recordset
Dim X As Integer
Dim RTYPE As Variant

Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("tblTEST")

With RST
    .MoveFirst
    [green]'don't rely on recordcount unless you MoveLast[/green]
    Do Until .EOF
        RTYPE = ![BLAH]
        [green]'identify the field to insert into and use Values()[/green]
        DBS.Execute "INSERT INTO TBLTEST (DATA) Values ('" & RTYPE & "')"
       .MoveNext
    Loop
    [green]'clean up[/green]
    .Close
End With
[green]'clean up[/green]
SET RST = Nothing
SET DBS = Nothing

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Bonediggler1]

Thank you dhookom

It basically came down to changing:

.[BLAH] to ![BLAH]


Strange though because in the real code i have an outer loop using only .[] which works, but for the inner loop it had to be ![]

 

[PWise]

I dont understand what you are doing

opening a table and copying the table on to itself

btw this can be done like this

Dim DBS As DAO.Database
DBS.Execute "INSERT INTO TBLTEST Select BLAH from TBLTEST "