New Record using an old record (1 Viewer)

[FreonIceMan]

I am trying to take an existing record in a table and make a new entry in the same table using some of the values of the existing cell and new values based on user input. I have been searching and I haven't had any luck.

What I want to happen is, the user clicks a record from a list of options it opens a form which has the data from the record, it also has blank text boxes to enter the new data, when i click a button I want it to write a new record, and also edit one field on the old record. Can someone point me in the right direction?

Thanks,
-Freon

 

[EternalMyrtle]

You could do something like make a combo box and then use If..thens or select case to open a new record and set the values in the controls.

So something like:

If Me.comboboxcontrol.value = x Then
Docmd.OpenForm "FormName", , , ,acFormAdd
Forms!FormName!ControlName.SetValue= x
'Repeat above for other controls you want to set
End If

Or select case which is more efficient but a little more difficult to explain

 

[EternalMyrtle]

Or you could just use the add duplicate record button built in to access which uses a macro to select the whole record. That would copy all of the controls and the user would have to change them manually. Also, this would only work if you want to copy the record in the same form and not open up a new form.

BTW, the above is wrong, you don't need SetValue in vba.

Also Forms!FormName!ControlName.SetValue= x should be set to the current (or "old" value) so you would probably want it to be = Me.ControlName instead

So that line should be something like:

Forms!FormName!ControlName= Me.ControlName

This would be problematic if you are trying to add the data to the same form. it should work if you are adding to a different form.

There may be a better way but hard to say without more details.

 

[FreonIceMan]

The table has few columns: (Policy_ID is primary key for this table)
Policy_ID, User_ID, Name, Policy_Number, Deductible, Deductible_Remaining, Active

The User_ID, Name, Policy Number, and Deductible will be the cells I want to copy the data from.

I start from my main form it has a report list that I can chose different from different Policies (Policy_ID) associated with a specific user (User_ID). When I click the Policy it opens a new form. It has the User_ID, Name, Policy_Number, and Deductible fields for the policy select showing their values. Deductible_Remaining is an empty text box. I want to enter the new value for Deductible_Remaining into the text box, then press my Command button.

I want to have the Command button perform the following actions:
1. For the current record change the Active column to "No"
2. Create a new record with the User_ID, Name, Policy Number, and Deductible the same value as the current record, and Deductible_Remaining is the value I entered in the text box.

I hope this helps clear up what I am trying to accomplish.

Thanks,
-Freon

 

[alexl-123]

make a form

property's set to Data Entry

create the user input fields required
set the property of default value = [forms]![Formname]![FormInputFieldName]

this would be reference to your original form so when the new form loaded it would get the values from the originating form

then set all the control sources to the relevant fields in your table

just to keep things tidy hide the first form when going to the new form this way it can still find values from it but wont be visible to the user