Database

Private Sub ProcessHeadcountRecords()
    Dim dbsHeadcount As Database
    Dim Cnxn As ADODB.Connection
    Dim strConn As String
    Dim rstInputFile As ADODB.Recordset
    Dim cmdSQLInputFile As ADODB.Command
    Dim strSQLInputFile As String
    Dim rstHyperionMany As ADODB.Recordset
    Dim cmdSQLHyperionMany As ADODB.Command
    Dim strSQLHyperionMany As String
    Dim rstHyperionOne As ADODB.Recordset
    Dim cmdSQLHyperionOne As ADODB.Command
    Dim strSQLHyperionOne As String
    Dim strDBPath As String
    Dim strFileName As String
    Dim strMessage As String
    Set dbsHeadcount = CurrentDb
    
    Set cmdSQLInputFile = New ADODB.Command
    Set cmdSQLInputFile.ActiveConnection = Application.CurrentProject.Connection
    strSQLInputFile = "SELECT [COUNTRY], [TYPE], [BUSINESS UNIT], " & _
        "[L/R/G], [REGION], [JOB FUNCTION], [09/12/2007 Reported], " & _
        "[NUMINDEX] FROM [TBLINPUTFILE]"
    cmdSQLInputFile.CommandType = adCmdText
    cmdSQLInputFile.CommandText = strSQLInputFile
    Set rstInputFile = cmdSQLInputFile.Execute()
    rstInputFile.MoveFirst
    
    Set cmdSQLHyperionMany = New ADODB.Command
    Set cmdSQLHyperionMany.ActiveConnection = Application.CurrentProject.Connection
    strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], [BUSINESS UNIT], " & _
        "[L/R/G], [REGION], [JOB FUNCTION], [NUMFOREIGNKEY], [09/12/2007 Reported] " & _
        "FROM [TBLHYPERIONMANY]"
    cmdSQLHyperionMany.CommandType = adCmdText
    cmdSQLHyperionMany.CommandText = strSQLHyperionMany
    Set rstHyperionMany = cmdSQLHyperionMany.Execute()
    
    Do Until rstInputFile.EOF
        With dbsHeadcount
            strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], " & _
                "[BUSINESS UNIT], [L/R/G], [REGION], [JOB FUNCTION], " & _
                "[NUMFOREIGNKEY], [09/12/2007 Reported] " & _
                "FROM [TBLHYPERIONMANY] " & _
                "WHERE [COUNTRY]='" & rstInputFile![COUNTRY] & "' " & _
                "AND [TYPE]='" & rstInputFile![Type] & "' " & _
                "AND [BUSINESS UNIT]='" & rstInputFile![BUSINESS UNIT] & "' " & _
                "AND [L/R/G]='" & rstInputFile![L/R/G] & "' " & _
                "AND [REGION]='" & rstInputFile![REGION] & "' " & _
                "AND [JOB FUNCTION]='" & rstInputFile![JOB FUNCTION] & "'"
            MsgBox (strSQLHyperionMany)
            rstHyperionMany.Requery
            'If rstInputFile![COUNTRY].RecordCount = 1 Then
                rstInputFile![NUMINDEX] = rstHyperionMany![NUMFOREIGNKEY]  /// HERE IS THE ERROR LINE WHERE I CANNOT ASSIGN VALUES
            'Else
                'MsgBox ("Record not found")
            'End If
        End With
        rstInputFile.MoveNext
    Loop
End Sub