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
>