[guyfromri]

So, I read a program written by someone else recently where the query was treated as a recordset. I like this idea but I'm having some issues with making it work. Every time I run my code, I get error '3167' "Record is Deleted". Now this obviously isn't true since I know the record is freshly imported so I imagine I missed something small or stupid before this. Any help is greatly appreciated.

This is where I get the error

Open DemoOPFile For Output As #52

    rsQuery.MoveFirst
        Do Until rsQuery.EOF
            '****************************ERROR HERE***********************************'
            If rsQuery.Fields("MRN") > "" Then Demo.MRN = rsQuery.Fields("MRN")
            '*************************************************************************'
            If rsQuery.Fields("SSN") > "" Then Demo.SSN = rsQuery.Fields("SSN")
            If rsQuery.Fields("FName") > "" Then Demo.FName = rsQuery.Fields("FName")
            If rsQuery.Fields("MName") > "" Then Demo.MName = rsQuery.Fields("MName")

Also, I did the first and most obvious thing which is move to the next line and see if I get the error...I did.

This is my entire program

'4/23/10 -- We have 4 files;

'1. Demos (in our format but no insurance company names)
'2. Insurance Company names (query together with ins ID's)
'3. Appointments
'4. Diagnosis' (No need to query for inital program but possibly in future)

'All files came in .csv so we don't have to monarch, only import then query. The demo file can  be imported to 'main_demo' table. Have to create table for Insurance ID's

Option Compare Database
Option Explicit

Dim rsDemoImp As Recordset
Dim rsApptImp As Recordset
Dim rsQuery As Recordset
'Dim rsCache As Recordset
Dim IntLastSlash As Integer
Dim IntSlashHold As Integer
Dim DemoFile As String
Dim InsFile As String
Dim ApptFile As String
Dim InitDir As String
Dim sDemoInputFile As String
Dim sApptInputFile As String
Dim sInsInputFile As String
Dim DemoOPFile As String
Dim ApptOPFile As String
Dim OPRec As String
Dim ProName As String
Dim LCount As Long
Dim LDone As Long
Dim Demo
Dim Appt

Private Sub Command14_Click()

