Repeating values for a field in several records

I am using Access 2007, and I have a database in which one field is likely to have the same value for several records (eg the Age field in a table of students). How can I set this field on a form so that when a new record is created, the Age field defaults to the value used in that field on the previous record (so that I don't have to keep entering the same data)?

Thanks in advance.

Robert

Hi

In my humble opinion, I think you should be storing the date of birth not the age (which changes)!!??

As usual there are many ways to do this I'm sure, but of the top of my head, this is one way

Declare a module level variable, say intAge

in the form before update event set this to intAge = age_control

in the form on current event set the age_control = intAge IF it is a new record
ie If Me.NewRecord Then age_control = intAge

This may need tweeking, depending on how the form operates.


MTB

I am using Access 2007, and I have a database in which one field is likely to have the same value for several records (eg the Age field in a table of students). How can I set this field on a form so that when a new record is created, the Age field defaults to the value used in that field on the previous record (so that I don't have to keep entering the same data)?

Thanks in advance.

Robert

Hi, Robert.

I suggest you to set DefaultValue property to current control value on AfterUpdate event.

Example for Date type field

Example for Numeric type field

Example for Text type field

Hi, Robert.

I suggest you to set DefaultValue property to current control value on AfterUpdate event.

Example for Date type field

Expand|Select|Wrap|Line Numbers

1. Private Sub dteDate_AfterUpdate()
2.     With Me.dteDate
3.         .DefaultValue = "#" & .Value & "#"
4.     End With
5. End Sub
6.  

Example for Numeric type field

Expand|Select|Wrap|Line Numbers

1. Private Sub lngNum_AfterUpdate()
2.     With Me.lngNum
3.         .DefaultValue = .Value
4.     End With
5. End Sub
6.  

Example for Text type field

Expand|Select|Wrap|Line Numbers

1. Private Sub txtText_AfterUpdate()
2.     With Me.txtText
3.         .DefaultValue = "'" & .Value & "'"
4.     End With
5. End Sub
6.  

Thanks, but I couldn't get it to work.

I tried with a numeric field and also a text field, without success.

My numeric field is called Age and I entered the code as you suggested. It looks like this:

I presume the "Me" is a generic reference to the current form, but I tried the full form name as well, also without success.

Do you have any other suggestions for what I might be doing wrong?

Thanks

Robert

Thanks, but I couldn't get it to work.

I tried with a numeric field and also a text field, without success.

My numeric field is called Age and I entered the code as you suggested. It looks like this:

Expand|Select|Wrap|Line Numbers

1. Private Sub Age_AfterUpdate()
2.     With Me.Age
3.         .DefaultValue = .Value
4.     End With
5. End Sub
6.  

I presume the "Me" is a generic reference to the current form, but I tried the full form name as well, also without success.

Do you have any other suggestions for what I might be doing wrong?

Thanks

Robert

Hi, Robert.
The code was tested multiple times before. It just must work. ;)
1. Did you set Age.AfterUpdate property to "[Event procedure]"?
2. Toggle breakpoint on the line and debug the sub.

I presume the "Me" is a generic reference to the current form

Yes. It is reference to the form - owner the form module.

Hi, Robert.
The code was tested multiple times before. It just must work. ;)
1. Did you set Age.AfterUpdate property to "[Event procedure]"?
2. Toggle breakpoint on the line

Expand|Select|Wrap|Line Numbers

1. Private Sub Age_AfterUpdate()
2.  

and debug the sub.

Yes. It is reference to the form - owner the form module.

Thanks, and OOPS!!!

My version of Access (perhaps all of them) has a security warning saying that "Certain content in the database has been disabled." When I clicked the Options to "Enable this content" everything worked fine.

Sorry that my ignorance caused you to spend time unnecessarily on this.

Thanks (and sorry) again

Robert

Hi

In my humble opinion, I think you should be storing the date of birth not the age (which changes)!!??

MTB

I agree, but I was just using this as an example - I actually have several fields which I want to repeat.

The problem has now been solved.

Thanks

Robert

Thanks, and OOPS!!!

My version of Access (perhaps all of them) has a security warning saying that "Certain content in the database has been disabled." When I clicked the Options to "Enable this content" everything worked fine.

Sorry that my ignorance caused you to spend time unnecessarily on this.

Thanks (and sorry) again

Robert

Not a problem.
You are welcome.

Regards,
Fish