Your recordset uses all INNER JOIN statements when adding the prodtype, lubetype, prodsize, and contsize tables. So if any of those tables don't have a related record, then no results would show.
I updated it to use LEFT OUTER JOIN instead and now a record is returned on that page. You would have to check those 4 tables to see which one is missing an entry for this product.