Solved

Access 97 SQL string won't open unbound form

Posted on 1998-06-25
15
278 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

751 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