Message box question

[bone head]

Hi

I am looking to create a pop up message box to warn that a field has been duplicated.

I.e. I do not want to set the field as “no duplicates” as I want to allow duplicates, however wish to warn the user that they may be creating a duplicate record and to check existing records first

Ideally I then want to exit the form from data entry mode without saving data if the user chooses to.

Is this possible please

Many thanks

 

[smed]

have a look at this :

Private Sub Textbox0_BeforeUpdate(Cancel As Integer)
Dim counter As Integer
counter = (DCount("*", "TableName", "FieldName=Textbox0"))
If counter > 0 Then
MsgBox "Duplicate Value"
Me.Undo
End If
End Sub

HTH

Smed

 

[bone head]

Thanks smed however this does not seem to work unless i have cocked it up


Private Sub Textbox0_BeforeUpdate(Cancel As Integer)
Dim counter As Integer
counter = (DCount("*", "customer_contact", "invoice_No=Textbox0"))
If counter > 0 Then
MsgBox "Duplicate Value"
Me.Undo
End If
End Sub


End Sub
( i assume that i remove the Private sub text which is automaticly raised when you start build event)
Customer contact being the table name, invoice being the feild name ( text feild )

nothing occurs when the feild is entered, it may do when i finish entering all the data on the foprm, however this is what i wish to avoid, ie the Invoice No is one of the first feilds.

what i am trying to do is that it is possible although not normal that more than one entry could be made against an invoice No, so i need to flag up a warning for the user if this occurs, one which they can either over ride or cancel the current input.

does this help clarify

 

[smed]

Have a look at the attached db as an example.

Smed

 

[bone head]

Hi I am really living up to my nick on this one

Down loaded the Zip and yes it works fine just what I am looking for, however when I put the code into my form it just doesn’t work.

I get no error messages or anything it just carries on to the next field as if the code was not even there.

It is no doubt something blindingly obvious however I have spent hours looking at it and can not see what I am doing wrong.

Would some kind soul kindly take a look at it if I e-mailed it in Zip format the file site is 587k

Many thanks in anticipation

 

[ColinEssex]

On the example of the code you posted earlier, you have 2
"End Subs"

Does that help you?

Col

 

[bone head]

Sorry for delay been busy for a few days.

No it does not i had noticed that previously.

I just can not figure it out at all I have tried all sorts, as i said it is probably sonething blindingly obvious, maybe it will come to me eventually.

 

[Pat Hartman]

The sample that smed posted was passing the string value"textbox0" as the lookup value rather than the contents of textbox0.

counter = DCount("*", "customer_contact", "invoice_No = " & Me.Textbox0)

Textbox0 needs to be the name of the control that contains invoice number. In this example, invoice number is assumed to be numeric. If it is not, you'll need to embed single quotes around the string value.

counter = DCount("*", "customer_contact", "invoice_No = '" & Me.Textbox0 & "'")