MS Access 2013: Incomplete Duplication of Record

I have a really simple set-up:
  1) Continuous form based on a query
  2) The query is based on one table
The table fields include a date, numerical values (= combo box on the form) and short text.

The continuous form has a command button which replicates (or more accurately should replicate) the relevant record when clicked. The problem is that when the command button is clicked two fields in the record (the date and a numerical value) are not replicated but the rest of the fields are (I have checked this in the table).
I tried creating the button as an embedded macro using both the command button wizard and also in VBA code but the incomplete replication is the same, so I guess it is unlikely to be an issue with the code. I also compared the table and form field properties between replicating and non-replicating fields, but can see no difference.
I am sure I must be overlooking something really basic, and it is driving me nuts ! Does anyone have any ideas as to how to solve this problem ? Thank you in advance for any thoughts.
Paul McCabeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
This is a bit complicated but it would do the job
Dim fld As Field
Dim rst,rstFlt as DAO.Recordset
set rst = Me.RecordsetClone
        rst.Filter = "ID = '" & Me.ID "'"  ' Criteria for your current Record
      
        Set rstFlt = rst.OpenRecordset
        DBEngine.Workspaces(0).BeginTrans
        Me.Recordset.AddNew
        
            For Each fld In Me.Recordset.Fields
            Select Case fld.Name
                ' Skip the AutoNumber Field or create your custom Numbering
                Case Else
                    fld = rstFlt.Fields(fld.CollectionIndex)
                End Select
            Next fld
        Me.Recordset.Update
        DBEngine.Workspaces(0).CommitTrans

Open in new window

0
Paul McCabeAuthor Commented:
Thank you very much for the code. I used it as is for the command button , but the following line gives a compile error:

rst.Filter = "ID = '" & Me.ID "' " ' Criteria for your current Record

I cannot figure out what the problem is; may I ask you to review this line again ?
0
John TsioumprisSoftware & Systems EngineerCommented:
This line simply filters only the current record ...in order to clone only the specific record you selected....
I need your table structure to change the line to your design
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Paul McCabeAuthor Commented:
If you mean the table fields, they are as follows:
   ProjectID (primary key)
   ParticipantID (numerical)
   CreationDate (date / time)
   ParticipationStatus (numerical)
   ProjectClassification1 (numerical)
   ProjectClassification2  (numerical)
   Notes (short text)
0
John TsioumprisSoftware & Systems EngineerCommented:
So it is
rst.Filter = "ProjectID = '" & Me.ProjectID  "'"

Open in new window

I guess that ProjectID  is Autonumber...if not then you have to implement your own increment like this
case is "ProjectID " 
fld= dmax("ProjectID ",YourTableName)+1

Open in new window

0
Paul McCabeAuthor Commented:
Apologies, I should have mentioned that ProjectID is an autonumber. However, I still get a compiler error for:
rst.Filter = "ProjectID = '" & Me.ProjectID  "'"

I really appreciate your help with the (to me rather difficult) code, but wonder what the fundamental problem is. Record duplication should "in theory" work with a standard embedded macro or simple VBA code (which work fine on other continuous forms I have built).
0
John TsioumprisSoftware & Systems EngineerCommented:
Correction...i missed the point where the key is numeric
rst.Filter = "ProjectID = " & Me.ProjectID  

Open in new window

0
Paul McCabeAuthor Commented:
The code stops at

Me.Recordset.Update

with the following error message:

Run-time error 3022: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data or delete the index, or redefine the duplicate values so that they can used.

I am not sure if this is an issue with the code, or with the way the table has been set up (although I cannot see any obvious problems with the latter.....)
0
John TsioumprisSoftware & Systems EngineerCommented:
I guess you didn't  checked the part about "' Skip the AutoNumber Field or create your custom Numbering"
So the code is
Dim fld As Field
Dim rst,rstFlt as DAO.Recordset
set rst = Me.RecordsetClone
        rst.Filter = "ID = '" & Me.ID "'"  ' Criteria for your current Record
      
        Set rstFlt = rst.OpenRecordset
        DBEngine.Workspaces(0).BeginTrans
        Me.Recordset.AddNew
        
            For Each fld In Me.Recordset.Fields
            Select Case fld.Name
               Case "ProjectID"
                 ' Just Skip it since is an Autonumber
                Case Else
                    fld = rstFlt.Fields(fld.CollectionIndex)
                End Select
            Next fld
        Me.Recordset.Update
        DBEngine.Workspaces(0).CommitTrans

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul McCabeAuthor Commented:
It works ! (note I changed line 4 of the last code post as you previously indicated to take into account a numerical value). I have two follow-up questions as follows:
1) Can you comment briefly on why "standard" record replication procedures (e.g. embedded macros created by the command button wizard) are not working in this case ? I am completely baffled, as I have other continuous forms where it works perfectly.

2) A minor improvement, if possible: When the button is clicked on an empty (new) record (end-users do things like this), a VBA error message appears (Run-time error '3075':Syntax error(missing operator) in query expression). I attempted to fix this by sandwiching the code between: "If ProjectID <> Null Then" and "End If", but this just leads to nothing happening when the button is clicked, on either an existing or new record. Can you suggest how to fix this ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.