No returned records on Subform.

[The Shaker]

Hi,

Repeating the query on 06/09/05.

I had a db on a computer system running XP Pro at work. The main form
returns records that show up in a subform.

When the db was relocated to another computer system at another place of
work, the db returns NO records in the subform.

Then I took this db to my own computer at home to investigate the problem,
the db began to work properly, returning records in the subform. No changes
have been made to the db.

Could someone please explain what is happening here?


Suggested Solutions:

"Also verify that no references are listed as MISSING on the relocated PC -
this is the most likely cause as Maha stated.

Assuming:
1. Both work fine. What do I do now?
2. Neither works. Does not return records. What do I do now?


Ta.

Adam

Possible Solution 1: Check for missing files.

Looking up the list under code>tools>references. There are NO MISSING Files
checked or unchecked.

Possible Solution 2: Verify that the query when run alone returns records.

The query does not return any records on the faulty machine. It does run on
other machines.

Possible Solution 3: Verify whether the subform shows records when opened
independently.

Because query does not work, the subform also does not return records. This
is because the recordset comes from the query.

So, we still have a db on a machine that does not return records to the
subform. On other machines, it does return records.

Any other suggestions out there please?

Thanks.

Adam

 

[Dirk Goldgar]

Hi,

Repeating the query on 06/09/05.








Possible Solution 1: Check for missing files.

Looking up the list under code>tools>references. There are NO MISSING
Files checked or unchecked.

Possible Solution 2: Verify that the query when run alone returns
records.

The query does not return any records on the faulty machine. It does
run on other machines.

Possible Solution 3: Verify whether the subform shows records when
opened independently.

Because query does not work, the subform also does not return
records. This is because the recordset comes from the query.

So, we still have a db on a machine that does not return records to
the subform. On other machines, it does return records.

Any other suggestions out there please?

So what's the SQL of the query? Anything unusual about it? Does it use
any VBA or user-written functions?

 

[The Shaker]

Well...here it is...

