Append Query via code: Data getting lost

[michael.p.logue.42]

OK. This is a really weird problem that I've never encountered
before, and can't seem to figure out what's happening. Here's the
scenario.

Two tables (one front end, one backend) with exactly the same data
fields. Frontend table is temporary, and at a certain stage this data
is appended to the backend duplicate. Pretty straight forward.
However, here's the problem.

I'm using a straightforward SQL statement in code:

Dim txtSQL
txtSQL = "INSERT INTO tblLeads SELECT tblLeadsTmp.* FROM tblLeadsTmp
DoCmd.RunSQL txtSQL

Every thing worked fine, until I noticed that one of my date fields
wasn't carrying over. Double checked everything, and found nothing
wrong with the tables. Tried re-naming the offending field in both
tables, but still no joy.

So then I created a standard Append query, and ran that from code
instead:

DoCmd.OpenQuery "qyrUpdLeads"

Same problem - the same date field was not carrying over.

However, when I run the Append query manually, the date field is
appended to the backend table as it should.

I've tried running the sql statement and saved query by listing every
field (no wildcard), and still the same problem. It'll transfer the
field correctly if I do it manually, but gets dropped when run by
code.

I've also done a Compact/Repair on both databases with no change in
the error.

Any Idea what's going on here? I'm at a complete loss.....

 

[michael.p.logue.42]

Found out what the problem was. Normally, this update procedure was
only doing a single record at a time, then flushing the data in the
temp table - in readyness for the next new record. However, I tested
the append function with multiple records - just to see what
happened. It turned out that it was only the last (active) record
that was dropping fields (I found out that more than the one date
field was not appending correctly).

It then struck me that I still had that particular record active in my
form while I was running the append function. So, even though I used
a DoCmd.Save before the append, it was not carrying the data over.
So,before it runs, I just reset the form's recordset to nothing via
code, and everything transferred perfectly.

Problem solved!

 

[John W. Vinson]

It then struck me that I still had that particular record active in my
form while I was running the append function. So, even though I used
a DoCmd.Save before the append, it was not carrying the data over.

DoCmd.Save does NOT save the current record: it saves design changes to the
Form object!

To save the record, use either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False