Visual Basic 4 / 5 / 6 RSS Visual Basic 4 / 5 / 6 RSS

VB6 - "FindFirst" with multiple search Criteria

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site
Thread Solved

•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

VB6 - "FindFirst" with multiple search Criteria

 
0
  #1
Aug 20th, 2007
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:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. ii = 5
  2.     ii = ii + 1
  3.  
  4.     Do Until M.qBW.EOF
  5.         xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
  6.         xlWksht.Cells(ii, 2).Value = M.qBW![Description]
  7.         xlWksht.Cells(ii, 3).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
  8.         xlWksht.Cells(ii, 4).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]
  9.  
  10.         rsinPers.MoveFirst
  11.         Do Until rsinPers.EOF
  12.            If rsinPers![Name] = M.qBW![Personnel2] Then
  13.            xlWksht.Cells(ii, 5).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
  14.            rsinPers.MoveNext
  15.         Loop
  16.  
  17.         rsinPers.MoveFirst
  18.         Do Until rsinPers.EOF
  19.            If rsinPers![Name] = M.qBW![Personnel3] Then xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
  20.            rsinPers.MoveNext
  21.         Loop
  22.  
  23.           rsinPers.MoveFirst
  24.         Do Until rsinPers.EOF
  25.            If rsinPers![Name] = M.qBW![Personnel4] Then xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
  26.            rsinPers.MoveNext
  27.         Loop
  28.  
  29.         rsinPers.MoveFirst
  30.         Do Until rsinPers.EOF
  31.            If rsinPers![Name] = M.qBW![Personnel5] Then xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
  32.            rsinPers.MoveNext
  33.         Loop
  34.  
  35.         rsinPers.MoveFirst
  36.         Do Until rsinPers.EOF
  37.            If rsinPers![Name] = M.qBW![Personnel6] Then xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hr]
  38.            rsinPers.MoveNext
  39.         Loop
  40.  
  41.         xlWksht.Cells(ii, 10).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
  42.         xlWksht.Cells(ii, 11).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
  43.         xlWksht.Cells(ii, 12).Value = M.qBW![EstimatedStartDate] & Chr(10) & M.qBW![Start Date]
  44.         xlWksht.Cells(ii, 13).Value = M.qBW![EstimatedEndDate] & Chr(10) & M.qBW![End  Date]
  45.         xlWksht.Cells(ii, 14).Value = "Comments:" & Chr(10) & "'" & M.qBW![Comments]
  46.     Loop
Thanks,
tgifgemini.
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2006
Posts: 848
Reputation: QVeen72 is on a distinguished road 
Solved Threads: 120
QVeen72's Avatar
QVeen72 QVeen72 is offline Offline
Practically a Posting Shark

Re: VB6 - "FindFirst" with multiple search Criteria

 
1
  #2
Aug 20th, 2007
Hi,

It shud be some thing like this:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. rsinPers.MoveFirst
  2. Do
  3.    rsinPers.Find "Name='" & M.qBW![Personnel2] & "'"
  4.   If  rsinPers.EOF
  5.        Exit Do
  6.   Else
  7.        xlWksht.Cells(ii, 5).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
  8.    End If
  9.  Loop

Regards
Veena
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

Re: VB6 - "FindFirst" with multiple search Criteria

 
0
  #3
Aug 21st, 2007
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

Re: VB6 - "FindFirst" with multiple search Criteria

 
0
  #4
Aug 21st, 2007
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.
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. M.qBW.MoveFirst
  2. rsinPers.MoveFirst
  3.  
  4. Do Until M.qBW.EOF = True
  5. xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
  6. xlWksht.Cells(ii, 2).Value = M.qBW![Description]
  7. xlWksht.Cells(ii, 3).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
  8. xlWksht.Cells(ii, 4).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]
  9.  
  10. SrchCriteria = "[Name]= '" & rsinPers![Personnel2] & "'"
  11. M.qBW.FindFirst SrchCriteria
  12. If M.qBW.NoMatch = False Then
  13. xlWksht.Cells(ii, 5).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
  14. End If
  15.  
  16. SrchCriteria = "[Name]= '" & rsinPers![Personnel3] & "'"
  17. M.qBW.FindFirst SrchCriteria
  18. If M.qBW.NoMatch = False Then
  19. xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
  20. End If
  21.  
  22. SrchCriteria = "[Name]= '" & rsinPers![Personnel4] & "'"
  23. M.qBW.FindFirst SrchCriteria
  24. If M.qBW.NoMatch = False Then
  25. xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
  26. End If
  27.  
  28. SrchCriteria = "[Name]= '" & rsinPers![Personnel5] & "'"
  29. M.qBW.FindFirst SrchCriteria
  30. If M.qBW.NoMatch = False Then
  31. xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
  32. End If
  33.  
  34. SrchCriteria = "[Name]= '" & rsinPers![Personnel6] & "'"
  35. M.qBW.FindFirst SrchCriteria
  36. If M.qBW.NoMatch = False Then
  37. xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hrs]
  38. End If
  39.  
  40. xlWksht.Cells(ii, 10).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
  41. xlWksht.Cells(ii, 11).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
  42. xlWksht.Cells(ii, 12).Value = M.qBW![Start Date] & Chr(10) & M.qBW![Start Date]
  43. xlWksht.Cells(ii, 13).Value = M.qBW![End Date] & Chr(10) & M.qBW![End Date]
  44. xlWksht.Cells(ii, 14).Value = "Comments:" & Chr(10) & "'" & M.qBW![Comments]
  45.  
  46. rsinPers.MoveNext
  47. M.qBW.MoveNext
  48. Loop
Thanks.
tgifgemini.
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2006
Posts: 848
Reputation: QVeen72 is on a distinguished road 
Solved Threads: 120
QVeen72's Avatar
QVeen72 QVeen72 is offline Offline
Practically a Posting Shark

Re: VB6 - "FindFirst" with multiple search Criteria

 
0
  #5
Aug 22nd, 2007
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2006
Posts: 848
Reputation: QVeen72 is on a distinguished road 
Solved Threads: 120
QVeen72's Avatar
QVeen72 QVeen72 is offline Offline
Practically a Posting Shark

Re: VB6 - "FindFirst" with multiple search Criteria

 
0
  #6
Aug 22nd, 2007
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

Re: VB6 - "FindFirst" with multiple search Criteria

 
0
  #7
Aug 22nd, 2007
Good morning Veena.
Yes, I am using DAO and it has "FindFirst" object.
Thanks.
tgifgemini.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the Visual Basic 4 / 5 / 6 Forum
Thread Tools Search this Thread



* 6 429 2007 access activex add age append application basic beginner birth bmp calculator cd cells.find click client code college column component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report retrieve save search sendbyte sites sort sql sql2008 sqlserver subroutine table tags textbox time urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC