Run-time error '3265' - Item cannot be found in the collection corresponding to the requested name or ordinal.

[rsbuzatto]

Hello everybody,

I'm developing a VBA application to control some datas from my workmates. I'm using Excel 2007 for everything, however, I need to consolidate those datas into one simples database.

My workmates are gonna fill some userforms and those form datas are transferred to a new sheet in the same workbook. What I need to do now is:

- When the user click in the SAVE BUTTON, those datas will be transferred to the sheet as I'm doing now AND, I need the same datas transferred to an Access 2007 (.accdb) file.

Nevertheless, I've got a problem in my database code.

Take a look below!

1. Sub Transf_BD(Codigo As Variant)
2.    
3.    Dim cnn As ADODB.Connection
4.    Dim rst As ADODB.Recordset
5.    
6.    'Abre a conexão com o Banco de Dados
7.    Set cnn = New ADODB.Connection
8.    
9.    cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" & _
10.                "Data Source=C:\BD_Ideia.accdb;"
11.
12.    'Definir o Recordset
13.    Set rst = New ADODB.Recordset
14.    rst.CursorLocation = adUseServer
15.    
16.    'Abre a tabela do Banco de Dados
17.    rst.Open Source:="tblTransfer", _
18.                      ActiveConnection:=cnn, _
19.                      CursorType:=adOpenDynamic, _
20.                      LockType:=adLockOptimistic, _
21.                      Options:=adCmdTable
22.    
23.    'Adiciona um Registro
24.    rst.AddNew
25.    
26.    'Configura os valores para os campos.
27.    'Os campos são passados do userform.
28.    rst("Codigo") = "TEST"
29.
30.    'Escreva os valores para este registro
31.    rst.Update
32.    
33.    'Fechar
34.    rst.Close
35.    cnn.Close
36    
37.End Sub

In the end of my SAVE FUNCTION I wrote:

Transf_BD (Codigo)

When I run this code, I've got a problem: Run-time error '3265' Item cannot be found in the collection corresponding to the requested name or ordinal. in Line 28.

Please, can anyone help me with this error? I really need to fix it as soon as I can!

EDITING: I'm doing that with only Codigo as Variant (Code as Variant, in English) as a parameter to test my macro. When everything's running clearly, I'll insert other parameters for all of my userforms...

Cheers folks!

 

[RoryA]

it means that whatever data you are trying to save the second time is violating an index or primary key (presumably because one of the key fields contains the same value as a previous record). I don't know what your data is or how your db is set up so I can't suggest anything.

 

[rsbuzatto]

hmm.... I forget to "increment" my Code...

I've defined my PK as my "user code", however, I need to use "user_code_1", "user_code_2", "user_code_3"... and so on... understand?

'cause my PK will be duplicated and it's impossible :P

 

[RoryA]

Glad you figured it out.

 

[rsbuzatto]

Just one more thing dude...

I created another column in my .accdb table and, instead of insert UserNameLog + Counter, I'd rather insert Code column to be a counter (01, 02, 03 and so on for every inserted index) and my UserNameLog will be as it is now...

How can I do this "counter for each index" inserted on my database? Is there any easy configuration to do in Access or I need to do it in my VBA code?


Thanks one more time!

 

[rsbuzatto]

Well... now my application and my database are perfect!

Rorya... thanks very very much for every help dude! Sorry if I bothered you and 'bout my english, as well... hehe!

Many thanks again... I really enjoyed every help on this thread!


Cheers!

 

[RoryA]

My pleasure and, for the record, I think your English is very good!

 

[Sorezz]

Thank you very much this help me to solve the pROBLEMS