Default value for subform field (using previous record)

[knpaddac]

I have a field in a subform that I want to default to the same value as the previous record. I have done this for a field in a form using the 'after update' and 'on enter' events but cannot figure out how to do it for a subform.

[Micron]

since you have done it before, then maybe your issue is about how to reference a subform control?
[Forms]![Main form name]![subform control name].[Form]![control name on subform]

where [subform control name] is the control (container) holding the subform itself.

[knpaddac]

Part of my problem is that I do not even know where you go to enter any sort of code when it comes to a subform.

This is what I did to achieve my goal when working with a form: "We will base this on a field called UserName...

In the After Update event of the UserName field put code like this:

Me![UserName].Tag = Me![UserName].Value

In the On Enter Event of the same field put code like this:

If Not Me.NewRecord Then Exit Sub
If Not (IsNull(Me![UserName].Tag) Or Me![UserName].Tag = "") Then
Me![UserName].Value = Me![UserName].Tag
End If


The UserName will not carry over into your next session with the form but will work as long as the form is open....

Add the code to the fields you want to update, just change the code to reflect the field names."

[Micron]

OK, that's a little more information, but not enough. The same approach cannot be taken in all cases, and the method you posted might be something that one would do if the form in question was unbound, but seems a bit clunky for a bound form. Also, you cannot use Me unless the event code is on the same form as the one whose control you want to do this to. I was guessing that the event you want to do this for is on the main form while the control is on the subform, which is why I posted the syntax for subform referencing.

Describe what is going on in more detail and we can figure it out; e.g.

user is on subform, leaves control/changes combo/goes to new subform datasheet record, etc. etc and I want next new record to hold the value from previous record
OR
similar but I want user to be able to be on any old record, choose go to new record and put value from a/some field(s) into the new record
OR
similar but the action will be driven by the main form, such as a combo change

That's probably enough to give you an idea of what information to provide. This may be as simple as setting the .DefaultValue of a control or controls to what they had when you go to a new record if you're more inclined to try that instead. Probably even simpler if the value will always be the same - then you likely only need to set it once in design view instead of every time some event fires. For that case, see

https://support.office.com/en-us/art...e-6c2b9522b8ee

[knpaddac]

I am not well-versed in the exact terminology, so I will do my best to explain....
My database is for basketball information. I have a table of game information (date, winner, loser, winning score, losing score, etc.) and a table for individuals performances that took place in those games (name, school, points, etc.).
I have a form for entering game information, it is in datasheet appearance and when I click the little "+" a subform of the performances opens.
When I enter the game information it is typically many consecutive that have the same date, so I wanted the date field to default to whatever the previous entered record was, and I achieved that with my last explanation above. What I would like next is for the performance subform 'school' field to do the same and default to what the most recent previously entered record has.
Does that at least make a good start explanation?
If you have an answer it is very likely I will need in depth help in how to get there step by step.

[June7]

Maybe making this more difficult than it needs to be.

Put code in the AfterUpdate event of the control you want to carry value forward.

For a date field, use # delimiters.

Me.controlName.DefaultValue = "#" & Me.controlName & "#"

For a text field use apostrophe delimiters. Number fields do not use any delimiters.

[Micron]

I'll let june7 take over since she seems to know the control being updated isn't something like a combo on a main form and the updating control isn't on a subform.

[June7]

Well, I am taking my lead from the statements "I have a field in a subform that I want to default to the same value as the previous record." and "When I enter the game information it is typically many consecutive that have the same date, so I wanted the date field to default to whatever the previous entered record was".

So why would setting DefaultValue property not be applicable to the school control?

If you want to provide db for analysis, follow instructions at bottom of my post.

[knpaddac]

It is very likely you are right that I am making it more complicated than need be (that is likely due to the fact that all I know about access tends to be learned on an as needed basis while I have been working with this db i have created).

I did what your instructions said and have attached a very stripped down version of the database. It has only the 2 associated tables and the 1 form (with subform) that I am trying to bend to my will.

The reason that I have not used the default value for the 'school' field in the performance table is because I did not think you could do that. I thought that for the goal that I am trying to get you had to put it in an event in the form. If it is possible to simply tie it to the table itself, that would be best because it will ALWAYS be the case that when I am adding new records to the performance table there will be 5 to 10 in a row that have the same exact school.

Any help you can give me here is greatly appreciated.

[June7]

Your subform is not a form, table is the SourceObject of subform container. This means there are no controls whose properties can be set, including the Value. There are no form events that can be coded. Neither your code nor my suggestion will work for the 'subform' but my suggestion could be used on main form for its controls.

Recommend you build a form.

The instructions you cite that refer to 'field' should really say 'control' or 'textbox'. Tables and queries have fields, forms and reports have controls.

Can shorten the main form RecordSource SQL to: SELECT * FROM GAMES_current WHERE GameDate=[Date_to_enter] ORDER BY Season, GameDate, Winner;

Also advise not to use dynamic parameter input popup prompts, reference an UNBOUND control on form for user input.

[knpaddac]

So the basis of what you are saying is that if the performance data is entered via a subform, then defaulting to the value from the previous record is not possible?
To enter the performances as its own form would be difficult because that data is linked to a game. Basically I enter the game information and then immediately the associated performances.

Your subform is not a form, table is the SourceObject of subform container. This means there are no controls whose properties can be set, including the Value. There are no form events that can be coded. Neither your code nor my suggestion will work for the 'subform' but my suggestion could be used on main form for its controls.

Recommend you build a form.

The instructions you cite that refer to 'field' should really say 'control' or 'textbox'. Tables and queries have fields, forms and reports have controls.

Can shorten the main form RecordSource SQL to: SELECT * FROM GAMES_current WHERE GameDate=[Date_to_enter] ORDER BY Season, GameDate, Winner;

Also advise not to use dynamic parameter input popup prompts, reference an UNBOUND control on form for user input.

[knpaddac]

I tried rebuilding the form and subform from the ground up.... and it seems to work how it should and I have been able to use that original code for controls in both form and subform. Should make things a little easier. Still not certain how I had the screwed up "subform." Thanks for the help everybody.

[June7]

The original subform container had Performances table as SourceObject. Your revision must be using a form as SourceObject.

Still think code to set DefaultValue property is simpler.

However, glad it is working satisfactory.