Can you attach your workbook? It is very difficult to analyze this code out of context, and your description is not complete. Therefore the references in your description to "record" and "projectID" are mysterious, and I have no idea what you mean by, "It doesn't return every value.". It would be helpful to see the form and be able to run modified code to see results. It would also be helpful to be able to see the data on the worksheet.
That being said, I have some comments based on my best guess as to what's going on.
Your Sub statement and declarations are not provided. I recommend using Option Explicit to require declaration of all variables.
I cannot figure out why you are using DoEvents. It returns control to the operating system to allow queued events to be processed. No clue as to why that would be needed here.
The following line of code does not do anything useful:
row_number = row_number
' Should it be
row_number = row_number + 1
As it stands this will put you in an infinite loop if the value in A1 is not blank.
Why do you start with item_in_review2? What happened to item_in_review1?
You restart on row 1 each loop. I would think you should start each loop where the previous one left off.
I cannot figure out your strategy for incrementing the row number each time through each loop. In the first loop you do not increment at all (see comment above). Then you increment by 1, then in the next loop by 2, and then 3. Why would you want to look only at every third record by the time you get to the last loop? Maybe this is a logic error.
The reason that it shows the found values from bottom to top is that the loop continues until cell "A" & row number is blank. Therefore the values left in the textboxes are the last ones found. Instead, you should exit the loop the first time you find a match.
Your indentation is a bit eccentric. The convention is to have Do and Loop at the same level, then indent the code in between them. Similar for If and End If, and all other control structures.
A With can streamline the appearance of the code (and also make it more efficient but that is probably not an issue here) since it always refers to the same sheet.
I have shown the code with some modifications that I think make some corrections. However, this is a bit shooting in the dark without more complete information.
Private Sub WhatIsTheNameOfThisSub()
With Sheets("Permitting Agency Information")
row_number = 1
Do
DoEvents
row_number = row_number + 1 ' Assuming that omission of "+ 1" was an error
item_in_review2 = Range("A" & row_number)
If item_in_review2 = txtProjectID.Text Then
txtPermitAgencyName1.Text = Range("B" & row_number) ' These values are returned that match the ProjectID (Does not include other sheets as it stands (only Master Project Form))
txtPermitNumber1.Text = Range("C" & row_number)
cmboPermitStatus1.Text = Range("D" & row_number)
txtApplicationDate1.Text = Range("E" & row_number)
End If
Loop Until item_in_review2 = txtProjectID.Text Or item_in_review2 = ""
Do
DoEvents
row_number = row_number + 1
item_in_review3 = Range("A" & row_number)
If item_in_review3 = txtProjectID.Text Then
txtPermitAgencyName2.Text = Range("B" & row_number) ' These values are returned that match the ProjectID (Does not include other sheets as it stands (only Master Project Form))
txtPermitNumber2.Text = Range("C" & row_number)
cmboPermitStatus2.Text = Range("D" & row_number)
txtApplicationDate2.Text = Range("E" & row_number)
End If
Loop Until item_in_review3 = txtProjectID.Text Or item_in_review3 = ""
Do
DoEvents
row_number = row_number + 1
item_in_review4 = Range("A" & row_number)
If item_in_review4 = txtProjectID.Text Then
txtPermitAgencyName3.Text = Range("B" & row_number) ' These values are returned that match the ProjectID (Does not include other sheets as it stands (only Master Project Form))
txtPermitNumber3.Text = Range("C" & row_number)
cmboPermitStatus3.Text = Range("D" & row_number)
txtApplicationDate3.Text = Range("E" & row_number)
End If
Loop Until item_in_review4 = txtProjectID.Text Or item_in_review4 = ""
Do
DoEvents
row_number = row_number + 1
item_in_review5 = Range("A" & row_number)
If item_in_review5 = txtProjectID.Text Then
txtPermitAgencyName4.Text = Range("B" & row_number) ' These values are returned that match the ProjectID (Does not include other sheets as it stands (only Master Project Form))
txtPermitNumber4.Text = Range("C" & row_number)
cmboPermitStatus4.Text = Range("D" & row_number)
txtApplicationDate4.Text = Range("E" & row_number)
End If
Loop Until item_in_review5 = txtProjectID.Text Or item_in_review5 = ""
End With
End Sub
LinkBack URL
About LinkBacks
clapforthewolfman 
Register To Reply
Originally Posted by clapforthewolfman
Bookmarks