Help with Auto Incremental (1 Viewer)

[lookforsmt]

HI! I have below table where i need to get a auto-number based on date, each time a new date is identified the counter reset starts from 1 onwards in field IDNos

I also have a field SrNo which has PK with Data-type as auto-numbering.

i have found the below code and tried to work with below table. Any help on what i am doing wrong.

Table Name:   Table3
---------------------------------------------------------------
SrNo          EntryDate        OurRef              IDNos
1             20/08/2019       OCC026200810        1
2             20/08/2019       OCC026200810        2
3             20/08/2019       OCC026200810        3
4             21/08/2019       OCC026210810        1
5             21/08/2019       OCC026210810        2
7             22/08/2019       OCC026220810        1
9             24/08/2019       OCC026240810        1

Code found on the internet

Public Function RowNumber(OurRef As String, _
                          EntryDate As Date, _
                      qry As String) As Long
Dim rst As DAO.Recordset
Dim lngVA As Long
On Error Resume Next
    
    Set rst = CurrentDb.OpenRecordset(qry, dbOpenDynaset)
    With rst

   .FindFirst "[" & OurRef & "] = #" & EntryDate & "#"
         
         lngVA = .AbsolutePosition + 1
    End With
        
RowNumber = lngVA
End Function

In the query i have placed the below expression but i am getting below result

IDNos: RowNumber("SrNo",[SrNo],"Query1")

Result in Query1

IDNos
---------
1
2
1
1
1
1
1

i I will be using the query1 linked to a subform for data entry
Any help pls., Thanks

 

[theDBguy]

Hi. Can you post the SQL statement for Query1? Does it have an ORDER BY clause? For something like this, you really don’t need to use code.

 

[arnelgp]

first of all you are passing SrNo to the function while the function is treating it as date (# Entry Date #).
it is ok to pass the SrNo, since this is the one you want to work with.
just modify the function:

Public Function RowNumber(OurRef As String, _
                            ourRefValue As Long, _
                            dteFieldName As String, _
                            qry As String) As Long
Dim rst As DAO.Recordset
Dim lngVA As Long
Dim dte As Date
On Error Resume Next
    
    If InStr(1, OurRef, "[") = 0 Then OurRef = "[" & OurRef & "]"
    
    Set rst = CurrentDb.OpenRecordset(qry, dbOpenDynaset)
    With rst
    
        .FindFirst OurRef & " = " & ourRefValue
        If Not .NoMatch Then
            dte = .Fields(dteFieldName).value
            Do While Not .BOF
                If .Fields(dteFieldName) <> dte Then
                    Exit Do
                End If
                lngVA = lngVA + 1
                .MovePrevious
            Loop
        End If
    End With
    
    RowNumber = lngVA
End Function

now change the way you call the function:

IDNos: RowNumber("SrNo", [SrNo], "EntryDate", "Query1")

 

[lookforsmt]

Thanks Arnelgp, for the function code. I did as mentioned. It gives me result as 0 for all the rows in query1

 

[arnelgp]

what is the SQL of Query1, should be sorted by SrNo, and EntryDate.

 

[lookforsmt]

SELECT RowNumber("SrNo",[SrNo],"EntryDate","Query1) AS IDNos
FROM Table1;

 

[lookforsmt]

sorry if missed any punctuation cause sending through mobile

 

[arnelgp]

insert it inside Query1 and use Query1 as the recordsource of your form.

Query1:

select SrNo, EntryDate, OurRefNo,
RowNumber("SrNo",[SrNo],"EntryDate","Query1") AS IDNos
From Table1 Order by SrNo, EntryDate;

 

[lookforsmt]

Thankyou very much Arnelgp for your support. Yes it is working fine now thank you very much

 

[arnelgp]

you need to change the code to this:

    Set rst = CurrentDb.OpenRecordset("select " & OurRef & ", " & _
                                        dteFieldName & " from " & qry & " Order by " & _
                                        dteFieldName & ", " & OurRef, dbOpenDynaset)

reverse also the sort order:

select SrNo, EntryDate, OurRefNo, 
RowNumber("SrNo",[SrNo],"EntryDate","Query1") AS IDNos
From Table1 Order by EntryDate, SrNo;

 

[lookforsmt]

Thanks Arnelgp, i missed to thank you. I have marked this thread as solved.