dlookup picks first record only

[mgbeye]

I am trying to set it up so that when someone enters the sap code, the description is filled in automatically. I have the info stored in a seperate table in the database. My problem is this. It goes to the first record in the file and displays the description for it. It does not sort through the whole thing. Here is the code I have. I believer the problem is in the do while loop. I am getting an error on the else so I believe the loop is stopping directly after the first following line. Can anyone help??
Here is my code.

Private Sub SAP_Code_AfterUpdate()
    Dim vardesc As Variant
    Dim tbl As Recordset

    Set tbl = db.OpenRecordset("MatLookup")
    
    tbl.MoveFirst
    Do While Not tbl.EOF
        vardesc = DLookup("description", "Mat Lookup", "SAP_code =" & [SAP_Code])
        Forms!productionreconciliation![description] = vardesc
    Else
     tbl.MoveNext
    Loop
End Sub

THANKS A TON!!! lol

 

[mgbeye]

I just thought of something else. I want to add an error message if the number is not found in the list. Can anyone help me with that. I have no idea. Thanks!

 

[scking]

Try removing the 'else' that has no matching 'if' statement. Since you don't have error checking it could be exiting prematurely, but I don't even know how it can compile. Also, since you are looking at every single record in the 'MatLookup' table you are performing the assignment to vardesc and subsequently to the text box the same number of times as there are records in the table. Since the SAP_Code doesn't change from within the Do Loop it will not change the value of vardesc.

Steve King Growth follows a healthy professional curiosity