vba - Different AutoNumbers to specific selection

Question

I need some vba help at my Access project.

In our company we got different construction sites. Each construction site have their own cost centre. We have to make reports to each construction sites related to the cost centre. These reports have their own continious number(related to the cost centres)

I got a formular, where I can choose the cost centre and want to be shown the latest report number +1, when making a new input, so no mistakes can happen for users.

Formular looks like this

Nr. is related to the textfield Nummer with the content raumBTBNR for the increasing Report numbers. Kostenstelle got the combo box called KostenstelleAuswahl with the content raeumKostenstelleIDRef for the cost centres.

Code so far: (credits by Gustav)

Private Sub Nummer_GotFocus()
Dim NextNumber As Long

                   highest Rep.Nr.        related table      Content of KostenstelleAuswahl
NextNumber = DMax("[raumBTBNR]",        "[tbl_Raeumstellen]", "[KostenstellenIDRef] = " & Me!KostenstelleAuswahl.Value & "") + 1
Me!Nummer = NextNumber

End Sub

My solution right now is a query, where i count the number of the reports, related to the cost centres. But it does not fill automatically and is more worse than nice. Which is saved at MaxNr Example in my form

I would appreciate some help!:)

标签： vbams-access

Answer 1

OK, then this should work - when selecting Kostenstelle:

Private Sub KostenstelleAuswahl_AfterUpdate()

    Dim NextNumber As Long

    NextNumber = DMax("[raumBTBNR]", "[tbl_Raeumstellen]", "[raeumKostenstelleIDRef] = " & Me!KostenstelleAuswahl.Value & "") + 1

    Me!Nr.Value = NextNumber

End Sub