Recordset question involving sql and a subform

[MichaelJohnson168]

Newsgroups: microsoft.public.access.formscoding
From: (e-mail address removed) - Find messages by this author
Date: 25 Jun 2005 10:00:37 -0700
Local: Sat,Jun 25 2005 1:00 pm
Subject: Recordset question involving SQL and subforms
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I am attempting to grab the records from an order form's subform and
then retrieve the "Estimated Repair Time" for the part that is located
in the Parts Orders table.


To accomplish this I am using the Part ID from the subform and running
an sql statement on the Parts Orders table to:


retrieve from the parts table the "estimated repair time" where the
part id from the subform matches the part id from the Parts Orders
table.


However I am getting an error please see below.


Dim subformRecordset As DAO.Recordset
Set subformRecordset =
Forms!PartsOrders![PartsOrders­Subform].Form.RecordsetClone
------------------------------­-------------------
Above I am retrieving the record for each Auto Part that is in the
subform


Dim db3 As DAO.Database
Dim aRecordset As DAO.Recordset
Set db3 = CurrentDb


Set aRecordset = db3.OpenRecordset("SELECT [PartsOrders].EstRepairTime

From PartsOrders WHERE [PartsOrders].PartID =

'subformRecordset.Fields(PartI­D)' ")

------------------------------­-------------------
aRecordset.MoveFirst
------------------------------­-------------------
The above code is where I am getting an error message saying:


Run-time Error '3021'.
No Current Record


When I substitute 'subformRecordset.Fields(PartI­D)' with the id of a
part it works. For example 'WSG74512'. But I need to be able to
retrieve based on the part id that is from the subform's recordset.


Thanks in advance.

 

[Ofer]

Try and write it like that
' If PartID field type is number
Set aRecordset = db3.OpenRecordset("SELECT [PartsOrders].EstRepairTime
From PartsOrders WHERE [PartsOrders].PartID = " &
subformRecordset.Fields(PartI­D))

' If PartID field type is text
Set aRecordset = db3.OpenRecordset("SELECT [PartsOrders].EstRepairTime
From PartsOrders WHERE [PartsOrders].PartID = '" &
subformRecordset.Fields(PartI­D) & "'")

Newsgroups: microsoft.public.access.formscoding
From: (e-mail address removed) - Find messages by this author
Date: 25 Jun 2005 10:00:37 -0700
Local: Sat,Jun 25 2005 1:00 pm
Subject: Recordset question involving SQL and subforms
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I am attempting to grab the records from an order form's subform and
then retrieve the "Estimated Repair Time" for the part that is located
in the Parts Orders table.


To accomplish this I am using the Part ID from the subform and running
an sql statement on the Parts Orders table to:


retrieve from the parts table the "estimated repair time" where the
part id from the subform matches the part id from the Parts Orders
table.


However I am getting an error please see below.


Dim subformRecordset As DAO.Recordset
Set subformRecordset =
Forms!PartsOrders![PartsOrders­Subform].Form.RecordsetClone
------------------------------­-------------------
Above I am retrieving the record for each Auto Part that is in the
subform


Dim db3 As DAO.Database
Dim aRecordset As DAO.Recordset
Set db3 = CurrentDb


Set aRecordset = db3.OpenRecordset("SELECT [PartsOrders].EstRepairTime

From PartsOrders WHERE [PartsOrders].PartID =

'subformRecordset.Fields(PartI­D)' ")

------------------------------­-------------------
aRecordset.MoveFirst
------------------------------­-------------------
The above code is where I am getting an error message saying:


Run-time Error '3021'.
No Current Record


When I substitute 'subformRecordset.Fields(PartI­D)' with the id of a
part it works. For example 'WSG74512'. But I need to be able to
retrieve based on the part id that is from the subform's recordset.


Thanks in advance.