SELECT [Service - Docket Table].DocketTableIndex, [Service - Docket
Table].WorkorderID, [Service - Docket Table].EmployeeID, [Service - Docket
Table].CustomerID, [Service - Docket Table].AccountNo, [Service - Docket
Table].UpdateEmployee, [Service - Docket Table].UpdateDate, [Service - Docket
Table].UpdateTime, [Service - Docket Table].DocketDate, [Service -
Concentrator Deliver].ConcentratorTypeDelivered, [Service - Concentrator
Deliver].ConcentratorSerialNo, [Service - Concentrator
Deliver].ConcentratorHours, [Service - Concentrator Deliver].ServiceDate,
[Service - Concentrator Deliver].NextServiceDate, [Service - Concentrator
Details].[Concentrator Service Details], [Service - Concentrator
Return].ConcentratorTypeReturned, [Service - Concentrator
Return].ConcentratorSerialNo, [Service - Concentrator
Return].ConcentratorHours, [Service - Concentrator Return].ServiceDate,
[Service - Concentrator Return].NextServiceDate, [Service - Cyl Various
Delivered].[Various Delivered], [Service - Cyl Various Returned].[Various
Returned], [Service - Cyl D Delivered].[D Cylinders Delivered], [Service -
Cyl D Returned].[D Cylinders Returned], [Service - Cyl CL Delivered].[CL
Cylinders Delivered], [Service - Cyl CL Returned].[CL Cylinders Returned],
[Service - Cyl CM Delivered].[CM Cylinders Delivered], [Service - Cyl CM
Returned].[CM Cylinders Returned], [Service - Cyl G Delivered].[G Cylinders
Delivered], [Service - Cyl CS Delivered].[CS Cylinders Delivered], [Service -
Cyl CS Returned].[CS Cylinders Returned], [Service - Cyl E Delivered].[E
Cylinders Delivered], [Service - Cyl E Returned].[E Cylinders Returned],
[Service - Cyl G Returned].[G Cylinders Returned], [Service - Cylinders
Details].Cylinders, [Service - Regulators 1
Details].Regulator1ServiceDetails, [Service - Regulators 1
Deliver].Regulator1TypeDelivered, [Service - Regulators 1
Deliver].Regulator1SerialNo, [Service - Regulators 1
Return].Regulator1TypeReturned, [Service - Regulators 1
Return].Regulator1SerialNo, [Service - Regulators 2
Details].Regulator2ServiceDetails, [Service - Regulators 2
Deliver].Regulator2TypeDelivered, [Service - Regulators 2
Deliver].Regulator2SerialNo, [Service - Regulators 2
Return].Regulator2TypeReturned, [Service - Regulators 2
Return].Regulator2SerialNo, [Service - OCD Details].OCDServiceDetails,
[Service - OCD Deliver].OCDTypeDelivered, [Service - OCD
Deliver].OCDSerialNo, [Service - OCD Return].OCDTypeReturned, [Service - OCD
Return].OCDSerialNo, [Service - Concentrator
Service].ConcentratorTypeServiced, [Service - Concentrator
Service].ConcentratorSerialNo, [Service - Concentrator
Service].ConcentratorHours, [Service - Concentrator Service].ServiceDate,
[Service - Concentrator Service].NextServiceDate, [Service -
Trolleys].Trolley, [Service - Bags].Cannula, [Service - Bags].Tubing,
[Service - Bags].Bags, [Service - Docket Table].Package, [Service - Docket
Table].Comments
FROM ((((((((((((((((((((((((((((([Service - Docket Table] LEFT JOIN
[Service - Bags] ON [Service - Docket Table].WorkorderID = [Service -
Bags].WorkorderID) LEFT JOIN [Service - Cyl D Delivered] ON [Service - Docket
Table].WorkorderID = [Service - Cyl D Delivered].WorkorderID) LEFT JOIN
[Service - Cyl CL Delivered] ON [Service - Docket Table].WorkorderID =
[Service - Cyl CL Delivered].WorkorderID) LEFT JOIN [Service - Cyl CL
Returned] ON [Service - Docket Table].WorkorderID = [Service - Cyl CL
Returned].WorkorderID) LEFT JOIN [Service - Cyl CM Delivered] ON [Service -
Docket Table].WorkorderID = [Service - Cyl CM Delivered].WorkorderID) LEFT
JOIN [Service - Cyl CM Returned] ON [Service - Docket Table].WorkorderID =
[Service - Cyl CM Returned].WorkorderID) LEFT JOIN [Service - Cyl CS
Delivered] ON [Service - Docket Table].WorkorderID = [Service - Cyl CS
Delivered].WorkorderID) LEFT JOIN [Service - Cyl CS Returned] ON [Service -
Docket Table].WorkorderID = [Service - Cyl CS Returned].WorkorderID) LEFT
JOIN [Service - Regulators 2 Deliver] ON [Service - Docket Table].WorkorderID
= [Service - Regulators 2 Deliver].WorkorderID) LEFT JOIN [Service - Cyl D
Returned] ON [Service - Docket Table].WorkorderID = [Service - Cyl D
Returned].WorkorderID) LEFT JOIN [Service - Cyl E Delivered] ON [Service -
Docket Table].WorkorderID = [Service - Cyl E Delivered].WorkorderID) LEFT
JOIN [Service - Cyl E Returned] ON [Service - Docket Table].WorkorderID =
[Service - Cyl E Returned].WorkorderID) LEFT JOIN [Service - Cyl G Delivered]
ON [Service - Docket Table].WorkorderID = [Service - Cyl G
Delivered].WorkorderID) LEFT JOIN [Service - Cyl G Returned] ON [Service -
Docket Table].WorkorderID = [Service - Cyl G Returned].WorkorderID) LEFT JOIN
[Service - Cylinders Details] ON [Service - Docket Table].WorkorderID =
[Service - Cylinders Details].WorkorderID) LEFT JOIN [Service - OCD Deliver]
ON [Service - Docket Table].WorkorderID = [Service - OCD
Deliver].WorkorderID) LEFT JOIN [Service - OCD Details] ON [Service - Docket
Table].WorkorderID = [Service - OCD Details].WorkorderID) LEFT JOIN [Service
- OCD Return] ON [Service - Docket Table].WorkorderID = [Service - OCD
Return].WorkorderID) LEFT JOIN [Service - Regulators 1 Deliver] ON [Service -
Docket Table].WorkorderID = [Service - Regulators 1 Deliver].WorkorderID)
LEFT JOIN [Service - Regulators 1 Details] ON [Service - Docket
Table].WorkorderID = [Service - Regulators 1 Details].WorkorderID) LEFT JOIN
[Service - Regulators 1 Return] ON [Service - Docket Table].WorkorderID =
[Service - Regulators 1 Return].WorkorderID) LEFT JOIN [Service - Regulators
2 Details] ON [Service - Docket Table].WorkorderID = [Service - Regulators 2
Details].WorkorderID) LEFT JOIN [Service - Regulators 2 Return] ON [Service -
Docket Table].WorkorderID = [Service - Regulators 2 Return].WorkorderID) LEFT
JOIN [Service - Trolleys] ON [Service - Docket Table].WorkorderID = [Service
- Trolleys].WorkorderID) LEFT JOIN [Service - Concentrator Deliver] ON
[Service - Docket Table].WorkorderID = [Service - Concentrator
Deliver].WorkorderID) LEFT JOIN [Service - Concentrator Details] ON [Service
- Docket Table].WorkorderID = [Service - Concentrator Details].WorkorderID)
LEFT JOIN [Service - Concentrator Return] ON [Service - Docket
Table].WorkorderID = [Service - Concentrator Return].WorkorderID) LEFT JOIN
[Service - Concentrator Service] ON [Service - Docket Table].WorkorderID =
[Service - Concentrator Service].WorkorderID) LEFT JOIN [Service - Cyl
Various Delivered] ON [Service - Docket Table].WorkorderID = [Service - Cyl
Various Delivered].WorkorderID) LEFT JOIN [Service - Cyl Various Returned] ON
[Service - Docket Table].WorkorderID = [Service - Cyl Various
Returned].WorkorderID
WHERE ((([Service - Docket Table].CustomerID)=[Forms]![AMT]![Workorders by
Customer]![CustomerID]))
ORDER BY [Service - Docket Table].WorkorderID DESC;

