DLookup with multiple Values

[cstickman]

Hello Everyone - I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but I have no idea how to write that as I have never used it before. Below is what I was using until this new request came up. If someone could help walk me through the recordset that would be greatly appreciated. Thanks

<code>
Private Sub txtloan1_AfterUpdate()
If IsNull(DLookup("[loan1]", _
"settlement", _
"[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then
Cancel = True
MsgBox "Test", vbOKOnly, "Warning"
End If
End Sub
</code>

This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.

 

[pbaldy]

You say "I want it to return a certain one"; which one? Maybe the DLookup() just needs another criteria.

 

[cstickman]

I currently have it searching the loan number and then if it is in an open state within the status column. The status column can have broken, closed or open and when it finds the closed one first it stops and does not report back as a duplicate, but it could be in the database all three times with each state in the status column.

 

[pbaldy]

You're already testing for Open, so it should only report a duplicate if there's a record with that status. Is that not happening?

 

[cstickman]

That is correct - what I did to test it was I entered one loan in four times and put the status as Open, Closed, Broken and left one blank. So is my code maybe wrong?

 

[pbaldy]

Can you post the db here?

 

[cstickman]

Thank you very much for your help! I figured it out and I am an idiot. When entering in the number into the database it added one each time. So it was doing it correctly. I figured it out when I ran it in my test database and compared it. It has been a long day!!

 

[pbaldy]

Happy to help!