VBA query recordset results and query results are different
hi guys, here's a snippet of my code below. when i run this parameterised query manually by double clicking on it, it's ok. gives me the right results. but when i put the query results into a recordset, things start going awry.
i've checked that the parameters i'm putting in the query through VBA is correct by using a debug.print method to check they are what i want. i've also closed all forms and run the query where it pops up the parameter popup and then i manually key in what the debug.print method wrote out. all's correct for this as well. just that when i put the query into a recordset i keep getting the wrong results. i'm really not sure why.
i've faced this problem before so i've tried all the solutions to no avail. last time there was a problem because in the query window i didnt right click and then click parameters and put in the parameters. but i've done that for this one. help help guys!!!
P.S. below i've got the code snippet for "New RPX SRs Assigned To Us" and also "Open RPX Cases". I added these 2 components at the same time. "Open RPX Cases" is doing ok no problem but the "New RPX SRs Assigned To Us" is giving a different result in the recordset vis a vis the manually opened query. so so weird. why why why guys?!?!!
Dim qdfNewRPXSRsAssignedToUs As QueryDefSet qdfNewRPXSRsAssignedToUs = CurrentDb.QueryDefs("qryWBR-Servicing-Drivers-New RPX SRs")qdfNewRPXSRsAssignedToUs("[Forms!frmWBRPreparation!Set1StartDateValue]") = Forms!frmWBRPreparation!Set1StartDateValueqdfNewRPXSRsAssignedToUs("[Forms]![frmWBRPreparation]![Set1EndDateValue]") = Forms!frmWBRPreparation!Set1EndDateValueqdfNewRPXSRsAssignedToUs("Forms!frmWBRPreparation!txtInvisibleStaffNames") = Forms!frmWBRPreparation!txtInvisibleStaffNamesDim qdfOpenRPXCases As QueryDefSet qdfOpenRPXCases = CurrentDb.QueryDefs("qryWBR-Servicing-Drivers-IDV RPX Open Cases")qdfOpenRPXCases("[Forms]![frmWBRPreparation]![Set1EndDateValue]") = Forms!frmWBRPreparation!Set1EndDateValueqdfOpenRPXCases("Forms!frmWBRPreparation!txtInvisibleStaffNames") = Forms!frmWBRPreparation!txtInvisibleStaffNamesDim rsqdfNewRPXSRsAssignedToUs As RecordsetSet rsqdfNewRPXSRsAssignedToUs = qdfNewRPXSRsAssignedToUs.OpenRecordset()Dim rsqdfOpenRPXCases As RecordsetSet rsqdfOpenRPXCases = qdfOpenRPXCases.OpenRecordset()Do While Not rsqdfOpenRPXCases.EOF rstblConversionRate.FindFirst "[Team Members]= '" & rsqdfOpenRPXCases![Team Members] & "'" If Not rstblConversionRate.NoMatch Then rstblConversionRate.Edit rstblConversionRate![Open RPX Cases] = rsqdfOpenRPXCases![No of Open RPX Cases] rstblConversionRate.Update End If rsqdfOpenRPXCases.MoveNextLoopDo While Not rsqdfNewRPXSRsAssignedToUs.EOF rstblConversionRate.FindFirst "[Team Members]= '" & rsqdfNewRPXSRsAssignedToUs![Team Members] & "'" If Not rstblConversionRate.NoMatch Then rstblConversionRate.Edit rstblConversionRate![New SRs] = rsqdfNewRPXSRsAssignedToUs![No of New RPX SRs] rstblConversionRate.Update End If rsqdfNewRPXSRsAssignedToUs.MoveNextLoop
Dim qdf as dap.querydef
Dim rs as dao.recordset
Set qdf =currentdb.querydefs("quer
.parameters(2) = forms!formname.control2nam
.parawmeters(parameter name)= forms!formname.control3nam
Set rs = add.openrecordset
You can use either the parameter # or name to identify the parameter.
Sent from my iPad,so check for typos!