error: No Current Record

[davyre]

Hi
I have a query that goes into a recordset:

LSQL2 = "SELECT TblUnits.UnitID, TblParts.PartNumber, TblParts.PartDescription, TblParts.Material, " & _
            "TblUnitPart.Qty, TblParts.PartCost, TblParts.VendorPart, TblVendor.VendorID, " & _
            "TblVendor.VendorDesc, TblParts.Comments, TblOrderUnit.OrderunitID " & _
            "FROM (TblVendor INNER JOIN ((TblUnits INNER JOIN TblOrderUnit ON TblUnits.UnitID = TblOrderUnit.UnitID)" & _
            "INNER JOIN (TblParts INNER JOIN TblUnitPart ON TblParts.PartID = TblUnitPart.PartID) " & _
            "ON TblUnits.UnitID = TblUnitPart.UnitID) ON TblVendor.VendorID = TblParts.VendorID) " & _
            "WHERE ((TblUnits.UnitID)=" & Lrs("UnitID") & " AND (TblOrderUnit.OrderID)=" & TempOrderID & ")"

The thing is, when I open it using recordset Rst=DB.OpenRecordset(LSQL2) and then Rst.MoveFirst, it shows error saying no current record (which I assume the recordset is blank/empty). But the thing is if I try to open the query itself using:

Dim qdf As QueryDef
With DB
    Set qdf = .CreateQueryDef("tmpTableForQRY", LSQL2)
    DoCmd.OpenQuery "tmpTableForQRY"
   .QueryDefs.Delete "tmpTableForQRY"
End With
qdf.Close

the tmpTableForQRY shows records that should be inside the recordset. Any idea why my recordset is blank?

 

[dhookom]

What do you get if you debug.print LSQL2? It looks like you might be missing a space in the fourth line.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

I got
SELECT TblUnits.UnitID, TblParts.PartNumber, TblParts.PartDescription, TblParts.Material, TblUnitPart.Qty, TblParts.PartCost, TblParts.VendorPart, TblVendor.VendorID, TblVendor.VendorDesc, TblParts.Comments, TblOrderUnit.OrderunitID FROM (TblVendor INNER JOIN ((TblUnits INNER JOIN TblOrderUnit ON TblUnits.UnitID = TblOrderUnit.UnitID) INNER JOIN (TblParts INNER JOIN TblUnitPart ON TblParts.PartID = TblUnitPart.PartID) ON TblUnits.UnitID = TblUnitPart.UnitID) ON TblVendor.VendorID = TblParts.VendorID) WHERE ((TblUnits.UnitID)=41 AND (TblOrderUnit.OrderID)=1)

I have added a space in the fourth line

"FROM (TblVendor INNER JOIN ((TblUnits INNER JOIN TblOrderUnit ON TblUnits.UnitID = TblOrderUnit.UnitID)[highlight #FCAF3E] [/highlight]" & _

but it is still no current record.

 

[dhookom]

And what do you get if you take the SQL statement and paste it into a new query?

Have you added a breakpoint to step through the code? You can then use something in the debug window like:

? Rst.BOF

? Rst.EOF

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

by debug window, do you mean immediate window?
while I debug the code, these results are shown:
? Rst.BOF
True
? Rst.EOF
True

I assume that means the Rst is empty. I tried to rename the sql and insert it into the recordset, and it does not change anything.

 

[dhookom]

There are no records returned from the SQL. Did you attempt to copy and paste this into a new, blank query SQL view:

SELECT TblUnits.UnitID, TblParts.PartNumber, TblParts.PartDescription, TblParts.Material, TblUnitPart.Qty, 
TblParts.PartCost, TblParts.VendorPart, TblVendor.VendorID, TblVendor.VendorDesc, TblParts.Comments, 
TblOrderUnit.OrderunitID 
FROM (TblVendor INNER JOIN ((TblUnits INNER JOIN TblOrderUnit ON TblUnits.UnitID = TblOrderUnit.UnitID)
 INNER JOIN (TblParts INNER JOIN TblUnitPart ON TblParts.PartID = TblUnitPart.PartID) ON TblUnits.UnitID = TblUnitPart.UnitID) 
 ON TblVendor.VendorID = TblParts.VendorID) WHERE ((TblUnits.UnitID)=41 AND (TblOrderUnit.OrderID)=1)

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

I tried, and the new SQL returns no record (empty). But why when I use querydef on my first post, it shows the records?

 

[davyre]

update: I tried to use the querydef again, and now it is empty. Maybe there is something wrong with the query. AFAIK I did not modify anything regarding the query and the data should be there. I will check the query again anyway. Thanks

 

[dhookom]

You should try remove some criteria, change joins to outer joins, and if that doesn't work begin removing tables.

Duane
Hook'D on Access
MS Access MVP