Recordset question involving SQL and subforms

[MichaelJohnson168]

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![PartsOrdersSubform].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(PartID)'")

-------------------------------------------------
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(PartID)' 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.

 

[Steve Schapel]

Michael,

Try it like this...
Set aRecordset = db3.OpenRecordset("SELECT [PartsOrders].EstRepairTime
FROM PartsOrders WHERE [PartsOrders].PartID = '" &
subformRecordset!PartID & "')"

For what it's worth, as far as I understand what you are doing here,
aRecordset will only ever contain a maximum of one record. As such, I
would probably be inclined to look for a simpler way of finding the
EstRepairTime rather than opening a DAO recordset.

--
Steve Schapel, Microsoft Access MVP

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![PartsOrdersSubform].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(PartID)'")

-------------------------------------------------
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(PartID)' 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.

 

[Marshall Barton]

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![PartsOrdersSubform].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(PartID)'")

-------------------------------------------------
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(PartID)' 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.

The index into a collection is either the numeric position
within the collection OR the **string** containg the name of
element. You also have extra apostrophes around the
recordset reference.

Try it this way:

Set aRecordset = db3.OpenRecordset( _
"SELECT [PartsOrders].EstRepairTime " & _
"FROM PartsOrders " & _
"WHERE [PartsOrders].PartID = " & _
subformRecordset.Fields("PartID") )

The MoveFirst failed because your query couldn't return any
records. It might be a good idea to check for that case by
adding:

If aRecordset.RecordCount > 0 Then
aRecordset.MoveFirst
Else
MsgBox "No match for PartID=" & PartID
End If