problem with updating tables

[ray147]

i am having a problem with creation and updating of table rows...i am using VBA code for INSERT and UPDATE SQL statements on a table. However, whenever a new record is added to a table, it is inserted at the end (last record of the table), which is correct. however, the first record in the table is always being updated with the data of the last record added.

this is also happening when i'm amending a record through the UPDATE query...apart from updating the data of the respective record, the first record in the table also updates with the same data....

i tried running the Compact and Repair Database option from tools menu coz maybe i thought the database is corrupted, but it made no effect...also i tried deleting all records in table and starting with a clean table...still same happens again...

any help appreciated

 

[Mile-O]

Can you give an example of your SQL statements?

Unless you have specific criteria the UPDATE changes will apply to all relevant records.

And, regarding the INSERT, tables are unordered recordsets. Wherever a record is inserted is irrelevant; it just goes into the table. If you want to have an ordered recordset then you will need a query.

 

[ray147]

hi, thanks for yr answer...
I have the following SQL statements :


INSERT SQL

      DoCmd.RunSQL "INSERT INTO Bookings " & _
        "([BookingRef],[BookingType],[ParcelRef],[DateCreated],[DateLastUpdate],[SupplierRef],[PlanDespDate],[PlanDespTime],[Qty],[Shift],[TransMode],[Comments],[Status]) " & _
        " VALUES (" & recNo & ",BookingType,ParcelRef,DateCreated,DateLastUpdate,SupplierRef,PlanDespDate,PlanDespTime,Qty,Shift,TransMode,Comments,Status);"

UPDATE SQL

       DoCmd.RunSQL _
        "UPDATE Bookings" & _
        " SET [DateLastUpdate] = #" & DateLastUpdate & "#" & _
        ", [PlanDespDate] = " & date1 & _
        ", [PlanDespTime] = " & time1 & _
        ", [Qty] = " & qty1 & _
        ", [Shift] = " & shift1 & _
        ", [TransMode] = " & trans1 & _
        ", [Comments] = " & Comments1 & _
        ", [Status] = " & Status & _
        " WHERE [BookingRef] = " & BookingRefx.Value & ";"

Looking forward to yr feedback...

 

[SammyJ]

I've never seen an INSERT query alter any other record, in fact I'd bet it is impossible.

Do you have an autonumber primary key? Are you sure you're looking at the new record?

Where do you think your VALUES clause is getting the rest of its data from, by the way? It is expecting constants.

As for your UPDATE query, it will definitely affect all records with the particular BookingRef you're passing in. And only those.

If you're saying that this isn;t happening then something very spooky is going on, but I'm guessing that there is just confusion somewhere else.

Sam.

 

[ray147]

Sam, thanks for yr answer...in fact i do thing something spooky is going on!

In the INSERT case, the new record is always inserted at the bottom, the last record. However, the first record in the table is also affected..the first record in the table keeps changing with every insert or update operation..can't understand y and how..

as regard the values of the INSERT query, most of them are coming from text boxes..which are bound controls...actually in this case..the first record in the table is just affected in the sense that the DateLastUpdate field is updated with the now() date/time but the fields are left empty...

in the UPDATE query, values are coming from unbound textbox controls...in this case, the first record in the table will also be updated with all the new data, not just the LastUpdate field..

plsss help!!!

 

[ray147]

no more ideas, guys?!

 

[ray147]

I've tried something else to double-check this problem...

I've created a new database with only one table containing three fields "ID" (autonumber field), "name" and "tel". also i created one form with two BOUND text boxes (bound to the two fields respectively). on the form i also put one command button to add records to the table...the code I attached to this button is as follows:

Private Sub Command4_Click()
DoCmd.RunSQL "INSERT INTO Table1 " & _
"([name],[tel]) " & _
" VALUES (Xname,Xtel);"
End Sub


The table was empty containing no records at all...I opened the form, completed the form and clicked on the command button. I checked the data appended to the table and found this...

id name tel

1 pat 123456
2 pat 123456


I did the same thing the second time and inserted another record through the form...and the data in the table was as follows:

id name tel

1 ray 992200
2 pat 123456
3 ray 992200

I repeated this for a number of times and what's happening is that the new record is always inserted correctly at the end of the table....however, the first record in the table is also being update for some reason...

can anyone give me some advice on this? i really can't understand why this is happening..all i've got in the form VBA code is just the INSERT query shown...can't understand how an update on the first record is being done all the time..

tnx!

 

[Bat17]

Can you Zip and post the new DB so others can have a play please.

Peter

 

[Bat17]

A light dawns the records are being updated because they are bound to the form.

Peter

 

[ray147]

Testing database is attached in ZIP format..
the DB contains just one table and one form..

Any help appreciated!

 

[ray147]

Thanx Bat, that solved the problem!! I've been worrying abt this for quite some time but now it was time to deal with it...

Tnx again