"Insert Into" statement not working

[djsdaddy]

Good Day All, I have some EEO data in an old dBase4 database that I have converted to an Access table. Since dBase was not a relational database, I didn't create any key fields. I linked all of the tables together on Employees' SSN; therefore, in order to use this data with the current Access database, I need to create a relationship between the old dBase4 EEO data and the Access employee data. The Access employee data was also an old dBase table, but after importing it into Access and adding a key field, EmployeeID, Access automatically assigned a number to each of my 1,200+ records. This part of the equation was simple, and additionally, I am attempting to do a few other things here:

1st - I need to use the SSN in the old dBase data (EEO) and do a FindFirst in tblMasterEmplyee. If I find a match, I want to take the EmployeeID from that record and put it in tblEEO_EmployeeData, along with the data from the associated record in the old dBase table (tblEEO_DataFromDBase).

2. If no match is found for the SSN in tblMasterEmployee, I want to Insert a new record and place the SSN in the table; save the record and then take the new EmployeeID from tblMasterEmployee with the data in tblEEO_DataFromDBase and place it in tblEEO_EmployeeData

Pretty Simple; huh? :o)

Here is the code:

    Dim blnExist As Boolean
    Dim rstSource As DAO.Recordset
    Dim rstDest As DAO.Recordset
    Dim rstGetID As DAO.Recordset
    Dim lngEmpID As Long
    Dim lngJGCtr As Long
    lngJGCtr = 0
 
    Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase")
    Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData")
    Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee")
 
    Do Until rstSource.EOF
        blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
        If Not blnExist Then
            CurrentDb.Execute "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
            DoCmd.GoToRecord , , acLast
            rstGetID.FindFirst "rstGetID [SSN] = '" & rstGetID!SSN & "'"
'----------------^Get error on this line "Operation is not supported 'for this object" Run-time error 3251 
        End If
 
 
            If rstGetID.NoMatch Then
                MsgBox "Unable to find match!"
            Else
                MsgBox "The item is : " & rstGetID![SSN]
            End If  
            rstDest.AddNew
            rstDest!EmployeeID = rstGetID!EmployeeID
            rstDest!Race = rstSource!Race
            rstDest!Gender = rstSource!Gender
            rstDest!PositionID = rstSource!Position
            lngJGCtr = lngJGCtr + 1
            If lngJGCtr < 6 Then
               rstDest!JobGroupID = 5
            Else
               lngJGCtr = 0
               rstDest!JobGroupID = 4
            End If
            rstDest!CategoryID = rstSource!Category
            rstDest!Veteran = rstSource!Vet_Stat
            rstDest!DisVet = rstSource!Dis_Vet
            rstDest!VietNamVet = rstSource!Nam_Stat
            rstDest!DisabilityStatus = rstSource!Handicap
            rstDest!Disability = rstSource!H_Cap
            rstDest.Update
            rstSource.MoveNext
    Loop
 

The program keeps blowing up at the Insert Into command with the following error:

"Operation is not supported for this object" Run-time error 3251

That error is only if SSN not found and I attempt to add the record and then find it for applicable data manipulation. However, all of the records that exist and do process shows an EmployeeID of the number "1". The first person in rstGetID has an employeeID of 1, so for reason, the program is only reading the first record.

Many thanks to whoever comes to my rescue.

David

Running on Windows XP and Access 2002 in 2000 mode.

[MMcCarthy]

I've made a couple of changes and also I have a question?

 
Dim blnExist As Boolean
    Dim rstSource As DAO.Recordset
    Dim rstDest As DAO.Recordset
    Dim rstGetID As DAO.Recordset
    Dim lngEmpID As Long
    Dim lngJGCtr As Long
    lngJGCtr = 0
 
    Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase")
    Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData")
    Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee")
 
    rstSource.MoveFirst
    Do Until rstSource.EOF
        blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
        If Not blnExist Then
       DoCmd.RunSQL "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
 ' what are you trying to do here? 
' If you are trying to find the record you've just inserted than you
' can't as you would have to close and then reopen the recordset
            DoCmd.GoToRecord , , acLast 
            rstGetID.FindFirst "rstGetID [SSN] = '" & rstGetID!SSN & "'"
 
        End If
>
>
>
 

[djsdaddy]

