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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 Architect / Systems AnalystCommented:

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


mx
0
Dale FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.