SQL produces no records as a form's RecordSource, but does as a standalone query.

[Solipcyst]

The RecordSource is a simple "SELECT field1, field2, field3 FROM table WHERE field1=value". I am not passing a WHERE clause in my DoCmd.OpenForm statement. The where clause is set programmatically in the Open even of the form (I make sure to Requery) with the value of the where clause being passed via OpenArgs.

Using Debug.Print on the form's RecordSource (actually via Timer, just to be sure nothing is changing it after the Open event) returns a SQL string which I can then plug in to query design and verify that there are in fact records, yet they fail to populate in the form (Debug.Print on the form's Recordset.RecordCount yields 0).

I am able to add/edit records to the table the query references using the bound controls on the form. The form has a blank filter, is not set to filter on load, and does not allow filters. So, I can't imagine that is messing things up.

I've noticed that not using a where clause will correctly return all records in the table and populate the bound form controls. Very confused.

 

[vbaInet]

I don't see the use of the Timer and if you want to handle records you should be doing it in the Load event instead.

If you get rid of your Timer and manually type in the Record Source what happens?

Could you also explain the problem a bit more clearly.

 

[Solipcyst]

Hello, and thanks for your reply.

Yes, the Timer is unnecessary. I only used it to illustrate a point that I don't have any code anywhere that could potentially be altering the RecordSource of the Form after it has opened at any point.

I'l try to be more concise with my problem.

I have a SQL statement set as a Form's RecordSource with Text Boxes in the Detail of the Form bound to each of the fields in said SQL statement. The SQL statement includes a Where clause and is known to be "good", in that if I save it as a query and run it, it returns records. However, it is not returning any records on the Form whose RecordSource I have plugged it in to.

If I remove the where clause from the SQL, it returns records (as expected).

So, the question remains: Why would this SQL statement return records as a query (or a DAO/ADODB RecordSet, for that matter) but not return records as a Form's RecordSource?

EDIT: I've tried moving the code that sets the RecordSource from the Open event to the Load event of the Form. No change.

 

[vbaInet]

Have you thought about using the Filter and FilterOn properties of the form via code?

 

[Solipcyst]

Yes, I have. That doesn't seem to work either. I've also tried hard-coding the SQL statement without a where clause to the form and passing the where clause in the DoCmd.OpenForm call. Absolutely confounded.

EDIT: Furthermore, even if I was able to get this to work using a filter, I admit that I don't know if I would have a way of easily preventing a user from removing that filter.

 

[vbaInet]

Can you show me the way you implemented the Filter properties? The code.

Also, have you considered using the WHERE argument of the OpenForm command? Here -->

http://baldyweb.com/wherecondition.htm

 

[Solipcyst]

Yes, I've tried the where condition with OpenForm. No good.

For trying the Filter, I've set the form to FilterOnLoad=Yes and AllowFilters=Yes. In the Load event of the form, I then set Me.Filter = "[Field]='Value'" (single quotes used because this particular field is a string). More specifically: Me.Filter = "[Field]='" & passedValue & "'".

 

[vbaInet]

You would normally, set the criteria before turning on the filter.

Perhaps you need to create a new form.

Can you upload a stripped down version.

 

[Solipcyst]

Ok, that last reply gave me an idea, and I seem to have gotten the filter to work properly.

The RecordSource of the form is now hard-coded as the entire table without any where clause. I have AllowFilters=Yes but FilterOnLoad=No. I then set the Filter for the form during the load event as follows:

Me.Filter = "[Field]='" & passedValue & "'"
Me.FilterOn = True
Me.Requery 'Not sure if this is necessary

My only remaining question now is how to prevent the user from removing the filter...

 

[vbaInet]

You can use the form's ApplyFilter event and Cancel when the ApplyType is 0.

But I don't see why the OpenForm command won't work. With this you won't need to worry about stopping the user from removing it.

Can you show me that line of code?

 

[Solipcyst]

Actually, I'm back to square one now, it seems. This is so confusing....

I was trying to set up the OpenForm command with the where clause and now form doesn't seem to want to load any records from the query even without a where clause.

Form.RecordSource = "SELECT field1, field2, field3 FROM table;"

On the calling form:
DoCmd.OpenForm "form", , , , , , "string"

The last bit there is just a string of text I need to pass to the form that isn't used by or relevant to the form's recordsource. I know where to put the where clause portion in that command, but that would be moot until I can figure out why the form isn't populating the controls with no where clause.

 

[vbaInet]

Form.RecordSource = "SELECT field1, field2, field3 FROM table;"

Are you doing this in code? And in what event?

 

[Solipcyst]

Ok, I appear to be missing something extremely fundamental.

I've noticed that if I click the sort Ascending or sort Descending button in the filter tab of the ribbon (I suppose I should have mentioned this is Access 2007), suddenly all the records are populated. Does this provide any clues as to what I'm missing?

 

[Solipcyst]

Are you doing this in code? And in what event?

Not in code. In the form properties.

 

[vbaInet]

Alright, I'm working blindly now without seeing a sample db.

 

[Solipcyst]

Ok, I will try and duplicate the problem in a sample DB and upload it here in a few (if I'm allowed to upload files). Thanks for all your help thus far, by the way.

 

[vbaInet]

10 posts is the threshold. You are now on 11 so you should be able to upload files now

 

[Solipcyst]

Ok, here is a sample with the problem. Again, I suspect I'm missing something very rudimentary. I admit that I'm very new to all this, and I don't normally even bother with bound controls.

Attached is a zipped accdb. Please open "frmOpenMeFirst", and thanks for looking in to this for me!

As you can see, after the second form is opened, it doesn't populate the text boxes. Only if I perform some kind of filter function (such as sorting) does it populate.

 

[vbaInet]

So, I've just selected both Test 1 and Test 2 and it runs ok.

 

[Solipcyst]

Right you are, sir. It didn't work until after I re-opened the database for me, though. Strange.

Anyway, it doesn't matter anymore because I've come up with a fix for the problem on my end. I added the following to the load event of the form:

Me.OrderBy = "[AnyField]"
Me.OrderByOn = True
Me.OrderByOn = False

It's a bandaid, but it works. Still unsure why the data won't populate without performing some kind of sort/filter on it first.

Thanks again for all your help!