Projectnummer opbouwen (Automatisch nummeren)

[janrunhart]

Gewaardeerde Medeforumbezoeker,

Ik ben momenteel bezig met het maken van een programma voor het administreren van projecten nu heb ik het volgende:

• tbl_klanten - met hierin het veld opdrachtgevernummer (000)
• tbl_project - met hierin het veld projectnummer.

wat ik graag zou willen is dat bij het aanmaken van een project, het projectnummer automatisch gemaakt wordt al volgt:

Als eerst de twee eindcijfers van het jaar (10)
Dan het opdrachtgevernummer (000-199)
En als laatst het volgnummer

Het geheel zou er dan uit moeten zien als:

1012201


Heeft er iemand enig idee hoe dit aan te pakken? Ik ben al flink aan het surfen geweest maar nog geen goede oplossing gevonden.

Groet, Jan

 

[OctaFish]

Je kunt bijvoorbeeld een functie maken die het laatste projectnummer ophaalt uit de tabel op basis van jaar en klantnummer en het eerstvolgende nummer genereert.

 

[janrunhart]

@ OctaFish

Dank voor je hulp. maar in dat geval dient er al een projectnummer aanwezig te zijn voor de debetreffende klant/opdrachtgever.

Ik zat meer te denken aan een standaardwaarde in het veld projectnummer bij het openen van form!nieuwproject.

Ik heb alleen geen idee hoe dan het laatst aangemaakte nummer op te halen. En hoe de laatste twee cijfers van een jaartal te noteren.

groet, Jan

 

[OctaFish]

