Adding "10" to a previous record when a new record is created

[megs]

Hi,

I want to be able to add 10 incrementally when I create a new record in a subform from the previous record. Ideally, then when a new record is created from the main form the field in the sub form goes blank. Then the process is repeated, so that when a new subform record is created it adds 10 incrementally again.

I did play around with VBA and macros but to no avail. I am VERY new to coding.



Thanks!!!

[Robeen]

Can you post a screenshot of your Form - and tell us what field in what table you are wanting to add 10 to?
We'll need more details to be able to help.

[megs]

Here is a screenshot of the form:Attachment 8464
Here is a screenshot of the relationships:Attachment 8466

I want to increment the field "Quadrat" in the sub-form Quadrat flower Survey by clicking the Add record button by muliples of ten: 0-250 (however it is not always 250). Then when you select a new transect in the "find transect" field at the top of the main form the Quadrat field automatically returns back to 0.... ideally but if it always adds 10 to the previous record

[ssanfu]

Hi,

Your screenshots did not get attached.

[megs]

sorry don't know why it did that... the preview worked fine for me :S

[amrut]

Try following code in the subform's after update event -
With Me.YouControlName
If Not IsNull(.Value) Then
.DefaultValue = """" & .Value + 10 & """"
End If
End With

Looking at table relationships, Quadrat in your Quadrat_flower_count table is related to primary key of your Quadrat_Id table ( which will be unique for each record). In that case you cannot change that value.

[megs]

The coding works for me but it doesn't create a new "Quadrat ID" when I add a new record. Is this the problem you are referring to when you say "Looking at table relationships, Quadrat in your Quadrat_flower_count table is related to primary key of your Quadrat_Id table ( which will be unique for each record). In that case you cannot change that value."

By I cannot change that value do you mean that the Quadrat ID primary key value? It was changing for me before I put in the coding.

I also didn't include the whole relationship diagram for some reason, but have got carried away. Here is the final version:




Thanks a MILLION!!

Try following code in the subform's after update event -
With Me.YouControlName
If Not IsNull(.Value) Then
.DefaultValue = """" & .Value + 10 & """"
End If
End With

Looking at table relationships, Quadrat in your Quadrat_flower_count table is related to primary key of your Quadrat_Id table ( which will be unique for each record). In that case you cannot change that value.

[amrut]

Is Quadrat Id (Primary key) in the QuadratID table an autonumber field or you are assigning it ? Is that field always in a multiple of 10 ? According to the table relationships, you may not be able to increment (control) it. It will be automatically added to the sub-form whenever you enter data in the first field.

[megs]

I'm not changing Quadrat ID, that is an automatic number. I am wanting to change the field 'quadrat' only in Quadrat ID table. Quadrat ID can continue to be autonumber filled.

Is Quadrat Id (Primary key) in the QuadratID table an autonumber field or you are assigning it ? Is that field always in a multiple of 10 ? According to the table relationships, you may not be able to increment (control) it. It will be automatically added to the sub-form whenever you enter data in the first field.