Restart Numbering (1 Viewer)

[syedadnan]

I have a table with a field OD_Number now in form i want a command button to restart numbering from 1 till so on unless again the command button pressed to start numbering from 1

 

[Ranman256]

Make a query to sort the items in the order you want, then click the button to number them via:

Sub btnGo_click()
Dim rst
Dim I as long
Set rst =currentDb.openrecordset("qsQuery")
With rst
   While  not .eof
        .edit
        .fields("[RowNum]").value = I
        .update

        .movenext
    End with
Set rst = nothing
End sub

 

[syedadnan]

Make a query to sort the items in the order you want, then click the button to number them via:

Sub btnGo_click()
Dim rst
Dim I as long
Set rst =currentDb.openrecordset("qsQuery")
With rst
   While  not .eof
        .edit
        .fields("[RowNum]").value = I
        .update

        .movenext
    End with
Set rst = nothing
End sub

Thanks for the reply!

Sorry i think i missed something during explanation above,

I am having an OPD for in that form i had a field token i want with todays date the token to be populated automaticaly but with below mentioned criteria,

like to increase token number from 1 to so one for one doctor where the doctor's unique id is CID
the increment should be different for different doctors based on cID and when pressed command button all doctors data to be restart from 1

 

[The_Doc_Man]

The method mentioned only works for autonumber on a table. From your description, you are generating this number independently for each doctor.

If you have a compound primary key for this form that includes date, doctor ID, and sequence number, you can AUTOMATICALLY get this using code similar to what I show below, and I am going to omit DIM declarations for brevity.

DocDate = "( [DoctorID] = " & CStr( doctor-ID-goes here ) & ") AND " & _
    "( [SeqDate]=#" & Format( Now(), "dd-MMM-yyyy" ) & "# )"

If DCount( "*", "qsQuery", DocDate ) = 0 Then
    lNewSeqNum = 1
Else
    lNewSeqNum = 1 + DMax( "[SeqNum]", "qsQuery", DocDate ) 
End If

This tells you the next sequence number to use for a given doctor on this date. Then you can use lNewSeqNum in whatever way you need it.

I used "qsQuery" for your source, and if it is bound to a single table, you could do that. Or you could use the underlying table name if that is more convenient. I also just used a generic set of potential field names for dates and doctors. You would of course have to decide what to use for these values in your app.

 

[syedadnan]

The method mentioned only works for autonumber on a table. From your description, you are generating this number independently for each doctor.

If you have a compound primary key for this form that includes date, doctor ID, and sequence number, you can AUTOMATICALLY get this using code similar to what I show below, and I am going to omit DIM declarations for brevity.

DocDate = "( [DoctorID] = " & CStr( doctor-ID-goes here ) & ") AND " & _
    "( [SeqDate]=#" & Format( Now(), "dd-MMM-yyyy" ) & "# )"

If DCount( "*", "qsQuery", DocDate ) = 0 Then
    lNewSeqNum = 1
Else
    lNewSeqNum = 1 + DMax( "[SeqNum]", "qsQuery", DocDate ) 
End If

This tells you the next sequence number to use for a given doctor on this date. Then you can use lNewSeqNum in whatever way you need it.

I used "qsQuery" for your source, and if it is bound to a single table, you could do that. Or you could use the underlying table name if that is more convenient. I also just used a generic set of potential field names for dates and doctors. You would of course have to decide what to use for these values in your app.

First of all thank you very much for your valuable reply,

now i tell you that i exactly required what u mentioned above above i am not getting how and where to use this function i am not very much handy with vba if kindly elloborate this as i am mentioning you my exact field names,

Table TOK
Doctor id field name "DR"
Increment number field is "Tokens"
Date field name is TKDate

i want to increase number automatically if date n Dr id is same for example;

DR=1 TKDate=4/29/2017 then the token should be 1
DR=1 TKDate=4/29/2017 then the token should be 2
DR=1 TKDate=4/29/2017 then the token should be 3

DR=2 TKDate=4/29/2017 then the token should be 1
DR=2 TKDate=4/29/2017 then the token should be 2

DR=1 TKDate=4/30/2017 then the token should be 1
DR=2 TKDate=4/30/2017 then the token should be 1

 

[The_Doc_Man]

DocDate = "( [DR] = " & CStr( DRX ) & ") AND " & _
    "( [TKDate]=#" & Format( TargetDate, "dd-MMM-yyyy" ) & "# )"

If DCount( "*", "TOK", DocDate ) = 0 Then
    Tokens = 1
Else
    Tokens = 1 + DMax( "[Tokens]", "TOK", DocDate ) 
End If

DocDate is a temporary string used because the criteria for several of the tests are all the same, so define it once and use it often. So you would want to DIM it where you defined the other variables in your code.

That reference to DRX is because you need to supply the desired doctor ID in a variable. I used your field names for the domain functions.

As to TargetDate, that has to be the variable holding the date for which you are doing your query. For the thing that makes today's entries, you could use NOW() in place of that variable. However, if you wanted to look at previous day's entries because you needed to back-date an entry, you would put the date string for that date in TargetDate and that would focus on the selected date.

Since you are not sure about VBA, note this fine point: I used a construct that causes the DocDate string to contain a sequence like "#29-Apr-2017#" as part of the string. It DOES NOT MATTER if you have this date field's default format declared differently elsewhere. That construct of using the hash-tag symbol (technically, an octothorpe) surrounding dates will convert a date string to an internal date format for comparing against another date. It is required because you have to set dates apart specially when used in literal strings such as I showed for the domain function criteria clause.

 

[syedadnan]

DocDate = "( [DR] = " & CStr( DRX ) & ") AND " & _
    "( [TKDate]=#" & Format( TargetDate, "dd-MMM-yyyy" ) & "# )"

If DCount( "*", "TOK", DocDate ) = 0 Then
    Tokens = 1
Else
    Tokens = 1 + DMax( "[Tokens]", "TOK", DocDate ) 
End If

DocDate is a temporary string used because the criteria for several of the tests are all the same, so define it once and use it often. So you would want to DIM it where you defined the other variables in your code.

That reference to DRX is because you need to supply the desired doctor ID in a variable. I used your field names for the domain functions.

As to TargetDate, that has to be the variable holding the date for which you are doing your query. For the thing that makes today's entries, you could use NOW() in place of that variable. However, if you wanted to look at previous day's entries because you needed to back-date an entry, you would put the date string for that date in TargetDate and that would focus on the selected date.

Since you are not sure about VBA, note this fine point: I used a construct that causes the DocDate string to contain a sequence like "#29-Apr-2017#" as part of the string. It DOES NOT MATTER if you have this date field's default format declared differently elsewhere. That construct of using the hash-tag symbol (technically, an octothorpe) surrounding dates will convert a date string to an internal date format for comparing against another date. It is required because you have to set dates apart specially when used in literal strings such as I showed for the domain function criteria clause.

Thats Perfect !!! Thanks a billion