Access query parameter from control

Question

Fish out of water looking for direction/advice not necessarily code (Although that would be helpful). I am an ME that designs machines for a living and I am trying to automate an existing 2010 Access database used for engineering part numbers. I want the user to select the project number and have the next available part number automatically appear in the part number field.

I have tried unsuccessfully using ADO and DAO examples from the various sources to pass a parameter that is based on the value of a form control to a select query. My latest attempt is as follows:

Private Sub ctlProject_AfterUpdate()

Dim dblProject As Double

dblProject = Me.ctlProject.Value

MsgBox "The curret project number is " & dblProject, vbOKOnly, "Project Number"


Const cstrQueryName As String = "qryDetails"
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("Project") = dblProject

' Open recordset on the query
Set rst = qdf.OpenRecordset()

rst.MoveLast

Debug.Print ("Project ID: " & rst!Project)

rst.Close
qdf.Close
dbs.Close

End Sub

Code produces a "Run-Time Error '3265'. Item not found in this collection" that stems from this line of code:

qdf.Parameters("Project") = dblProject

Original Source code here

I have had similar failures using the ADO Command object. I would greatly appreciate any help or recommendations for references/resources. I have been using "Access 2003 vba programmers's manual" by WROX and "Access 2010: the missing manual" as hardcopy references.

Here is the code from the SQL view of qryDetails:

SELECT tblDetails.Project, tblDetails.Number, tblDetails.Title, tblDetails.Initials,
tblDetails.IssuedOn
FROM tblDetails
WHERE (((tblDetails.Project)=[Project]));

Silly question really but judging by your error are you sure the query qryDetails expects a Parameter called "Project"? — user692942
@Lankymart Short answer is I think so. In response to @HansUp request I added the SQL view from the qryDetails. — user3042766

HansUp HansUp · Accepted Answer · 2013-12-23T17:07:16

I'm skeptical about Project as the parameter name because there is a field with the same name. It's also a reserved word, but I'm unsure whether that is an extra challenge here.

Revise qryDetails to use a different name for the parameter.

SELECT tblDetails.Project, tblDetails.Number, tblDetails.Title, tblDetails.Initials,
tblDetails.IssuedOn
FROM tblDetails
WHERE (((tblDetails.Project)=[pProject]));

And don't forget to revise the VBA to use that new name ...

qdf.Parameters("pProject") = dblProject

If you still have trouble after those changes, see what Access thinks about your query parameter(s).

Const cstrQueryName As String = "qryDetails"
Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs(cstrQueryName)
MsgBox "Parameters.Count: " & qdf.Parameters.Count
For Each prm In qdf.Parameters
    MsgBox "Parameter Name: " & prm.Name
Next

Access query parameter from control

1 Answers