Problems with FindFirst Method

[SergioRocha]

Hi,
I've been improving a database and i would like to add a new functionality.
I have the following tables:
Carteira, Compras, Vendas...

I have a main form where i can see the information recorded in those tables. Main form gets the data from table Carteira but i also have subforms in the main form to see the information of the others tables. I want to build a new form that allows me to insert new data into the table Compras. The idea is when i press a button, this new form will appear, then i will insert the data press another button that will record the introduced data into table Compras. Bellow i describe the table fields.
Table Carteira has the fields: Referencia,nome,quantidade,total...
Table Compras has the fields: Referencia, Ref_compra,data,quantidade,total

I've created the following Sub. The goal would be to record the introduced data into table Compras:

Private Sub Comprar()
Dim DB As DAO.Database
Dim rstCarteira, rstCompras As DAO.Recordset
Dim strSQL As String

Set DB = CurrentDb
Set rstCarteira = DB.OpenRecordset("Carteira")
Set rstCompras = DB.OpenRecordset("Compras")

rstCompras.MoveLast
strSQL = "[Ref_compra] =' " & Me.Texto6 & "'"

MsgBox strSQL

With rstCompras
.MoveLast
.FindFirst (strSQL)

If .NoMatch Then
MsgBox "No Match was found."
.MoveNext
Else
MsgBox "It worked!"
End If
End With

Set rstCompras = Nothing
Set rstCarteira = Nothing
Set DB = Nothing

End Sub

When i execute the code i have the following error:
Run-time error '3251'
Operation not supported for this type of object

The debuger points to the line ".FindFirst (strSQL)"

What seems to be the problem???

 

[RuralGuy]

While I study your code I thought I'd let you know that this line:
Dim rstCarteira, rstCompras As DAO.Recordset
...will dimension the rstCarteira variable as a Varient in Access
To Dim it as a DAO.Recordset on one line you need to write
Dim rstCarteira As DAO.Recordset, rstCompras As DAO.Recordset

 

[RuralGuy]

See if my changes help:

Private Sub Comprar()
   Dim DB As DAO.Database
   Dim rstCarteira As DAO.Recordset, rstCompras As DAO.Recordset
   Dim strSQL As String

   Set DB = CurrentDb
[COLOR="Red"]   Set rstCarteira = DB.OpenRecordset("Carteira", dbOpenDynaset)
   Set rstCompras = DB.OpenRecordset("Compras", dbOpenDynaset)
[/COLOR]
   rstCompras.MoveLast
   strSQL = "[Ref_compra] =' " & Me.Texto6 & "'"

   MsgBox strSQL

   With rstCompras
[COLOR="Red"]      .MoveFirst
[/COLOR]      .FindFirst (strSQL)

      If .NoMatch Then
         MsgBox "No Match was found."
[COLOR="Red"]'         .MoveNext[/COLOR]
      Else
         MsgBox "It worked!"
      End If
   End With

   Set rstCompras = Nothing
   Set rstCarteira = Nothing
   Set DB = Nothing

End Sub

It's a lot easier to read with the code tags isn't it?

 

[SergioRocha]

While I study your code I thought I'd let you know that this line:
Dim rstCarteira, rstCompras As DAO.Recordset
...will dimension the rstCarteira variable as a Varient in Access
To Dim it as a DAO.Recordset on one line you need to write
Dim rstCarteira As DAO.Recordset, rstCompras As DAO.Recordset

Thanks for the help... i've just tried it now but it didn't work

 

[SergioRocha]

I've tried your changes and it worked ! I do not have the error message. But there's another problem. I write in the textbox named Texto6 data that exists in table Compras in the field Ref_compra but the code returns always the message "No Match was found." given by the code line

MsgBox "No Match was found."

What's the problem now?

 

[jal]

Do you have an extra space in

strSQL = "[Ref_compra] =' " & Me.Texto6 & "'"

as opposed to


strSQL = "[Ref_compra] ='" & Me.Texto6 & "'"

 

[RuralGuy]

Get rid of the extra space:
strSQL = "[Ref_compra] =' " & Me.Texto6 & "'"
...to
strSQL = "[Ref_compra] = '" & Me.Texto6 & "'"

 

[SergioRocha]

That's it RuralGuy Now it's working perfectly fine. Thank you for the help.

 

[RuralGuy]

Excellent! Glad we could help.