If/Then help!

[KelHemp]

I have a form that I want to automatically fill in information on.

When the LeaseNumber field is equal to the PrevLeaseNumber field, I want to fill in LeaseName and Location with DLookup or some other action that would retrieve them from the previous record.

Should I use a macro? SQL? An OnUpdate event?

[MMcCarthy]

In the After Update event of the LeaseNumber field control.

 
Public Sub LeaseNumber_AfterUpdate()
 
  If Me.LeaseNumber = Me.PrevLeaseNumber Then
    Me.LeaseName = Dlookup("[LeaseName]","TableName","[LeaseNumber]=" & Me.LeaseNumber)
    Me.Location = Dlookup("[Location]","TableName","[LeaseNumber]=" & Me.LeaseNumber)
  End If
 
End Sub
 
 

or if LeaseNumber is a text field then:

 
Public Sub LeaseNumber_AfterUpdate()
 
  If Me.LeaseNumber = Me.PrevLeaseNumber Then
    Me.LeaseName = Dlookup("[LeaseName]","TableName","[LeaseNumber]='" & Me.LeaseNumber & "'")
    Me.Location = Dlookup("[Location]","TableName","[LeaseNumber'" & Me.LeaseNumber & "'")
  End If
 
End Sub
 
 

[KelHemp]

In the After Update event of the LeaseNumber field control.

 
Public Sub LeaseNumber_AfterUpdate()
 
  If Me.LeaseNumber = Me.PrevLeaseNumber Then
    Me.LeaseName = Dlookup("[LeaseName]","TableName","[LeaseNumber]=" & Me.LeaseNumber)
    Me.Location = Dlookup("[Location]","TableName","[LeaseNumber]=" & Me.LeaseNumber)
  End If
 
End Sub

It is a number type. I went into the AfterUpdate and pasted the code, but nothing happened like I wanted. I hit the "Code Bulder" option and it automatically created the lines

Option Compare Database
Public Sub Lease_Number_AfterUpdate()
 
End Sub
 

And I pasted the middle section in and tried that.

Now, it gives me a compile error: Method or data member not found.
It also highlights the "Public Sub..." line. it selects the ".LeaseNumber" bit too.

[KelHemp]

okay, I'm an idiot. I forgot to replace "TableName" with the name of my table, Products1. And I replaced the "." in each line with "!" and it works great now.

Thanks for the help!
Here's what I ended up with:

Option Compare Database
Public Sub Lease_Number_AfterUpdate()
 
If Me!LeaseNumber = Me!PrevLeaseNumber Then
    Me!LeaseName = DLookup("[LeaseName]", "Products1", "[LeaseNumber]=" & Me!LeaseNumber)
    Me!Location = DLookup("[Location]", "Products1", "[LeaseNumber]=" & Me!LeaseNumber)
  End If
 
End Sub

[MMcCarthy]

LeaseNumber is Lease_Number on the form.

Public Sub Lease_Number_AfterUpdate()

If Me.Lease_Number = Me.PrevLeaseNumber Then
Me.LeaseName = Dlookup("[LeaseName]","TableName","[LeaseNumber]=" & Me.Lease_Number)
Me.Location = Dlookup("[Location]","TableName","[LeaseNumber]=" & Me.Lease_Number)
End If

End Sub