Solved

VBA query recordset results and query results are different

Posted on 2013-05-26
6
648 Views
Last Modified: 2013-06-28
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

Open in new window


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
0
Comment
Question by:developingprogrammer
  • 3
  • 2
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39197831
Whenever I use a querydef as the source of a recordset, I use a syntax similar to

Dim qdf as dap.querydef
Dim rs as dao.recordset

Set qdf =currentdb.querydefs("query name")
With qdf
    .parameters(1)=forms!formname.controlname
    .parameters(2) = forms!formname.control2name

    .parawmeters(parameter name)= forms!formname.control3name
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!
0
 

Author Comment

by:developingprogrammer
ID: 39197841
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!!! = ))
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39199009
can you post the SQL of the query?  It might have something to do with the way you defined your parameters, so include those lines from the SQL as well.  Notice the difference in the way your parameters are listed on the left side of the assignment.
qdfNewRPXSRsAssignedToUs("[Forms!frmWBRPreparation!Set1StartDateValue]") = Forms!frmWBRPreparation!Set1StartDateValue
qdfNewRPXSRsAssignedToUs("[Forms]![frmWBRPreparation]![Set1EndDateValue]") = Forms!frmWBRPreparation!Set1EndDateValue
qdfNewRPXSRsAssignedToUs("Forms!frmWBRPreparation!txtInvisibleStaffNames") = Forms!frmWBRPreparation!txtInvisibleStaffNames

Open in new window

The standard method would look like:

[Forms]![frmWBRPreparation]![Set1EndDateValue]

If you are only using this query for this purpose, and not as the rowsource for another form or query, I'd redefine the parameters as:
StartDate   (date/time)
EndDate    (date/Time)
Staff Names  (text)

And then rewrite those previous three lines as:
qdfNewRPXSRsAssignedToUs("StartDate") = Forms!frmWBRPreparation!Set1StartDateValue
qdfNewRPXSRsAssignedToUs("EndDate") = Forms!frmWBRPreparation!Set1EndDateValue
qdfNewRPXSRsAssignedToUs("StaffNames") = Forms!frmWBRPreparation!txtInvisibleStaffNames

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 45

Expert Comment

by:aikimark
ID: 39199456
What data types are assigned to those parameters?
Be sure to use the exact property of the controls:
Forms!frmWBRPreparation!Set1StartDateValue.Value
0
 

Author Comment

by:developingprogrammer
ID: 39261311
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!! = )
0
 

Author Comment

by:developingprogrammer
ID: 39283702
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!! = ))
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question