Open SQL statement as a recordset, error [3001]: Invalid Arguement

[dowlingm815]

The code below uses a query as a dataset. I have used this code in the past and it works fine. However, it is now generating a error [3001]: Invalid Arguement.

any suggestions would be appreaciate:

Private Sub Create_tblTempPO()

On Error GoTo Err_Hndlr

'**************************************************************
' this routine takes the data from groups the P0 tables' PO #
' and the PO line data.  thereafter, it reads the recordset
' and writes only the first PO # with its first P0 Line
'**************************************************************


Dim rstGroupedPO As Recordset
Dim rsttblTempPO As Recordset
Dim strSQL As String

'**************************************************************
' temp fields for writing records
'**************************************************************
Dim strPR_ID_TEMP As String
Dim strPR_Date_TEMP As Date


'**************************************************************
' create output table
'**************************************************************
'Delete temporary table
'DoCmd.RunSQL "DROP TABLE tblTempPO;"


'**************************************************************
' initialize temporary fields
'**************************************************************

strFirstRec = "Yes"

'**************************************************************
' get to data to be analyzed
'**************************************************************

strSQL = "SELECT POData.[PO #], POData.[PR Line ID] FROM POData " & _
        "GROUP BY POData.[PO #], POData.[PR Line ID];"
  
Set rstGroupedPO = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


'**************************************************************
' Create P0 table with only 1 PO Line ID
'**************************************************************

CurrentDb.Execute ("CREATE TABLE tblTempPO ([PO #] VARCHAR(10), [PR Line ID] int)")

'Bind rstSummary to the temporary table
Set rsttblTempPO = CurrentDb.OpenRecordset("tblTempPO")



rstGroupedPO.MoveFirst

Do While rstGroupedPO.EOF = False
   
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
        strPO_No_TEMP = " "
        strPR_Line_ID_TEMP = "0"
    End If

    If rstGroupedPO![PO #] <> strPO_No_TEMP Then
         If rstGroupedPO![PR Line ID] <> strPR_Line_ID_TEMP Then
            '***   write record
            rsttblTempPO.AddNew
                rsttblTempPO![PO #] = strPR_ID_TEMP
                rsttblTempPO![PR Line ID] = strPR_Date_TEMP
            rstTotalDays.Update
            
            '***  assign temp values
            strPO_No_TEMP = rstGroupedPO![PO #]
            strPR_Line_ID_TEMP = rstGroupedPO![PR Line ID]
         End If
    End If
    rstGroupedPO.MoveNext

Loop

rstGroupedPO.Close
rsttblTempPO.Close

Create_tblTempPO_Exit:
  Exit Sub
  
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tblTempPO()"
End Sub

[Megalog]

You should be putting stops in your code and stepping through to find the error. If I had to take a guess at your problem, I'd say you're missing a line where you declare:

Dim strFirstRec As String

also I would specify your recordsets as DAO like:

Dim rstGroupedPO As DAO.Recordset 
Dim rsttblTempPO As DAO.Recordset

[dowlingm815]

the code errors at

strSQL = "SELECT POData.[PO #], POData.[PR Line ID] FROM POData " & _ 
        "GROUP BY POData.[PO #], POData.[PR Line ID];" 
  
Set rstGroupedPO = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

[dowlingm815]

the data is linked to a back-end access db.

[Megalog]

the code errors at

strSQL = "SELECT POData.[PO #], POData.[PR Line ID] FROM POData " & _ 
        "GROUP BY POData.[PO #], POData.[PR Line ID];" 
  
Set rstGroupedPO = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

There's nothing wrong with the SQL string, or the recordset statement.

I would try refreshing the links to the tables, and verify the table & field names are correct as well.

[dowlingm815]

the re-linking didn't work. the field names are correct.

[missinglinq]

This is purely a guess, but I wonder if the problem could lie with the field name [PO #]. The pound sign, #, is used by Access as a delimiter for dates, such as #5/5/2010# and having two of them in the SQL statement, with something other than a date between them, could be confusing Access.

Linq ;0)>

[Megalog]

Hmm.. Try commenting out the error handling on line 3, does it stop at the same place you posted above?

[dowlingm815]

by accident i commented the first record definition. added it

Dim strFirstRec As String

Commented out the error handling, but the error is still occurring and bombs at

Set rstGroupedPO = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

i am stumped.

the # sign has no effect since i attempted to change the field names to eliminate it, but the same error occurred.

when the "DAO." is the prefix to Recordset, a compiler error is generated stating "User-defined type not defined."

[missinglinq]

when the "DAO." is the prefix to Recordset, a compiler error is generated stating "User-defined type not defined."

This indicates that the Microsoft DAO 3.6 Object Library is not loaded in your references. From any form's code module goto Tools - References and select the DAO Library.

Linq ;0)>

[dowlingm815]

This indicates that the Microsoft DAO 3.6 Object Library is not loaded in your references. From any form's code module goto Tools - References and select the DAO Library.

Linq ;0)>

i am using 2007, i can't seem to find it.

[dowlingm815]

The microsoft error code states the following related to my error:

You tried to perform an operation that involves a routine in a DLL, and one of the arguments to the routine is invalid. Check your entry to make sure you have specified the correct arguments, and then try the operation again.

This error also occurs when you attempt to use mutually exclusive constants in an argument to a method, such as specifying both dbConsistent and dbInconsistent in the options argument to the OpenRecordset method.

i don't see any issues with the code. i am missing something?

[Megalog]

I'm betting the issue is in a reference like Missinglinq suggested, especially if this was upgraded from an older database. Check your references list and see first if there's anything showing as missing, errored, etc. If so remove those entries. Then scroll down and add 'Microsoft Office 12.0 Access Database Engine Object Library' to the list. This should fix any DAO reference issues.

[leach613]

On line 73, when was the recordset rstTotalDays created? Seems out of place when you are adding a record to rsttblTempPO.

Evy

[dowlingm815]

This indicates that the Microsoft DAO 3.6 Object Library is not loaded in your references. From any form's code module goto Tools - References and select the DAO Library.

Linq ;0)>

Missinglinq Thank you!....Microso ft DAO 3.6 Object Library was not loaded in the references. this was a former 2003 access db. yes, as i glazed over your direction....In 2007, you have to go the module, where the form's code resides, and select Tools, Reference. at that point, Microsoft DAO 3.6 Object Library can be selected.

thanks again.