highest number + 1

[octatyan]

I'm not sure if this topic has been posted yet, and I couldn't find any on this forum. Here's my situation.

I have a form that displays information about employee salaries. When an employee gets a raise we enter this info into this form. so if an employee recieves his first raise the "amendment number" is 1.

here's my problem :

How do I get the form to automatically insert a "2" for the "amendment number" field next time this person gets a raise?

I don't want to use autonumber because it can start to miss numbers down the road (ie deleting amendments). I want the form to find the largest "amendment number" for this employee and then add 1. How would I code this? or is there an easier way to do this?

also, if the employee currently has no amendments yet, how do I make the amendment number field automatically insert a 1?

thanks for any help

 

[dcx693]

Check this thread: Generate Automatic Number

 

[octatyan]

ok, i've tried the dmax function but cant seem to get it to work.

This is the code i'm using in the VBA section:

EmpSalary = dmax (SalaryAmendmentNum, tblSalaryAmendments)

where:
- EmpSalary is the field name in the form
- SalaryAmendmentNum is the set of records in the table
- tblSalaryAmendments is the table that SalaryAmendmentNum resides in.

I get errors that say "can't find the field tblSalaryAmendments".

Am I doing something wrong?

 

[boblarson]

For one thing, you need quotation marks around your table name and brackets and quotation marks around your field name.

For example:

Dim curX As Currency
curX = DMax("[Freight]", "Orders")

 

[octatyan]

Yay, that works. Sorry for my lame questions, but I don't have any good sources for proper syntax.

2 questions though...

1.
When I do this, I would like the "amendment number" field to be unchangeable by the user. Previously I unenabled and locked that field, but i had to enable and unlock that field because the dmax would only work if "amendment number" has the focus. And it can't get focus unless it's been enabled.

2.
The amendment number is automatically being set as the highest amendment number that ANY employee has. I would like the amendment number to be limited to the highest amendment number for the currently displayed employee. I assume this would go in the criteria section of the dmax function.

the employee's record is the main form, while the amendment info is in the subform. How does the syntax work to make the amendment number in the subform link to the main form?

any help is greatly appreciated!

 

[boblarson]

You can leave it enabled, but in the properties set the Locked property to YES.

2. The example revised for criteria (this example is based on a text box for a date):

Dim curX As Currency
curX = DMax("[Freight]", "Orders", "[OrderDate] = #" _
& Forms!Orders!OrderDate & "#")