So what's the SQL of the query? Anything unusual about it? Does it use
any VBA or user-written functions?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Many thanks but I'm still stumped.

Adam

 

[Dirk Goldgar]

Well...here it is...

[SQL snipped]

LOL I see now why you didn't just post the SQL in the first place.
That's a big query!

Unfortunately, I don't see anything about it that would explain why it's
returning no records on just one PC; at least, not if the database and
all the tables involved are the same. Are all these PCs running the
same version of Access and Jet? Are they all patched and updated to the
same level?

 

[The Shaker]

Thanks Dirk. Further comments in line.

Well...here it is...

[SQL snipped]

LOL I see now why you didn't just post the SQL in the first place.
That's a big query!

too big...I show people to frighten them...

Unfortunately, I don't see anything about it that would explain why it's
returning no records on just one PC; at least, not if the database and
all the tables involved are the same.

No changes to the db at all. All forms, queries, tables and VB code are
exactly the same.

Are all these PCs running the
same version of Access

Here is where it gets interesting. Another collegue with whom I am trying to
resolve the issue said that the faulty machine has a "corporate" version of
Access. I don't know if that has any to do with it. My version, and the one
at work are registered versions bought off the shelf (the work machine) or
came with the computer (my home machine). I built the program at home and
installed it at work, later transferring it to another place of work(the
faultly machine with the "corporate" Access version).

and Jet?

Does it make any difference with the various jet engines?
How do I know which Jet Engine is installed, and which one is required to
run a db (in any scenario, because I've always just taken it for granted that
what works on my machine will work on any machine with the same platform and
operating system. Where there were problems, Access would "convert" the db.)?

Are they all patched and updated to the
same level?

How do I determine the patched status and update level?

Ta.

Adam.

 

[Dirk Goldgar]

Here is where it gets interesting. Another collegue with whom I am
trying to resolve the issue said that the faulty machine has a
"corporate" version of Access. I don't know if that has any to do
with it. My version, and the one at work are registered versions
bought off the shelf (the work machine) or came with the computer (my
home machine). I built the program at home and installed it at work,
later transferring it to another place of work(the faultly machine
with the "corporate" Access version).

That does seem suspicious, but it's not a "smoking gun" on the face of
it.

Does it make any difference with the various jet engines?
How do I know which Jet Engine is installed, and which one is
required to run a db (in any scenario, because I've always just taken
it for granted that what works on my machine will work on any machine
with the same platform and operating system. Where there were
problems, Access would "convert" the db.)?

I wouldn't *expect* any problems, but we're trying to figure out why the
same query works on some PCs and not others. So it only makes sense to
look carefully at any differences in the core components.

How do I determine the patched status and update level?

Access will tell you the exact version, SP, and patch level in the
Help -> About Microsoft Access ... dialog. The detailed, three-part,
numeric version number in parentheses is pretty much the last word. Or
you can click the "System Info..." button on that dialog and explore the
information presented in the System Information applet.

For the Jet version, then assuming you're using Access 2000 or later,
locate the file "msjet40.dll" in your System folder, right-click on it
and choose Properties, and see what the Version tab says. On my system,
it's 4.0.8618.0 -- I'm not completely sure that's the very latest
version, but it's pretty recent.