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]

Nevermind - just checked and you called your type 'Parametros', so that is what you should be using instead of 'Parameters'.

 

[rsbuzatto]

I've replaced 'Parametros' to 'Parameters' as indicates to me but the error is the same...

However, if I use 'Parametros' in both Subs, the VBA tells me that I need to define this type... weird...

 

[RoryA]

Where did you put the Type definition?

 

[rsbuzatto]

Sorry dude... as I told you before... I've never created an UDT!

I 'googled' and found some definitions 'bout it... however, the body of my UDT will be the body of my Sub Executa_Transf.

Isn't it?

PS: I was understanding and manipulating my code successfully... now I'm a little bit lost :| (hahaha)... this access database are freaking me out! it's seems to be simple, except when you're doing for the 1st time... :X


Thanks guy...

 

[RoryA]

well, if it makes life easier, skip the UDT and just pass all 18 arguments to the sub instead.

 

[rsbuzatto]

Hey dude...

No... i'd like to learn how to use UDT as well as simplifying my code... :\

Can you help me with the declaration as so on... please?


Thanks man!

 

[RoryA]

Went back and looked at the code again - noticed you declared it private. It should be (in a normal module):

Public Type Parametros
 
    Codigo As Variant           '01 - Código
    DataCadastro As Variant     '02 - Data de Cadastro
    DataEnvio As Variant        '03 - Data de Envio
    NomeArqEnviado As Variant   '04 - Nome do Arquivo Enviado
    TituloIdeia As Variant      '05 - Título da Idéia
    DescIdeia As Variant        '06 - Descrição da Idéia
    ObjIdeia As Variant         '07 - Objetivo da Idéia
    TemaEstrat As Variant       '08 - Tema Estratégico
    WhyImplem As Variant        '09 - Por que Implementar?
    PossBeneficios As Variant   '10 - Possíveis Benefícios?
    QtdPessoas As Variant       '11 - Quantidade de Pessoas
    MatEquipam As Variant       '12 - Materiais e/ou Equipamentos
    CustoEstimado As Variant    '13 - Custo Estimado (R$)
    OndeImplem As Variant       '14 - Onde Implementar?
    AreaGerencia As Variant     '15 - Área/Gerência
    IdeiaPodeGEPDP As Variant   '16 - Idéia pode ser Implementada pela GEPDP?
    Envolvidos As Variant       '17 - Envolvidos?
    SupDireto As Variant        '18 - Superior Direto
 
End Type

 

[rsbuzatto]

well.... lemme see if I get it!

I need to declare my PUBLIC TYPE PARAMETROS:

Public Type Parametros
 
    Codigo As Variant           '01 - Código
    DataCadastro As Variant     '02 - Data de Cadastro
    DataEnvio As Variant        '03 - Data de Envio
    NomeArqEnviado As Variant   '04 - Nome do Arquivo Enviado
    TituloIdeia As Variant      '05 - Título da Idéia
    DescIdeia As Variant        '06 - Descrição da Idéia
    ObjIdeia As Variant         '07 - Objetivo da Idéia
    TemaEstrat As Variant       '08 - Tema Estratégico
    WhyImplem As Variant        '09 - Por que Implementar?
    PossBeneficios As Variant   '10 - Possíveis Benefícios?
    QtdPessoas As Variant       '11 - Quantidade de Pessoas
    MatEquipam As Variant       '12 - Materiais e/ou Equipamentos
    CustoEstimado As Variant    '13 - Custo Estimado (R$)
    OndeImplem As Variant       '14 - Onde Implementar?
    AreaGerencia As Variant     '15 - Área/Gerência
    IdeiaPodeGEPDP As Variant   '16 - Idéia pode ser Implementada pela GEPDP?
    Envolvidos As Variant       '17 - Envolvidos?
    SupDireto As Variant        '18 - Superior Direto
 
End Type

After that, I need to declare my SUBS:

'Main Sub: Execute my Arguments to pass 'em to my Insertion Sub
Sub Executa_Transf()
    
Dim pm As Parameters
With pm
     .TituloIdeia = tbTitulo.Value
     (and so on...)
End With

Inserir_BD pm

End Sub

And finally:

'Insertion Sub: Insert new data into my Database
Sub Inserir_BD(pm As Parameters)

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
    
'Abre a conexão com o Banco de Dados
Set cnn = New ADODB.Connection
    
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" & _
            "Data Source=C:\BD_Ideia.accdb;"
    
'Definir o Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
    
'Abre a tabela do Banco de Dados
rst.Open Source:="tblTransfer", _
                  ActiveConnection:=cnn, _
                  CursorType:=adOpenDynamic, _
                  LockType:=adLockOptimistic, _
                  Options:=adCmdTable
    
'Adiciona um Registro
rst.AddNew

rst("Título da Idéia") = pm.TituloIdeia
(and so on...)

rst.Update
rst.Close
cnn.Close

End Sub

Just it?

Thanks!

 

[RoryA]

almost:

Sub Executa_Transf()      
Dim pm As Parametros

and

Sub Inserir_BD(pm As Parametros)

 

[rsbuzatto]

Hey bro...

Now seems to be or almost be GREAT!

The datas are transferred perfectly to my .accdb file... however, when I try to SAVE another data (suppose to be the 2nd line on my Access Table) another error appears to me...

Runtime error '-2147217887 (80040e21)' The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

So... I guess my Access file didn't "skip" the line and are trying to replace my 1st saved line there... isn't it?

Is there anything I need to configure on my .accdb file or on my Insertion Code? oO

Any idea?


thanks...