[AccessD] (no subject)

Well, thanks, Joe.  I'll keep it in the kit.  I have a feeling I haven't
heard the last of this one.

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe O'Connell
Sent: Monday, December 14, 2009 8:24 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] (no subject)

Rocky,

It is indeed very strange.  The important thing is that it is fixed and now
working properly.

Although you no longer need  the fix, here is the code that I have used to
reset the seed for the autonumber field.

Public Sub FixEmpID()
    Dim lngSeed As Long
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT Max(tblEmployee.atnEmpID) AS MaxOfatnEmpID FROM
tblEmployee;"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    lngSeed = rs("MaxOfatnEmpID") + 1
    rs.Close
    Set rs = Nothing
    strSQL = "ALTER TABLE tblEmployee ALTER COLUMN atnEmpID COUNTER(" &
lngSeed & ",1)"
    CurrentDb.Execute strSQL
    strSQL = ""
End Sub

Joe O'Connell


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, December 14, 2009 4:32 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] (no subject)

Oddly, adding the record manually seemed to have 'fixed' the problem.
Odd. 

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, December 14, 2009 1:21 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] (no subject)

Joe:

I added a record manually with the same data as the preceding record - no
problem there. The autonumber field incremented normally.  But I could try
your suggestion.  How do you reset the autonumber field to be one greater
than the highest value already in use?

TIA

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe O'Connell
Sent: Monday, December 14, 2009 12:30 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] (no subject)

Rocky,

If there is an autonumber field in the table, try to open the table and add
a record manually.  Does the autonumber field try to use a value that is
already in the table?  I have seen this happen a few times.  The solution is
to reset the initial value for the autonumber field to be one greater than
the highest value already in use.

Joe O'Connell

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, December 14, 2009 2:47 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] (no subject)

Dear List:

 

I am getting an error 3022 - "changes you requested were not successful
because they would product duplicate values..." etc. when the following code
gets to the .Update line.

Dim rstCOCITemp As DAO.Recordset

'add to check-out/check-in table the number of times it's in quantity

Set rstCOCITemp = db.OpenRecordset("Select * FROM tblCheckOutCheckIn") With
rstCOCITemp
    
    .AddNew
    !fldCOCIRADID = argRADID
    !fldCOCIInventoryID = argPackageID
    !fldCOCIInventoryItemID = Null
    !fldCOCICheckOut = Null
    !fldCOCICheckIn = Null
    .Update


fldCOCIRADID, fldCOCIInventoryID, fldCOCIInventoryItemID, are all indexed,
duplicates OK, default value zero.  db is set to Currentdb.

Any ideas why this chunk of code should generate an error 3022?

MTIA

 

Rocky Smolin

Beach Access Software

858-259-4334

www.e-z-mrp.com <http://www.e-z-mrp.com/> 

www.bchacc.com <http://www.bchacc.com/> 

 

 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com