Having Issues Opening a Query with Parameters

[Aberrant_Geek]

Hi all, and thanks for taking a look.

Here's my Goal: To open a saved query that has a parameter, setting that parameter via a VBA sub.

Here's my Problem: I was getting various errors, but after debugging my program a bit, it comes down to a "Data Type Conversion Error"

Here's my Code:

    Set db = CurrentDb
    Set qd = db.QueryDefs("qryMY_DATA")
        qd.Parameters(0) = Me.txt_ReferenceID
        
    Set rs = qd.OpenRecordset("qryMY_DATA", dbDynaset)

'***  Database Variables
Dim db As DAO.Database, rs As DAO.Recordset, gq As DAO.QueryDef, prm As DAO.Recordset

I've been all over the forums and tried several different approaches, all to no avail. Any help is appreciated! If you need any other code, let me know. The Query runs fine in the QDT, but kicks back an error when I try to run it from my sub.

 

[pbaldy]

On what line is the error? You might try this:

Set rs = qd.OpenRecordset()

 

[Aberrant_Geek]

Hey, thanks for the quick reply. The error is highlighting the OpenRecordset line of code, I've tried the method you're suggesting before and the result was an error, "Expecting 2 Parameters".

 

[pbaldy]

The query only has 1 parameter? The textbox returns the value expected by the query? Can you post the db here?

 

[Aberrant_Geek]

I can't upload the entire db (as per restrictions from the project manager), but I'd be happy to upload a dummy database with the form and table. Will that do?

 

[pbaldy]

Sure, as long as it recreates the problem.

 

[Aberrant_Geek]

Actually: I messed up, and will have to put this all on hold. I appreciate the responses, but I'll have to get back to this at a later time.

 

[pbaldy]

No problem, I'll wait right here.

 

[Aberrant_Geek]

Unfortunately, I will not be able to upload a database for you (Dummy or Otherwise), as all the queries are to linked .dbo objects. I'll do my best to include all the relevant data here.

My only coded module, aside from generic debugging and form commands, looks like this:

Option Compare Database

'***  PROC_ERR Variables and Declarations
Private mintStackPointer As Integer, mastrCallStack() As String
Private Const mcintIncrementStackSize As Integer = 10, gcfHandleErrors As Boolean = False

'***  Database Variables
Dim db As DAO.Database, rs As DAO.Recordset, gq As DAO.QueryDef, prm As DAO.Parameters

Private Sub lookupMacola()
If gcfHandleErrors Then On Error GoTo PROC_ERR
PushCallStack "lookupMacola"

'***  Set and Define necessary variables:
    Set db = CurrentDb
    Set qd = db.QueryDefs("qryMacola_Data")
        qd.Parameters(0) = Me.txt_ReferenceID
        
    Set rs = qd.OpenRecordset("qryMacola_Data", dbDynaset)
    
    If rs.RecordCount = 0 Then
        If Not MsgBox("The requested data query yielded 0 records. If you feel this is an error, check the item number and try again.", vbExclamation + vbDefaultButton2, "Data Pull Error") Then GoTo PROC_EXIT
    Else
    '***  Check if query was a fast-track query and, if true, omit updating of part number and upc.
        If fastTracked Then
            For i = 3 To 8
                macolaControls(i) = Trim(rs(i))
            Next i
        Else
            For i = 0 To 8
                macolaControls(i) = Trim(rs(i))
            Next i
        End If 

        
    End If
    
'***  Begin PROC Code:
PROC_EXIT:
    PopCallStack
    Exit Sub
    
PROC_ERR:
    GlobalErrHandler
    Resume PROC_EXIT

Here's the SQL from my Query, please note however, that this was built for me via the Design Tool, so I didn't manually write it out. There is only one Parameter defined, and it is as follows: [Reference Number] | Type: Text

PARAMETERS [Reference Number] Text ( 255 );
SELECT dbo_imitmidx_sql.item_no, dbo_imitmidx_sql.upc_cd, dbo_imitmidx_sql.mat_cost_type, dbo_imitmidx_sql.search_desc, dbo_imitmidx_sql.item_desc_1, dbo_imitmidx_sql.uom, dbo_imitmidx_sql.item_weight, MINERALLAC_ITMMASTER.ZUNSPSC_0, MINERALLAC_BPSUPPLIER.BPSNUM_0, dbo_iminvloc_sql.vend_no, dbo_APVENFIL_SQL.vend_name, dbo_iminvloc_sql.Loc
FROM (((dbo_imitmidx_sql INNER JOIN MINERALLAC_ITMMASTER ON dbo_imitmidx_sql.item_no = MINERALLAC_ITMMASTER.ITMREF_0) INNER JOIN dbo_iminvloc_sql ON dbo_imitmidx_sql.item_no = dbo_iminvloc_sql.item_no) LEFT JOIN dbo_APVENFIL_SQL ON dbo_iminvloc_sql.vend_no = dbo_APVENFIL_SQL.vend_no) LEFT JOIN MINERALLAC_BPSUPPLIER ON dbo_APVENFIL_SQL.vend_name = MINERALLAC_BPSUPPLIER.BPSNAM_0
WHERE (((dbo_imitmidx_sql.item_no)=[Reference Number]) AND ((dbo_iminvloc_sql.Loc)="02"));

The Relationships for this query look like in the image below, however the selects do not quite match up to the SELECT statement above.

I actually can't insert images yet due to my post count, so here is the image location (minus the protocol, because I can't post links either.):
i44.tinypic.com/xlkruw.jpg

Typically speaking, the reference number is an alphanumeric string, between 3 and 30 characters in length. The first character of this string can be either a number or a character.

The form is just an OK/Cancel Dialog with a Text Box (txt_ReferenceID) and a few on/off option groups (irrelevant).

I should say, the form was in great working condition prior to this issue. I am changing away from using a sql string in favor of using a saved query with parameters. (For Ease-of-Use and overall to be more user-friendly.)

I apologize that I couldn't get the database files uploaded, and I hope this will make do in it's place. Thanks for your help!

 

[Aberrant_Geek]

Lo and Behold, the issue has been resolved. I reverted the code to the proper form (as suggested above, shown below), but I was getting the same error. I assumed it was something wrong in the Query.

So I tore it down and rebuilt it up. I changed the Parameters from [Reference ID] to [Forms]![Data Import]![txt_ReferenceID] and the issue was resolved.

I'm hoping this will help someone else who is having like issues. Thanks for your time and help PBALDY!

Set db = CurrentDb
Set qd = db.QueryDefs("qryMacola_Data")
     qd.Parameters(0) = Me.txt_ReferenceID
        
Set rs = qd.OpenRecordset()

 

[pbaldy]

Happy to help! Glad you found the problem.