Default Values for subform

[tmort]

This may be more a VBA question than a forms question.

I have a main form and a subform. The suform contains several fields and there are several records on it for any given record on the main form.

I also have a command button on the main form that creates a new record. I would like to have the default records to disply be based on the values from the previous subform.

I don't know if that is clear. An example the main form would contain a location and date and the subform would have sales figures for Tom, Jane and George for items a, b, and c. Tom, Jane and George would be records and a, b and c would be fields.

Generally a weeks worth of data would be entered and generally new values would be added for the same group of records from one day to the next for any given week, however, there might be times when more or less records were entered.

I would like to set it up so that the subform is populated with the same records as are in the subform before the command button is pressed to create a new record on the main form. For instance if on day one items sold are entered for Tom and Jane when a new record is created it would display Tom and Jane on the subform with blank values for items sold.

I thought I could use min or max to get a default value, but, the table that the values go into is by date so max might get the value for Tom but not Jane and George (I think).

Any Ideas?

Thanks

 

[Tim L]

Sorry, can't give you any code examples (yes I agree that the solution probably lies in code) but I have a theory that you might want to investigate.

You already have a query that can isolate the records in the subform for the displayed record. Can you store the fields that identify Tom et al? Next create the new record, then add records to the subform based on who was present in the previous record?

It might go something like this:

1. Click on button to create new record
2. Store the names of the people (or other id used) (in an array?)
3. Create new master (weekly?) record
4. Move focus to the subform.
5. Create a new record in the subform, update person field with data stored in the array.
6. Repeat previous step until all names entered.

Just a thought.

Tim