By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,964 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,964 IT Pros & Developers. It's quick & easy.

If/Then help!

P: 16
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?
Oct 20 '06 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
In the After Update event of the LeaseNumber field control.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub LeaseNumber_AfterUpdate()
  3.  
  4.   If Me.LeaseNumber = Me.PrevLeaseNumber Then
  5.     Me.LeaseName = Dlookup("[LeaseName]","TableName","[LeaseNumber]=" & Me.LeaseNumber)
  6.     Me.Location = Dlookup("[Location]","TableName","[LeaseNumber]=" & Me.LeaseNumber)
  7.   End If
  8.  
  9. End Sub
  10.  
  11.  
or if LeaseNumber is a text field then:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub LeaseNumber_AfterUpdate()
  3.  
  4.   If Me.LeaseNumber = Me.PrevLeaseNumber Then
  5.     Me.LeaseName = Dlookup("[LeaseName]","TableName","[LeaseNumber]='" & Me.LeaseNumber & "'")
  6.     Me.Location = Dlookup("[Location]","TableName","[LeaseNumber'" & Me.LeaseNumber & "'")
  7.   End If
  8.  
  9. End Sub
  10.  
  11.  
Oct 20 '06 #2

P: 16
In the After Update event of the LeaseNumber field control.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub LeaseNumber_AfterUpdate()
  3.  
  4.   If Me.LeaseNumber = Me.PrevLeaseNumber Then
  5.     Me.LeaseName = Dlookup("[LeaseName]","TableName","[LeaseNumber]=" & Me.LeaseNumber)
  6.     Me.Location = Dlookup("[Location]","TableName","[LeaseNumber]=" & Me.LeaseNumber)
  7.   End If
  8.  
  9. 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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Public Sub Lease_Number_AfterUpdate()
  3.  
  4. End Sub
  5.  
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.
Oct 20 '06 #3

P: 16
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Public Sub Lease_Number_AfterUpdate()
  3.  
  4. If Me!LeaseNumber = Me!PrevLeaseNumber Then
  5.     Me!LeaseName = DLookup("[LeaseName]", "Products1", "[LeaseNumber]=" & Me!LeaseNumber)
  6.     Me!Location = DLookup("[Location]", "Products1", "[LeaseNumber]=" & Me!LeaseNumber)
  7.   End If
  8.  
  9. End Sub
Oct 20 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 20 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.