Insert Into statement is adding to table, but data not displayed ...

[grtammi]

Here's one for the archives ...

In a nutshell, I am attempting to execute the following SQL statement:

INSERT INTO tblHSE (id, salesperson, trans_date, product, RKNumber, ATSComm, ATSPaid, SalesComm, SalesPaid, phone_num, ATSStatus) VALUES ('9','1','10/10/2003','3','','20','','4','','7057894563','')

via the RunSQL method of the DoCmd object. Looks good right? How about this: I check the table after my success message comes back (no errors generated at all), and the data I just added isn't there. But here's an even bigger kicker: my auto-number field increments 2, not 1 like it should.

i.e. I have no records in this table, and no other users in the database at all. I've compacted the back-end (using a fe/be application, btw) so that the auto-number resets to 1 ... all with me so far? I manually add a new record to the table ... auto-number field is 1, like it should be. I then manually add another field to the table, and auto-number increments to 2, again like it should. I then close the table, open the form, enter the data, click save, get my "Info Successfully Added!" happy message, close the form, open the table, and find no data.

BUT ... I manually add another record to the table ... and the auto-number field is 4 ... not 3 like it should be.

'Tis a funny problem, and I'm REALLY stumped right now ... especially considering that ALL of my other SQL statements execute correctly via the RunSQL method, except this one.

Any ideas? (sorry for the long winded explanation ... but I think I got everything there that was needed).



Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[vcurry]

I was trying to do something similar, the query I was trying to run being:
INSERT INTO xref_Article_Referee ( Article_ID, Referee_ID, [Date due from referee], [Date back from referee], [Date Rev to Referee], [Date Rev from Referee] )
SELECT Manuscripts.Article_ID, Manuscripts.Referee_ID, Manuscripts.[Date due from referee], Manuscripts.[Date back from referee], Manuscripts.[Date Rev to Referee], Manuscripts.[Date Rev from Referee]
FROM Manuscripts;

I set this up in the query builder, and clicked on Data Sheet view. The records I that were to be inserted were displayed, but the table I was trying to insert into wasn't updated.

After trying various things for sometime, I eventually just double clicked the query icon in the list of queries in the database. I recieved a warning message that n records were about to be updated. Clicked OK, the query ran, and the records destination table was populated as expected.

Hope this is of some help.

 

[grtammi]

Ok ... never mind. I had a corrupted data table - rebuilt the table (there was no data anyways) and linked it into the db - all works fine.

Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz