asked on
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?!?!!
i've thought of a few possible problems and checked to rule them out (but if yall think they are still problems let me know, i'll double / triple check)
- incorrect query name - resulting in pulling wrong info into recordset
- incorrect parameters in query design
- incorrect parameters in VBA
- wrong recordset
- wrong field in recordset
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 QueryDef
Set qdfNewRPXSRsAssignedToUs = CurrentDb.QueryDefs("qryWBR-Servicing-Drivers-New RPX SRs")
qdfNewRPXSRsAssignedToUs("[Forms!frmWBRPreparation!Set1StartDateValue]") = Forms!frmWBRPreparation!Set1StartDateValue
qdfNewRPXSRsAssignedToUs("[Forms]![frmWBRPreparation]![Set1EndDateValue]") = Forms!frmWBRPreparation!Set1EndDateValue
qdfNewRPXSRsAssignedToUs("Forms!frmWBRPreparation!txtInvisibleStaffNames") = Forms!frmWBRPreparation!txtInvisibleStaffNames
Dim qdfOpenRPXCases As QueryDef
Set qdfOpenRPXCases = CurrentDb.QueryDefs("qryWBR-Servicing-Drivers-IDV RPX Open Cases")
qdfOpenRPXCases("[Forms]![frmWBRPreparation]![Set1EndDateValue]") = Forms!frmWBRPreparation!Set1EndDateValue
qdfOpenRPXCases("Forms!frmWBRPreparation!txtInvisibleStaffNames") = Forms!frmWBRPreparation!txtInvisibleStaffNames
Dim rsqdfNewRPXSRsAssignedToUs As Recordset
Set rsqdfNewRPXSRsAssignedToUs = qdfNewRPXSRsAssignedToUs.OpenRecordset()
Dim rsqdfOpenRPXCases As Recordset
Set 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.MoveNext
Loop
Do 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.MoveNext
Loop
i've thought of a few possible problems and checked to rule them out (but if yall think they are still problems let me know, i'll double / triple check)
- incorrect query name - resulting in pulling wrong info into recordset
- incorrect parameters in query design
- incorrect parameters in VBA
- wrong recordset
- wrong field in recordset
Microsoft Access
Last Comment
developingprogrammer
ASKER
thanks fyed! i checked my code according to what you wrote and it's identical - just that i don't use the with statement. but it still can't work. sorry to trouble you but could you help me out? thanks!!! = ))
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
What data types are assigned to those parameters?
Be sure to use the exact property of the controls:
Forms!frmWBRPreparation!Se
Be sure to use the exact property of the controls:
Forms!frmWBRPreparation!Se
ASKER
hrmm guys eventually i created a temp table to write the results to there and then exported it. it's a work around but doesn't solve the problem. let me upload this code to yall soon to see what's wrong. thanks so much guys for your patience with me!! = )
ASKER
hrmm ok guys i think my whole system was a bit too messy. too many built up queries. i'm rewriting my system again now with a lot better practices in place - design patters, UML, optimsation, OOP - essentially much much better organisation and factoring (the step before refactoring hahaha) so i think the problems if they occur again will be much easier to spot!! thanks for your help though guys, ya'll are always so reliable - the best!! = ))
Dim qdf as dap.querydef
Dim rs as dao.recordset
Set qdf =currentdb.querydefs("quer
With qdf
.parameters(1)=forms!formn
.parameters(2) = forms!formname.control2nam
.parawmeters(parameter name)= forms!formname.control3nam
End with
Set rs = add.openrecordset
You can use either the parameter # or name to identify the parameter.
Sent from my iPad,so check for typos!