Requery TextBox on Lost Focus

[jandrade15]

Requery TextBox on Lost Focus- SOLVED

Hi Everyone,

I have a property management db and I need to know if I have a duplicate before entering all the data on the property.
I have a text box (Property Address) and on the lost focus event I want to put a function where it can do the following:

- Check for a duplicate address
- Give a msgbox letting me know that I have a duplicate
- Take me back to the Property Address text box and do nothing so that I can either close the form or add a new property. I have the form in data entry mode.

Ive been beating my brain senselessly to make this code work but im incredibly rusty and have been on a Access hiatus since 2002 when I started a Real Estate Business. I'm getting back into it now so any help would be tremendously appreciated. i'm hoping someone can give me some sample code that i can work off of....
BTW: I've searched all over the forums and on google to no avail. there seems to be a lot of posts but nothing i tried has worked! Thanks a million guys and gals!

 

[boblarson]

1. You don't use the Lost Focus Event. You use the BEFORE UPDATE event.

2. You can use a DCount to find if that Address exists (I don't know your fields so this is generalized):

Private Sub YourAddressTextBoxNameHere_BeforeUpdate()
If DCount("*", "YourTableNameHere", "[AddressFieldNameHere] = " & Chr(34) & Me!YourAddressTextBoxNameHere.Text & Chr(34))> 0 Then
   MsgBox "This address is already entered.", vbExclamation + vbOKOnly, "Duplicate Entry"
   Cancel = True
   Me.YourAddressTextBoxNameHere.Undo
End If
End Sub

 

[jandrade15]

Hi Boblarson,

Thanks so much for your lightning fast response. Below are my specs:
Table: Master Table
Text Box Name: Property Address
Address Field Name : ? im assuming its the same as the textbox? Below is the modified code you gave me....

Private Sub Property_Address_BeforeUpdate(cancel As Integer)
If DCount("*", "Master_Table", "[Property_Address] = " & Chr(34) & Me!Property_Address.Text & Chr(34)) > 0 Then
MsgBox "This address is already entered.", vbExclamation + vbOKOnly, "Duplicate Entry"
cancel = True
Me.Property_Address.Undo
End If
End Sub

im getting a runtime error '2465' that it cant find the field Property_Address reffered in my expression... can you see where I may be going wrong? Thanks a million for all your help!

 

[boblarson]

If the field and text box are really named Property Address and not Property_Address then you need to use square brackets and not the underscore between to deal with the space:

[Property Address]

Me.[Property Address]

It is a good idea to not use spaces in your field or object names as it simplifies things where spaces complicate matters.

 

[boblarson]

Oh, and as far as the procedure header goes, Access WILL put in the underscore, as it will use it for the event name (because event names can't have spaces) but it doesn't mean you can use them

 

[jandrade15]

Boblarson you're Awesome worked like a charm!!!

my only other issue is that if i have a duplicate and I want to close the form without entering another record it won't let me because i'm in data entry mode. so it keeps giving the message from access that i have a duplicate record, so i need to put something in the primary key.... Is there anyway around this that I can possibly put in the code you gave me? Thanks again!!!

 

[boblarson]

Boblarson you're Awesome worked like a charm!!!

my only other issue is that if i have a duplicate and I want to close the form without entering another record it won't let me because i'm in data entry mode. so it keeps giving the message from access that i have a duplicate record, so i need to put something in the primary key.... Is there anyway around this that I can possibly put in the code you gave me? Thanks again!!!

You can see if using

Me.Undo

instead of Me.[Property Address].Undo

will do what you want.

 

[jandrade15]

Boblarson COMPLETE GENIUS!!!!!! Again worked like a charm. Thank you very much for helping with 12 hours of frustration!!! Have an awesome day!

 

[boblarson]

Boblarson COMPLETE GENIUS!!!!!! Again worked like a charm. Thank you very much for helping with 12 hours of frustration!!! Have an awesome day!

Glad we could help out. As for an awesome day, it is 105 degrees Fahrenheit out so I don't know how awesome it can be