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]

that bit is part of the sub that then calls Transf_BD and passes the pm argument to it.

 

[rsbuzatto]

If I do such a thing... I'll get an error in Transf_Bd pm...

So... that's why I'm asking you...

Where should I write or what I'm doing wrong with my code..? :\

I sent a Private Mess with my Sub Transf_BD ... if can have a look...

 

[RoryA]

Please do not PM parts of questions - it prevents anyone else benefitting from this thread later.
Please post the code here and I will take a look when I get home later.

 

[rsbuzatto]

Well...

Take a look at what I've done...

'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

'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

Into my SAVE BUTTON CODE I put:

Executa_Transf

Nevertheless, its not running!
The error is in line .TituloIdeia = tbTitulo.Value (Sub Executa_Transf)...

Thanks for all your help man...

 

[RoryA]

what is the error?

 

[rsbuzatto]

what is the error?

Runtime error '91' - Object variable or with block variable not set

 

[RoryA]

is the code in the form with the textbox on it?

 

[rsbuzatto]

is the code in the form with the textbox on it?

Yeah...

.TituloIdeia is the name that I gave now that will receive the title;

tbTitulo is my textbox's title name;

 

[RoryA]

Just thought - choose a different name for your UDT - Parameters is an ADO object, so not a good name.

 

[rsbuzatto]

Just thought - choose a different name for your UDT - Parameters is an ADO object, so not a good name.

What do you mean... should I change all names of my 18 parameters?

Yet, do you have any tip of what kind of name should I use?


Thanks!