troubleshooting Question

VBA query recordset results and query results are different

Avatar of developingprogrammer
developingprogrammer asked on
Microsoft Access
6 Comments1 Solution747 ViewsLast Modified:
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 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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros