Autofill 1

[mgbeye]

I am working with a form in which people will enter an inventory count. What I want is to have the "Description" field automatically fill in after an "SAP Code" has been entered. I have a table ("Mat Lookup&quot setup containing the information. I have tried using the Dlookup function, but I can not seem to get it to work. HELP!!

 

[kathryn]

Are you using the AfterUpdate event of the SAP field? What is the code that you are using? Are you getting an error message? Kathryn

 

[simco]

Do you have a table1 as the datasource for the form and a different table MatLookup in which you want to search? And, more, are those 2 tables related by the field "sapCode"? If yes, try this:

Function description() as string
dim db as database
dim tbl as recordset

set db=CurrentDb
set tbl=db.OpenRecordset ("MatLookup&quot

tbl.movefirst
do while not tbl.eof
if tbl![SapCode]=Forms![FormName]![SapCode] then
description=tbl![Description]
exit
else
tbl.MoveNext
loop
end function

Assign this on an event (like"on got focus&quot of the description control from the form.

Hope this gives you an ideea at least.

 

[mgbeye]

I worked with the code given and its not giving me any errors, however it does not put the info into the field on the form. Here is what I have. Is there something wrong with it?

Private Sub SAPCode_LostFocus()
Function description() As String
Dim db As Database
Dim tbl As Recordset

Set db = CurrentDb
Set tbl = db.OpenRecordset("MatLookup&quot

tbl.MoveFirst
Do While Not tbl.EOF
If tbl![SapCode] = Forms! [productionreconciliation]![SapCode] Then
Forms![productionreconciliation]![description] = tbl![description]
Else
tbl.MoveNext
Loop
End Function
End Sub

Also, how do I make it produce an error message if the code is not in the list?

Thanks so much for your help!

NyteIZ

 

[simco]

Write the function into the Module section. Than use the "Lost focus" event of the SapCode, like
Private Sub SAPCode_LostFocus()
Forms![FormName]![Description].Setfocus
If Description()<>&quot;&quot; then
Forms![FormName]![Description]=Description()
else
Msgbox &quot;Error message!!&quot;
endif
Forms![Formname]![NextControl on the Form].Set Focus
end sub

Hope this helps

 

[simco]

Sorry, I'm using Description for the function name and for the field name(I did'n know what name are you using). I guess you are conffused. In the function I was trying to assign the function name &quot;description&quot; to the return value (e.g. description=tbl![Description] -> &quot;description&quot; is the function name and &quot;[Description]&quot; is the field name from the table). Same thing for the new code that I just wrote. So just replace those with your fields name.
Also, I think is better to keep the &quot;exit&quot; command because this is going to exit the do while..loop by the moment you found a match (less time).
;-)

 

[mgbeye]

I hate be such a bother, but it still isn't working. Here is exactly what my code says. I took out the exit because it was giving me errors.

Production Reconciliation=>form name

Description=>field name for both the &quot;mat lookup&quot; table and the form

SAP Code=> field name for both the &quot;mat lookup&quot; table and the form

I really appreciate this.



Private Sub SAPCode_LostFocus()
Forms![productionreconciliation]![description].SetFocus
If description() <> &quot;&quot; Then
Forms![productionreconciliation]![description] = description()
Else
MsgBox &quot;Error message!!&quot;
End If
Forms![productionreconciliation]![pallettagcount].Set Focus
End Sub

Private Sub SAP_Code_lostfocus()
Function description() As String
Dim db As Database
Dim tbl As Recordset

Set db = CurrentDb
Set tbl = db.OpenRecordset(&quot;MatLookup&quot

tbl.MoveFirst
Do While Not tbl.EOF
If tbl![SapCode] = Forms![productionreconciliation]![SapCode] Then
Forms![productionreconciliation]![description] = tbl![description]
Else
tbl.MoveNext
Loop
End Function

 

[simco]

This in going to work only if &quot;mat lookup&quot; IS NOT the record source for your form &quot;Production Reconciliation&quot;

1. Copy the function (from &quot;function description()...&quot; to &quot;end function&quot into the &quot;Module&quot; section of your database ( Do not assign it on the lost focus event!). For the &quot;Lost focus&quot; event leave only the first one: Private Sub SAPCode_LostFocus()
Forms![productionreconciliation]![description].SetFocus
If description() <> &quot;&quot; Then
Forms![productionreconciliation]![description] = description()
Else
MsgBox &quot;Error message!!&quot;
End If
Forms![productionreconciliation]![pallettagcount].Set Focus
End Sub
2. In the function description ()... change the line: Forms![productionreconciliation]![description] =
tbl![description]
into:
description=tbl![description]

Hope is ok

 

[mgbeye]

ok I did all of that, but still nothing is happening. I went back through and checked all of the names to be sure they matched. I'm sorry to be bugging you so much, but I really need figure this out. Do I need to call the function or module or something. How does it know when to run?

Thanks SOOO much for your help!!