• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

amend query criteria line

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
PeterBaileyUk
Asked:
PeterBaileyUk
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
worked great thx
0
 
rockiroadsCommented:
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now