Creating a custom increment number at table level in access or maybe a query?

[d9pierce1]

Hi all,


Is there a way to create a incremental number in the table of access in a specified field? I have spent most of the day reviewing the post and such but cant seem to find what I am seeking?
May not be possible in the table? I have tried many things but cant get it to perform!


I have a append query that updates a table however it does not have the TransCode in it which is a unique number I get from a function and I need that number.
On form it works like clock work but this event does not open a form, when I click a button it fires the append query and updates a table with mismatched dates.


tblTransactions
TransactionID - AutoNumber
TransactionTypeID - Number
TransCode - Number (Which I want to make an incrementNumber like the TransactionID)
Plus more fields....


I use this on the forms to get the TransCode

Code:
Option Compare Database
Option Explicit




'This will return the last autonumber inserted into the table. It will be unique.
'You can format it into something else for display purposes.




Public Function fnHeaderID() As Long
    
    Dim sSql As String
    Dim db As Database
    
    sSql = "INSERT INTO tblUniqueIDs (UniqueDate) Values (Now())"
    Set db = CurrentDb
    
    db.Execute sSql, dbSeeChanges
    fnHeaderID = db.OpenRecordset("SELECT @@IDENTITY")(0)
    Set db = Nothing
End Function

But without a form open, I cant seem to get this number into table? I would actually like to just call that function at table level if possible?
Else, I need to find a way to create it. I tried putting fnHeaderID() in a query but then it changes all of them to the next number in sequence.


Any Ideas would be helpful, I have searched this forum, googled it, and cant find anything except how to change the primary key format and I need the PK besides this code.
Thanks
Dave

[CJ_London]

@@identity relates to the pk. From you description, that is not transcode

[d9pierce1]

The transcode field on forms calls that function and it is saved in table as trans code
Sorry

[Welshgasman]

Normal way is to store the last used and add 1 to that.
However as you are using @@identity, you may as well use the autonumber, providing you never delete a record.

[d9pierce1]

Thanks,
I have been using the @@identity but I cant use that in the table itself? I use the auto number in that table for other things, I need to generate an auto number for this specific field
or figure out how to insert it into my append query as I dont open a form for this to create a record in that table.
Thanks
Dave

[Welshgasman]

So your autonumber is only set on certain conditions?

[Gicu]

How many record in the append query? If you want to use your existing function you will need to loop at some point, either append the records one by one using a CurrentDB.Execute "Select Into..." and use the function in that SQL statement or leave it blank and run a loop with Update statement and use the function in that one.

Cheers,

[d9pierce1]

Hi Vlad
How are you doing these days? Hope great

Where do I begin,,, I take it is not possible to creat an autonumber in table level other than the autonumber PK
So when I run this append query (MIsmatch) it creates a record in the table for my monthly bills due. It may only create
30 records a month. Basically a record of mnthly bills, when due, and so on... so I dont miss any and I have a record of them,
and when they are paid, how much, and so on...
I have a form to post the payments, funds received, and such to record those things. On the form I use your function to get the TransCode
That works awsome, but without using a form with the creation of the bills due, then I cant figure out how to get that number from the function in the table or in the append query.

I thought I could put it in the append query but that didnt work. It ended up changing all the TransCodes to the same number so that was a bust.

I am really lost with this. Maybe run a separate query to run after it and add that next function number to missing fields in the table for the transcode?
I am really not sure, may have to re-think how I did this and try something different. I was so happy when I finally got a query to work to auto create these without
having to fill out a form i forgot about the trans code. When I click a button (PayBills) it opens up a data sheet form with all bills due, I can tic paynow for those I want to
pay and then it opens up form to pay the ones I ticked. I may have to leave that field blank but it would be nice to have the transcode for searches in my search forms.

I even tried to get the TransactionID + 1 witch is PK in that table and set it as default for the TransCode but that didnt work either.
Let me know, no hurry either.

Thanks
Dave

[CJ_London]

I take it is not possible to creat an autonumber in table level other than the autonumber PK

any field can be an autonumber, it doesn't have to be the PK, but you can only have one autonumber field in a table.

However there is another autonumber option called replicationID which is accepted. These are basically GUID's much the same as you will find in global scale systems.

It may be you would want to use this as your PK then a standard long integer autonumber for your trancode. Just be aware that GUID's are 16 bytes v 4 bytes for a long so will impact data storage, particularly of there are a lot of related records.

Probably isn't a solution for you, but thought I would throw it into the mix, see if any of it sticks

[d9pierce1]

thank you, I di think of that but thought I could come up with a simple solution with a simple number
Dave

[CJ_London]

other option is to use a row number function - something like this

Function RowNumber(Optional r As Variant = -1) As Variant
Static X As Long
Static s


    If r = -1 Then
    
        X = 0
    ElseIf s <> r Then
    
        X = X + 1
        s = r
        
    End If
    
    RowNumber = X
    
End Function

Not clear what you are actually trying to do overall or your table structure but a basic select query would be something like

SELECT *, DMax("trancode","destTable")+rownumber([PK]) AS RN
FROM srcTable
WHERE rownumber()=False

which you can convert to an append query.

Two things to note:

1. you must include the criteria to reset the rownumber when the query is run
2. the value passed in the rownumber call needs to be unique from the previous row otherwise the number won't increment. It doesn't have to be a PK, could be a timestamp or some other field.

[d9pierce1]

Thank you all,
I beleive I got it figured out but need to change a lot of my forms and programming
which will take me several days to complete. I will marked this solved and repost something
if this doesnt work for me.
Thanks
Dave