Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS-Access question

Posted on 1997-09-05
2
Medium Priority
?
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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