Blank Record Being Added to Table

[Andrew]

I have a form where the user enters their data and clicks on the save button. This runs some code that uses the INSERT INTO command to store the information as a record in a table. I then pop up a message box (default type) to let the user know that the record was inserted correctly.



This all works fine as long as the user clicks the ok button on the message box. If they click the "X" to close the box, it inserts the data but also inserts a blank record before it. The section of code that performs this operation is:

    Sql = "INSERT INTO Cooperative (PDate,EDate,COProgramID,Comment,DistrictID,BlockID,VillageID,ColonyID,TargetNo,ActualNo,Female,Male,Family,HowOftenID) VALUES (#" & PDate & "#,#" & EDate & "#," & CoProgram & ",""" & Comment & """," & District & "," & Block & "," & Village & "," & Colony & "," & Target & "," & Actual & "," & Female & "," & Male & "," & Family & "," & HowOften & ")"
    CurrentDb.Execute (Sql)
    MsgBox("Record Added Successfully!")

It doesn't seem as though I can disable the "X" on the message box and was wondering if there is another way to fix the problem.

Thanks

[Rainlover]

Your code, while it is not the best, should do as you ask.

There must be something else happening to cause the blank record.

Suggestion. Use a Bound Form and you will overcome all this sort of problem and eliminate a bunch of redundant code.

[Andrew]

Unfortunately I think I need to use an unbound form as I want to do some integrity checking before the entered data is saved. Is there a better way of coding the addition of a record to a table?

[Rainlover]

Unfortunately I think I need to use an unbound form as I want to do some integrity checking before the entered data is saved.

You think or are you sure.

If you are sure what do you need to check.

Bound forms still work for me.

Is there a better way of coding the addition of a record to a table?

There is always a better way.

I am not understanding your reasoning for using unbound Forms.

[hertfordkc]

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

I have the ROLLBACK method in an AFTERUPDATE procedure to undo the record addition when the fields didn't satisfy integrity checks. With a bound form.

[Andrew]

Before allowing them to add a new record I need to make sure that they have entered values in 8 fields and that at least one of the remaining fields has had a value entered. I didn't use a bound form as I couldn't find a simple way to check that all of the required fields were entered and then abort the entry if it was not valid. The unbound form allowed me to easily do this with a save button and a bit of simple vba code.

Thanks for the rollback code. With that I should be able to get a bound form working. I'll let you know how it goes.

[Rainlover]

Andrew

The same code use use on the UnBound Form can be used on a Bound Form.

Just put it in the Before Update Event of the Form

[Andrew]

It is all working nicely as a bound form now and no longer getting blank entries.

Thanks for the help Rainlover and hertfordkc.

[Rainlover]

Good luck with the project