unable to open recordset (1 Viewer)

[iworkonline]

Hi Guys

I am using Access 2003 and running the following code but I am not able to run.

I am getting the following error message:
"No value given for one or more required parameters." and the debugger stop at the following line

 rs.Open strSql, CurrentProject.Connection

Here is the full code.

Dim rs As New ADODB.recordset
Dim strSql As String
Dim cmd As ADODB.Command
 
    strSql = "Select * from Q_Shipper_Export"
    cout = 0
    rs.Open strSql, CurrentProject.Connection
 
    Do While Not rs.EOF
        Debug.Print rs!Dear_Name
        rs.MoveNext
        count = count + 1
    Loop
 
        MsgBox CStr(count) & " records emailed"
    rs.Close
    Set rs = Nothing

I am not sure what is wrong in the following statement

rs.Open strSql, CurrentProject.Connection

because I was able to run the above statement in a different module.

Thanks

 

[boblarson]

I'm guessing that Q_Shipper_Export has at least one parameter in it and you've not provided it to the query.

 

[iworkonline]

you are absolutely right.

Query Q_Shipper_Export is a select statment doing one join and filtering data.

Here is the Query Q_Shipper

SELECT [Select], Network_ID, INV_PMR_TE AS Pay_Terms, SHP_NR_TE AS Shipper_No, Trim([CNT_NA_TE]) AS Dear_Name, Trim([CNT_EML_TE]) AS Contact_EMail, Trim([ACC_NA_TE]) AS CompanyName, Trim([ACC_AD_ST_TE]) AS Street_Address, Trim([ACC_AD_CTY_TE]) & ", " & Trim([ACC_AD_STT_CD]) & ". " & Trim([ACC_AD_ZIP_TE]) AS City_ST_Zip, Collector, EMP_EML_AD AS Coll_E_Mail, Ext, TBL_Select_Shippers.*
FROM [TBL_E-Mail_Shippers] 
INNER JOIN TBL_Select_Shippers
ON CTC_SYS_NR = TBL_Select_Shippers.CTC_SYS_NR
WHERE ((([Select])=-1) AND ((Network_ID)=[Forms]![FRM_Shippers_Past_Due]![Userid] Or (Network_ID)=[Forms]![FRM_Shippers_Past_Due]![NewUser]));

How do I pass the data from the filter?

Thanks a lot for the guidance.

 

[iworkonline]

I am trying to run the Query "Q_Shipper_Export" from the code and don't know how to pass the data from the form.

 

[LPurvis]

The query parameters can indeed retrieve the parameters upon execution - if it's executed within the Access UI (and hence evaulatable by the expression service).

In recordsets of either DAO or ADO (as Bob well knows but hasn't spotted... ;-) you need explicit parameter evaluation - the expression service's scope doesn't include Access objects any longer.

The simple fix is to use Eval. Not that I'd ever berate my friend Brent's preferences - but that has always been the quick and dirty method for me. ;-)
Explicit evaluation allows you to maintain your queries as you normally would.

The DAO version is here: http://www.access-programmers.co.uk/forums/showthread.php?t=151586
I'm not sure if I've posted an ADO equivalent thus far... I'll check.

Cheers.

 

[LPurvis]

Nope - oh well. Have a go with this then.

Function fADOGenericRst(ByVal strSource As String, Optional cnn As ADODB.Connection, _
                                Optional pCursorLocation As ADODB.CursorLocationEnum = adUseServer, _
                                Optional pCursorType As ADODB.CursorTypeEnum = adOpenKeyset, _
                                Optional pLockType As ADODB.LockTypeEnum = adLockOptimistic, _
                                Optional pOption As ADODB.ExecuteOptionEnum = -1) As ADODB.Recordset
    
    Dim cmd As New ADODB.Command
    Dim prm As ADODB.Parameter
    
    If cnn Is Nothing Then
        Set cnn = CurrentProject.Connection
    End If
    Set cmd.ActiveConnection = cnn
    
    If Left(strSource, 11) <> "PARAMETERS " And Left(strSource, 7) <> "SELECT " Then
        strSource = "SELECT * FROM [" & strSource & "]"
    End If
    
    cmd.CommandText = strSource
    
    'cmd.Parameters.Refresh 'Is implicit - this is a Jet util so doesn't incur overhead penalties
    For Each prm In cmd.Parameters
        prm.Value = Eval(prm.name)
    Next
    
    Set fADOGenericRst = New ADODB.Recordset
    With fADOGenericRst
        .CursorLocation = pCursorLocation
        .Open cmd, , pCursorType, pLockType, pOption
    End With
    
    Set prm = Nothing
    Set cmd = Nothing
    
End Function

Cheers

 

[iworkonline]

Hello LPurvis

Thanks for the code and the clarification.
Do I have to clean up the SQL from the access to pass it into your function.

If so, is there an easier way to convert the SQL from Access into simple SQL statement that I can assign to strSQL in VBA.

I am using the following code after removing the [] from the SQL from Access.

If I am using your function than I will have to remove "& _" characters.

My challenge is to re-write the SQL from Access and convert that into simple version to use it in my app.

Thanks

"SELECT TBL_E-Mail_Shippers.Select, TBL_E-Mail_Shippers.Network_ID, " & _
"TBL_E-Mail_Shippers.INV_PMR_TE AS Pay_Terms, " & _
"TBL_E-Mail_Shippers.SHP_NR_TE AS Shipper_No," & _
"Trim(CNT_NA_TE) AS Dear_Name, Trim(CNT_EML_TE) AS Contact_EMail, " & _
"Trim(ACC_NA_TE) AS CompanyName, " & _
"Trim(ACC_AD_ST_TE) AS Street_Address, " & _
"Trim(ACC_AD_CTY_TE)" & "& ', ' & Trim([ACC_AD_STT_CD]) & '. ' & Trim(ACC_AD_ZIP_TE) AS City_ST_Zip, " & _
"TBL_E-Mail_Shippers.Collector, TBL_E-Mail_Shippers.EMP_EML_AD AS Coll_E_Mail, [TBL_E-Mail_Shippers].Ext, " & _
"TBL_Select_Shippers.* " & _
"FROM [TBL_E-Mail_Shippers] " & _
"INNER JOIN TBL_Select_Shippers ON TBL_E-Mail_Shippers.CTC_SYS_NR = TBL_Select_Shippers.CTC_SYS_NR " & _
"WHERE (((TBL_E-Mail_Shippers.Select)=-1) AND ((TBL_E-Mail_Shippers.Network_ID)= " & [Forms]![FRM_Shippers_Past_Due]![Userid] & "Or (TBL_E-Mail_Shippers.Network_ID)= " & [Forms]![FRM_Shippers_Past_Due]![NewUser] & "));"

 

[LPurvis]

If you've already moved your SQL into being written dynamically in code rather than working with it as a fixed query def, then you don't need to force that parameter evaluation. (I'd assumed this was fixed statements you were working with).

If you're using VBA then all you need to worry about is getting that SQL statement to be well formed. i.e. include any delimiters required around the form values and make sure the clauses are well separated (including spaces etc).

Once there it can be executed either by normal standards or passed to a function as I offered (which will execute non-parameterised statements just fine too, that's the whole point of it, you don't have to worry if there are or not ;-)

Cheers.