Data Entry Form, default value consecutive numbers

[davefwelch]

Can someone suggest to me the way I would get this done?

I have a button on a form for Projects, the button opens a form (Date Entry) to create a new Pay Request for the selected Project. I know how to "pass" the Project key to the Pay Request form, but what I am wanting to do is have one of the fields on the Pay Request form (PayReqNum) default to the NEXT consecutive pay request for that project.

So my PayRequest table may end up looking like the Excel attachment. As you can see, project 2009-15 has had three pay requests submitted, each consecutively number 1, 2, 3. I was thinking that I would have a field (say, PayReqNumPrev) in the PayRequest table that would simply contain PayReqNum + 1, and I could set the new PayReqNum to be equal to PayReqNumPrev. Problem with this is, when I open the form in Data Entry, none of the fields will ave any value (short of their given default value). Any suggestions folks?

 

[DCrake]

This all depends on the number of users that going to using the system at the same time. You could use a function to calculate the next number however if two or more users are using the same screen with the same project is will generate duplicate numbers until one user saves the record. You would need to create the sequence number at the point of the record being saved.

David

 

[davefwelch]

Thanks for the quick reply. At this point, I'm just going to get it to work for one user, and I have been able to. Rather than putting the field NextPayReqNum in the PayRequst table, I put it in the Projects table. So, when the PayReq form is opened in Data Entry mode, the PayReqNum field defaults to that field back on the form pulling from the Projects table. And, when the AfterUpdate event takes place on the PayReq form, 1 is added to the NewPayReqNum field back on that first form, too. Seems like it will work as long as I am the only user, eh?

 

[DCrake]

Ok for now, but consider the future development and rhe consequences of changing the logic down the line. Anticipation is a wonderous gift. If you have it then use it.

David