Stop Duplicate value in number field on form ?

[fibayne]

Hi

I have tried using this in the before update event of ChqRecNo on a form, it is getting stuck at [Forms]![frm_DataForm_ChqRec]![ChqRecNo] = "" giving the error message

Runtime error 2115
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field

Private Sub ChqRecNo_BeforeUpdate(Cancel As Integer)
If DCount("ChqRecNo", "tbl_DataStore", "ChqRecNo = " & [Forms]![frm_DataForm_ChqRec]![ChqRecNo]) Then
MsgBox "That Cheque Rec Number already exists"
[Forms]![frm_DataForm_ChqRec]![ChqRecNo] = ""
[Forms]![frm_DataForm_ChqRec]![ChqRecNo].SetFocus
End If

End Sub

Woudl anyone be able to help work out the problem or suggest a better way to do this...as always many thanks in advance...cheers Fi

 

[KenHigg]

Is ChqRecNo a string or numeric data type?

 

[fibayne]

Hi ken ChqRecNo is set as a number in the table...cheers Fi

 

[KenHigg]

What is the exact text you have in the BeforeUpdate or ValidationRule properties for the ChqRecNo text box?

 

[fibayne]

Hi Ken

Exact test in the BeforeUpdate Event of the ChqRecNo Test box on the form is....

Private Sub ChqRecNo_BeforeUpdate(Cancel As Integer)
If DCount("ChqRecNo", "tbl_DataStore", "ChqRecNo = " & [Forms]![frm_DataForm_ChqRec]![ChqRecNo]) Then
MsgBox "That Cheque Rec Number already exists"
[Forms]![frm_DataForm_ChqRec]![ChqRecNo] = ""
[Forms]![frm_DataForm_ChqRec]![ChqRecNo].SetFocus
End If

End Sub

cheersFi

 

[KenHigg]

So you should have the following in the properties window for the ChqRecNo BeforeUpdate event:

[Event Procedure]

Then do you have anything in the ValidationRule property?

 

[Dennisk]

you have forgotton the cancel the event

Cancel = true

 

[fibayne]

Hi ken ...I dont have anything in the ValidationRule property ! should I ?? sorry not great at this ..cheers Fi

 

[KenHigg]

What about what Dennisk posted - ?

 

[fibayne]

Hi Dennisk...I have tried adding Cancel=true to the statement possible not correctly but am gettign the same error message in the same place ? cheers Fi

 

[fibayne]

Hi,,,my code now looks like this

Private Sub ChqRecNo_BeforeUpdate(Cancel As Integer)
If DCount("ChqRecNo", "tbl_DataStore", "ChqRecNo = " & [Forms]![frm_DataForm_ChqRec]![ChqRecNo]) Then
MsgBox "That Cheque Rec Number already exists"
[Forms]![frm_DataForm_ChqRec]![ChqRecNo] = ""
[Forms]![frm_DataForm_ChqRec]![ChqRecNo].SetFocus
Cancel = True

End If

End Sub

cheers Fi

 

[fibayne]

Hi Ken & Dennisk....just tried this and it seems to work....what do you think ??

thanks again Fi

Private Sub ChqRecNo_BeforeUpdate(Cancel As Integer)
If DCount("ChqRecNo", "tbl_DataStore", "ChqRecNo = " & [Forms]![frm_DataForm_ChqRec]![ChqRecNo]) Then
MsgBox "That Cheque Rec Number already exists"
Cancel = True
Me.Undo

End If
End Sub

 

[KenHigg]

I think Dennisk fixed it -

Except for one thing - I've never seen a dcount() used like that before. But if it works the cool -

 

[fibayne]

Hi ...what it was doing though was, if the number was a duplicate all other fields that had been entered prior to the ChqRecNo were being undone by I assume the me.undo statement...I added me.refresh to the OnLost Focus event of the fields that are populated before this one ...which seems to work...thanks to you both..cheers Fi

 

[gemma-the-husky]

i think the problem was caused by the error in the before update event, and trying to recover from that by setting the value to "", which will surely not be valid for a numeric field

setting cancel = true (incidentally cancel = vbcancel is better - the value may not be true) prevents the save, and undo cancels the entire record edit, i think

to cancel the value entered try instead

sendkeys "{esc}", which will merely issue an escape char, and undo the last entry the chq number only