Struggling to set the value of a field in a form automatically

[Chris]

I have a form I created that is based off a table I created. There are also
two subforms within this form that are based off two other tables. In this
form I want to set the value of a field equal to the value of the same field
from the last record + 1. This way each time I open the form the next
sequential value automatically appears without me having to input it. I
can't use the autonumber as the field type because of the field types from
the other two tables that my subforms are based off of. When I tried it
messed up the data I set in my subforms.

The field is currently set as a text field. I'm not sure if I should use
code builder or if I should use a macro to do this. I tried using visual
basic to write a code, but had no luck. This was the code I used:
[Text5]=DLast("QAR No", "tblQARmaster")+1
Text5 is the field name in the form and QAR No is the field name in the
table, "tblQARmaster", the form is based off of.

I kept getting error messages when I tried this, so I went to the macro
builder and tried using the action "SetValue", but couldn't figure out where
to go from there. Can anyone help me? Here's an example of what I want to
do in this form:

If the value of this field from the last record is 1655, then when I open a
new record in this form I want the value to automatically appear as 1656.

 

[John W. Vinson]

I have a form I created that is based off a table I created. There are also
two subforms within this form that are based off two other tables. In this
form I want to set the value of a field equal to the value of the same field
from the last record + 1. This way each time I open the form the next
sequential value automatically appears without me having to input it. I
can't use the autonumber as the field type because of the field types from
the other two tables that my subforms are based off of. When I tried it
messed up the data I set in my subforms.

The field is currently set as a text field.

Why? If it's a numeric value that you're incrementing, a Number (probably Long
Integer) field would be easier. You can do it with text but it's a bit more
work converting back and forth.

I'm not sure if I should use
code builder or if I should use a macro to do this. I tried using visual
basic to write a code, but had no luck. This was the code I used:
[Text5]=DLast("QAR No", "tblQARmaster")+1
Text5 is the field name in the form and QAR No is the field name in the
table, "tblQARmaster", the form is based off of.

Where did you put this code? Note that DLast() is the wrong function - it gets
the last record *in disk storage order*. You need DMax() instead.

I kept getting error messages when I tried this, so I went to the macro
builder and tried using the action "SetValue", but couldn't figure out where
to go from there. Can anyone help me? Here's an example of what I want to
do in this form:

If the value of this field from the last record is 1655, then when I open a
new record in this form I want the value to automatically appear as 1656.

Use the Form's BeforeInsert event. Click ... by the property, choose Code
Builder, and edit in

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Text5 = NZ(DMax("[QAR No]", "[tbQARMaster]")) + 1
End Sub

Do rename Text5 to something meaningful, and consider leaving blanks out of
your fieldnames; they're legal but can cause a hassle if you upsize your
database.