autofill does not work when corrections are made. 1

[mgbeye]

On a form that I have set up I have created an event that will enter a description of the material automatically when an SAP code is entered. The data it is pulling from is in a seperate table in the database. Here is the code I am using.

Private Sub SAP_Code_LostFocus()
    Dim vardesc As Variant
    
    vardesc = DLookup("description", "Mat Lookup", "SAP_code = [sap_code]")
    Forms!productionreconciliation![description] = vardesc
End Sub

It works when you first enter the SAP code, however if you go back and change the code it does not change the description.

Can Anyone HELP!?!?!

 

[mgbeye]

ok I lied. It doesn't work. It only pulls out the first record from the file instead of searching for the correct SAP code.

Now can anyone help. LOL

 

[Rohdem]

Two things jump out at me:

I would use the after update event instead of the lost focus event. After update runs each time the value is changed.

Also, if the sap_code datatype is a number, you dlookup should be:

DLookup("description", "Mat Lookup", "SAP_code =" & [sap_code])

If it is a string, it should be:

DLookup("description", "Mat Lookup", "SAP_code ='" & [sap_code] & "'&quot
Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[mgbeye]

ok I did the things you said. Here is my new problem. It still only goes to the first record and enters it into the field. I am attempting to put a do while else loop in it so that it will move through the records. My problem now is that I can't remember how to get the do while par to go through two lines of code instead of just ending. I am getting an error message from the else because of this. Here is my code. Any suggestions??

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

 

[mgbeye]

While you are glancing at that. Is there an easy way to have it bring up an error message if the sap code is not found?

 

[Rohdem]

Ok, first off, a loop is not necessary.

I'm going to assume that you have a field on your form called description and one called sap_code (i'm taking about the name of the text boxes, not the control source).

One line of code should be able to accomplish what you want:

Private Sub SAP_Code_AfterUpdate()
me.description = DLookup("description", "Mat Lookup", "[SAP_code] =" & me.sap_code)

End Sub

You still do not have your quotes("&quot right on your dlookup.
In the criteria part of the dlookup function, try imagining the string that you are passing to the function:

The way you have it, you are passing the string:
"SAP_code = [sap_code]"

You have to concatenate the value that is in the sap_code text box into the string as I did above, so if the value of sap_code is 52, then the string that is passed to the dlookup function is
"sap_code = 52"

As for nyteiz's question, you could add another line to check the value for a null and messagebox accordingly like this:

Private Sub SAP_Code_AfterUpdate()
me.description = DLookup("description", "Mat Lookup", "[SAP_code] =" & me.sap_code)
if IsNull(me.description) then
msgbox("SAP Code not found!!&quot
end if
End Sub Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[mgbeye]

I have corrected it but I am still having the same problem. No matter what I enter it picks the first record description. Mine looks just like yours. Sorry to be such a pain, but thanks for your help

 

[Rohdem]

Is it a very large database? Can you e-mail it to me?

The other possible problem that could be occuring is if SAP_code is declared as 'text' in your table. If this is the case, you need to alter your dlookup slightly:


me.description = DLookup("description", "Mat Lookup", "[SAP_code] = '" & me.sap_code & "'&quot

Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[mgbeye]

I have sent it to you. Thanks again!!