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!

 

[rsbuzatto]

For Gods Sake! I got it...

However, my problem now is... how can I get the userforms datas and transfer them to my ".accdb" file?

For example:

(below Line 28 in my code)
rst("Título da Idéia") = tbTitulo.Value
rst("Qual é a Idéia") = tbDescricao.Value
rst("Qual o objetivo da Idéia?") = tbobjetivo.Value

When I do it, the following error appears to me:

Run-time error '424' Object Required

I guess my tbTitulo.Value and so on cannot be used as I'm using... any help?


Cheers...

 

[RoryA]

pass the values as arguments to your routine (as you did with codigo) instead of referring to the controls inside the routine.

 

[rsbuzatto]

pass the values as arguments to your routine (as you did with codigo) instead of referring to the controls inside the routine.

Hey Rorya,

I have 18 fields (18 columns on my Access table) to transfer from my userforms to my database file. Should I pass the whole 18 values as argument and forget 'bout (delete) the code part below?

'rst("Título da Idéia") = tbTitulo.Value
'rst("Descrição da Idéia") = tbDescricao.Value
'rst("Objetivo da Idéia") = tbobjetivo.Value
(etc...)

Thanks dude...

Cheers,

 

[RoryA]

yes - you may want to use a user-defined type (or class) rather than passing 18 separate values but the principle is the same.

 

[rsbuzatto]

I've never done this kind of stuff before...

Are you telling me to do it?

Private 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

I just need to write "Parametros" as my arguments when I call my Sub? How does it gonna work?...

Thanks man!

 

[RoryA]

you declare a variable of type parametros and populate it with your values:

DIm pm as parametros
with pm
.TituloIdeia = tbTitulo.Value
' and so on
End With

Transf_BD pm

then in the called code, read the values into the recordset fields.

 

[rsbuzatto]

I created another Sub with those informations...

Sub Parametros()

Dim pm As Parameters
With pm
   .TituloIdeia = tbTitulo.Value
   (and so on...)
End With

End Sub

Now... How can I use this Sub as an argument's name into my Sub Transf_BD(?????)?

Another doubt... do I need to tell my Transf_BD the Recordset (in my case, rst("TituloIdeia") = ???) or, with this method above my database will recognize it through "pm"?

Sorry if I'm not clear enough...

Thanks one more time dude!

 

[RoryA]

why create another sub? it would have to be a function so that you can return the variable you created and pass it to the Transf_BD sub - which needs to be:

Sub Transf_BD(pm As Parameters)
...
rst("Título da Idéia") = pm.TituloIdeia
' etc

 

[rsbuzatto]

Wait a sec... I didn't understand!

The last post it's clear enough... however, the {...}

Dim pm As Parameters
With pm
   .TituloIdeia = tbTitulo.Value
   (and so on...)
End With

{...} will appear in another Function... new Sub... part of my Transf_BD Sub or what...?

Sorry about my questions dude...