Database duplicate entries

[Lauren B]

I am attempting to code my form so that if a user enters a Customer ID
already in use, they will get a message box that tells them immediately
after leaving the corresponding text box. (as opposed to getting an error
message when attempting to save the entry after fully completing the form).

I following codes in the before update property of the text box
("TxtBxCustomer_ID"):

DLookUp ("Customer_ID" , "Table1" , Customer_ID = " ' " &
Me.TxtBxCustomer_ID & " ' " then Msgbox "Duplicate entry. Please make a new
selection."

and

If not IsNull(DLookUp ("Customer_ID" , "Table1" , Customer_ID = " ' " _
& Me.TxtBxCustomer_ID & " ' " ) then
Msgbox "Duplicate entry. Please make a new selection."
Cancel = True
End If

Neither of these codes are working. I am not getting an error message, it
just simply is doing nothing. How can I get either of these codes to work
properly.

Thank you for any assistance.

LB

 

[Guest]

Your DLookUp syntax is incorrect in the third part. From what you are trying
to do, the Customer_ID is text and not numeric ?
If text is correct you need to keep your single quotes within the double
quotes
DLookUp("Customer_ID" , "Table1" ,"Customer_ID = '" & Me.TxtBxCustomer_ID &
"'")
If Customer_ID is numeric then you don't need any single quotes
DLookUp("Customer_ID" , "Table1" ,"Customer_ID = " & Me.TxtBxCustomer_ID)

 

[Nikos Yannacopoulos]

Lauren,

Just a small syntax error. Try:

If not IsNull(DLookUp ("Customer_ID" , "Table1" , "Customer_ID = '" _
& Me.TxtBxCustomer_ID & "'" ) then
Msgbox "Duplicate entry. Please make a new selection."
Cancel = True
End If

HTH,
Nikos

 

[Guest]

I am attempting to code my form so that if a user enters a Customer ID
already in use, they will get a message box that tells them immediately
after leaving the corresponding text box. (as opposed to getting an error
message when attempting to save the entry after fully completing the form).

I following codes in the before update property of the text box
("TxtBxCustomer_ID"):

DLookUp ("Customer_ID" , "Table1" , Customer_ID = " ' " &
Me.TxtBxCustomer_ID & " ' " then Msgbox "Duplicate entry. Please make a new
selection."

and

If not IsNull(DLookUp ("Customer_ID" , "Table1" , Customer_ID = " ' " _
& Me.TxtBxCustomer_ID & " ' " ) then
Msgbox "Duplicate entry. Please make a new selection."
Cancel = True
End If

Neither of these codes are working. I am not getting an error message, it
just simply is doing nothing. How can I get either of these codes to work
properly.

Thank you for any assistance.

LB

Lauren,

Along with the other suggestions, I would put the code in the AfterUpdate()
event of "TxtBxCustomer_ID".

You said you wanted notification "**after** leaving the corresponding text
box"...

HTH

 

[Guest]

I believe Before Update would be the appropriate choice in that the number
would be checked before anything else happens. Before Update would run
immediately upon exiting the text box, and would serve in this case to
validate the data. After Update would be for anything that needs to happen
after the data is validated. For instance, if the number checks out as being
original, the next step (After Update) may be to display a message box or
open another form.

 

[Guest]

I believe Before Update would be the appropriate choice in that the number
would be checked before anything else happens. Before Update would run
immediately upon exiting the text box, and would serve in this case to
validate the data. After Update would be for anything that needs to happen
after the data is validated. For instance, if the number checks out as being
original, the next step (After Update) may be to display a message box or
open another form.

After re-reading Help for both events, I see where maybe I should have been
using the BeforeUpdate instead of AfterUpdate event. The AfterUpdate did what
I wanted, so I didn't try the control BeforeUpdate event (but I have used the
form BeforeUpdate).

Lauren, listen to Bruce.....

"I am learning, therefore I am not dead....just sometimes brain dead" <g>