Not Inserting new records instead it updates the last record in Ms access vba

Question

I have a form made in ms access and vba. I have this code in an insert with the default given upon creating the button DoCmd.GoToRecord , , acNewRec My form is bound to a table and linked tables to a SQL database. It successfully added if the table has no records yet but if there is already a record in the table and the form was closed and opened again and add new data again it will no longer insert new rows of record instead it just updates the last record.

After the form is called I click the new button which clears all the textbox value and then input a new record and click add, what happened was, it will just update the last record. and I have an auto increment id. Anyone experiencing this one?

the code on my add button is this

Private Sub btnAdd_Click()
On Error GoTo Err_CustomerNew_Click


    DoCmd.GoToRecord , , acNewRec

Exit_CustomerNew_Click:
    Exit Sub

Err_CustomerNew_Click:
    MsgBox Err.Description
    Resume Exit_CustomerNew_Click

End Sub

no script is involve only the form is bound to a table linked to sql datbase. and also the recordsource of the form is I'm using the query not table directly. like Select * from tablename order by id desc.

anyone have experienced on this one? – Androidz Jun 05 '15 at 14:47 — Androidz, Jun 05 '15 at 14:47
Any work around with this one? – Androidz Jun 06 '15 at 03:32 — Androidz, Jun 06 '15 at 03:32
Is this a bug on ms access? – Androidz Jun 06 '15 at 15:31 — Androidz, Jun 06 '15 at 15:31

score 0 · Answer 1 · answered Jun 05 '15 at 03:37

0

If this is a script you are trying to write, post the code and I will try to help you. If the script was originally in MSAccess, the namespace may be slightly different. For example, if the code for the data does not look like a postback item from the form to SQL (for whatever reason), or the action from the field is somewhere besides the button click event, it will not post your data to the database and can actually remove your entry from the form field depending upon how your script is written. Hope that helps, ClaireW

answered Jun 05 '15 at 03:37

ClaireW

24

I updated my question please check. – Androidz Jun 05 '15 at 04:54
I don't know what do you mean. – Androidz Jun 06 '15 at 03:09

score 0 · Answer 2 · answered Jun 07 '15 at 17:03

Hi I found out a solution and I don't know if this an appropriate answer since I don't have any experienced in vba. this will do the trick in inserting new records with bound forms to a linked table of SQL and have an autoincrement value of ID's. When the form is Opened with existing records already the ID that was captured by the form is the first ID or the latest ID on the table. this will updates the record whenever you click the add button using on this command DoCmd.GoToRecord , , acNewRec It depends if your using ascending or descending on your query which of those it will update the record and not insert. So, In order to successfully insert it without updating existing record, the form must create a new set of ID to be inserted. all you have to do is to have a on_load event and on that event add this code DoCmd.GoToRecord , , acNewRec so that when it loads a new set of record the form gets and insert it..Hope it helps.

Not Inserting new records instead it updates the last record in Ms access vba

2 Answers2