Solved

MS-Access question

Posted on 1997-09-05
2
333 Views
Last Modified: 2010-07-27
Using an unbound main form and unbound subform, a passthru query is used to pull records back from an Oracle Database via ODBC connections. The criteria for the query is taken from the unbound main form. The query runs great by itself and displays two records in a datasheet.

However - trying to get those records into an unbound subform (using no source) results in the same record being shown twice. Clicking on the record selector shows the second record twice - but neither at the same time!

Trying to use the passthru query as the source results in an error message saying that columnheadings are required. Yet that is part of a crosstab query - not a passthru!!!

Advice please. Is it possible to use a passthru in a subform - bound or unbound?

How can I get the records to be displayed in datasheet format within a main form - preferably avoiding list boxes, etc.?

0
Comment
Question by:pamelacamel
2 Comments
 
LVL 4

Accepted Solution

by:
ozphil earned 0 total points
ID: 1956136
I found this kb article after a prolonged search.

I had a similar problem. This article should answer your questions.

INF: How to Base Subforms on SQL Pass-Through Queries
Article ID: Q112746

Copyright 1993 Microsoft Corporation. All rights reserved.

---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access version 2.0
---------------------------------------------------------------------

SUMMARY
=======

This article describes how to use an SQL pass-through query for a form's
record source. It assumes that you know how to build and use SQL pass-
through queries.

The following considerations should be kept in mind when you are using SQL
pass-through queries for forms' record sources:

 - SQL pass-through query record sources are read-only.

 - The Query Builder does not save the connect string unless you save
   the query.

 - Subforms can use SQL pass-through queries as record sources only if
   you set the record source in the main form's OnLoad property. Subreports
   cannot use SQL pass-through queries as record sources.

MORE INFORMATION
================

SQL Pass-Through Queries Are Read-Only
--------------------------------------

Forms based on SQL pass-through queries are read-only because SQL pass-
through queries are read-only. The recordset returned by an SQL pass-
through query is a snapshot, or read-only recordset. This behavior is by

design. In order for the form to be updateable, base your form on an
attached table with a unique index.

The Query Builder Does Not Save the Connect String
--------------------------------------------------

The RecordSource property's Query Builder displays a window that looks
similar to a query's Design window. You use this window to build the SQL
string or query for the RecordSource property. The Query Builder will set
the RecordSource property to an SQL string if the SQL string is not saved
as a query. If you save the string as a query, the name of the query will
be used as the RecordSource property.

When you are using an SQL pass-through query created using the Query
Builder, the ODBC connect string defined in that query will not be returned
as part of the RecordSource property SQL string. This can produce the error
message "Couldn't find input table or query '[Table from ODBC Server]'"
because without the ODBC connect string, the form will look for a local
table, instead of a table on the server. If you save the SQL string as a

query, the RecordSource property will contain the name of the query instead
of the SQL string, and the form will be able to retrieve the remote data
correctly.

Subforms Cannot Directly Use SQL Pass-Through Queries as Record Sources
-----------------------------------------------------------------------

The best way to use server-based data in a subform is to attach the remote
table and then base the subform on the attachment.

If you base a subform directly on an SQL pass-through query, you will
receive the following error message when you open the form:

   The crosstab query underlying a subform or subreport must have fixed
   column headings.

If you have to use an SQL pass-through query as a subform's record source,
you can dynamically set the subform's RecordSource property with the main
form's OnLoad event. The following is an example of an OnLoad event
procedure in the main form MainForm that dynamically sets the RecordSource
property in the subform SPTSubForm:

   Sub MainForm_OnLoad
     Me!SPTSubForm.Form.RecordSource = "[<Name of SQL pass-through query>]"
   End Sub

The subform itself should be saved without a RecordSource property.

REFERENCES
==========

For more information about SQL pass-through queries, search for "pass-
through query" then "Creating a Pass-Through Query" using the Microsoft
Access Help menu.

Additional reference words: 2.00
KBCategory:
KBSubcategory: FrmsPrp

Copyright 1993 Microsoft Corporation. All rights reserved.
0
 

Author Comment

by:pamelacamel
ID: 1956137
Thank you very much! The method currently used won't work with this - so we can move in a new direction! The current design of the system relies on the subform in the main form and criteria entered in the main form determines the subform query results.

Hence - as both will be loaded - we cannot use the on-load event.

However - this has saved oodles of time - THANKS AGAIN!!!

Pamelcamel
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question