Code to clear entry in form

[Rotsjk]

Hi,



i've build a form/subform with a master/detail data construction. In the subform is an entry of unique numbers. In the vba code i check if the unique code allready exists in the detail table. If not then the entry is correct and additionally i update another table where all possible numbers are registered and i update the status there. So far so good.
If the entry is a number that is allready used, i want to show a msgbox and then remove that entry before i get the standard message that the entry is in violation with the uniqueness.

I can't figure out how to do that. The message is no problem, but how to remove that last entry?

Thanks for help!

[Welshgasman]

Use the .Undo method of the control?

[moke123]

You can use the before update event of the control to test your value and use the cancel argument if needed and undo the controls value.

What is that number for? I would think the additional table wouldn't be necessary.

[Rotsjk]

Use the .Undo method of the control?

I tried that, and a few other things, but i always get an Run-time Error '-2147352567 (800200009)

[Rotsjk]

This program is build for paper giftcertificates. I have one table where all the numbers wich exists contains. There is the status of the number on the certificate. So we know wich numbers have been returned, and wich are still outstanding. I use that table to check if one certificate is used twice. The tables i use to register the incoming certificated is called invoice_head and invoice_detail. What i need to do is to remove the entry on the invoice_detail when te status in the extra table (called certificate_status) is allready returned.
But i geta Run-time Error '-2147352567 (800200009) ​when i try to remove the last entry

[Bob Fitz]

IMHO, validation is best done in the Before Update event of a form or in the Before Update event of its controls.

[Rotsjk]

Indeed, the validation is in the before update of the control.

[moke123]

It would help to see your code. Those long error numbers, I believe, indicate it is a control error.
A sample database would be even better.

[Rotsjk]

This is the code: The entry is in the field nummer. When i try this code i get the error on the line me.nummer.undo




Private Sub Nummer_BeforeUpdate(Cancel As Integer)
Dim chkBestaan, chkBestaan1, strSQL

chkBestaan = DLookup("nummer", "bonnen_status", "nummer=" & Me.Nummer & " and soort=" & Me.Soort)
If chkBestaan > 0 Then


chkBestaan1 = DLookup("nummer", "bonnen_status", "nummer=" & Me.Nummer & " and soort=" & Me.Soort & " and vastgesteld='N'")
If chkBestaan1 > 0 Then

strSQL = "INSERT INTO Bonnen_status_TMP (nummer, soort, datum_in_omloop, datum_retour, status, eigenaar, nieuw) select nummer, soort, datum_in_omloop, datum_retour, status, eigenaar, 'N' FROM bonnen_status WHERE nummer=" & Me.Nummer & " AND soort = " & Me.Soort & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = "UPDATE Bonnen_status SET status='R', vastgesteld='J', datum_retour='" & Forms![invoice_head]!Datum & "' WHERE nummer=" & Me.Nummer & " AND soort = " & Me.Soort & ";"


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Else
MsgBox ("Deze bon heeft al een definitieve status. Strookje opvragen!")

Me.Nummer.Undo




End If
Else


MsgBox ("Onbekend bonnummer. Controleren!")






End If
End Sub

[madpiet]

So check for the existence of the certificate ID in the table before doing anything. if it exists, return a message and reject the insert.

[moke123]

For the benefit of others, here's your code with indents and code tags. Makes for easier reading.

Private Sub Nummer_BeforeUpdate(Cancel As Integer)
    Dim chkBestaan, chkBestaan1, strSQL


    chkBestaan = DLookup("nummer", "bonnen_status", "nummer=" & Me.Nummer & " and soort=" & Me.Soort)
    If chkBestaan > 0 Then


        chkBestaan1 = DLookup("nummer", "bonnen_status", "nummer=" & Me.Nummer & " and soort=" & Me.Soort & " and vastgesteld='N'")
        If chkBestaan1 > 0 Then


            strSQL = "INSERT INTO Bonnen_status_TMP (nummer, soort, datum_in_omloop, datum_retour, status, eigenaar, nieuw) select nummer, soort, datum_in_omloop, datum_retour, status, eigenaar, 'N' FROM bonnen_status WHERE nummer=" & Me.Nummer & " AND soort = " & Me.Soort & ";"


            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True


            strSQL = "UPDATE Bonnen_status SET status='R', vastgesteld='J', datum_retour='" & Forms![invoice_head]!Datum & "' WHERE nummer=" & Me.Nummer & " AND soort = " & Me.Soort & ";"


            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
        Else
            MsgBox ("Deze bon heeft al een definitieve status. Strookje opvragen!")


            Me.Nummer.Undo


        End If
    Else


        MsgBox ("Onbekend bonnummer. Controleren!")


    End If
    
End Sub

Without looking too closely at your code, note that you don't set cancel = true.

I would also use the db.Execute method with dbFailOnError that will roll back changes if there's an error.

Dim chkBestaan, chkBestaan1, strSQL

These are all being dimensioned as variants. I would use Dcount rather than Dlookup and dim them as integers and strSql as string.

[Rotsjk]

Hi moke123,

i cannot see the code you've posted.
Thank you!

[jojowhite]

i would not recommend doing anything in a Validation event.
use the event for it's sole purpose, that is to Validate.

if you need to Insert/Update, validate it first then
invoke the Timer Event and do the Inserting/Updating
on that event.

[jojowhite]

i would not recommend doing anything in a Validation event.
use the event for it's sole purpose, that is to Validate.

if you need to Insert/Update, validate it first then
invoke the Timer Event and do the Inserting/Updating
on that event.

also, are you trying to Update (using SQL) the same record
that is in the subform? if so that is a no, no (the db engine
will give you warning about conflicting records).

[Welshgasman]

You can edit your posts you know?