Moving data to table conditionally

[adams.bria]

I'll start with what I would like to do. I have data coming from a google docs questionnaire that is all on one line (department, timstamp, # of A requested, # of B requested, # of C requested) (many more fields, just simplifying here)


The data is available via linked table, and updates a permanent table in my db. What I am trying to do is to convert that data into my db in a more normalized and friendly format. DC_ID is the primary key for my employers. So I am trying to move my data into a normalized structure based on how many requests an employer is making of a specific occupation:

empl_requests.tbl =
Req_ID; DC_ID; Occ_ID; Num_req

The data to fill that table is coming from: qrymove_requests

I have tried doing this two ways, both of which leave me stuck. Here is #1

Public Function move_Request_Forms()

Dim dbsworkstudy As DAO.Database
Dim rstempl_requests As DAO.Recordset
Dim rstmoverequests As DAO.Recordset
Dim rststatus As DAO.Recordset

    Set dbsworkstudy = CurrentDb
    Set rstempl_requests = dbsworkstudy.OpenRecordset("empl_request")
    Set rstmoverequests = dbsworkstudy.OpenRecordset("qrymove_requests")
    Set rststatus = dbsworkstudy.OpenRecordset("Employer_request_Form_data")
    
   For Each Record In rstmoverequests
    Where rstmoverequests.Fields(8).Value = Null
                
        rstempl_requests.AddNew
        rstempl_requests!DC_ID = rstmoverequests!DC_ID
        rstempl_requests!Occ_ID = "1"
        rstempl_requests!num_req = rstmoverequests!office_assistants
        rststatus!Status = "1"
        rstmoverequests.Update
        rstmoverequests.MoveNext
    Next
     
End Function

the italicized part is where my syntax breaks down. Not sure how to write that. Field 8 is my status field. I only want it to do the next part if the status is null, because if the status is 1 then that request has already been moved over.

Here is the second way I tried accomplishing this

Public Function Request_Form_Data()

Dim MVREQ As New ADODB.Recordset
Dim EMREQ As New ADODB.Recordset

EMREQ.Open "empl_request", CurrentProject.Connection, adOpenDynamic
MVREQ.Open "qrymove_requests", CurrentProject.Connection, adOpenDynamic

    Select Case MVREQ.Fields(1).Value
    Case Is > 0
        Set EMREQ.Fields(2).Value = MVREQ.Fields(0)
        Set EMREQ.Fields(3).Value = "1"
        Set EMREQ.Fields(4).Value = MVREQ.Fields(1)
        MVREQ.MoveNext
            
    Case Else
        MsgBox "nope"
        
End Select

End Function

Here I am getting error 3021 "either bog or eof is true, or the current record has been deleted. requested operation requires a current record" on the first "SET" line


Not sure which way to go about accomplishing this task, or which way would be more efficient. Obviously I need to write this for each occupation, but once I get the first done I can easily replicate it for the individual lines. Thanks for any help or advice!

[hertfordkc]

In the first case, with the italicized code, maybe you should use isnull(....)
In the second case, I believe you may need to do a
.Movefirst

[adams.bria]

So I switched around the first code and now I am getting "3251: operation is not supported for this type of object"

Public Function move_Request_Forms()

Dim dbsworkstudy As DAO.Database
Dim rstempl_requests As DAO.Recordset
Dim rstmoverequests As DAO.Recordset
Dim rststat As DAO.Recordset

    Set dbsworkstudy = CurrentDb
    Set rstempl_requests = dbsworkstudy.OpenRecordset("empl_request")
    Set rstmoverequests = dbsworkstudy.OpenRecordset("qrymove_requests")
    Set rststat = dbsworkstudy.OpenRecordset("Employer_request_Form_data")
    
For Each Record In rstmoverequests
     IsNull (rstmoverequests!stat)

        rstempl_requests.AddNew
        rstempl_requests!DC_ID = rstmoverequests!DC_ID
        rstempl_requests!Occ_ID = "1"
        rstempl_requests!num_req = rstmoverequests!office_assistants
        rststat!stat = "1"
    Next Record

End Function

As for the second way I am still getting '3021' on the first SET line with this code

Public Function Request_Form_Data()

Dim MVREQ As New ADODB.Recordset
Dim EMREQ As New ADODB.Recordset

