Copy and Paste Append not appending every field

[branston]

Hi.
I have some VBA that has been working for a long time, but has suddenly decided to stop!
The following steps are run via vba:
- open a table
- filter for a specific record
- copy that record
- Paste append record into a temporary table (and close original table)
- run a query which pastes the records in the temporary table back into the original table with one of the primary key fields changed. (Rev is changed to Rev + 1)

This was working fine, but for some reason, now it misses off information from some of the fields (i.e. Title is blank). I can't see any patern, and when i carry out the steps manually (rather than through the vba) it works fine.

I thought it might be that the records were too big to paste in time before the query is run, so I added a pause to the code after the paste append but that seemed to make it worse.

To add to the quandry... it only happens to some records! I carried out this 'up-rev' procedure to a test record we have and it worked ok, but when I later tried to do it to a real record (which happened to be larger - could that cause it?) it didnt work.

Any suggestions would be greatly appreciated!

Thank you

 

[ghudson]

Are you using an apend query to copy the record to your temp table? If not, I suggest you try that. Set your append query criteria to find the record you want.

I trust that you are not really opening a table, filtering records, selecting and copy the record, pasting the record into the temp table and then closing a table.

 

[SOS]

Changing primary key field? That sounds suspicious to me. Do you mean APPEND a record with the new primary key number (which is incremented from the previous value)?

 

[pbaldy]

For starters, that seems like an unnecessarily complex method. Wouldn't this work?

INSERT INTO TableName(Field1, Field2, Field3)
SELECT Field1, Field2, (Field3 + 1)
FROM TableName
WHERE KeyField = Whatever

 

[pbaldy]

Geez, talk about your slow typists!

 

[SOS]

Geez, talk about your slow typists!

Aren't you supposed to be at the MVP Summit this week?

 

[pbaldy]

I was, until employer decided I should go to Vegas tomorrow.

 

[SOS]

What's happening in Vegas? (well, besides the usual stuff )

 

[pbaldy]

Nobody outside Vegas knows, because what happens in Vegas stays in Vegas!

We have an office there, and I have to meet with a vendor. The really annoying thing is that the vendor may put it off until next week now, which will really peeve me.

 

[SOS]

Bummer - people can be such a pain. Sometimes I think the world would be awesome if it weren't for all the people.

Well, good luck with everything and sorry you didn't get to go to Seattle. Hopefully "next year" you will be able to go. I've always wondered about what goes on there.

 

[pbaldy]

I've been to the previous two Summits. They are part geek-fest and part social get-together, but the meat of them is two days with your product group (Access in my case). Two years ago they talked to us about the new features they were planning for v2010, and we even played with an early version of it. Last year we played with a much more refined version that incorporated some of the things we had talked about the year before. MS uses the Summit as a way of getting feedback about the direction they're going in, features they should add/drop, etc. For all the criticism many give them, including myself sometimes, the Access folks are a group of people trying very hard to provide us with a good product.

 

[c_smithwick]

Try this instead of your temporary table:

Public Sub addRevision(strFilter As String) 'strFilter will be your SQL to filter out your record
Dim rstOriginal As DAO.Recordset
Dim rstFilter As DAO.Recordset
Set rstOriginal = CurrentDb.OpenRecordset("OriginalTableName", dbOpenDynaset)
Set rstFilter = CurrentDb.OpenRecordset(strFilter)
With rstFilter
    If Not .EOF Then
        rstOriginal.AddNew
        rstOriginal![Field1] = ![Field1]
        'repeat his for each field to be copied
        rstOriginal![Rev] = ![Rev] + 1
        rstOriginal.Update
    End If
End With
Set rstFilter = Nothing
Set rstOriginal = Nothing
    
End Sub

 

[branston]

ghudson - I'm afraid I am doing that. I first set up this code a long time ago, when I was very new to access, so it was very much a 'what can I do... ooh, that could work' rather than a 'what's the most efficient way of doing this'.
Thanks for all your suggestions everyone, I'll do a bit of changing.
Just out of curiousity though, deos anyone know WHY this happened?