Run-time error 3022 with .AddNew

[Michael.K]

Hi all,

I have a problem with something that I thought would be very simple and trivial.

I have a table with three fields: an Autonumber field, a date field and a text field. I also have a form with a command button to add a new record. When I add the record for the first time it works fine. If/when I try add another record(s) it gives me run-time error 3022 (no duplicates allowed).

Here is my code:

Private Sub cmdGetInMod_Click()
Dim intModID As Integer
Dim strTable as string
 
strTable = "tbl_TEST"
intModID = getInModID(strTable)
 
end Sub
 
Private Function getInModID(strTable As String) As Integer
Dim tempDB As DAO.Database
Dim tempRST As DAO.Recordset
 
Dim strSQL As String
Dim strUsername As String
Dim strDate As String
 
strUsername = Environ("username")
strDate = Date
 
strSQL = "SELECT * FROM " & strTable & ";"
Set tempDB = CurrentDb()
Set tempRST = tempDB.OpenRecordset(strSQL, dbOpenDynaset)
    With tempRST
        .AddNew
        .Fields(1).Value = strDate
        .Fields(2).Value = strUsername
        .Update '<= this is what gives error message
        .bookmark = .LastModified
        getInModID = .Fields(0).Value
        .Close
    End With
 
Set tempRST = Nothing
Set tempDB = Nothing
End Function

Other words, I am trying to add a new record and get its autonumber ID.

When I open my table I can only see one line. However if I delete that line and add another "first" record I can see that the Autonumber field is actually changed, so my code is working (just not the way I want it to... ).

Any help or advice will be appreciated.

Regards,
Michael

 

[vbaInet]

Hello there,

1. How many fields do you have as the Primary Keys?
2. Did you set the INDEXED property of some fields to Yes (No Duplicates)?

 

[Michael.K]

Hi

Hello there,

1. How many fields do you have as the Primary Keys?

Just one, the first field, which is set to Autonumber.

2. Did you set the INDEXED property of some fields to Yes (No Duplicates)?

Yes, again this is the first field. I think Access automatically sets the indexed property to Yes(No Duplicates) for the primary key fields.

I have pretty much the same code for adding new data to other tables that also have autonumbers/primary keys. The only difference with this procedure is that I need to know the Autonumber ID at the time of writing the new data.

 

[vbaInet]

Are there any other fields in the table and are there any fields that have their Default Value properties set plus a Yes (No Duplicates) index?

 

[Michael.K]

Hi,

Are there any other fields in the table

Actually, yes there is one more number field in the table. I plan to set its value with an update query at the very end of the procedure. It will hold the ID for the record that will be created as a result of the first record.

Here is the full logic: I want to create a record with an autonumber, date and username. Then I will take that autonumber ID and append it to a string, other words if the autonumber is 100, my string will be "XY-0100" and so on.

I will then take this "XY-0100" value and create another record in my main table. At the end of the procedure I will take the autonumber ID for "XY-0100" and write it back to my first table, which will serve as an audit table.

and are there any fields that have their Default Value properties set

No.

plus a Yes (No Duplicates) index?

No again. All fields (with exception of the autonumber, of course) are not required and not indexed. Autonumber is the only field that is indexed and set as required.

Could it be that use of function instead of sub is the reason for the error? That is what I want to try next: I will change the function into a sub and create an integer that will get its value at the end of the procedure. Maybe this will fix it...

 

[vbaInet]

A sub and a function are literally the same except that the latter returns a function and the former doesn't. So that wouldn't solve the problem.

Can you post a stripped down version your db so I can have a quick look.

 

[boblarson]

A sub and a function are literally the same except that the latter returns a function and the former doesn't. So that wouldn't solve the problem.

??? slight typo there

the latter returns a function?

the latter returns a VALUE (perhaps and only if the function is set to do so).

 

[Khalid_Afridi]

bob! function not always returns a VALUE so former and latter typo has no meaning

 

[vbaInet]

??? slight typo there

the latter returns a function?

Haha!! Quite a slight typo there

...(perhaps and only if the function is set to do so).

True and this would depend on how it's been dimensioned. For Variants yes, isn't initialiased hence it will be "empty". Booleans, Strings, Numbers a different story there, will return false, vbNullString, 0.

 

[boblarson]

True and this would depend on how it's been dimensioned. For Variants yes, isn't initialiased hence it will be "empty". Booleans, Strings, Numbers a different story there, will return false, vbNullString, 0.

Well, what I was getting at is that

Function MyFunction()

will not return a value where

Function MyFunction() As Something

will.

 

[vbaInet]

Got ya Bob!

 

[Michael.K]

SOLVED: Run-time error 3022 with .AddNew

Hi vbaInet!

Can you post a stripped down version your db so I can have a quick look.

Thanks for the offer, I appreciate it.

I have just noticed that the last number field was set to Indexed(No Duplicates), which wasn't a particularly smart idea...

Once I changed to Not Indexed and Not required everything works like a charm.

Thanks everyone!

 

[vbaInet]

We would have solved this problem since post #2 Michael

But glad you eventually noticed the problem.