EMREQ.Open "empl_request", CurrentProject.Connection, adOpenDynamic
MVREQ.Open "qrymove_requests", CurrentProject.Connection, adOpenDynamic

    Select Case MVREQ.Fields(1).Value
    Case Is > 0
        MVREQ.MoveFirst
        Set EMREQ.Fields(2).Value = MVREQ.Fields(0)
        Set EMREQ.Fields(3).Value = "1"
        Set EMREQ.Fields(4).Value = MVREQ.Fields(1)
        MVREQ.MoveNext
            
    Case Else
        MsgBox "nope"
        
End Select

End Function

[hertfordkc]

So I switched around the first code and now I am getting "3251: operation is not supported for this type of object"

Public Function move_Request_Forms()

Dim dbsworkstudy As DAO.Database
Dim rstempl_requests As DAO.Recordset
Dim rstmoverequests As DAO.Recordset
Dim rststat As DAO.Recordset

    Set dbsworkstudy = CurrentDb
    Set rstempl_requests = dbsworkstudy.OpenRecordset("empl_request")
    Set rstmoverequests = dbsworkstudy.OpenRecordset("qrymove_requests")
    Set rststat = dbsworkstudy.OpenRecordset("Employer_request_Form_data")
    rstmoverequests.movefirst
For Each Record In rstmoverequests where IsNull (rstmoverequests!stat)
        rstempl_requests.AddNew
        rstempl_requests!DC_ID = rstmoverequests!DC_ID
        rstempl_requests!Occ_ID = "1"
        rstempl_requests!num_req = rstmoverequests!office_assistants
        rststat!stat = "1"
    Next Record

End Function

As for the second way I am still getting '3021' on the first SET line with this code

Public Function Request_Form_Data()

Dim MVREQ As New ADODB.Recordset
Dim EMREQ As New ADODB.Recordset

EMREQ.Open "empl_request", CurrentProject.Connection, adOpenDynamic
MVREQ.Open "qrymove_requests", CurrentProject.Connection, adOpenDynamic

  MVREQ.MoveFirst

     Select Case MVREQ.Fields(1).Value
    Case Is > 0
  '  Not here       MVREQ.MoveFirst
        Set EMREQ.Fields(2).Value = MVREQ.Fields(0)
        Set EMREQ.Fields(3).Value = "1"
        Set EMREQ.Fields(4).Value = MVREQ.Fields(1)
        MVREQ.MoveNext
            
    Case Else
        MsgBox "nope"
        
End Select

End Function

Sorry, I was running late and hurried my reply.

[adams.bria]

First method:


When I use there "where" I get the "compile error: expected end of statement"

When I remove the where I get "3251 Operation is not supported for this type of object" on the "for each record in rstmoverequests" line

Public Function move_Request_Forms()

Dim dbsworkstudy As DAO.Database
Dim rstempl_requests As DAO.Recordset
Dim rstmoverequests As DAO.Recordset
Dim rststat As DAO.Recordset

    Set dbsworkstudy = CurrentDb
    Set rstempl_requests = dbsworkstudy.OpenRecordset("empl_request")
    Set rstmoverequests = dbsworkstudy.OpenRecordset("qrymove_requests")
    Set rststat = dbsworkstudy.OpenRecordset("Employer_request_Form_data")
    rstmoverequests.MoveFirst
    
For Each Record In rstmoverequests
   IsNull (rstmoverequests!stat)

        rstempl_requests.AddNew
        rstempl_requests!DC_ID = rstmoverequests!DC_ID
        rstempl_requests!Occ_ID = "1"
        rstempl_requests!num_req = rstmoverequests!office_assistants
        rststat!stat = "1"
    Next Record
End Function

2nd method

I am getting error "3021 either bog or eof is true, or the current record has been deleted. Requested operation requires a current record" on the first SET line.
I am wondering is this because I have two recordsets, MVREQ and EMREQ? I tried adding EMREQ.movefirst which created the same error except on the EMREQ.movefirst line.

Public Function Request_Form_Data()

Dim MVREQ As New ADODB.Recordset
Dim EMREQ As New ADODB.Recordset

EMREQ.Open "empl_request", CurrentProject.Connection, adOpenDynamic
MVREQ.Open "qrymove_requests", CurrentProject.Connection, adOpenDynamic
MVREQ.MoveFirst

    Select Case MVREQ.Fields(1).Value
    Case Is > 0
        Set EMREQ.Fields(2).Value = MVREQ.Fields(0)
        Set EMREQ.Fields(3).Value = "1"
        Set EMREQ.Fields(4).Value = MVREQ.Fields(1)
        MVREQ.MoveNext
            
    Case Else
        MsgBox "nope"
       
End Select
End Function

Thanks for helping me work through this!