Subform Recordsource not responding (1 Viewer)

[DCrake]

I have a main form subform setup, Mainform unbound subform bound.

I have a txtSearch textbox that is used to create a where condition for the subform.

My Sql is built correctly, as I have tested the results in both a query and a count. The syntax is

Me.SubformName.Form.RecordSource = StrSQL

However it done not refresh the contents. I have tried Requery nothing happens.

The strSQL is something like

Select * From Table Where FieldName Like "*ABC*" Order By FieldName

ABC being the contents of the textbox.

Funny thing is if I enter a wildcard character in the textbox such as * or ? it displays 1 record in the subform.

Anybody any ideas. I have this working in other mdb's with the same syntax.

I have even tried adding a new subform and simplifying the syntax but still not working.

Very Frustrating.

 

[Uncle Gizmo]

If you place a command button on your subform which produces a message box showing you the record source of your sub form:

MsgBox “ >>> “ & me.RecordSource

The message box will return the record source which has been passed into sub form.
You can capture this SQL statement contained in the Message Box by pressing Control C

This is an example of capturing the content of a similar message box:

---------------------------
Microsoft Office Access
---------------------------
>>> 0
---------------------------
OK
---------------------------

Your SQL statement should appear where the “zero” is --- above....

Add the SQL statement to your sub form record source in design view, then open the sub form as a standalone form and see if you get the expected results.

 

[vbaInet]

Funny thing is if I enter a wildcard character in the textbox such as *

If this is the case then the subform is being requeried. You mentioned when you use * it produces one result. Is there supposed to be more results?

I would say look in the Filter property to ensure there's nothing in there.

Also, when you first open the form, does the subform show all records?

 

[DCrake]

The default recordsource for the subform is Select * From Table where False. This shows the grid and the columnheadings but does not list any records.

I cannot put a button on the subform as it is in datasheet view and as it displays no records a button is not available for clicking.

 

[vbaInet]

And you say the built sql StrSQL displays records in a query when filtered?

 

[DCrake]

Yes if I do a Debug.Print strSQL
then paste it into a query it displays the correct number of records ok.

 

[vbaInet]

Any chance of seeing a db that acts this same way?

 

[DCrake]

Have stripped out loads of code so don't expect anything else to work.

To Test open up the badges form and type in Jo in the search box then click Search. Nothing happens.

Close the form then press Ctrl+G and look at the sql and the count test.

 

[Uncle Gizmo]

>>>I cannot put a button on the subform as it is in datasheet view <<<

You can change from datasheet View to Form view for testing.

 

[DCrake]

Sorry but because no records are returnd you get a blank subform. No button.
I did put it on the mainform an returned the recordsource which ws syntactically correct. Tried and tested in query but still no records returned.

 

[vbaInet]

Right David, the problem seems to be coming from the parent form being bound but not linked to the subform. If you clear the record source of the parent form it will work, or you can follow what I did in the attached if you must have it that way.

 

[Uncle Gizmo]

The Result is:

---------------------------
Microsoft Office Access
---------------------------
>>> Select * from tblPeople where firstname & " " & lastname & " " & firstname & " " & Companyname & " " & str(PersonID) Like "*jo*" order by lastname, firstname
---------------------------
OK
---------------------------

this should not be visible: & " " &

Shouldn't --- lastname --- be "txtLastname" (From the AddNew Tab?) If not, where do you want to retrieve last name from? And also the other Variables where are they to come from? (Bearing in mind the AddNew controls are unbound.


Please could you post the expected SQL Statement

 

[DCrake]

Gizmo

I was just returning to tell you all I found the problm, and you were right it was to do with the mainform having a recorsource. I removed it and it works fine.

The sql you are looking at is not mine I am only enhancing the form with other objects. Anyway thanks for your help. We seemed to find the anser more or les at the same time.

Thanks again.
DC

 

[Uncle Gizmo]

Thanks for your reply David; it is clear to me that there is a lot going on in this database that I do not understand. I get quite excited when I discover something I don’t understand because it usually means I have the opportunity to learn something new.

Firstly I cannot find the “field” (Or possibly it’s a property,) “ChildLink”

Secondly, the SQLstatement:
SELECT 0 AS ChildLink, * FROM tblPeople WHERE (((False)<>False));
Doesn’t make sense to me either!

Third:
This SQLstatement:
Select 0 as ChildLink, * from tblPeople where firstname & " " & lastname & " " & firstname & " " & Companyname & " " & str(PersonID) Like "*joe*" order by lastname, firstname

Contains text formatting instructions which are not formatting?

Once I have done some investigations of my own, hopefully I will understand better, however I am sure I am going to need some assistance. Therefore I wondered if I should post my questions back in this thread or start a new one as I don’t want to hi-Jack David’s thread!

 

[DCrake]

gizmo

Don't know where you are finding ChildLink I don't regocnise it?

In the subform if you enter the default recordsource as

Select * From TblPeople Where False;

All this will do is acknowledge that the fields on the subform belong to the bound recordsource and the field/column headings appear in the subform but with no matching records.

The sql string is attempting to concat 4 fields together to make a search string, whereby the txtsearch entry will perform a Like "*???* on the concatenated strings. ie First name, last name, company name and id. So the user can enter lets say, Smith Acme. So any occurance of Smith Acme will appear. So if Joe Smith works for Acme Ltd it will appear in the list.

Not my programming as stated.

 

[vbaInet]

I suppose you didn't see my last post #11.

ChildLink was a field I created to allow the subform to be linked to the mainform but still allow it to return all values. As mentioned in that thread, it's either you link the parent form to the subform or get rid of the record source of the parent form.

 

[DCrake]

I completely missed #11 as you can see from my later posts. Any all hunkydory now. Thanks all the same.

 

[vbaInet]

No probs.

 

[hk1]

I recognize that you've found the problem already but I just want to mention that I think you should use Option Explicit in your code. You're referencing fields and functions that don't even exist. Maybe it's just because you stripped out stuff, I don't know. But I personally feel that the lack of Option Explicit is a huge no no.

 

[vbaInet]

I recognize that you've found the problem already but I just want to mention that I think you should use Option Explicit in your code. You're referencing fields and functions that don't even exist. Maybe it's just because you stripped out stuff, I don't know. But I personally feel that the lack of Option Explicit is a huge no no.

DCrake is an expert in this. Notice he's an Administrator on here. He knows this Option Explicit quite well

The database he posted was just a test db, stripped from his original db.