How can I access new autonumber value before saving new record? (1 Viewer)

[David Anderson]

I am returning to Access 2003 VBA programming (with DAO rather than ADO) after a gap of a year and my rusty brain needs some assistance.

I am trying to write some code for a form that will add a new record to a table. For complicated reasons I won't bore you with, the Primary Key of this table is a 5-character text field of the format 'P####', where #### is a number with leading zeros derived from an Autonumber field in the same table. For example, if the autonumber value is 345, then the PK would be set to 'P0345'.

My problem is that I need to find out what the next autonumber value will be before I can create the PK and save the new record.

I am certain that there is a very simple answer to this problem, but it escapes me for the moment and I would appreciate some guidance.

David

 

[dcb]

Save the record -> Update the record?
OR
Select Top1 of Increment field add 1?

 

[Rabbie]

I am returning to Access 2003 VBA programming (with DAO rather than ADO) after a gap of a year and my rusty brain needs some assistance.

I am trying to write some code for a form that will add a new record to a table. For complicated reasons I won't bore you with, the Primary Key of this table is a 5-character text field of the format 'P####', where #### is a number with leading zeros derived from an Autonumber field in the same table. For example, if the autonumber value is 345, then the PK would be set to 'P0345'.

My problem is that I need to find out what the next autonumber value will be before I can create the PK and save the new record.

I am certain that there is a very simple answer to this problem, but it escapes me for the moment and I would appreciate some guidance.

David

There is no point in having an autonumber field in a table if it is not beig used as a primary key. It is not guaranteed to be in strict sequence but only to be unique. For your purposes it may be better to have a field you compute uing a DMAX function and increment this each time you use it. This should give you the predictablity you want

 

[ghudson]

Sticky subject for there are potential problems ahead when not using a true autonumber field as the primary key.

What if two users are about to create a new record at the same time and each user has not saved their new record when your function to create the next primary key is called?

 

[David Anderson]

Save the record -> Update the record?
OR
Select Top1 of Increment field add 1?

Hi dcb,
Your answer was too brief for me to understand with any certainty. I'm guessing that your first suggestion is to save the record with some sort of temporary PK and then update it with the desired PK value. Is that correct?

I don't follow your second suggestion. Please clarify.

David

 

[dcb]

Sticky subject for there are potential problems ahead when not using a true autonumber field as the primary key.

What if two users are about to create a new record at the same time and each user has not saved their new record when your function to create the next primary key is called?

Failover code? Try three times?

 

[David Anderson]

There is no point in having an autonumber field in a table if it is not beig used as a primary key. It is not guaranteed to be in strict sequence but only to be unique. For your purposes it may be better to have a field you compute uing a DMAX function and increment this each time you use it. This should give you the predictablity you want

Hi Rabbie,
I don't care about strict sequence but I do care about uniqueness. That was why I was hoping to use the autonumber field as the basis for the PK.

On the other hand, GHudson's point about multiple users may make your DMAX idea a better solution.

David

 

[dcb]

Hi dcb,
Your answer was too brief for me to understand with any certainty. I'm guessing that your first suggestion is to save the record with some sort of temporary PK and then update it with the desired PK value. Is that correct?

I don't follow your second suggestion. Please clarify.

David

My appologies - yes I was suggesting exactly that for the first method - it will also help you with the problem that ghudson refers too.

Second suggestion:
The DMAX function will return the highest key then you can add one - simpler than using a sql select (Thanks Rabbie)

 

[David Anderson]

My appologies - yes I was suggesting exactly that for the first method - it will also help you with the problem that ghudson refers too.

Second suggestion:
The DMAX function will return the highest key then you can add one - simpler than using a sql select (Thanks Rabbie)

Actually, doesn't GHudson's point apply equally to the DMAX idea? I'm beginning to think that your first solution might be the safest.

David

 

[dcb]

Actually, doesn't GHudson's point apply equally to the DMAX idea? I'm beginning to think that your first solution might be the safest.

David

The truly safe way is to allow the access sequencer(sp) to inc the field itself
However I would never store the field as you have done

P0123 = P & 0123 therefore ether save the "P" if it denotes something other than aesthetics, or add it on for display purposes (on your form or report)
so a row would be
Prefix | autonumber(PK)
P | 123

 

[David Anderson]

The truly safe way is to allow the access sequencer(sp) to inc the field itself
However I would never store the field as you have done

P0123 = P & 0123 therefore ether save the "P" if it denotes something other than aesthetics, or add it on for display purposes (on your form or report)
so a row would be
Prefix | autonumber(PK)
P | 123

I was hoping not to need to explain why I have this odd form of PK but I guess the time has come - and, who knows, someone might have a better solution anyway. This Access application combines data entered directly into the database with data derived from a similar online SQL Server database associated with an ASP.NET website. The table in question is a Payments table and both the Access and SQL Server versions of the table originally used an Autonumber field as the PK.

When I came to merge the data for the first time, I realised that there was duplication of PKs as there was no mechanism to ensure that the Autonumber values in Access were different from those in SQL server. My initial solution has been to apply a 'W' prefix for web payments and a 'P' prefix for what are postal payments.

Is there a better way?

David

 

[Khalid_Afridi]

Sticky subject for there are potential problems ahead when not using a true autonumber field as the primary key.

