Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

amend query criteria line

Posted on 2010-11-12
4
Medium Priority
?
339 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

618 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