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
Code:
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
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
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!