De functie waar ik het over heb genereert een nummer. De enige in te voeren waarde is die van het driecijferige klantnummer, en dat nummer heb je altijd tot je beschikking. Bij een eerste project heb je uiteraard nog geen op te zoeken waarde, en begin je (neem ik aan) met [##(jaartal)][Klantnummer(drie cijfers)][01].
Ik ben een voorbeeldje aan het maken om het te verduidelijken.

 

[janrunhart]

Dat klopt. Als het het eerste project van dit jaar betreft met het betreffende projectnummer 122 dan wordt het projectnummer 1012201

Ik kijk uit naar de code.

Alvast bedankt,

 

[OctaFish]

Wat is je werkwijze precies? Maak je bijvoorbeeld een project aan vanuit het opdrachtgever formulier, of heb je een formulier Projecten, waar je eerst een opdrachtgever kiest? In dat geval kun je denk ik niet met een standaardwaarde werken voor het projectnummer, omdat dat afhankelijk is van de opdrachtgever (als ik het goed heb begrepen).

 

[janrunhart]

Ik heb een form nieuw project. In dit formulier wordt de opdrachtgever geselecteerd. A.d.h.v de opdrachtgever is er dus een opdrachtgevernummer bekend. Is er geen manier om via een functie of vba het opdrachtgevernummer dan aan te roepen?

 

[OctaFish]

Daarom wil ik je werkwijze weten... Zoals je het doet is het overigens OK, want a.d.h.v. het Opdrachtgevernummer kan ik nu een functie maken die het eerstvolgende Projectnummer genereert.
Dat ziet er dan ongeveer zo uit:

Private Sub cboCompany_ID_AfterUpdate()
Dim sJaar As String, sKlant As String, sVolgnr As String, sProjectNr As String
Dim strSQL As String

strSQL = "SELECT TOP 1 Right([orderid],2) AS OrderNr" & vbCrLf
strSQL = strSQL & "FROM [transaction]" & vbCrLf
strSQL = strSQL & "WHERE (Left([OrderID], 2) = Format(Date(), 'yy') "
strSQL = strSQL & "And (Mid([OrderID], 3, 3) = " & Me.cboCompany_ID & "))" & vbCrLf
strSQL = strSQL & "GROUP BY Right([orderid],2)" & vbCrLf
strSQL = strSQL & "ORDER BY Right([orderid],2) DESC;"

[COLOR="blue"]If Me.chkNieuw = -1 Then[/COLOR]
    With CurrentDb.OpenRecordset(strSQL)
        If .RecordCount > 0 Then
            sVolgnr = .Fields(0).Value
            .Close
            sVolgnr = Format(CInt(sVolgnr) + 1, "00")
            sJaar = Format(Date, "yy")
            sKlant = Format(Me.cboCompany_ID, "000")
            sProjectNr = sJaar & sKlant & sVolgnr
            Me.OrderID = sProjectNr
            Me.chkNieuw = 0
        End If
    End With
[COLOR="blue"]End If[/COLOR]

End Sub

In het voorbeeld werk ik overigens met een checkbox die controleert of je op een nieuw record zit; bij een bestaand record is het uiteraard niet de bedoeling om het projectnummer te veranderen. De blauwe regels kun je dus verwijderen.

 

[janrunhart]

Ik ga het proberen. Alvast bedankt...

 

[janrunhart]

Ik zit te "knoeien" met de SQL strings:

Ik heb dus een tabel tbl_Projects
hierin moetl het Projectnummer komen te staan en staat ook de klant en is voorzien van een ID met de naam ID

Ik heb een tabel tbl_klant
Hierin staan dus de klant en het Opdrachtgevernummer

Kun je me nog een beetje op weg helpen?

 

[OctaFish]

Het Projectnummer dat je wilt opbouwen komt uit je tabel Projecten, dus tbl_Projects in jouw geval. Op je formulier heb je vermoedelijk een keuzelijst met invoervak gemaakt om een Klantnr te kunnen selecteren. De waarde die je met de keuzelijst ophaalt gebruik je in de SQL om te matchen met het Projectnummer dat je uitleest in de tbl_Projects.
Ik kan verder niet heel goed aangeven wat je verder moet doen, omdat ik dan de veldnamen in de tabellen nodig heb, en de naam van de combobox. Als je een testversie kunt maken van de db, dan wrodt het uiteraard een stuk makkelijker...

 

[janrunhart]

Dit is wat ik nu heb:

Private Sub cboOpdrachtgevernummer_AfterUpdate()
Dim sJaar As String
Dim Klant As String
Dim sVolgnr As String
Dim sProjectNr As String
Dim strSQL As String

strSQL = "SELECT TOP 1 Right([Projectnummer],2)" & vbCrLf
strSQL = strSQL & "FROM [Projects]" & vbCrLf
strSQL = strSQL & "WHERE (Left([Projectnummer], 2) = Format(Date(), 'yy') "
strSQL = strSQL & "And (Mid([Projectnummer], 3, 3) = " & Me.cboOpdrachtgevernummer & "))" & vbCrLf
strSQL = strSQL & "GROUP BY Right([Projectnummer],2)" & vbCrLf
strSQL = strSQL & "ORDER BY Right([Projectnummer],2) DESC;"

    With CurrentDb.OpenRecordset(strSQL)
        If .RecordCount > 0 Then
            sVolgnr = .Fields(0).Value
            .Close
            sVolgnr = Format(CInt(sVolgnr) + 1, "00")
            sJaar = Format(Date, "yy")
            sKlant = Format(Me.cboOpdrachtgevernummer, "000")
            sProjectNr = sJaar & sKlant & sVolgnr
            Me.tbProjectnummer = sProjectNr
            End If
    End With
End Sub

En er wijzigt niets momenteel

 

[OctaFish]

Wat gebeurt er als je stapsgewijs door de code loopt? Ik zou dan een onderbrekingspunt instellen op de With... statement (<F9> drukken als je op die regel staat)

 

[janrunhart]

Hier is een voorbeeld van hetgeen waar ik het in wil gebruiken.

Het lukt me niet om de stappen te doorlopen.


Misschien dat het iets meer zegt zo,

Groet,

 

[OctaFish]

Kun je er ook nog een 2003 variant van maken?

 

[janrunhart]

Hierbij een 2003 variant .MDB

 

[janrunhart]

Kun je er iets mee?

 

[OctaFish]

Zal er nu even naar kijken!

 

[OctaFish]

Ik heb de code een beetje aangepast, zodat hij ook een projectnummer genereert bij een nieuw project voor een klant.

    With CurrentDb.OpenRecordset(strSQL)
        If .RecordCount > 0 Then
            sVolgnr = .Fields(0).Value
            .Close
            sVolgnr = Format(CInt(sVolgnr) + 1, "00")
            sJaar = Format(Date, "yy")
            sKlant = Format(Me.cboOpdrachtgevernummer, "000")
            sProjectNr = sJaar & sKlant & sVolgnr
            Me.tbProjectnummer = sProjectNr
        Else
            sVolgnr = Format(1, "00")
            sJaar = Format(Date, "yy")
            sKlant = Format(Me.cboOpdrachtgevernummer, "000")
            sProjectNr = sJaar & sKlant & sVolgnr
            Me.tbProjectnummer = sProjectNr
        End If
    End With

Ik constateerde wel dat je tabellenstructuur een beetje onlogisch in elkaar zit. Je hebt voor een aantal velden in de tabel [Customers] een validatieregel gemaakt (Is Not Null) maar niet aangegeven dat de velden verplicht zijn. Zowiezo zou ik de validatie op tabelniveau doen, maar goed.
Verder heb je een [Lijst met waarden] gemaakt voor het veld [Opdrachtgevernummer], met een gruwelijk lange lijst met waarden waaruit je kunt kiezen. Daar zie ik het nut eigenlijk ook niet van in. Ook hiervoor geldt, dat je beter de validatie daarvan op tabelniveau kun doen. Plus dat je straks drie cijfers wilt hebben in het projectnummer, en je in de lijst met opdrachtgevernummers begint met getallen met twee cijfers....

Op het formulier moet je ook een aanpassing maken. De lijst met waarden daarop voor het veld cboOpdrachtgevernummer moet je eigenlijk ook vervangen door een query, waarbij je de opdrachtgevernummers uit de tabel Customer haalt. Je wilt immers alleen klanten kunnen kiezen die je al hebt...
Bovendien heb je in de tabel Customer geen sleutelveld gemaakt van het veld Opdrachtgevernummer. Lijkt mij absolute noodzaak... Het sleutelveld dat je nu gebruikt, is eigenlijk waardeloos. (omdat je al een veld Opdrachtgevernummer hebt).

Kortom: er is nog wel wat werk aan de winkel. Ik heb de keuzelijst nu wel werkend, dus hij genereert het juiste projectnummer. Maar door de andere problemen werkt het formulier nog niet optimaal. Ik kan de huidige versie wel posten, maar ik wil ook nog wel even doorprutsen aan het formulier, zodat die foutjes er ook uit zijn straks. Zeg het maar!

 

[janrunhart]

Michel,

Ik sta uiteraard open voor alle hulp m.b.t. de tabellen. Het probleem met het opdrachtgevernummer was ik zelf ook al tegen gekomen. Het probleem is echter wel dat het opdrachtgevernummer niet uniek hoeft te zijn aangezien een klant hetzelfde opdrachtgevernummer kan hebben. opdrachtgevernummer staat gelijk aan bedrijf. Ik had denk ik beter nog een aparte tabel kunnen maken waarin een bedrijf wordt gezet, deze gekoppeld aan het opdrachtgevernummer.