Kopi af record

Nedestående er en kode fra ACCESS egen wizard og virker

Private Sub Command169_Click()
On Error GoTo Err_Command169_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Command169_Click:
    Exit Sub

Err_Command169_Click:
    MsgBox Err.Description
    Resume Exit_Command169_Click
   
End Sub

Den tager en kopi af den record som du står på og lægger den sidst

if you know which record you want to copy what is wrong with using a simple INSERT into ... SELECT --- FROM syntax?

Terry: Oh .. my good old SQL... I forgot. But 62 (!) fields....

Lets say we have a table with a number of fields and one is an autonumber.

Make an append query where you append the records you want WITHOUT the ID, this gets generated automatically.

Add a paramater to the query by right clicking in the area where teh table is and choose parameters. Give it a name (ID) and choose longer integer as type.

Now you query will look something like this.

PARAMETERS ID Long;
INSERT INTO tbl1 ( Dato, Lonnummer, afdeling )
SELECT tbl1.Dato, tbl1.Lonnummer, tbl1.afdeling
FROM tbl1
WHERE (((tbl1.ID)=[ID]));


If you run th equery you will get prompted for the ID, but if you do it in code you can set the parameter to what ever you want.

EG:


PARAMETERS ID Long;
INSERT INTO tbl1 ( Dato, Lonnummer, afdeling )
SELECT tbl1.Dato, tbl1.Lonnummer, tbl1.afdeling
FROM tbl1
WHERE (((tbl1.ID)=[ID]));

So you only need to make the query once with all 62 fields :o)

Sorry Example here:

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("QryAppend")

qdf.Parameters("ID") = 2

qdf.Execute

obviously the query is saved with the name "QryAppend"

schoesler: Det var næsten for nemt til at være sandt. Og det bar for nemt... umiddelbart.
I den form der skal kopieres er der også et felt som skal udfyldes manuelt, men som ikke må indeholde dubletter.

I formen er der nogle subforms - der lader til at blive opdateret / relinked.

Og i nogle tilfælde får jeg også meddelelse om at poster vil blive lagt i tabellen "Indsætningsfejl".

Terry: Good work. But Schoesler's example moves the record-browser to the last record in the forms recordset. Further more, I tested your first tip, using docmd.runsql when click-event on a simple button. But Nothings happend.
After having tested schoesler's hook, I found that your SQL maybee is the best. Plz remark that the form has some subforms, including som datasheets, but using SQL this may be a smaller prob.
So: How navigate to the newest record using VBA and update a bound datasheet are the final issues.

Terry, It works. Plz make a svar, and you got your deserved ponts. But I have two more qeustions

Terry last comment:
The table Personer has a autonumber as primary key. How can I get the new generated ID ?

eg.:
form.recordset.movelast
ID = form.recordset("PID")
... or something like this ?

I wasnt aware that you were trying to copy a record on a form only from a table, at least thats what your code is doing.

You could use something like

Dmax("ID", "Personer")

Every day I get new features of Access.... :o)
Now Terry, when I got the dmax-value, I want view the new record.
How do I move the form to the new record ?

I think I fixed last comment:

Set rs = [Forms]![Personer].Recordset.Clone
rs.FindFirst "[Personkonto] = " & CStr(lPersonkonto)
If Not rs.EOF Then [Forms]![Personer].Recordset.Bookmark = rs.Bookmark

All right, but could I use the docmd.gotorecord instead of setting a bookmark ?

I'm very puzzled.
What is wrong with the code schoesler gave originally, doesnt that do what you want and move the cursor to the record?

If you can give an explanation of all of waht your trying to do then maybe its possible to give the correct solution first time

status?

Thaks for splended help Terry. Well I used the hard-way/hard-code as we discussed. Soory being late to give the points.

as they say, better late than never :o)