CODEWITHSUNDEEP
ms-accessvba
vbnewbie
vbnewbie

Reputation: 29

vba access run time error 3265

I am new to programming in VBA. I am trying to copy Form data from an existing form when I click on the Copy Record button. This is supposed to copy the current form data as a new record with a new master_id (that is autonumbered) and have Brand as blank field for them to fill in. I get a:

Run Time Error 3265 "Item not found in this collection"

at the new_master_id that i created. I am not sure how to fix this problem. Any help is appreciated.

Private Sub Copy_Record_Click()

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer
Dim New_MASTER_ID As Integer
New_MASTER_ID = (DMax("[MASTER_ID]", "tbl_Drug_Master") + 1)
Dim BRAND As String
BRAND = ""

Set RS = CurrentDb.OpenRecordset(Name:="tbl_Drug_Master", Type:=RecordsetTypeEnum.dbOpenDynaset)

With RS
    .AddNew
    ![MASTER_ID] = ![New_MASTER_ID] <--this is where the problem is...
    ![MASTER_KEY] = Me![MASTER_KEY]
    ![PRODUCT_CATEGORY] = Me![PRODUCT_CATEGORY]
    ![BRAND] = Me![BRAND]
    ![GENERIC] = Me![GENERIC]
    ![STUDY_NAME] = Me![STUDY_NAME]
    ![MANUFACTURER] = Me![MANUFACTURER]
    ![MASTER_COMMENTS] = Me![MASTER_COMMENTS]

    .Update

End With

End Sub

Upvotes: 0

Views: 1563

Answers (1)

LiamH
LiamH

Reputation: 1502

ok so firstly, im not sure why the following are required:

dim c as control
Dim FillFields As String, FillAllFields As Integer
New_MASTER_ID = (DMax("[MASTER_ID]", "tbl_Drug_Master") + 1)
Dim BRAND As String
BRAND = ""

therefore I am leaving them out as part of this question because they appear unnecessary. Brand is not required because you are creating a new record and putting nothing in the brand field so it will remain blank.

I am also not too sure why you have 2 tables both that are the same? I think what should happen is that you simply copy the data to a new record in the same table.

You will see I have put a save record command in to the routine. other additions such as error handling is also recommended. 

Private Sub Copy_Record_Click()
docmd.runcommand accmdsaverecord
Dim RS As Recordset

Set RS = CurrentDb.OpenRecordset(Name:="tbl_Drug_Master", Type:=RecordsetTypeEnum.dbOpenDynaset)

  With RS
    .AddNew
       ![MASTER_KEY] = Me.MASTER_KEY.value
       ![PRODUCT_CATEGORY] = Me.PRODUCT_CATEGORY.value
       ![GENERIC] = Me.GENERIC.value
       ![STUDY_NAME] = Me.STUDY_NAME.value
       ![MANUFACTURER] = Me.MANUFACTURER.value
       ![MASTER_COMMENTS] = Me.MASTER_COMMENTS.value
    .Update 
  End With
Set RS = Nothing
End Sub

I was mistaken with my comment rs.close it would be db.close but you are using the currentdb and no reason to close it. This procedure will remain on the original record, if you want to go to the new record you will have to add a command like docmd.gotorecord acdataform, , aclast before the end of the routine.

Upvotes: 1

Related Questions