What if two users are about to create a new record at the same time and each user has not saved their new record when your function to create the next primary key is called?

yes its true....

I use only a safe side while two users are creating a new record. I would recommend that:

1) count the records before creating a new ID for specific issue (in my case contract no.)
2) concatenate the prefix (in your case P) to the calculated total records.

This might help:

Sub CreateWONo()
On Error GoTo Err

    If IsNull(Me!txtWONo.Value) Then
        If Not IsNull(Me!cboAccountingString) And Not IsNull(Me!cboLocation) And Not IsNull(Me!cboTypeWO) Then [COLOR="DarkGreen"]'Here are some conditions, but you can omit it in your case[/COLOR]
        
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSQL As String
        
        Set db = CurrentDb
        strSQL = "SELECT count(tblSOF.ContractNo) as TotRec FROM tblSOF WHERE (tblSOF.ContractNo)= '" & Me.cboContractNo & "'"
        
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
        

           [COLOR="Blue"] Me!txtWONo = "P0" & rs!TotRec + 1
            Me!WONo = rs!TotRec + 1
             [/COLOR]
       Set rs = Nothing
      
      
        Else
        Exit Sub
        End If
        
    End If
ExitErr:
    Exit Sub
Err:
    MsgBox Err.DESCRIPTION
    Resume ExitErr
End Sub

 

[dcb]

I was hoping not to need to explain why I have this odd form of PK but I guess the time has come - and, who knows, someone might have a better solution anyway. This Access application combines data entered directly into the database with data derived from a similar online SQL Server database associated with an ASP.NET website. The table in question is a Payments table and both the Access and SQL Server versions of the table originally used an Autonumber field as the PK.

When I came to merge the data for the first time, I realised that there was duplication of PKs as there was no mechanism to ensure that the Autonumber values in Access were different from those in SQL server. My initial solution has been to apply a 'W' prefix for web payments and a 'P' prefix for what are postal payments.

Is there a better way?

David

so you can potentially have a w0123 and a p0123 - so if the w0123 gets the inc from the sql server then your autonumber for that record is no longer w0123 = 123?

I would store the autonumber from the sql server as a seperate column like a Foreign Key ---- column would be indexed no duplicates, required = no

Then use your autonumber field as the PK
You can still have a column denoting W and P however if your FKID column is >0 then it was a web payment

 

[dcb]

yes its true....

I use only a safe side while two users are creating a new record. I would recommend that:

1) count the records before creating a new ID for specific issue (in my case contract no.)

If you delete a record you will have a duplicate using count - Thus the suggestions of using DMAX or TOP1

 

[Khalid_Afridi]

If you delete a record you will have a duplicate using count - Thus the suggestions of using DMAX or TOP1

There is a very little chance even no chance to delete any created record by user (in my case)

 

[David Anderson]

so you can potentially have a w0123 and a p0123 - so if the w0123 gets the inc from the sql server then your autonumber for that record is no longer w0123 = 123?

Yes indeed, there could be a web payment of W0123 and a totally unrelated postal payment of P0123 in the Access version of the Payments table. I didn't understand the rest of your sentence. Please clarify.

I would store the autonumber from the sql server as a seperate column like a Foreign Key ---- column would be indexed no duplicates, required = no

Then use your autonumber field as the PK

Hmmm. That might work though I'll have to think about it a little longer to be certain. The PK column is called PaymentID and your solution would mean that a given web payment would carry a different PaymentID in SQL Server than it would in Access. It would also be the case that a given PaymentID in SQL Server would identify an entirely different transaction in Access, which generates a potential risk of confusion.

David

 

[dcb]

Yes indeed, there could be a web payment of W0123 and a totally unrelated postal payment of P0123 in the Access version of the Payments table. I didn't understand the rest of your sentence. Please clarify.

IE. your PK can be W0123 and you autonumber 172

Hmmm. That might work though I'll have to think about it a little longer to be certain. The PK column is called PaymentID and your solution would mean that a given web payment would carry a different PaymentID in SQL Server than it would in Access.

No Your FKID = SQLS_ID

It would also be the case that a given PaymentID in SQL Server would identify an entirely different transaction in Access, which generates a potential risk of confusion.

As it is the only item indicating the source is the "P" or "W" so carry it in a seperate column

 

[David Anderson]

I think we might be talking at cross purposes about the PaymentID field. New website payments are created only in SQL Server. The PaymentID will always match the SQL Server autonumber field in the same record, i.e. if the autonumber is 7891 then the PaymentID would be W7891 (in SQL Server terminology, an autonumber column is actually called an Identity column but it's essentially the same animal).

Similarly, postal payments are created only in Access and the PaymentID will always match the Access autonumber field in the same record, i.e. if the autonumber is 4567 the PaymentID would be P4567.

The Access version of the Payments table contains both web and postal payments (note that I don't transfer the SQL Server autonumber column to Access). All related forms and reports would present a mixture of web and postal payments and I therefore want to access this data using a common PaymentID column. A separate column to identify web payments does not work in this scenario.

David

 

[David Anderson]

Since some doubt has been cast on the wisdom of my original plan to create a new PK from an autonumber field, I have now raised a new thread to address the core problem of how best to merge the payment data from the website with that on the Access database. Perhaps some better solutions will emerge.....

http://www.access-programmers.co.uk/forums/showthread.php?p=925906#post925906