Adding sequental number with DMax.

[MarcelWoltex]

I'm trying to create a query where rows in a table which were newly added with an addquery get assigned an article number depending on the highest number that exists in the table.


The new rows were given TOEVOEGEN value as fields Actie and Artikelnr to as a temp value so I can recognise them with a query and let the query edit only them.

I managed to create the code below. Unfortunatly it doesn't work how its supposed to and gives all the new rows the same number.
It appears if it only looks up all values first and then adds all the values to the table.

I think I need to add one of these 2 methods:
1. Make the query look up the value again for each record it adds. So that DMax also finds the value added in the previous record by the same query.
2. Add a counter instead of the +1 that counts up for each record it edits. So that it adds 1 for the first record it edits, adds 2 for the second 3 for the third and so on.

My knowledge of Acces formulas and MySQL however is very limited so I don't know how to do this.

Any help would be greatly appreciated.

UPDATE artikelen_nieuw SET artikelen_nieuw.Artikelnr = (DMax("[Artikelnr]","[artikelen_nieuw]","IsNumeric([Artikelnr])")+1), artikelen_nieuw.Actie = "TOEVOEGEN"
WHERE (((artikelen_nieuw.Artikelnr)="TOEVOEGEN") AND ((artikelen_nieuw.Actie)="TOEVOEGEN"));

[burrina]

An example of getting the previous value would be:

Me.ControlName = Me.ControlName.OldValue

Maybe this can get you started.

[MarcelWoltex]

I've managed to get it to work by adding a few VB scripts.


Option Explicit
Global RecordNum
Function DoesIncrement(AnyValue)
RecordNum = RecordNum + 1
DoesIncrement = RecordNum
End Function
Function ShouldIncrement()
RecordNum = RecordNum + 1
ShouldIncrement = RecordNum
End Function
Function ResetRecordNum(AnyValue)
RecordNum = 0
ResetRecordNum = 0
End Function



This query worked.
UPDATE artikelen_nieuw SET artikelen_nieuw.Artikelnr = ResetRecordNum(0)+((DoesIncrement([Artikelnr]))+(DMax("[Artikelnr]";"[artikelen_nieuw]";"IsNumeric([Artikelnr])"))), artikelen_nieuw.Actie = "TOEGEVOEGD"
WHERE (((artikelen_nieuw.Artikelnr)="TOEVOEGEN") AND ((artikelen_nieuw.Actie)="TOEVOEGEN"));

Problem was that it counted up from some values that were not supposed to be articles but special values. So everything with a value higher then 2147400000 should be ignored.

So I changed it to this but that got a conversion of type error:
UPDATE artikelen_nieuw SET artikelen_nieuw.Artikelnr = ResetRecordNum(0)+((DoesIncrement([Artikelnr]))+(DMax("[Artikelnr]","[artikelen_nieuw]","2147400000>(IsNumeric([Artikelnr]))"))), artikelen_nieuw.Actie = "TOEGEVOEGD"
WHERE (((artikelen_nieuw.Artikelnr)="TOEVOEGEN") AND ((artikelen_nieuw.Actie)="TOEVOEGEN"));

Any ideas why?


I want DMax to search for the highest numeric value in Artikelnr which is lower then 2147400000 (as the values that high are reserved for special values).

[ssanfu]

Any ideas why?

An update query selects all the records that meet the criteria and changes the field(s) at one time. There is no looping, The value is calculated once - all records get the same number.

It appears that "Artikelnr" is a text field?
I threw together some code.... not sure that it will work because I am not sure what the field types are.

I am not sure how you were executing the query, but try this on a copy of the dB:

Option Compare Database  '<-should be at the top of every module
Option Explicit          '<-should be at the top of every module

Public Sub UpdateArtikelnr()
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim MaxNum As Long
    Dim sSQL As String
    Dim RC As Long

    Set d = CurrentDb

    'intialize max number
    MaxNum = 0

    'get max Artikelnr number - convert from text to Long
    sSQL = "SELECT Max(CLng(artikelen_nieuw.Artikelnr)) AS MaxArtikelnr"
    sSQL = sSQL & " FROM artikelen_nieuw"
    Set r = d.OpenRecordset(sSQL)

    If Not r.BOF And Not r.EOF Then
        MaxNum = r.Fields(0)
    End If
    r.Close

    'get the records to update
    sSQL = "SELECT artikelen_nieuw.Artikelnr, artikelen_nieuw.Actie"
    sSQL = sSQL & " FROM artikelen_nieuw"
    sSQL = sSQL & " WHERE (((artikelen_nieuw.Artikelnr) ='TOEVOEGEN') AND ((artikelen_nieuw.Actie) = 'TOEVOEGEN'));"
    '    Debug.Print sSQL
    Set r = d.OpenRecordset(sSQL)

    If r.BOF And r.EOF Then
        MsgBox "No records found"
    Else
        r.MoveLast
        'get number of records
        RC = r.RecordCount
        r.MoveFirst

        MsgBox "Updating " & RC & " records" & vbNewLine & vbNewLine & "Current Max number is " & MaxNum

        Do Until r.EOF
            MaxNum = MaxNum + 1
            r.Edit
            r("Artikelnr") = CStr(MaxNum)
            r("Actie") = "TOEGEVOEGD"
            r.Update

            r.MoveNext
        Loop
    End If

    On Error Resume Next
    'done - now clean up
    r.Close
    Set r = Nothing
    Set d = Nothing

    MsgBox "Done"

End Sub

[MarcelWoltex]

It is correct that Artkelnr is a text field. But DMAX doesn't seem to bother when selecting the maximum value, not is this a problem when adding up.

This code does work and it doesn't seem to bother DMAX that its a text field. I added the IsNumeric so that it would only select numbers (otherwise it would take the tempory text TOEVOEGEN (which I use to determine if it has been newly added) as the highest value).
UPDATE artikelen_nieuw SET artikelen_nieuw.Artikelnr = ResetRecordNum(0)+((DoesIncrement([Artikelnr]))+(DMax("[Artikelnr]";"[artikelen_nieuw]";"IsNumeric([Artikelnr])"))), artikelen_nieuw.Actie = "TOEGEVOEGD"
WHERE (((artikelen_nieuw.Artikelnr)="TOEVOEGEN") AND ((artikelen_nieuw.Actie)="TOEVOEGEN"));

The only problem I ran into that that largest numbers in the database are reserved for special values and all the new article numbers should be numbers below 2147400000.
So I ned DMAX to get the highest number smaller then 2147400000.

So I changed it to this which doesn't work:
UPDATE artikelen_nieuw SET artikelen_nieuw.Artikelnr = ResetRecordNum(0)+((DoesIncrement([Artikelnr]))+(DMax("[Artikelnr]","[artikelen_nieuw]","2147400000>(IsNumeric([Artikelnr]))"))), artikelen_nieuw.Actie = "TOEGEVOEGD"
WHERE (((artikelen_nieuw.Artikelnr)="TOEVOEGEN") AND ((artikelen_nieuw.Actie)="TOEVOEGEN"));

This one doesn't work either, even when I remove all values from the field Artikelnr which are not numbers.
ResetRecordNum(0)+((DoesIncrement([Artikelnr]))+(DMax("[Artikelnr]";"[artikelen_nieuw]";"2147400000>(CLng([Artikelnr]))")))
It simply gives an unknown error. Very helpfull Access.