Add new record basing data on previous record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have set up a form with a number of combo boxes. When I have selected all
the details, I click the Add new record button which saves this record and
gives me a new blank record, but I would like the new record to appear with
some of the data from the previous record. i.e if the "country" combo box
shows England, when I add a new record I want the new and subsequent records
to show England in the combo box until I change it to another country etc.
 
Sue,

There are two common ways to do this:

1) Add code to your Add Record button that copies the current values to an
array, and then writes them to the new record:

Dim MyArray(10) As Variant
MyArray(0) = Me![MyControl]
MyArray(1) = Me![My2ndControl]
....etc.

' Add record code
Me![MyControl] = MyArray(0)
Me![My2ndControl] = MyArray(1)
....etc.

or

2) Use the AfterUpdate event of each control to reset the control's
DefaultValue property:

Me![MyControl].DefaultValue = Me![MyControl]

Hope that helps.
Sprinks
 
Do a search. This is asked and answered often. You need to create code to
change the default value of the control.
 
Many thanks, item 1 worked but I now have a problem with the synchronisation
of my 2nd combo with my 1st combo box when I add a new record.

In the open form event I have a macro that creates a blank new record, when
I select my grower in combo 1, combo 2 comes up with only the plots for that
grower etc. When I click on the add button, the required combo boxes do
match the previous record except the plot(combo2) which is completely blank.

The rowsource for plot(combo2) is as follows:-

SELECT Plotname.PlotId, Plotname.GrowerId, Plotname.Farm, Plotname.Plot FROM
Plotname WHERE (((Plotname.GrowerId)=Forms!BookingInForm!Grower)) ORDER BY
Plotname.Plot;

Sue


Sprinks said:
Sue,

There are two common ways to do this:

1) Add code to your Add Record button that copies the current values to an
array, and then writes them to the new record:

Dim MyArray(10) As Variant
MyArray(0) = Me![MyControl]
MyArray(1) = Me![My2ndControl]
...etc.

' Add record code
Me![MyControl] = MyArray(0)
Me![My2ndControl] = MyArray(1)
...etc.

or

2) Use the AfterUpdate event of each control to reset the control's
DefaultValue property:

Me![MyControl].DefaultValue = Me![MyControl]

Hope that helps.
Sprinks

Sue said:
I have set up a form with a number of combo boxes. When I have selected all
the details, I click the Add new record button which saves this record and
gives me a new blank record, but I would like the new record to appear with
some of the data from the previous record. i.e if the "country" combo box
shows England, when I add a new record I want the new and subsequent records
to show England in the combo box until I change it to another country etc.
Click to expand...
Click to expand...
 
Back
Top