I've made a couple of changes and also I have a question?

 
Dim blnExist As Boolean
    Dim rstSource As DAO.Recordset
    Dim rstDest As DAO.Recordset
    Dim rstGetID As DAO.Recordset
    Dim lngEmpID As Long
    Dim lngJGCtr As Long
    lngJGCtr = 0
 
    Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase")
    Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData")
    Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee")
 
    rstSource.MoveFirst
    Do Until rstSource.EOF
        blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
        If Not blnExist Then
       DoCmd.RunSQL "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
 ' what are you trying to do here? 
' If you are trying to find the record you've just inserted than you
' can't as you would have to close and then reopen the recordset
            DoCmd.GoToRecord , , acLast 
            rstGetID.FindFirst "rstGetID [SSN] = '" & rstGetID!SSN & "'"
 
        End If
>
>
>
 

' what are you trying to do here? 
' If you are trying to find the record you've just inserted than you
' can't as you would have to close and then reopen the recordset[/b]
            DoCmd.GoToRecord , , acLast 
            rstGetID.FindFirst "rstGetID [SSN] = '" & rstGetID!SSN & "'"
 
        End If

Thanks for the insight and assistance. I mistakenly thought that you had to save the record before you could manipulate the data. After looking at your code, I only included the DoCmd.RunSQL command, and although it ran through the EOF of the recordset, all 1,200+ records still has an EmployeeID of 1. So for some reason my logic is forcing the program to only read the first record of the rstGetID recordset.

Any more suggestions?

David

[MMcCarthy]

Try this ...

 
Dim blnExist As Boolean
    Dim rstSource As DAO.Recordset
    Dim rstDest As DAO.Recordset
    Dim rstGetID As DAO.Recordset
    Dim lngEmpID As Long
    Dim lngJGCtr As Long
    lngJGCtr = 0
 
    Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase")
    Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData")
    Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee")
 
    rstSource.MoveFirst
 
    Do Until rstSource.EOF
        blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
        If Not blnExist Then
      rstGetID.FindFirst "[SSN] = '" & rstSource!SSN & "'"
       DoCmd.RunSQL "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
        End If
      If rstGetID.NoMatch Then
                MsgBox "Unable to find match!"
            Else
                MsgBox "The item is : " & rstGetID![SSN]
            End If  
            rstDest.AddNew
            rstDest!EmployeeID = rstGetID!EmployeeID
            rstDest!Race = rstSource!Race
            rstDest!Gender = rstSource!Gender
            rstDest!PositionID = rstSource!Position
            lngJGCtr = lngJGCtr + 1
            If lngJGCtr < 6 Then
               rstDest!JobGroupID = 5
            Else
               lngJGCtr = 0
               rstDest!JobGroupID = 4
            End If
            rstDest!CategoryID = rstSource!Category
            rstDest!Veteran = rstSource!Vet_Stat
            rstDest!DisVet = rstSource!Dis_Vet
            rstDest!VietNamVet = rstSource!Nam_Stat
            rstDest!DisabilityStatus = rstSource!Handicap
            rstDest!Disability = rstSource!H_Cap
            rstDest.Update
            rstSource.MoveNext
    Loop
 

[djsdaddy]

The "If Not blnExist " condition was never true, so the program never hit the FindFirst statment. I commented out the If-Then statement, but still used the fiindfirst:

Do Until rstSource.EOF
        blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
'        If Not blnExist Then
'            rstGetID.FindFirst "[SSN] = '" & rstSource!SSN & "'"
'            DoCmd.RunSQL "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
'        End If
        rstGetID.FindFirst "[SSN] = '" & rstSource!SSN & "'"
        If rstGetID.NoMatch Then
'..............Rest of code

I received the following error:

Run-time error 3251 Operation is not supported for this type of object

However, when I ran your code with the If-Then statement, I noticed that the program is never finding a match on SSN in rstGetID, so the recordset pointer is always sitting at the first record, which is why when the program runs through EOF, all of the EmployeeIDs in rstDest is 1. For some reason, Access appears not to like the the FindFirst statement with the rstGetID recordset.

[djsdaddy]

I figured it out. I needed to add dbOpenDynaset to Current.db statements:

Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase", dbOpenDynaset)
Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData", dbOpenDynaset)
Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee", dbOpenDynaset)

Thanks for pointing me in the right direction. Have a blessed day as you have blessed mine.

David