How do I get ItemId of the selected record

[b_bds]

Hi

I have a Form that as a Subform (populated by a query). The subform query uses the values of the form combo boxes.

I have a command button in the form that is supposed to duplicate the record selected by the user (The duplication is there if the user wants to add almost the same item but the item is not available in the choises).

For the moment I use this code :

RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend

But this only copies what the user sees in the subform forgetting the hided fields like area, section, discipline. My first guess was to change the DoCmd.DoMenuItem to RunCommand but that didn't change anything.

My second guess was to do this :
RunCommand acCmdSelectRecord
(Get Item Id of old record)
RunCommand acCmdCopy
RunCommand acCmdPasteAppend
(Get item Id of new record)
(Copy missing fields of old record in new record)

But I can't find the way to get the item Id for the selected record old or new


Does anyone have an idea of what I could do?

thanks

For more info on my DB, u can look at my old post :
http://www.access-programmers.co.uk/forums/showthread.php?t=144559

 

[MarkK]

What I would do is write a public routine in the module of the form that contains the record you might want to copy. If you then reference the .Recordset property of that form you have access to all the data of the current record. Then you can open another recordset and add a record that way or use an insert query. Code for the recordset approach might look like...

public sub Copy
  dim rst as dao.recordset
  dim fld as dao.field

[COLOR="green"]  'open recordset containing only the fields you want to copy[/COLOR]
  set rst = currentdb.openrecordset( _
     "SELECT <fields you want to add> " & _
     "FROM <table you want to add to>")
  with rst
[COLOR="green"]    'add a record[/COLOR]
    .addnew
[COLOR="green"]    'traverse the fields in your destination rst[/COLOR]
    for each fld in .fields
[COLOR="Green"]      'copy each field value from the source, 
      'which is the current record in Me.Recordset[/COLOR]
      .fields(fld.name) = me.recordset.fields(fld.name)
      .movenext
    next
    .update
    .close
  end with
end sub

If you have an autonumber ID and you expect that the new record should appear in the subform you can navigate to it like this...

me.requery
me.recordset.findfirst "<idFieldName> = " & dmax("<idFieldName>", "<yourTable>")

You can execute this Copy routine anytime using a reference to the form that contains it, which, from a parent form might look like...

me.frmSubform.Form.Copy

 

[b_bds]

Hi

thanks for the extremely fast reply

I hadeed your code but keep gettting a "application defined or object defined error". I fairly new in vba so I'd like if you could explain my mistake

thanks

P.S. I'm not sure I know how to use your code so I just called it with :
me.frmSubform.Form.Copy

 

[MarkK]

What line of code causes the error?

 

[b_bds]

I can't seem to find the line that creates the error

Here's the code that I'm using and guessing by the result the error should be there :

Private Sub cmdCopy_Click()
On Error GoTo Err_cmdCopy_Click


Me.sfrEstimationResult.SetFocus
Me.sfrEstimationResult.Form.Copy

'Me.frmSubform.Form.Copy

'With Me.sfrEstimationResult
' Dim OldRecord As Variant
' Dim NewRecord As Integer

'Old way of copying
'RunCommand acCmdSelectRecord
'Me.sfrEstimationResult.SetFocus
'OldRecord = Me.ItemID
''OldRecord =
'RunCommand acCmdCopy
'RunCommand acCmdPasteAppend
'End With

Exit_cmdCopy_Click:
Exit Sub

Err_cmdCopy_Click:
MsgBox Err.Description
Resume Exit_cmdCopy_Click

End Sub

And here's the function (I'm not to sure where to put it so I just put it in the same page(Form_frmEstimation) on the beginning):

Public Sub Copy()
Dim rst As DAO.Recordset
Dim fld As DAO.Field

'open recordset containing only the fields you want to copy
Set rst = CurrentDb.OpenRecordset( _
"SELECT <Revision, EstimationType, AreaID, Year, DisciplineID, Description, Section, SubSection, ItemSD, Dia, Qty, Unit, DiretpurchasePrice, DirectpurchaseAmount, MaterialsPrice, MaterialsAmount, LabourmanhourUnit, Prod, LabourmanhourTotal, LabourAmount, Total> " & _
"FROM <tblEstimation>")
With rst
'add a record
.AddNew
'traverse the fields in your destination rst
For Each fld In .Fields
'copy each field value from the source,
'which is the current record in Me.Recordset
.Fields(fld.Name) = Me.Recordset.Fields(fld.Name)
.MoveNext
Next
.Update
.Close
End With
End Sub

Any idea where is my problem?
thanks

 

[MarkK]

How do you know you get an error? Commonly a popup appears with Buttons <End> and <Debug>. If you hit <Debug>, the line of code in which the error occurs is highlighted.

You've got to remove the "<" and the ">" from your SQL.

Are you sure you have a reference to the DAO object model. In a code window navigate the MainMenu->Tools->References and make sure the "Microsoft DAO 3.6" item is selected in the list.

Keep me posted...

 

[b_bds]

As for the error here you have an image of what I get :

[img=http://img165.imageshack.us/img165/9049/91152335kf7.th.png]



As for the reference to the DAO object model, I can't access the tools menu on the main menu because the person how works with the database removed the acces from all menu exept the ones he chose.

And I removes the <> and I still have the same result

Any idea what I could do?

thanks

 

[b_bds]

I was able to get access to the reference menu and the reference to the Microsoft DAO 3.6 is selected.

Now I have no idea what to do it still gets me the same message every time I click on the duplicate button...

anyone has an idea what I could do to copy a record?

Thanks