Automatically increase control value by one on open

[LDP]

Hello Everyone:

I would like to increment the numeric value of a particular control on a form by one then move the focus to another control when I open this form for data input only.
The related table is indexed and the number to be automatically increased is a primary key. This would set the value of the next record automatically. New records are always put in sequentially or at least last record value + 1 through this data entry form.
Simple and common task for Access right? It's a bad day for a beginner whose taking a long time to find a simple soluion.

Thank you in advance.

 

[hneal98]

What happens if one is deleted?

You can set up the autoincrement in the table and set the form to open in data entry mode. That way, when the user enters a new record, the table will automatically increment by one.

 

[mph1]

When you increment, are you using the same concept as that of an Invoice or member number? These would be separate from the autonumber that you would use for record distinction as your Primary Key.

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[LDP]

Thank you both.

Yes it is the same concept of an invoice. They are actually transmittals from an project management office.

I cannot set the datatype to autonumber, out of 830 records in this table 3 or 4 had to be suffixed with a decimal to represent a re-issue of the document. This is also the primary key related to other indexed tables.

The form is set to data entry mode, however I thought it might be simple to have Access look at the last value of this field (control)and increment it automatically...since you are in data entry only mode there is a good chance the new value will be the latest in the table incremented by one.

Thanks for your help

 

[mph1]

Keep in mind that the autonumber that you are using is not really meant to be used as you want. Its primary purpose is to give each record a unique value.

If you want to create a new number for each transmittal, try the following. One other thing to remember, there is usually more then one way to accopmolish a end resutl.

1. In your table, create a field named trnasnumber (if you don't already have.

2. On your form, click on the field transnumber(or whatever field name you are using) and in the properties of that field go to Default Value and put =GetNextTransnumber

3. Open a new module and enter:
Public Function GetNextTransnumber()
'get next free Trnasmittal Number[/green]
GetNextTransnumber = Nz(DMax("[Transnumber]", "[TableName]&quot + 1, 1)
End Function

This will automatically give you a new transmittal number for each new record.

HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[LDP]

Thank you HTH

I will build the public function you suggest and give it a try.

Best Regards
LDP

 

[Wantok52]

Here is something else you could try;

Using your database details;

Private Sub YourForm_Enter()

DoCmd.GoToRecord acActiveDataObject, YourForm, acNewRec

Me.YourForm!GetNextTransnumber = Nz(DMax("[Transnumber]", "[TableName]") + 1, 1)

End Sub