Copying a record in VBA 2 (the new question)

Question

I have this code that I am trying to copy a record with in VBA. Unfortunately I cannot get it to work. I do not knw why it won't do anything.

Dim OldRecord As DAO.Recordsets, NewRecord As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [Tote Log].* FROM [Tote Log] WHERE Number = " & _
            Me.tbScannerRead.Value

Set OldRecord = CurrentDb.OpenRecordset(strSQL)

If OldRecord.Count = 1 Then
    Set NewRecord = _
                CurrentDb.OpenRecordset("SELECT [Tote Log].* FROM [Tote Log]")
    With NewRecord
        .AddNew
        For i = 0 To .Fields.Count - 1
            .Fields(i).Value = OldRecord.Fields(i).Value
        Next i
        .Fields("Number").Value = Me.tbScannerRead & "B2"
        .Update
        varBookMark = .Bookmark
    End With
    NewRecord = varBookMark
    DoCmd.RunCommand acCmdSelectRecord
    Me.tbMessageBox = "Added new record"
    Me.tbMessageBox.Visible = True
    GoodToteRead = False
    Me.tbScannerRead.SetFocus
End If

I get nothing, I am trying to copying a record from the tote log and change the number from, lets say, L20444 to L20444B2 and have the same field information as the original. This is where I am so far but I get nothing. Ahy Help would be greatly, and I mean greatly, appreciated. Thanks

Answer 1

Well it might actually be saving the database record but not redisplaying it; I'm having a hard time deciphering that part of the code, and I don't know what your form is bound to.

Anyway, you should open your recordsets like this:

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

especially if you are using SQL Server as the backend (which you should).

Once you have saved the record, you should probably just reload the record back into your form by doing a recordset.find(), rather than trying to bookmark it. Bookmarks only work on the same recordset they originated from. This provides round-trip verification that the data was actually saved into the database.

Answer 2

There are a few things that could be causing it. Here is one. Does your table have a primary key? It looks like you are trying to update the primary key to a value that already exists in the table before changing it. Is this happening on a form? If so Access can get upset at you for changing a recordset behind it's back. a me.undo() before making changes can help. Also if you are on a form you can acomplish the same thing this way. It's a bit hacky, but it is the easy way.

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste

As an alternative, I would recommend something along these lines.

Dim sSql As String
Dim sUpdateSuffix as string
sUpdateSuffix="B2"
Const sTableName As String = "[Tote Log] "
sSql = "INSERT INTO "[Tote Log]([number],[whateverelse]) " & _
       "SELECT [number]" & sUpdateSuffix & ",[whateverelse]  FROM [Tote Log] WHERE Number = " & Me.tbScannerRead.Value
CurrentProject.Connection.Execute sSql

If you want to build the sql string dynamically use the same method as you already used to loop through the fields and build the query string. me.requery will rebuild the form recordset.

Hope that helps you