asked on

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

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

PeterBaileyUk

ASKER

worked great thx

rockiroads

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