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 - Most Recent Records from multiple tables

Hi,

I'm trying to create a view or TSQL statement to return in one recordset...

a) the most recent record of a PK in table1 [foodRecipes]

b) the most recent record (if exists) of FK from table 1 with the PK from table2

Goal: Each recipe can have many versions, and each version can have many historical attempts at making cookies...

example:

table 1: foodRecipes (PK = foodGroup + recipeName + recipeDateModified)

foodGroup [nvarchar (50)]

recipeName [nvarchar (50]

recipeDateModified [datetime]

cupsOfSugar [float]

sampleData:

cookies, peanutButter, 3/3/2007, 1.5

cookies, peanutButter, 3/4/2007, 2.0

cookies, sugar, 3/3/2007, 5.0

table 2: foodRecipeHistory (PK = foodGroup + recipeName + recipeDateModified + historyDateModified)

foodGroup [nvarchar (50)] ...FK from table1

recipeName [nvarchar (50] ...FK from table1

recipeDateModified [datetime] ...FK from table1

historyDateModified [datetime]

cupsOfSugarHistory [float]

sampleData:

cookies, peanutButter, 3/3/2007, 3/3/2007 10:15:00 AM, 1.5

cookies, peanutButter, 3/4/2007, 3/4/2007 10:20:00 AM, 2.0

cookies, peanutButter, 3/4/2007, 3/4/2007 10:21:00 AM, 2.2

What I want: the view or TSQL should provide the most recent unique recipes data + the most recent history (if exists, otherwise NULL)

SELECT * FROM myRecipies

sample Resultset:

foodGroup, recipeName, recipeDateModified, cupsOfSugar, historyDateModified, cupsOfSugarHistory

cookies, peanutButter, 3/4/2007, 2.0, 2.2

cookies, sugar, 3/3/2007, 5.0, <NULL>

What I've got now:

1. TSQL that gives me back the most recent recipes (No History yet)

SELECT foodGroup, recipeName, recipeDateModified, cupsOfSugar, CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

...and this works great, I get back each unique recipe from table #1, the most recent...

anyone good at this?

thanks in advance,

bsierad

You must have a sweet tooth if your only ingredient is CupsOfSugar.... ;)

Anyway, try the query below to see if this is what you're after.

Chris

SELECT foodGroup,

recipeName,

recipeDateModified,

cupsOfSugar,

CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate,

(SELECT TOP 1 frh.cupsOfSugarHistory

FROM dbo.foodRecipeHistory frh

WHERE frh.foodGroup = oher.foodGroup

AND frh.recipeName = oher.recipeName

AND frh.recipeDateModified = oher.recipeDateModified

ORDER BY frh.historyDateModified DESC) AS cupsOfSugarHistory

FROM dbo.foodRecipes oher

WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN

(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))

FROM dbo.foodRecipes

WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

|||

Thanks!

Works great...and I can soak this in and apply it in other areas...

My real fields don't taste this good...machineGasFlow sounds pretty boring...

Can't thank you enough,

bsierad