Project number increment

[shockerty]

Hi all,

I am setting up a database which includes a table of clients (tblClient) and a table of projects (tblProjects).

tblClients has a field called ClientID which is an integer.
tblProjects has a field called Client that looks up the ClientID from tblClients, linking the project to a client. There is also a field called ProjectID which is also an integer.

In a form designed to input new project info, I am looking for a way to autofill the project number with the next integer in the series once the Client has been chosen from the lookup combo box (called lstClient).

I have attempted some VBA to do this but it just gives 1 as the project number every time.

Private Sub ProjectID_AfterUpdate()

Dim HighestClientProject As Integer

HighestClientProject = DMax("ProjectID", "tblProject", "Client=" & lstClient)

Me = HighestClientProject + 1


End Sub

Effectively this is the kind of client & project number progression I would like to achieve:

Client / Project

1/1
1/2
2/1


2/2
2/3
2/4
3/1
3/2

I hope that makes sense. I am a VBA noob. Can anyone give me a pointer in the right direction?

[burrina]

Try this.

Nz(DMax("ProjectID", "tblProject") + 1)

HTH

[AlexHedley]

Check out this Tip
http://599cd.com/tips/access/increme...r-own-counter/

[June7]

Could try setting DefaulValue property then no VBA needed.

DMax("ProjectID", "tblProject", "Client=" & lstClient)

Is this a multi-user db?

[Missinglinq]

Private Sub ProjectID_AfterUpdate()

  Dim HighestClientProject As Integer

  HighestClientProject = DMax("ProjectID", "tblProject", "Client=" & lstClient)

  Me = HighestClientProject + 1


End Sub

First off, you're trying to populate the ProjectID Control by using the AfterUpdate event of ProjectID! ProjectID_AfterUpdate only fires after data is entered into the ProjectID Control! The code should be in the AfterUpdate event of the Control where the Client is selected; if I'm reading this correctly, that would be the Listbox named lstClient.

Secondly, to assign the generated ID, you're using the line

Me = HighestClientProject + 1

This needs to be

Me.ProjectID = HighestClientProject + 1

so, if this is a single user, free standing database, you can use

Private Sub lstClient_AfterUpdate()

  Dim HighestClientProject As Integer

  HighestClientProject = DMax("ProjectID", "tblProject", "Client=" & lstClient)

  Me.ProjectID = HighestClientProject + 1

End Sub

If, on the other hand, this is a multi-user database, the code must go in the Form_BeforeUpdate event, instead, so that two users, entering a New Record at the same time, won't end up with the same ProjectID for both. Using the DefaultValue Property, on a multi-user database, would present the same problem, as two users could start New Records, simultaneously, and the same ID would be assigned, because the Default Value is assigned when a Record is first started, not at the last moment prior to it being saved.

Linq ;0)>