Solved

amend query criteria line

Posted on 2010-11-12
4
277 Views
Last Modified: 2012-08-13
I have a recordset that gives me a list of codes that I need to validate based on qrymvriscodestovalidate.

for each [mvris code]  to validate i would like to run the query QrySMMTClientTechnicalMatch however how can I use vb code to change the criteria line of field [mvris code] in the query to match the [mvris code] field of the qrymvriscodestovalidate?
Sub GetWorstCase(ClientNameIn As String)
Dim db As DAO.Database
Set db = CurrentDb
Dim clientnamenew As String
Dim qd As DAO.QueryDef
Dim sqlstr As String
Dim newsql As String
Dim strtest As String
Dim valstr
Dim TotalMvrisCount As Long
Dim wordArray() As String
Dim rs As DAO.Recordset

Dim initialstr As String






'get [mvris codes] to validate
Set rs = db.OpenRecordset("QryMVRISCodesToValidate", dbOpenSnapshot)

If rs.EOF = True And rs.BOF = True Then

'no codes to validate
Else


rs.MoveFirst

  
Do While Not rs.EOF
    'get first [mvris code] in recordset list to validate
  

Debug.Print rs.Fields("mvris code").Value








    
    
  
    rs.MoveNext
Loop






End If

End Sub

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34120923
open another recordset using the rs("mvris code") as filter


dim rs1 as dao.recordset



Do While Not rs.EOF

    'get first [mvris code] in recordset list to validate

    set rs1=db.openrecordset("select * from QrySMMTClientTechnicalMatch where [Name of Field here]='" &  rs("mvris code") & "'")



    rs1.movefirst

   do until rs1.eof



  ' do your thing here

   



   rs1.movenext

   loop

Debug.Print rs.Fields("mvris code").Value

  

    rs.MoveNext

Loop

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 34121154
ok the next move was to take results from rs1 and save them for each mvris code to validate
Sub GetWorstCase(ClientNameIn As String)
Dim db As DAO.Database
Set db = CurrentDb
Dim clientnamenew As String
Dim qd As DAO.QueryDef
Dim sqlstr As String
Dim newsql As String
Dim strtest As String
Dim valstr
Dim TotalMvrisCount As Long
Dim wordArray() As String
Dim rs As DAO.Recordset

Dim initialstr As String



Set RsBestTable = db.OpenRecordset("Bestmatch")


'get [mvris codes] to validate
Set rs = db.OpenRecordset("QryMVRISCodesToValidate", dbOpenSnapshot)

If rs.EOF = True And rs.BOF = True Then

'no codes to validate
Else


rs.MoveFirst

  
Do While Not rs.EOF
    'get first [mvris code] in recordset list to validate

    Set Rs1 = db.OpenRecordset("select * from QrySMMTClientTechnicalMatch where [mvris code]='" & rs("mvris code") & "'")
    
    Do While Not Rs1.EOF

        RsBestTable.AddNew
        RsBestTable("MVRIS CODE").Value = Rs1("MVRIS CODE").Value
        RsBestTable("type_id").Value = Rs1("clientcode").Value
        RsBestTable("HighestScore").Value = Rs1("total").Value
        RsBestTable("ClientName").Value = clientnamenew
        RsBestTable("ModelCWRank").Value = Rs1("ModelCWRank").Value
        RsBestTable("ModelRank").Value = Rs1("ModelRank").Value
        RsBestTable("ccRank").Value = Rs1("ccRank").Value
        RsBestTable("DoorRank").Value = Rs1("DoorRank").Value
        RsBestTable("TransmissionRank").Value = Rs1("TransmissionRank").Value
        RsBestTable("FuelRank").Value = Rs1("FuelRank").Value
        RsBestTable("DateRank").Value = Rs1("DateRank").Value
        RsBestTable("BodyStrRank").Value = Rs1("BodyStrRank").Value
        RsBestTable("DriveRevRank").Value = Rs1("DriveRevRank").Value
        RsBestTable("DriveStrRank").Value = Rs1("DriveStrRank").Value
        RsBestTable("ValveRank").Value = Rs1("ValveRank").Value
        RsBestTable("CabRank").Value = Rs1("CabRank").Value
        RsBestTable("RoofRank").Value = Rs1("RoofRank").Value
        RsBestTable("BhpStrRank").Value = Rs1("BhpStrRank").Value
        RsBestTable("WheelBaseRank").Value = Rs1("WheelBaseRank").Value
        RsBestTable("Total").Value = Rs1("Total").Value
        
        RsBestTable.Update
        
        Rst1.MoveNext
    Loop



    rs.MoveNext
