autofill related field

[ssaucepr]

I am using Access 97. I have a subform in which I have the following two fields:

account number (100)
account number description (Office Supplies)

I want the description field to autofill when the user enters the account number.

I also have another table not the same one linked to the subform that has just two fields, account numbers and related descriptions.

I tried the help in Access for autofill. But it is not working.

 

[ntp]

Assuming tblAccounts is the table you have with two fields, AccountNumber and AccountDescription then you could do this:

Private sub txtAccountNumber_AfterUpdate(0
Forms![subfrmSomeName]![txtAccountDescription] = DLookup("[AccountDescription]", "tblAccounts", "[AccountNumber] ='" & Forms![subfrmSomeName]![txtAccountNumber] & "'")
End Sub

I referenced your sub form as subfrmSomeName in this code.

ntp

 

[cft]

This code is very clear and works! Modifications may be required, such as removing the "txt" before the Field Name, and if your form field is based on a query rather than a table. Thanks to ntp! cft