Solved

MS-Access question

Posted on 1997-09-05
2
328 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
Comment Utility
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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now