Solved

Access 97 SQL string won't open unbound form

Posted on 1998-06-25
15
279 Views
Last Modified: 2012-06-27
I am converting a 2.0 appliction to 97.  The rowsource is not being set from the DoCmd statement, see below
0
Comment
Question by:cremick
[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
  • 6
  • 5
  • 2
  • +2
15 Comments
 

Author Comment

by:cremick
ID: 1975830
Edited text of question
0
 
LVL 4

Expert Comment

by:tomook
ID: 1975831
If I understand correctly what you are saying, this should be no problem. Can you give us an example? Note that in Access97, the default value for the RecordSource property is "" (empty string) rather than Null. If you are checking for Null, this may be your problem.
0
 
LVL 17

Expert Comment

by:ramrom
ID: 1975832
It should work just fine. We need more details. Exactly what does not work; what is the evidence?

Forms per se are not bound or unbound; "bound" applies to controls.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:cremick
ID: 1975833
I'm upgrading the PrimeVest Access 2.0 app to 97.  One technique I used
often was to open an unbound form with a SQL string -
DoCmd OpenForm "frmTransactions", , strSQL

But, when I converted to 97, this didn't work.  The rowsource was not
getting set to the SQL string.  I got around the problem by setting the
rowsource of the form to a query and changing the DoCmd statement to use a
filter rather than a SQL statement, but I don't know why SQL didn't work.
Any ideas?
0
 
LVL 4

Expert Comment

by:tomook
ID: 1975834
I am surprised this worked in Access 2. The argument you are using is essentially for a filter. I assume Access 2 did not check, appended the string to the recordsource, and voila: there was a complete recordsource. MS probably saw this as a bug and "fixed" it for you.
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1975835
You could do the following;

DoCmd.OpenForm "frmTransactions"
forms![frmTransactions].recordsource = strSQL
forms![frmTransactions].requery ' Don't know if this line is necessary.
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1975836
From the developer CD;


Using Recordset Locking

You use exclusive mode to lock an entire database. You use recordset locking, on the other hand, to control how one or more individual tables in a database are locked. Recordset locking applies only to table- and dynaset-type Recordset objects; it doesn't apply to snapshot- and forward-only-type Recordset objects because these are inherently read-only objects.

Note   The default recordset lock is a write lock, which means that other users can't edit data in the locked records. You can also prevent users from reading the data by setting a read lock. You may want to do this if you are making bulk changes to a Recordset object and you want to prevent users from reading the data until you have completed your update. To set a read lock, specify the dbDenyRead constant in the options argument of the OpenRecordset method. For more information, search the Help index for "OpenRecordset method."
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1975837
Sorry about that. The comment on Record Locking is abviously posted to the wrong question
0
 

Author Comment

by:cremick
ID: 1975838
Edited text of question
0
 

Author Comment

by:cremick
ID: 1975839
Edited text of question
0
 
LVL 7

Accepted Solution

by:
spiridonov earned 100 total points
ID: 1975840
The way you are setting it will not work in Access'97. What you can do is to put your SQL in form OpenArgs property and  put the following code in form's On Load event:
ME.recordsource=me.OpenArgs

0
 

Author Comment

by:cremick
ID: 1975841
I will give spiridonov's a try and then give it a grade.  Thanks 6/27/1998
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1975842
Did you try my first posting (the one that was not to the wrong question) ?

DoCmd.OpenForm "frmTransactions"
forms![frmTransactions].recordsource = strSQL
forms![frmTransactions].requery ' Don't know if this line is necessary.
0
 

Author Comment

by:cremick
ID: 1975843
I still don't understand why in the Access 97 help it says this code will work and it doesn't.  When converting the code from 2.o to 97 the code will not work with out your workaround but in a newly created 97 access mdb it works.
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1975844
Without remembering the text from the original question I seem to remember that I have read an artivle about converting 2->97 where forms based no queries with some spesific words in it could be casued to not work correctly after the conversion. I could not find the article when I looked for it today.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 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