VB6 - "FindFirst" with multiple search Criteria

[tgifgemini]

VB6 - "FindFirst" with multiple search Criteria
Hi everyone,
I am trying to modify and expedite the execution of this VB module that was started by an someone else.
If you look at the underlying module, you'll see that there are numerous nested loops(Loops within a loop). I understand that he used this approach because he couldn't adequately code the FindFirst function.
Looking at the module below, what is the most efficient way for me to imbed the "FindFirst" function, because that will be more efficient than all these loops.

Below is the module:

ii = 5
    ii = ii + 1

    Do Until M.qBW.EOF
        xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
        xlWksht.Cells(ii, 2).Value = M.qBW![Description]
        xlWksht.Cells(ii, 3).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
        xlWksht.Cells(ii, 4).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]

        rsinPers.MoveFirst
        Do Until rsinPers.EOF
           If rsinPers![Name] = M.qBW![Personnel2] Then
           xlWksht.Cells(ii, 5).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
           rsinPers.MoveNext
        Loop

        rsinPers.MoveFirst
        Do Until rsinPers.EOF
           If rsinPers![Name] = M.qBW![Personnel3] Then xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
           rsinPers.MoveNext
        Loop
        
          rsinPers.MoveFirst
        Do Until rsinPers.EOF
           If rsinPers![Name] = M.qBW![Personnel4] Then xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
           rsinPers.MoveNext
        Loop
        
        rsinPers.MoveFirst
        Do Until rsinPers.EOF
           If rsinPers![Name] = M.qBW![Personnel5] Then xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
           rsinPers.MoveNext
        Loop
        
        rsinPers.MoveFirst
        Do Until rsinPers.EOF
           If rsinPers![Name] = M.qBW![Personnel6] Then xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hr]
           rsinPers.MoveNext
        Loop
        
        xlWksht.Cells(ii, 10).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
        xlWksht.Cells(ii, 11).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
        xlWksht.Cells(ii, 12).Value = M.qBW![EstimatedStartDate] & Chr(10) & M.qBW![Start Date]
        xlWksht.Cells(ii, 13).Value = M.qBW![EstimatedEndDate] & Chr(10) & M.qBW![End  Date]
        xlWksht.Cells(ii, 14).Value = "Comments:" & Chr(10) & "'" & M.qBW![Comments]
    Loop

Thanks,
tgifgemini.

[QVeen72]

Hi,

It shud be some thing like this:

rsinPers.MoveFirst
Do
   rsinPers.Find "Name='" & M.qBW![Personnel2] & "'"
  If  rsinPers.EOF
       Exit Do
  Else
       xlWksht.Cells(ii, 5).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
   End If
 Loop

Regards
Veena

[tgifgemini]

Good morning Veena.
I've see your code, but before I implement it, I want you to take a look at the way I used the FindFirst logic/Function and tell me what you think.
[code]
M.qBW.MoveFirst
rsinPers.MoveFirst

Do Until M.qBW.EOF = True
xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
xlWksht.Cells(ii, 2).Value = M.qBW![Description]
xlWksht.Cells(ii, 3).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
xlWksht.Cells(ii, 4).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]

SrchCriteria = "[Name]= '" & rsinPers![Personnel2] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 5).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
End If

SrchCriteria = "[Name]= '" & rsinPers![Personnel3] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
End If

SrchCriteria = "[Name]= '" & rsinPers![Personnel4] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
End If

SrchCriteria = "[Name]= '" & rsinPers![Personnel5] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
End If

SrchCriteria = "[Name]= '" & rsinPers![Personnel6] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hrs]
End If

xlWksht.Cells(ii, 10).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
xlWksht.Cells(ii, 11).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
xlWksht.Cells(ii, 12).Value = M.qBW![Start Date] & Chr(10) & M.qBW![Start Date]
xlWksht.Cells(ii, 13).Value = M.qBW![End Date] & Chr(10) & M.qBW![End Date]
xlWksht.Cells(ii, 14).Value = "Comments:" & Chr(10) & "'" & M.qBW![Comments]

rsinPers.MoveNext
M.qBW.MoveNext
Loop
Thanks.
tgifgemini.

[tgifgemini]

Please disregard the previous post. I forgot to use code tags:[///]

Good morning Veena.
I've see your code, but before I implement it, I want you to take a look at the way I used the FindFirst logic/Function and tell me what you think.

M.qBW.MoveFirst
rsinPers.MoveFirst

Do Until M.qBW.EOF = True
xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
xlWksht.Cells(ii, 2).Value = M.qBW![Description]
xlWksht.Cells(ii, 3).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
xlWksht.Cells(ii, 4).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]

SrchCriteria = "[Name]= '" & rsinPers![Personnel2] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 5).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
End If

SrchCriteria = "[Name]= '" & rsinPers![Personnel3] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
End If

SrchCriteria = "[Name]= '" & rsinPers![Personnel4] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
End If

SrchCriteria = "[Name]= '" & rsinPers![Personnel5] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
End If

SrchCriteria = "[Name]= '" & rsinPers![Personnel6] & "'"
M.qBW.FindFirst SrchCriteria
If M.qBW.NoMatch = False Then
xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hrs]
End If

xlWksht.Cells(ii, 10).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
xlWksht.Cells(ii, 11).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
xlWksht.Cells(ii, 12).Value = M.qBW![Start Date] & Chr(10) & M.qBW![Start Date]
xlWksht.Cells(ii, 13).Value = M.qBW![End Date] & Chr(10) & M.qBW![End Date]
xlWksht.Cells(ii, 14).Value = "Comments:" & Chr(10) & "'" & M.qBW![Comments]

rsinPers.MoveNext
M.qBW.MoveNext
Loop

Thanks.
tgifgemini.

[QVeen72]

Hi,

yes ur code is correct, i thought u had multiple results for search crieteria, so i put in a loop, but if it is only one, then u can go ahead, it seems al right to me..

regards
Veena

[QVeen72]

Hi,

I noticed, u r using Find First, there is no "FindFirst" for ADO, if using DAO then alright.
My code was for ADO.

Regards
Veena

[tgifgemini]

Good morning Veena.
Yes, I am using DAO and it has "FindFirst" object.
Thanks.
tgifgemini.