Loop






End If

End Sub

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 34121279
worked great thx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34121291
What are you trying to achieve here. I am assuming QrySMMTClientTechnicalMatch is a query but you are trying to add to a query? Really you should be adding records to a table.

Are you just wanting to check the mvris code exists in the result of the query QrySMMTClientTechnicalMatch?

Looking at your code it seems you are trying to copy a record that does not exist in the query. Is that right?
Sub GetWorstCase(ClientNameIn As String)

    Dim db As DAO.Database
    Dim clientnamenew As String
    Dim qd As DAO.QueryDef
    Dim sqlstr As String
    Dim newsql As String
    Dim strtest As String
    Dim valstr
    Dim TotalMvrisCount As Long
    Dim wordArray() As String
    Dim rs As DAO.Recordset
    Dim initialstr As String
    Dim RsBestTable As Recordset
    
    
    Set db = CurrentDb
    
    'get [mvris codes] to validate
    Set rs = db.OpenRecordset("QryMVRISCodesToValidate", dbOpenSnapshot)
    
    Set RsBestTable = db.OpenRecordset("MYTABLETOADDRECORDSGOESHERE")
    
    If rs.EOF = False Then
        Do While Not rs.EOF
            'get first [mvris code] in recordset list to validate
      
    
            Debug.Print rs.Fields("mvris code").Value
            
            'IF mvris code IS NUMERIC THEN DROP THE SINGLE QUOTeS
            If DCount("*", "QrySMMTClientTechnicalMatch", "[mvris code] = '" & rs("mvris code") & "'") = 0 Then
            
                RsBestTable.AddNew
                RsBestTable("MVRIS CODE").Value = rs("MVRIS CODE").Value
                RsBestTable("type_id").Value = rs("clientcode").Value
                RsBestTable("HighestScore").Value = rs("total").Value
                RsBestTable("ClientName").Value = clientnamenew
                RsBestTable("ModelCWRank").Value = rs("ModelCWRank").Value
                RsBestTable("ModelRank").Value = rs("ModelRank").Value
                RsBestTable("ccRank").Value = rs("ccRank").Value
                RsBestTable("DoorRank").Value = rs("DoorRank").Value
                RsBestTable("TransmissionRank").Value = rs("TransmissionRank").Value
                RsBestTable("FuelRank").Value = rs("FuelRank").Value
                RsBestTable("DateRank").Value = rs("DateRank").Value
                RsBestTable("BodyStrRank").Value = rs("BodyStrRank").Value
                RsBestTable("DriveRevRank").Value = rs("DriveRevRank").Value
                RsBestTable("DriveStrRank").Value = rs("DriveStrRank").Value
                RsBestTable("ValveRank").Value = rs("ValveRank").Value
                RsBestTable("CabRank").Value = rs("CabRank").Value
                RsBestTable("RoofRank").Value = rs("RoofRank").Value
                RsBestTable("BhpStrRank").Value = rs("BhpStrRank").Value
                RsBestTable("WheelBaseRank").Value = rs("WheelBaseRank").Value
                RsBestTable("Total").Value = rs("Total").Value
                
                RsBestTable.Update
            End If
     
            rs.MoveNext
        Loop
    End If

    rs.Close
    Set rs = Nothing
    RsBestTable.Close
    Set RsBestTable = Nothing
    
    
End Sub

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now