Automatisk nyt nummer afhængigt af produkttype

eksempel på DMax:

http://www.eksperten.dk/spm/555296

I stedet for en If ... Then, er det nok mere gennemskueligt at bruge en Select Case struktur:

Select Case Dincombo
Case is = 4200
Me.ordrenr = og så din DMax
Case is = 4500
Me.ordrenr = Ny DMax
End Select

Jeg har aldrig prøvet at kombinere en dmax med et kriterie, så det skal lige prøves.

I think you need to look a bit more closely at your logic!

You need to add 1 to the Highest value within the product Group which I think needs to be an interval.

Example:

ordrnr = Dmax("OrdreNr", "tblOrdre", "OrdreNr >= 4200 AND OrdreNr <= 4300")

Ja naturligvis skal det være et interval som terry skriver.

Oops! Need to add 1 to the number

ordrnr = Dmax("OrdreNr", "tblOrdre", "OrdreNr >= 4200 AND OrdreNr <= 4300") +1

And you also need to make sure that the value returned doesnt go into the next inteval.

Terry: kan du få tallet 4301 med din kode?
ordrnr = Dmax("OrdreNr", "tblOrdre", "OrdreNr >= 4200 AND OrdreNr <= 4300") +1???


ordrnr = Dmax("OrdreNr", "tblOrdre", "OrdreNr >= 4200 AND OrdreNr <= 4298") +1 bør give den maksimale værdi.

Ja det ser ud som noget rigtigt, men jeg har brug for et mere detaljeret svar. Jeg er rimelig til Access, men ikke så stiv i VBA. Her er koden bag event proceduren on click bag cmdknappen. Skal ordrenr declareres i en variabel først og hvordan?
Private Sub CmdButtonOpret_Click() 
On Error GoTo Err_CmdButtonOpret_Click
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FrmOrdreMeddelOver"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
   
    If Forms!FrmOpretNy!Combo13.Value = "40" Then
      Forms!FrmOrdreMeddelOver![Projektnr].Value = [Projektnr] + 1000
      Forms!FrmOrdreMeddelOver![Afd].Value = "40"
     
    ElseIf Forms!FrmOpretNy!Combo13.Value = "42" Then
      Forms!FrmOrdreMeddelOver![Projektnr].Value = [Projektnr] + 4500
      Forms!FrmOrdreMeddelOver![Afd].Value = "42"
   
    End If

Exit_CmdButtonOpret_Click:
    Exit Sub

Err_CmdButtonOpret_Click:
    MsgBox Err.Description
    Resume Exit_CmdButtonOpret_Click
   
End Sub

rbj_fp. I'm aware of the problem!
"And you also need to make sure that the value returned doesnt go into the next inteval."
and I should have also said:
and that the the value doesnt already exist, which it can using your example too

trineran> I'm not quite sure what the code is doing! Waht are the fields [Projektnr] and [Afd] ?

If the user is picking a departmenttype or produkttype (Afd) which is f.ex. "40" the ordernr. will start at 1000. Forms!FrmOrdreMeddelOver![Projektnr].Value = [Projektnr] + 1000
This works fine, but I can't get it to add 1 to the ordernr. Furthermore I need to save the choice "40" in the table Orders, which is what the next line does (Forms!FrmOrdreMeddelOver![Afd].Value = "40")This I know is a clumsy solutions, but it works. My biggest problem is that I can't get it to add 1 to projektnr and save it in the table. Hope this makes sense

You can try this (NOT TESTED) although I am still not sure where ordrenr is used!
If you cant get it to work then I think it would be best that I take a look at the dB.

    Select Case me.combo13

'You need to alter the maximum values for each inteval so that it doesnt go into the next inteval when you add 1
        Case 40
            Ordrenr = Nz(DMax("OrdreNr", "tblOrdre", "OrdreNr >= 999 AND OrdreNr <= 4498"), 999) + 1
           
        Case 42
            Ordrenr = Nz(DMax("OrdreNr", "tblOrdre", "OrdreNr >= 4499 AND OrdreNr <= 9998"), 4499) + 1
   
        Case Else
            MsgBox "Choose a valid departmenttype ..."
            Exit Sub

    End Select
   
    If DCount("OrdreNr", "tblOrdre", "OrdreNr = " & Ordrenr) > 0 Then
        MsgBox "OrdreNr exists ...."
    Else
        Forms!FrmOrdreMeddelOver![Projektnr].Value = Ordrenr
        Forms!FrmOrdreMeddelOver![Afd].Value = Me.combo13
    End If

and you also need to alter the field/table names in the Dmax
OrdreNr = Field
tblOrdre = table

I've now given it a bit of a test and I think this is better

    Select Case Me.combo13

'You need to alter the maximum values for each inteval so that it doesnt go into the next inteval when you add 1
        Case 40
            Ordrenr = Nz(DMax("OrdreNr", "tblOrdre", "OrdreNr >= 999 AND OrdreNr <= 4499"), 999)
           
        Case 42
            Ordrenr = Nz(DMax("OrdreNr", "tblOrdre", "OrdreNr >= 4499 AND OrdreNr <= 9999"), 4499)
   
        Case Else
            MsgBox "Choose a valid departmenttype ..."
            Exit Sub

    End Select
   
    If DCount("OrdreNr", "tblOrdre", "OrdreNr = " & Ordrenr) > 0 Then
        MsgBox "OrdreNr exists ...."
    Else
       
        Forms!FrmOrdreMeddelOver![Projektnr].Value = Ordrenr + 1
        Forms!FrmOrdreMeddelOver![Afd].Value = Me.combo13
    End If

can you use this trinerafn?

does it work?

and thanks for the points

To "Terry" Yes thank you very much. I've been doing some alterations today on the database. Amongst others I've moved the product type choice to the order form, and put some of your code in the on exit event procedure. Maybe that should have been in the on change procedure. I've only reached to try it on the choice of "40" - Ordrenr = Nz(DMax("OrdreNr", "tblOrdre", "OrdreNr >= 999 AND OrdreNr <= 4499"), 999), but it worked nicely. By the way isn't it right that the Case sentences are best when you're dealing with Integers or Number fields. That is what I seem to remember - my [Afd] field is now a text field in the TblType related to the TblOrder, but I suppose I should change it. I've tryed to give you and "Mugs" some points, but please comment if I've failed to do so.(I'm new at using this Experten site) Best regards and thank you for your quick replyes.

I think your right about using numbers in case sentences, although I'm sure you can use character values too. I myslef almost always try and use numbers.

Yes it went fine in sharing the points. (maybe too many to mugs :o) )
Have a nice evening, once again tak!