qdf.parameters

I am trying to learn about running a query with VBA arnd recordsets.  I have this example code that I want to understand.  

Private Function Run_qryJoin_TubeNumber_AssySerialNumber(strTubeNumber As String)
  ' Comments: Runs a query that contains parameters
  ' Input: strTubeNumber   Tube Number to select records

  Const cstrQueryName As String = "qryJoin_TubeNumber_AssySerialNumber"
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset

  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs(cstrQueryName)
  qdf.Parameters("Enter Tube Number: ") = strTubeNumber

  ' Open recordset on the query
  Set rst = qdf.OpenRecordset()
  Do While Not rst.EOF
    msgbox strTubeNumber
    rst.MoveNext
  Loop

  rst.Close
  qdf.Close
  dbs.Close
End Function

I call this function like this:
Private Sub txtSerialNumber_GotFocus()
    Dim strTubeNumber As String
    strTubeNumber = "100130"
    Me.txtSerialNumber = Run_qryJoin_TubeNumber_AssySerialNumber(strTubeNumber)
End Sub

What I think should happen is it should find that record and display it in a mesage box.

But it fails at line:
  qdf.Parameters("Enter Tube Number: ") = strTubeNumber

with the error Runtime error 3265.  Item not found in this collection.

Help me understand how this should work.

Thanks,
Brooks
gbnortonAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Side note:
RE

Set dbs = CurrentDb()
' ....

dbs.Close
Don't do this.  dbs is the CurrentDb, which is the instance of Access currently open. You can't close it. This line of code does nothing; however, in days gone by it actually created a subtle error.

So, instead of this:

  rst.Close
  qdf.Close
  dbs.Close

Do this:

  rst.Close
  qdf.Close
  Set rst = Nothing
  Set qdf= Nothing
  Set dbs= Nothing


Also ... can you post the SQL for qryJoin_TubeNumber_AssySerialNumber ?

mx
0
 
Dale FyeCommented:
Your text inside the quotes is probably not an exact match to the parameter defined in your SQL statement.  Try:

qdf.Parameters(0) = strTubeNumber
0
 
Dale FyeCommented:
Best way to figure this out is to look at the SQL statement that defines your querydef and identify the precise parameter name, or to use numeric indices, as I did above.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
gbnortonAuthor Commented:
Here is the SQL:

SELECT TOP 1 TubeData.SerialNumber, TubeData.Date_Received, TubeData.Assy_SerialNumber, TubeData.Date_Joined
FROM TubeData
WHERE (((TubeData.SerialNumber)=[Enter Tube Number: ]))
ORDER BY TubeData.Date_Received DESC;

Thanks,
Brooks
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

WHERE (((TubeData.SerialNumber)=[Enter Tube Number: ]))
                                                          ^                                ^
Seems to be missing brackets:
qdf.Parameters("[Enter Tube Number: ]") = strTubeNumber


mx
0
 
Dale FyeConnect With a Mentor Commented:
At first thought, I was going to say the same thing, but then I was surprised that I could actually refer to the parameter when it wasn't clearly defined in the SQL statment.  And I was able to use just the text, excluding the brackets.  I would advise that you clearly define the parameters datatype in a parameters statement within the SQL.

PARAMETERS [Enter Tube Number: ] Text ( 255 );
SELECT TOP 1 TubeData.SerialNumber, TubeData.Date_Received, TubeData.Assy_SerialNumber, TubeData.Date_Joined
FROM TubeData
WHERE (((TubeData.SerialNumber)=[Enter Tube Number: ]))
ORDER BY TubeData.Date_Received DESC;

is the SerialNumber field a text value (as anticipated by strTubeNumber)?  If so, the Parameters statement above may resolve the problem.  If not, then change the parameters statement to:

PARAMETERS [Enter Tube Number: ] Long;

0
 
gbnortonAuthor Commented:
What does this statement expect inside the  parenthesis?
qdf.Parameters("Enter Tube Number: ") = strTubeNumber

.And what is the purpose of = strTubeNumber?

When I call this this function I am supplying the tube number with strTubeNumber.

At least that is what I am trying to do...

Thanks,
Brooks
0
 
gbnortonAuthor Commented:
Thanks for your suggestions... I've been moved on to something else...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.