'Private Sub Detail_Click()
    
    Set rsDemoImp = CurrentDb.OpenRecordset("Main_Demo")
    Set rsApptImp = CurrentDb.OpenRecordset("Main_Appt")
    Set rsQuery = CurrentDb.OpenRecordset("InsertInsToLytec")
    Set Demo = New ctdemodata
    Set Appt = New CTApptData
    
    ProName = InputBox("Please enter provider name")
        If ProName = "" Then End
        If ProName = "Please enter provider name" Then End
        
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * from Main_Demo"
    DoCmd.RunSQL "DELETE * from Main_Appt"
    DoCmd.RunSQL "DELETE * from LytecInsInfo"
    DoCmd.SetWarnings True
    
    If Check7.Value = True Then
        MsgBox "Select Demographics File"
            With Application.FileDialog(msoFileDialogFilePicker)
                .Title = "Demo File"
                .AllowMultiSelect = False
                If .Show = False Then End
                DemoFile = .SelectedItems(1)
            End With
     End If
    IntLastSlash = InStr(1, DemoFile, "\")
    Do Until IntLastSlash = 0
        IntLastSlash = InStr((IntLastSlash + 1), DemoFile, "\")
        If IntLastSlash <> 0 Then
            IntSlashHold = IntLastSlash
        End If
    Loop
    ' remember the selected directory for subsequent dialog boxes...
    InitDir = Mid(DemoFile, 1, (IntSlashHold - 1))
    sDemoInputFile = Mid(DemoFile, (IntSlashHold + 1))
    DemoOPFile = InitDir & "\" & ProName & "_Demos_CtReady.csv"
            
            
    If Check11.Value = True Then
        MsgBox "Select Insurance Address File"
            With Application.FileDialog(msoFileDialogFilePicker)
                .Title = "Ins File"
                .AllowMultiSelect = False
                If .Show = False Then End
                InsFile = .SelectedItems(1)
            End With
    End If
    IntLastSlash = InStr(1, InsFile, "\")
    Do Until IntLastSlash = 0
        IntLastSlash = InStr((IntLastSlash + 1), InsFile, "\")
        If IntLastSlash <> 0 Then
            IntSlashHold = IntLastSlash
        End If
    Loop
    ' remember the selected directory for subsequent dialog boxes...
    'InitDir = Mid(InsFile, 1, (IntSlashHold - 1))
    sInsInputFile = Mid(InsFile, (IntSlashHold + 1))
    'OPFile = InitDir & "\" & SProName & "_CtReady.csv"
    
        
    If Check11.Value = True Then
        MsgBox "Select Appointment File"
            With Application.FileDialog(msoFileDialogFilePicker)
                .Title = "Appointment File"
                .AllowMultiSelect = False
                If .Show = False Then End
                ApptFile = .SelectedItems(1)
            End With
    End If
    IntLastSlash = InStr(1, ApptFile, "\")
    Do Until IntLastSlash = 0
        IntLastSlash = InStr((IntLastSlash + 1), ApptFile, "\")
        If IntLastSlash <> 0 Then
        IntSlashHold = IntLastSlash
        End If
    Loop
    ' remember the selected directory for subsequent dialog boxes...
    'InitDir = Mid(ApptFile, 1, (IntSlashHold - 1))
    sApptInputFile = Mid(ApptFile, (IntSlashHold + 1))
    ApptOPFile = InitDir & "\" & ProName & "_Appts_CtReady.csv"
    
    
    DoCmd.TransferText acImportDelim, "MainDemoSpec", "Main_Demo", DemoFile, False
    DoCmd.TransferText acImportDelim, "MainApptSpec", "Main_Appt", ApptFile, False
    DoCmd.TransferText acImportDelim, "LytecInsInfoSpec", "LytecInsInfo", InsFile, False
    
    'Now the files are imported, run the query and export
    
    LCount = 0
    LDone = 0
    
    rsQuery.MoveFirst
        Do Until rsQuery.EOF
            LCount = LCount + 1
            rsQuery.MoveNext
        Loop
           
    Open DemoOPFile For Output As #52

    rsQuery.MoveFirst
        Do Until rsQuery.EOF
            
            If rsQuery.Fields("MRN") > "" Then Demo.MRN = rsQuery.Fields("MRN")
            If rsQuery.Fields("SSN") > "" Then Demo.SSN = rsQuery.Fields("SSN")
            If rsQuery.Fields("FName") > "" Then Demo.FName = rsQuery.Fields("FName")
            If rsQuery.Fields("MName") > "" Then Demo.MName = rsQuery.Fields("MName")
            If rsQuery.Fields("LName") > "" Then Demo.LName = rsQuery.Fields("LName")
            If rsQuery.Fields("DOB") > "" Then Demo.DOB = rsQuery.Fields("DOB")
            If rsQuery.Fields("Sex") > "" Then Demo.Sex = rsQuery.Fields("Sex")
            If rsQuery.Fields("Email") > "" Then Demo.Email = rsQuery.Fields("Email")
            If rsQuery.Fields("Add1") > "" Then Demo.Addy1 = rsQuery.Fields("Add1")
            If rsQuery.Fields("Add2") > "" Then Demo.Addy2 = rsQuery.Fields("Add2")
            If rsQuery.Fields("City") > "" Then Demo.City = rsQuery.Fields("City")
            If rsQuery.Fields("State") > "" Then Demo.State = rsQuery.Fields("State")
            If rsQuery.Fields("Zip") > "" Then Demo.Zip = rsQuery.Fields("Zip")
            If rsQuery.Fields("Country") > "" Then Demo.Country = rsQuery.Fields("Country")
            If rsQuery.Fields("HomePhone") > "" Then Demo.Phone = rsQuery.Fields("HomePhone")
            If rsQuery.Fields("GuarMRN") > "" Then Demo.GuarMRN = rsQuery.Fields("GuarMRN")
            If rsQuery.Fields("GuarRel") > "" Then Demo.GuarRela = rsQuery.Fields("GuarRel")
            If rsQuery.Fields("EmpName") > "" Then Demo.EmpName = rsQuery.Fields("EmpName")
            If rsQuery.Fields("EmpAdd1") > "" Then Demo.EmpAddy1 = rsQuery.Fields("EmpAdd1")
            If rsQuery.Fields("EmpAdd2") > "" Then Demo.EmpAddy2 = rsQuery.Fields("EmpAdd2")
            If rsQuery.Fields("EmpCity") > "" Then Demo.EmpCity = rsQuery.Fields("EmpCity")
            If rsQuery.Fields("EmpState") > "" Then Demo.EmpState = rsQuery.Fields("EmpState")
            If rsQuery.Fields("EmpZip") > "" Then Demo.EmpZip = rsQuery.Fields("EmpZip")
            If rsQuery.Fields("EmpCountry") > "" Then Demo.EmpCountry = rsQuery.Fields("EmpCountry")
            If rsQuery.Fields("EmpPhone") > "" Then Demo.EmpPhone = rsQuery.Fields("EmpPhone")
            If rsQuery.Fields("EmpExt") > "" Then Demo.EmpExt = rsQuery.Fields("EmpExt")
            If rsQuery.Fields("MarStat") > "" Then Demo.MarStat = rsQuery.Fields("MarStat")
            If rsQuery.Fields("GPro") > "" Then Demo.GPro = rsQuery.Fields("GPro")
            If rsQuery.Fields("GNPI") > "" Then Demo.GProNPI = rsQuery.Fields("GNPI")
            If rsQuery.Fields("Notes") > "" Then Demo.PatNotes = rsQuery.Fields("Notes")
            If rsQuery.Fields("Group") > "" Then Demo.PatGroup = rsQuery.Fields("Group")
            If rsQuery.Fields("Ins1ID") > "" Then Demo.Ins1ID = rsQuery.Fields("Ins1ID")
            If rsQuery.Fields("Ins1Name") > "" Then Demo.Ins1Name = rsQuery.Fields("Ins1Name")
            If rsQuery.Fields("Expr1") > "" Then Demo.Ins1Addy = rsQuery.Fields("Expr1")
            If rsQuery.Fields("Ins1Pol") > "" Then Demo.Ins1PolNum = rsQuery.Fields("Ins1Pol")
            If rsQuery.Fields("Ins1CP") > "" Then Demo.Ins1CoPay = rsQuery.Fields("Ins1CP")
            If rsQuery.Fields("Ins1Group") > "" Then Demo.Ins1GroupNum = rsQuery.Fields("Ins1Group")
            If rsQuery.Fields("Ins1SubMRN") > "" Then Demo.Ins1SubMRN = rsQuery.Fields("Ins1SubMRN")
            If rsQuery.Fields("Ins1SubRel") > "" Then Demo.Ins1SubRela = rsQuery.Fields("Ins1SubRel")
            If rsQuery.Fields("Ins2ID") > "" Then Demo.Ins2ID = rsQuery.Fields("Ins2ID")
            If rsQuery.Fields("Ins2Name") > "" Then Demo.Ins2Name = rsQuery.Fields("Ins2Name")
            If rsQuery.Fields("Expr2") > "" Then Demo.Ins2Addy = rsQuery.Fields("Expr2")
            If rsQuery.Fields("Ins2Pol") > "" Then Demo.Ins2PolNum = rsQuery.Fields("Ins2Pol")
            If rsQuery.Fields("Ins2CP") > "" Then Demo.Ins2CoPay = rsQuery.Fields("Ins2CP")
            If rsQuery.Fields("Ins2Group") > "" Then Demo.Ins2GroupNum = rsQuery.Fields("Ins2Group")
            If rsQuery.Fields("Ins2SubMRN") > "" Then Demo.Ins2SubMRN = rsQuery.Fields("Ins2SubMRN")
            If rsQuery.Fields("Ins2SubRel") > "" Then Demo.Ins2SubRela = rsQuery.Fields("Ins2SubRel")
            If rsQuery.Fields("Ins3ID") > "" Then Demo.Ins3ID = rsQuery.Fields("Ins3ID")
            If rsQuery.Fields("Ins3Name") > "" Then Demo.Ins3Name = rsQuery.Fields("Ins3Name")
            If rsQuery.Fields("Expr3") > "" Then Demo.Ins3Addy = rsQuery.Fields("Expr3")
            If rsQuery.Fields("Ins3Pol") > "" Then Demo.Ins3PolNum = rsQuery.Fields("Ins3Pol")
            If rsQuery.Fields("Ins3CP") > "" Then Demo.Ins3CoPay = rsQuery.Fields("Ins3CP")
            If rsQuery.Fields("Ins3Group") > "" Then Demo.Ins3GroupNum = rsQuery.Fields("Ins3Group")
            If rsQuery.Fields("Ins3SubMRN") > "" Then Demo.Ins3SubMRN = rsQuery.Fields("Ins3SubMRN")
            If rsQuery.Fields("Ins3SubRel") > "" Then Demo.Ins3SubRela = rsQuery.Fields("Ins3SubRel")
            If rsQuery.Fields("GenData") > "" Then Demo.GenData = rsQuery.Fields("GenData")
            If rsQuery.Fields("RPro") > "" Then Demo.RefPro = rsQuery.Fields("RPro")
            If rsQuery.Fields("RNPI") > "" Then Demo.RefProNPI = rsQuery.Fields("RNPI")
            If rsQuery.Fields("RNPI") > "" Then Demo.RefProNPI = rsQuery.Fields("RNPI")
            If rsQuery.Fields("Diag1Code") > "" Then Demo.Diag1Code = rsQuery.Fields("Diag1Code")
            If rsQuery.Fields("Diag1Date") > "" Then Demo.Diag1Date = rsQuery.Fields("Diag1Date")
            If rsQuery.Fields("Diag1Notes") > "" Then Demo.Diag1Note = rsQuery.Fields("Diag1Notes")
            If rsQuery.Fields("Diag2Code") > "" Then Demo.Diag2Code = rsQuery.Fields("Diag2Code")
            If rsQuery.Fields("Diag2Date") > "" Then Demo.Diag2Date = rsQuery.Fields("Diag2Date")
            If rsQuery.Fields("Diag2Notes") > "" Then Demo.Diag2Note = rsQuery.Fields("Diag2Notes")
            If rsQuery.Fields("Diag3Code") > "" Then Demo.Diag3Code = rsQuery.Fields("Diag3Code")
            If rsQuery.Fields("Diag3Date") > "" Then Demo.Diag3Date = rsQuery.Fields("Diag3Date")
            If rsQuery.Fields("Diag3Notes") > "" Then Demo.Diag3Note = rsQuery.Fields("Diag3Notes")
        
            OPRec = Demo.FormatOutputRecord
            
            Print #52, OPRec
            
            OPRec = Demo.ClearValues
            
            DoEvents
            LDone = LDone + 1
            Form_Progress.AXC.Value = (LDone / LCount) * 100
            DoEvents

ReRun:
            rsQuery.MoveNext

        Loop

    Close #52

    LCount = 0
    LDone = 0
    
    rsApptImp.MoveFirst
        Do Until rsApptImp.EOF
            LCount = LCount + 1
            rsApptImp.MoveNext
        Loop
        
Open ApptOPFile For Output As #53

    rsApptImp.MoveFirst
        Do Until rsApptImp.EOF
            
                If rsApptImp.Fields("Date") > "" Then Appt.aDate = rsApptImp.Fields("Date")
                If rsApptImp.Fields("Time") > "" Then Appt.aTime = rsApptImp.Fields("Time")
                If rsApptImp.Fields("Duration") > "" Then Appt.aDuration = rsApptImp.Fields("Duration")
                If rsApptImp.Fields("Loc") > "" Then Appt.aLoc = rsApptImp.Fields("Loc")
                If rsApptImp.Fields("LocID") > "" Then Appt.aLocID = rsApptImp.Fields("LocID")
                If rsApptImp.Fields("Type") > "" Then Appt.aType = rsApptImp.Fields("Type")
                If rsApptImp.Fields("TypeID") > "" Then Appt.aTypeID = rsApptImp.Fields("TypeID")
                If rsApptImp.Fields("Pro") > "" Then Appt.aPro = rsApptImp.Fields("Pro")
                If rsApptImp.Fields("ProID") > "" Then Appt.aProID = rsApptImp.Fields("ProID")
                If rsApptImp.Fields("GroupID") > "" Then Appt.aGroupID = rsApptImp.Fields("GroupID")
                If rsApptImp.Fields("Group") > "" Then Appt.aGroup = rsApptImp.Fields("Group")
                If rsApptImp.Fields("MRN") > "" Then Appt.aMRN = rsApptImp.Fields("MRN")
                If rsApptImp.Fields("Notes") > "" Then Appt.aNotes = rsApptImp.Fields("Notes")
                If rsApptImp.Fields("Complaint") > "" Then Appt.aComplaint = rsApptImp.Fields("Complaint")
                If rsApptImp.Fields("FName") > "" Then Appt.aFName = rsApptImp.Fields("FName")
                If rsApptImp.Fields("MName") > "" Then Appt.aMName = rsApptImp.Fields("MName")
                If rsApptImp.Fields("LName") > "" Then Appt.aLName = rsApptImp.Fields("LName")
                If rsApptImp.Fields("DOB") > "" Then Appt.aDOB = rsApptImp.Fields("DOB")
                If rsApptImp.Fields("Sex") > "" Then Appt.aSex = rsApptImp.Fields("Sex")
                
                OPRec = Appt.FormatOutputRecord
                
                Print #53, OPRec
                
                OPRec = Appt.ClearValues
                
                DoEvents
                    Form_Progress.AXC.Value = (LDone / LCount) * 100
                DoEvents
                
                rsApptImp.MoveNext
                
        Loop

    Close #53
    
End Sub
 

Thanks in advance!!

This post has been edited by guyfromri: 26 April 2010 - 08:18 AM

[raziel_]

ok i am little confused

Dim Demo
Dim Appt

Private Sub Command14_Click()

'Private Sub Detail_Click()
    
    Set rsDemoImp = CurrentDb.OpenRecordset("Main_Demo")
    Set rsApptImp = CurrentDb.OpenRecordset("Main_Appt")
    Set rsQuery = CurrentDb.OpenRecordset("InsertInsToLytec")
    Set Demo = New ctdemodata

so what is ctdemodata you dont define it as variable no where i see. your code is little mess. dont get mad or something but your using to many global variables. also your using to many code in your button click and Command14_Click dont ring the bell too . i suggest you cut them in functions and send something like recordsets byref to functions and pls add more comments to your code. uhm i rly dont understand your original question so excuse me but if i understand correct you want to do something like so

Dim RecordSet1 As New ADODB.Recordset 
Dim RecrodSet2 As New ADODB.Recordset

RecordSet1.Opern "SOME QUERY"
While Not RecordSet1.EOF
RecordSet2.AddNew
RecordSet2.Field("1").Value=RecordSet1.Field("1").Value
RecordSet2.Update
RecordSet1.Movenext
Wend

This post has been edited by NoBrain: 26 April 2010 - 11:24 AM

[guyfromri]

NoBrain, on 26 April 2010 - 10:23 AM, said:

ok i am little confused

Dim Demo
Dim Appt

Private Sub Command14_Click()

'Private Sub Detail_Click()
    
    Set rsDemoImp = CurrentDb.OpenRecordset("Main_Demo")
    Set rsApptImp = CurrentDb.OpenRecordset("Main_Appt")
    Set rsQuery = CurrentDb.OpenRecordset("InsertInsToLytec")
    Set Demo = New ctdemodata

so what is ctdemodata you dont define it as variable no where i see. your code is little mess. dont get mad or something but your using to many global variables. also your using to many code in your button click and Command14_Click dont ring the bell too . i suggest you cut them in functions and send something like recordsets byref to functions and pls add more comments to your code. uhm i rly dont understand your original question so excuse me but if i understand correct you want to do something like so

Dim RecordSet1 As New ADODB.Recordset 
Dim RecrodSet2 As New ADODB.Recordset

RecordSet1.Opern "SOME QUERY"
While Not RecordSet1.EOF
RecordSet2.AddNew
RecordSet2.Field("1").Value=RecordSet1.Field("1").Value
RecordSet2.Update
RecordSet1.Movenext
Wend

lol...sorry...I used the code I have been working on and pasted it here. I haven't had a chance to go through and clean up the junk yet. You are correct though, I am trying to use a query as a recordset. I guess, basically, this boils down to...if I treat my query like a recordset without actually calling it a query anywhere, is it possible for this to work?

Also, I have been working with functions, byref and byval but I haven't implemented them into my programs yet because I don't feel quite comfortable with my own abilities. I'm trying to learn this as I go and not ask too many stupid questions here. I understand 'ByRef' means that I am referencing a variable from another subroutine and 'ByVal' means I am taking the variable from another subroutine so I can change it if need be, down the road. As far as functions go, I've written a few but I need to work on how and when I implement them in my code. I don't think it's the most efficient way for me(personally) to do this program just because I'll end up casuing myself more headaches than I need. Although, I am going to try and clean this up a little bit and insert a few functions. I'll be back here in a few hours to post what I have and you tell me what you think and if I am going in the right direction.

Please do me a favor - until then...any idea why I would get error 3167 at this point? Does anything stand out?

As always, thanks for your input! I would never get upset with constructive criticism.

[raziel_]

well error 3167 is record is deleted error from your Database and as i can see you delete them all here

    DoCmd.RunSQL "DELETE * from Main_Demo"
    DoCmd.RunSQL "DELETE * from Main_Appt"
    DoCmd.RunSQL "DELETE * from LytecInsInfo

and you make a query for them before you delete them here

    Set rsDemoImp = CurrentDb.OpenRecordset("Main_Demo")
    Set rsApptImp = CurrentDb.OpenRecordset("Main_Appt")
    Set rsQuery = CurrentDb.OpenRecordset("InsertInsToLytec")

but i am not very sure if that is the problem

[guyfromri]

NoBrain, on 28 April 2010 - 09:03 AM, said:

well error 3167 is record is deleted error from your Database and as i can see you delete them all here

    DoCmd.RunSQL "DELETE * from Main_Demo"
    DoCmd.RunSQL "DELETE * from Main_Appt"
    DoCmd.RunSQL "DELETE * from LytecInsInfo

This is just to purge the tables of old data that may exist

Quote

and you make a query for them before you delete them here

    Set rsDemoImp = CurrentDb.OpenRecordset("Main_Demo")
    Set rsApptImp = CurrentDb.OpenRecordset("Main_Appt")
    Set rsQuery = CurrentDb.OpenRecordset("InsertInsToLytec")

but i am not very sure if that is the problem

This is to set my object variables to reference the right tables.

After I get the error, I can open the query and the data is there. I've tried adding an index column and referencing the second column but I get the same error. I read somewhere that this may mean my table or query is corrupt...any thoughts on this?

[raziel_]

nope sorry i doubt it is a corruption of the database i have the same error on work now and i will fix it soon maybe if i find what is wrong i will post it here. or if you find what was wrong you can save me some time posting it here oh 1 more thing check your recordsets maybe you move to next record before your done with this one and that cause the problem. at what line you get the error btw

This post has been edited by NoBrain: 28 April 2010 - 10:23 AM

[thava]

seems this is vba
hopefully this is not problem for corrupting
have a look at this code
just create a table Test1 in your Database with one or two columns
and fill some data

Sub test()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Text1")
DoCmd.RunSQL "DELETE * from Text1"
If Not rs.EOF Then MsgBox rs(0)
End Sub

now run this, you will get the same error as you get,
now check this

Sub test()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Text1")
DoCmd.RunSQL "DELETE * from Text1"
rs.OpenRecordset
If Not rs.EOF Then MsgBox rs(0)
End Sub

now it's working perfectly because
you fetch the record First then
delete the recordset then again
you try to access the deleted records in the recordset
so the error is obvious

the soultion is
use the OpenRecordset

This post has been edited by thava: 28 April 2010 - 06:45 PM

[guyfromri]

NoBrain, on 28 April 2010 - 09:20 AM, said:

nope sorry i doubt it is a corruption of the database i have the same error on work now and i will fix it soon maybe if i find what is wrong i will post it here. or if you find what was wrong you can save me some time posting it here oh 1 more thing check your recordsets maybe you move to next record before your done with this one and that cause the problem. at what line you get the error btw

I hope I do find it so I can help. This is my only project today so hopefully I make some headway. I get the error at the very first row of my query. I'll follow up shortly

thava, on 28 April 2010 - 05:43 PM, said:

seems this is vba
hopefully this is not problem for corrupting
have a look at this code
just create a table Test1 in your Database with one or two columns
and fill some data

Sub test()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Text1")
DoCmd.RunSQL "DELETE * from Text1"
If Not rs.EOF Then MsgBox rs(0)
End Sub

now run this, you will get the same error as you get,
now check this

Sub test()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Text1")
DoCmd.RunSQL "DELETE * from Text1"
rs.OpenRecordset
If Not rs.EOF Then MsgBox rs(0)
End Sub

now it's working perfectly because
you fetch the record First then
delete the recordset then again
you try to access the deleted records in the recordset
so the error is obvious

the soultion is
use the OpenRecordset

In theory this is correct and please don't think I am trying to prove you wrong but there are two issues with this.

When I set the recordset then purge the table, I'm never closing the recordset and if you read further down in my code, I am importing fresh data to the same set.

The other reason I know this isn't true is because, prior to actually calling data from my recordset, I loop through to get a count for my activeX progress bar and it gives me an accurate count. This tells me that it recognizes the data in the recordset and the rows.

The last thing I did, just to test this because it does make sense in a way is, I moved the set rsQuery=currentdb.openrecordset("InsertInsToLytec"), and I got the same error. This should have accomplished what you were going for without having to reopen the recordset before the final output of data.

Thank you for the input, it's very appreciated. You're probably not far off. I'll follow up in a bit when I have figured it out.

Thanks!

This post has been edited by guyfromri: 29 April 2010 - 06:38 AM

[thava]

no that's true
just create a table Test1 in your Database with one or two columns
and fill some data
at least two rows
and
do not close the access table

now open the macro editor, this will minimize the opened table
and paste the following code

Sub test()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Text1")
DoCmd.RunSQL "DELETE * from Text1"
rs.OpenRecordset
If Not rs.EOF Then MsgBox rs(0)
End Sub

now run this macro
now maximized the open table

now the results shows what i am trying to say (and the theory)

is it clear now?

[guyfromri]

thava, on 29 April 2010 - 07:31 AM, said:

no that's true
just create a table Test1 in your Database with one or two columns
and fill some data
at least two rows
and
do not close the access table

now open the macro editor, this will minimize the opened table
and paste the following code

Sub test()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Text1")
DoCmd.RunSQL "DELETE * from Text1"
rs.OpenRecordset
If Not rs.EOF Then MsgBox rs(0)
End Sub

now run this macro
now maximized the open table

now the results shows what i am trying to say (and the theory)

is it clear now?

I believe so...if I follow correctly, you are coding to set the recordset then delete the records. My code reads like this

set rsQuery=OpenRecordset("Insert")
'
'
'
DoCmd.RunSQL "DELETE * From Insert"
'
'
'
DoCmd.TransferText AcImportDelim, -----and the file I am importing

So you see my dilema is, I am deleting the records from the table but I am also importing fresh records to the table. When I open the tables and/or the query, I can view the records but access is saying they're deleted.

The only thing I don't follow in your code is rsQuery.openrecordset is this the piece that you're saying I'm missing?

[raziel_]

what thava mean is open the recordset again after you delete the info. it fix my problem

This post has been edited by NoBrain: 30 April 2010 - 07:53 AM

[guyfromri]

NoBrain, on 30 April 2010 - 04:11 AM, said:

what thava mean is open the recordset again after you delete the info. it fix my problem

i have to apologize to Thava. You were absolutely correct. I was so bent out of shape on this code not working that I made a few small errors that prevented me from being able to implement that code you had correctly. Now that I fixed these issues, you were correct and it works.

Thank you both very much for the guidance and help. I hope i can help one of you out someday.

Thanks!

[thava]

wow that's the spirit
we welcome you i give you a rep point