?
Solved

amend query criteria line

Posted on 2010-11-12
4
Medium Priority
?
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses

764 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