[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

No Value given for one or more required parameters

Hi,

I have created a form in Access that contains 6 text boxes.  Two of the text boxes the user will enter a start and end date using the calendar.  There is then a command button that when clicked will run the queries to populate the remaining 4 text boxes and as part of those queries they will take the dates from the two text boxes containing the start and end dates.  Unfortunately I am getting the following error message: "No value given for one or more required parameters" .  If I run the query directly with the start and end dates filled in on the for it runs ok.  Via the command button I have some additional code that does a count of 1 field in the query results.  I have included the sql version of the Access query and also the code that sites behind the command button.

Query code:

SELECT dbo_PurchaseOrder.PurchaseOrderNumber, dbo_PurchaseOrder.InstallationDate, dbo_VehicleInstallation.RegNumber, dbo_VehicleInstallation.UnitSerialNumber, dbo_VehicleInstallation.SerialNumberRemoved, dbo_VehicleInstallation.Category, qryALLUnitShortSerialNo.ShortSerial, qryALLUnitShortSerialNo.UnitSerialNumber, qryALLUnitShortSerialNo.UnitVersion, qryALLUnitShortSerialNo.Status
FROM (dbo_PurchaseOrder INNER JOIN dbo_VehicleInstallation ON dbo_PurchaseOrder.PurchaseOrderNumber = dbo_VehicleInstallation.PurchaseOrderID) INNER JOIN qryALLUnitShortSerialNo ON dbo_VehicleInstallation.UnitSerialNumber = qryALLUnitShortSerialNo.ShortSerial
WHERE (((dbo_PurchaseOrder.InstallationDate) Between [Forms]![frmTCSV9StockBreakdown]![txtStartDate] And [forms]![frmTCSV9StockBreakdown]![txtEndDate]) AND ((dbo_VehicleInstallation.Category)=8))
ORDER BY dbo_PurchaseOrder.InstallationDate DESC;

Command Button Code:
Private Sub cmdUpgSCRun_Click()
On Error GoTo Load_err
Dim rs As New Recordset
Dim sqlstr As String

Form.Refresh

'UNIT UPGRADE AND SERVICE CALL STOCK DETAILS
'get number of TCSV9 Units USED During Service Calls
sqlstr = "Select Count(qrySTKUSEDASSERVICECALLS.ShortSerial) As TCSV9USEDINSC from qrySTKUSEDASSERVICECALLS WHERE (qrySTKUSEDASSERVICECALLS.UnitVersion) = 'TCSY'"
rs.Open sqlstr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Me.TCSY9SCINSTALLS = rs.Fields("TCSV9USEDINSC")
rs.Close
'get number of TCSV9 Units REPLACED During Service Calls
sqlstr = "Select Count(qrySTKREPLACEDASSERVICECALLS.ShortSerial) As TCSV9REPLACEDSC from qrySTKREPLACEDASSERVICECALLS WHERE (qrySTKREPLACEDASSERVICECALLS.UnitVersion) = 'TCSY'"
rs.Open sqlstr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Me.TCSY9SCREPLACED = rs.Fields("TCSV9REPLACEDSC")
rs.Close
'get number of TCSV9 Units USED During Upgrades
sqlstr = "Select Count(qrySTKUSEDASUPGRADES.ShortSerial) As TCSV9UPGRADES from qrySTKUSEDASUPGRADES where (qrySTKUSEDASUPGRADES.UnitVersion) = 'TCSY'"
rs.Open sqlstr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Me.TCSY9UPGINST = rs.Fields("TCSV9UPGRADES")
rs.Close
'get number of TCSV9 Units REPLACED During Upgrades
sqlstr = "Select Count(qrySTKREPLACEDASUPGRADES.ShortSerial) As TCSV9UPGRADED from qrySTKREPLACEDASUPGRADES WHERE (qrySTKREPLACEDASUPGRADES.UnitVersion) = 'TCSY'"
rs.Open sqlstr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Me.TCSY9UPGREPL = rs.Fields("TCSV9UPGRADED")
rs.Close

Exit Sub
Load_err:
    MsgBox Err.Description
End Sub

Thanks in advance.

Ian
0
ian_pawley
Asked:
ian_pawley
1 Solution
 
peter57rCommented:
A query run in code will not pick up any parameter values.
You have to set them explicitly.

Sample code...
Dim qdf As Querydef
Dim rst As DAO.Recordset

Set qdf = CurrentDB.OpenQueryDef(qryName)

' assign values to the parameters
qdf.Parameters(0) = forms!myform!texbox1
qdf.Parameters(1) = forms!myform!texbox2

Set rst = qdf.OpenRecordset
' do something with the recordset

rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
0
 
thydzikCommented:
can you attach an example database returning the error
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now