Intermittent errors..please help!!!

[new_phoenix]

I step through the code, and sometimes the code works, but then again, sometimes the code does not work. The error message I get is that "The Microsoft Jet database engine cannot find the input table or query 'qryPassThroughQuery'. Make sure it exists and that its name is spelled correctly." I cannot understand it. On one occasion it will accept the record and pass through it without a problem. On another occasion, it does not accept it at all, providing this message. Could somebody please provide some advice??? Why can't the code find the query when the line practically in front of it does just that -- creates the query that it needs in that line???

Private Sub ProcessHeadcountRecords()
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 rstQueryDef As ADODB.Recordset
Dim strPassThroughQuery As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String

strDBPath = "J:\\GELCO DATABASE\\Headcount Database\\Headcount Database.mdb"
Set dbsHeadcount = OpenDatabase(strDBPath)
Set Cnxn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & \_
    "Data Source=" & strDBPath & ""
Cnxn.Open strConn

Set rstInputFile = New ADODB.Recordset
strSQLInputFile = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS\_UNIT\], " & \_
    "\[L\_R\_G\], \[REGION\], \[JOB\_FUNCTION\], \[ACTUAL\], \[NUMINDEX\] " & \_
    "FROM TBLINPUTFILE"
rstInputFile.Open strSQLInputFile, Cnxn, adOpenKeyset, adLockOptimistic
rstInputFile.MoveFirst

Set rstHyperionMany = New ADODB.Recordset
strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS\_UNIT\], " & \_
    "\[L\_R\_G\], \[REGION\], \[JOB\_FUNCTION\], \[NUMFOREIGNKEY\] " & \_
    "FROM \[TBLHYPERIONMANY2\] ORDER BY \[NUMFOREIGNKEY\]"
rstHyperionMany.Open strSQLHyperionMany, Cnxn, adOpenKeyset, adLockOptimistic


strFileName = "qryPassThroughQuery"
txtMessageBoxText.SetFocus
txtMessageBoxText.Text = "Processing all the records from the upload file " & \_
    "to an updated file to be imported into Hyperion."
    
Do Until rstInputFile.EOF
    With dbsHeadc

[andyharman]

Can you not simplify your code with something like:

UPDATE [TBLINPUTFILE] SET [NUMINDEX] = [Hyp].[NUMFOREIGNKEY]
   FROM [TBLHYPERIONMANY2] AS [Hyp]
   WHERE [Hyp].[COUNTRY] = [TBLINPUTFILE].[COUNTRY]
   AND [Hyp].[TYPE] = [TBLINPUTFILE].[TYPE]
   AND [Hyp].[BUSINESS_UNIT] = [TBLINPUTFILE].[BUSINESS_UNIT]
   AND [Hyp].[L_R_G] = [TBLINPUTFILE].[L_R_G]
   AND [Hyp].[REGION] = [TBLINPUTFILE].[REGION]
   AND [Hyp].[JOB_FUNCTION] = [TBLINPUTFILE].[JOB_FUNCTION]

Regards Andy