removing prompt msg from access form

[donnie051280]

when saving information from the form to a table i sometimes get a prompt msg which states. "mircosoft access set 0 fields to null die to fail conversion failure...1 fields to to key violation etc etc". I understand what this means, as the primary key information has been entered twice into the table.. I just a error msg saying "error..please select new data". i dont want the long prompt

 

[BobStubbs]

Try this code in the 'Before Update' event of your form. In my example:

My table name = tblTesting
My key field name = MyKey

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim iResult As Integer

'---------------------------------------------------
'- Count the number of existing records with the   -
'- Key just typed into the form                    -
'---------------------------------------------------
iResult = DCount("[MyKey]", "tblTesting", "[MyKey]=" & Me.MyKey)

'---------------------------------------------------
'- If the result is greater than zero, display an  -
'- error message, cancel the Save process, move    -
'- cursor to the key field, and clear it.          -
'---------------------------------------------------
If iResult > 0 Then
    MsgBox "My error message"
    DoCmd.CancelEvent
    Me.MyKey.SetFocus
    Me.MyKey = ""
End If
    
End Sub

This checks for an existing record with the same primary key; if one is found it will display an error message and cancel the Save process, so you don't get the long Access message about creating duplicate values etc.


Bob Stubbs

 

[donnie051280]

sorry
im a little confused..
when i click a button i add all the information in the txtfields of that form(subform3) into the table(stock)
but if sometimes clicks the button twice the error messages occurs.. This is because the primary key has been entered twice.. the txtfield(pallets_ref) in the subform is the field that cant be entered twice..
so how would i change the VB if this is the situation

hope it's not confusing
thanks
don

 

[mustangcoupe]

are they only entering data to the table once?

if that is correct I personaly would change the enable property of the control to false on click.

and reset it to true when I need it agian say on form current if control X is null


--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[MD5150]

>when i click a button i add all the information in the >txtfields of that form into the table...

If you're adding a record via code from a button click, then you might want to just intercept the dup error in your error handling code(3022) and display a short message like:

MsgBox "Duplicate Entry...", vbInformation, "OOPS!"

Also if you clear your control values after every saved record, then when you accidentally click the button twice, use some initial validation code that checks for Nulls on required fields. Or you can probably intercept the 'required field' error message the same way.

Mike Dorthick