record saving twice in table

[ds_8805]

Hi Everyone! i have a form where the user will have to enter details relating to a site and click on save record in order for the details to be saved into the table. However I am facing a problem.

When I click save record after entering the details, the record gets saved onto the table twice. Then when I go on to enter a new site and its details, one of the entry of the previous one i added( which has double entry) gets deleted and the current new one gets 2 entries in the table.
Could someone pls tell me what I might be doing wrong.



thanks

[Datagopherdan]

My first question is, why do you have a Save button? Do you have it setup where if the user does not click that button, the entered data will be undone or a delete query will be ran to remove the user's entries? Access is unlike Word and Excel where you would need to save the changes. It does this automatically.

Dan
Access Development

Hi Everyone! i have a form where the user will have to enter details relating to a site and click on save record in order for the details to be saved into the table. However I am facing a problem.

When I click save record after entering the details, the record gets saved onto the table twice. Then when I go on to enter a new site and its details, one of the entry of the previous one i added( which has double entry) gets deleted and the current new one gets 2 entries in the table.
Could someone pls tell me what I might be doing wrong.

thanks

[ds_8805]

hey thanks for ur reply... the main purpose of my save button right is to give the assurance to the user that the record is saved and it would give a msg saying that the site is saved.. I have done similar thing in other forms but I have not encountered such a problem before!

thanks

[TheShabz]

well, whatever the purpose behind the save button is, that's what's causing your issue. it is being saved when the textboxes are filled, then again when the save button is being saved. If you want to only have it save when the button is pressed. have unbound textboxes and use an INSERT INTO sql statement to populate your table when the Save button is pressed.

[ds_8805]

hey thanks for ur reply.. I m not fully getting wat u mean.. can u explain more on how to go about doing this? thank u

[TheShabz]

ok lets assume you have 2 fields (this can be expanded obviously) that you need to create, ID and Name. you will create 2 txtboxes, txtID and txtName, and a button, cmdSave.

you will then choose "even procedure" from the button's on click event in the properties box. This will open up a VBA editor and an empty sub for you to fill in. your code will be:

dim strSQL as String

strSQL = "INSERT INTO myTable (columnID, columnName) VALUES (" &  Forms!myFrm!txtID & "," & Forms!myFrm!txtName & ");"

docmd.RunSQL (strSQL)

adjust myTable, myFrm, etc to make it work with what you have.

[ds_8805]

Thank u! I will try this way u suggested!

[ds_8805]

Hello again! I tried implementing the way u said. However I keep getting error. I have pasted the error msg here:
The expression On Click you entered as the event property setting produced the following error:
Argument not optional

This is my code
Private Sub Command4_Click()
Dim strSQL As String
strSQL = "INSERT INTO Capex Site Info (SiteID, SiteName) VALUES (" & Forms![testing33322222221]![SiteID] & "," & Forms![testing33322222221]![SiteName] & ");"
DoCmd.RunSQL
End Sub


am i doing smthg wrong?

[TheShabz]

docmd.runSQL (strSQL)

also change the name of your table to one word. dont have a multiple word table name. just bad practice. wrap the table name in brackets for now.

[ds_8805]

sorry I had just realised that mistake and edited it.. now the error it gives is that there is some problem with the insert into syntax! However when I went to check the syntax online, it seems to be correct only! any idea? sorry for the trouble!

[TheShabz]

try commenting out the docmd.runsql by putting a ' infront of it. then after the strSQL = line, enter debug.print strSQL and then in the window at the bottom it will display what is stored in strSQL. make sure it looks correct there. outside of that, i dont know what to tell you.

[ds_8805]

hey u noe i tried but nothing in displayed! there is no window

[TheShabz]

oh, look for the option for the "immediate window" i dont have access here at home and dont remember off the top of my head how to get it.

[ds_8805]

hey for only the date we have to use blah blah date"#" instead of "," right?

[Datagopherdan]

Shabz's is right and that will work but an alternative to Docmd.RunSQL and generally the better way to go is to use CurrentDB.Execute. This does not rely on the Docmd.Setwarnings command as RunSQL does.

Also, you have to careful doing this and generally theres not really a need for it. You can give them a confirmation message sayng that the record was committed but you should not be running a query like that to add it. You're opening yourself for alot of possible problems, such as users accidentally clicking that button when the record has already been added and then you have duplicates.

Really, the only reason that I can think of to do something like this is if you wanted some type of quality control where the record would be validated by management before being committed but even with that, you would not need to run an append query. You could just have a check box that management would check after they validated the record and some code, so it would be displayed when that value =-1.

Dan
Access Development

hey for only the date we have to use blah blah date"#" instead of "," right?