Record Copy function

[nyrob609]

Hi,

I have a form that needs to be filled out for authroization request and I have status field in the form "Approved", "Pending", "Denied". What I would like to do is keep history of denied cases.

For example, If I request an authorization an "Elective Admission" and the vendor comes back and say Denied Elective Admission but approved Ambulatory Surgery instead.

What I would like to do is update the record to say "denied" keep the information and I would like for the system to create a copy of the record but leave some fields blank to update with new information if necessary for example "Ambulatory Surgery"

Because the form has a lot of data that won't change like Vendor name, Service area, date requested etc.. I want to avoid user complaining that had to retype everything so I want for the system is to make a copy of old record.

I know access has a commad button to copy record but I want to copy some fields and the rest left blank for new information.

I hope I explain myself well enough.

Thank you

 

[CraigDolphin]

Not sure what your experience level is but, assuming you know enough to use vba to execute a SQL statement, I would simply use the after update event of your 'denied' control, to ask the user whether they wish to create a 'duplicate' record for the approved action to be recorded on, and if so, then use Docmd.runsql to execute an append query. You would build the SQL string in vba. You can obtain all the values for the fields you want duplicated from the current record. Then, after the sequel has executed you'd move the user to the newly created record.

So your SQL string might look somehting like:
strSQL = "Insert into tblYourTable ([Field1],[Field2],[Field3],[etc]) Values (" & Me.Field1ControlName & "," & Me.Field2ControlName & "," & Me.Field3ControlName & "," & Me.etcControldName & ");"

Of course, if any of the values are text or dates you'd need to supply the necessary delimiters for the values as well (' or #), and obviously change the names of the controls/table etc to match your database.

 

[nyrob609]

Thank you that I made me think actually did VBA code and it works great...plus I had index that can't duplicate this way I am able to copy of the prior index number into another field to keep records together.

creating DIM Variant for each field
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant

v1 = Me.[Referring Practice #].Value
v2 = Me.[Referring Dept/Practice].Value
v3 = Me.[Requesting Provider].Value
v4 = Me![Referring Service Area].Value

RunCommand acCmdRecordsGoToNew

Me![Referring Practice #] = v1
Me![Referring Dept/Practice] = v2
Me![Requesting Provider] = v3
Me![Referring Service Area] = v4