Newbie Q: VBA referencing recordset in linked MSDE SQL table

Hello,
I posted the following question a few days ago and then had to focus on a
different emergency so I didn’t have an opportunity to follow up with
additional information requested. Below is a copy of my initial post, an
example of the code, and the error that is triggered. I’m sorry I was too
vague before.
Thanks!!
Andrea
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hi! I am brand spankin' new at this and need help! We just upsized an
Access 2002 database to having an MSDE backend. We used Access's wizard.
In general, the forms seem to be working fine. My problem is with the VBA
underneath. The code breaks when it tries to work with recordsets. I get a
runtime error, “Item not found in this collection.” Between the text book I
have and my web searches, I can't seem to figure out how I need to reference
the SQL tables or if there is a reference library that I need to activate to
make this work now. I have a lot of code like this and for the time being I
am not interested in converting it all to ADO. That can come later. I just
need to get this functional for the client.
The error that is triggered is: Runtime Error 3265 “Item not found in this
collection”
Below is a sample of the code. It breaks at the line that reads:
If Me.NewRecord Then
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~
Private Sub Form_Current()
Dim rec As Recordset
' If the form is showing a new record, assignes next chronoligical id number
stored
' in system_maint
If Me.NewRecord Then
Set rec = CurrentDb.OpenRecordset("system_maint")
Me.ID = rec("last_id_num") + 1
rec.Edit
rec("last_id_num") = Me.ID
rec.Update
rec.Close
End If
' requeries all look-up combo boxes.
Me.Artist.Requery
Donor_s_Name.Requery
Building.Requery
End Sub
Andrea
Andrea M wrote:

> Hello,
> I posted the following question a few days ago and then had to focus on a
> different emergency so I didn’t have an opportunity to follow up with
> additional information requested. Below is a copy of my initial post, an
> example of the code, and the error that is triggered. I’m sorry I was too
> vague before.
> Thanks!!
> Andrea
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Hi! I am brand spankin' new at this and need help! We just upsized an
> Access 2002 database to having an MSDE backend. We used Access's wizard.
> In general, the forms seem to be working fine. My problem is with the VBA
> underneath. The code breaks when it tries to work with recordsets. I get a
> runtime error, “Item not found in this collection.” Between the text book I
> have and my web searches, I can't seem to figure out how I need to reference
> the SQL tables or if there is a reference library that I need to activate to
> make this work now. I have a lot of code like this and for the time being I
> am not interested in converting it all to ADO. That can come later. I just
> need to get this functional for the client.
> The error that is triggered is: Runtime Error 3265 “Item not found in this
> collection”
> Below is a sample of the code. It breaks at the line that reads:
> If Me.NewRecord Then
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~
> Private Sub Form_Current()
> Dim rec As Recordset
> ' If the form is showing a new record, assignes next chronoligical id number
> stored
> ' in system_maint

> If Me.NewRecord Then
> Set rec = CurrentDb.OpenRecordset("system_maint")
and try CurrentProject instead of CurrentDB.
CurrentProject.OpenRecordset will return an ADO recordset object, which
is what "dim rec as Recordset" has made a reference to.
CurrentDB.OpenRecordset returns a DAO Recordset object.

> Me.ID = rec("last_id_num") + 1
also try:
Me!ID = rec("last_id_num") + 1
> rec.Edit
> rec("last_id_num") = Me.ID
and...
rec("last_id_num") = me!ID
This will ensure that you're accessing the ID control on the form,
instead of an ID Property of the Form object that, of course, doesn't exist.

> rec.Update
> rec.Close
> End If
> ' requeries all look-up combo boxes.
> Me.Artist.Requery
> Donor_s_Name.Requery
> Building.Requery
> End Sub
>

Newbie Q. for referencing DAO recordsets with VBA in Access front

Hi! I am brand spankin' new at this and need help! We just upsized an
Access 2002 database to having an MSDE backend. We used Access's wizzard.
In general, the forms seem to be working fine. My problem is with the VBA
underneath. The code breaks when it tries to work with recordsets. I get a
runtime error, "Item not found in this collection." Between the text book I
have and my web searches, I can't seem to figure out how I need to reference
the SQL tables or if there is a reference library that I need to activate to
make this work now. I have a lot of code like this and for the time being I
am not interested in converting it all to ADO. That can come later. I just
need to get this functional for the client.
Any help appreciated!!!
Thanks,
Andrea
YOu sure have to be a more specific to solve solve your problem. When does
the error occur ? Can you send some code and point out what fails. The error
sounds like you are accessing a column which doesnt exists in the returned
recordset. If you are using a view and you designed t with the view
designer, keep in mind that sometimes the columns are renamed to something
like "expr1" and so on. Another possible error could be that you want to
access a provider specific property which you copied from some example but
isnt supported by that provider.
Send in some information and well try to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Andrea M" <AndreaM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:479D59C8-6D7B-4455-BA3B-D52FBE1163FB@.microsoft.com...
> Hi! I am brand spankin' new at this and need help! We just upsized an
> Access 2002 database to having an MSDE backend. We used Access's wizzard.
> In general, the forms seem to be working fine. My problem is with the VBA
> underneath. The code breaks when it tries to work with recordsets. I get
> a
> runtime error, "Item not found in this collection." Between the text book
> I
> have and my web searches, I can't seem to figure out how I need to
> reference
> the SQL tables or if there is a reference library that I need to activate
> to
> make this work now. I have a lot of code like this and for the time being
> I
> am not interested in converting it all to ADO. That can come later. I
> just
> need to get this functional for the client.
> Any help appreciated!!!
> Thanks,
> Andrea