Item cannot be found in the collection corresponding to the name 1

[gwar2k1]

hey im trying to add some data into an access 2000 file but im not sure of what Im doing and Ive ended up with the error:

Item cannot be found in the collection corresponding to the requested name or ordinal

and it stops (logically) on a field call. heres my code:

Sub addProduct()
Rem adds a product to the product table using data from the gui

Dim Cnct As ADODB.Connection 'Connection to the database
Dim ProdRec As ADODB.Recordset 'Product record structure

Set Cnct = New ADODB.Connection
Set ProdRec = New ADODB.Recordset
With Cnct
.CursorLocation = adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & flPath & mdb
.Open
End With

ProdRec.Fields("EAN 8/13&quot = form1.txtEAN
ProdRec.Fields("Title&quot = form1.txtTitle
ProdRec.Fields("Price&quot = form1.txtPrice
'ProdRec.Fields("Category&quot = form1.lstCategory
'ProdRec.Fields("Periodity&quot = form1.lstPeriodity

ProdRec.AddNew
Cnct.Close
Set Cnct = Nothing
Set ProdRec = Nothing

End Sub

Can any one give me a few pointers please?

TIA

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."

 

[DrJavaJoe]

You need to open ProdRec like first


ProdRec.open "Select * from TableName", Cnct
'Then do the Add new
ProdRec.AddNew
'insert record
ProdRec.Fields("EAN 8/13&quot = form1.txtEAN
ProdRec.Fields("Title&quot = form1.txtTitle
ProdRec.Fields("Price&quot = form1.txtPrice
'Update
ProdRec.update

"Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'."

 

[gwar2k1]

Thanks =D I actually chuckled at your sig the other day. Any who i took your advice but I got:

Object or provider is not capable of performing requested operation

On the ProdRec.AddNew line. If it helps, the .open line reads: "SELECT * FROM Product", Cnct

Baffled =S

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."

 

[DrJavaJoe]

hmm, try changing the cursor type before calling the open statement.

ProdRec.CursorType = adOpenDynamic

"Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'."

 

[gwar2k1]

Okay. Same error though =(. I dont suppose you have the time to explain whats happening with these fixes at all? It all looks impressive but i dont really have a clue. Maybe a web link?

Thanks doc =)

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."

 

[DrJavaJoe]

Sorry should have said locktype
ProdRec.LockType = adLockOptimistic


from MSDN:
adLockOptimistic = Specifying this value opens a recordset with optimistic locking. Record-by-record, the OLE DB Provider locks records only when the Update method is invoked on a Recordset object.


'specify actual the recordset
ProdRec.open "Select * from TableName", Cnct

'i'm going to insert a new record, if you did not do this it would edit the fields of the current record
ProdRec.AddNew

'insert fields into the record record
ProdRec.Fields("EAN 8/13&quot = form1.txtEAN
ProdRec.Fields("Title&quot = form1.txtTitle
ProdRec.Fields("Price&quot = form1.txtPrice

'Update
ProdRec.update

"Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'."

 

[gwar2k1]

Thankyou so much! Its people like you that make tek-tips a nice community as well as the expected knowledgeable one it is =)

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."