Solved

Access 97 SQL string won't open unbound form

Posted on 1998-06-25
15
271 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
  • 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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