Search - subform requery?

[gobogirl]

Hi.

I have a search form (enter text into a textbox, txtCoName then click cmdSearch) which works fine if displaying results in a listbox. However, i would like to display the results in a subform (continuous). My code to requery the subform is in VB and is not working.

'Pass the SQL to the subform
Me.subfrmCompanyResult.Requery = strSQL & " " & strWhere & "" & strOrder

Any and all suggestions, directions, corrections.... help!! is vehemently appreciated.

 

[Uncle Gizmo]

Maybe try it with a space , I'm doubtful this will work because SQL is quite good at formatting itself, if you've made a simple mistake.

Me.subfrmCompanyResult.Requery = strSQL & " " & strWhere & " " & strOrder

 

[adam_fleck]

This might work:

'Pass the SQL to the subform
Me.subfrmCompanyResult.Form.RecordSource = strSQL & " " & strWhere & " " & strOrder

You used interesting code which makes it look as if a requery action can be set to something, though I don't know how that might be useful.

 

[gobogirl]

Still unsolved: Search - subform requery

I've tried both suggestions and tried variations of them - to no avail. I've also found some other codes from a search thru the whole discussion forum, tried them and again, nothing but the debugger.

Please, any more ideas anyone?

 

[Uncle Gizmo]

On second thoughts this isn't right !
Christ I hope I don't start having third thoughts and forth thoughts!

Me.subfrmCompanyResult.Requery = strSQL & " " & strWhere & "" & strOrder

Correct me if I'm wrong but you just can't assign a string like that. You need to assign the string to the record source of the subform, and then requery.

I would suggest that you replace your string variables with the actual SQL string, include in that string some values you know you have in your table so that you can be sure that it will return results. Do that and check that the results it returns are the ones you are expecting. Once you know you have it working, then you can change the actual values back into variables. Then the fun begins !

 

[adam_fleck]

It's puzzling that "Me.subFormCompanyResult.Form.RecordSource" isn't working for you. I use this sort of expression a lot, and haven't had serious problems getting it to work.

To work out what's going wrong, try typing the code rather than copying it. VB's intelli-sense could help you diagnose the problem.

Here's what intelli-sense should do: typing "Me." should produce a list of objects on your form. "subFormCompanyResult" should be on this list. If you select "subFormCompanyResult", and this is recognised as a subform, typing "." should produce a list with "Form" on it. Selecting this and typing "." should pull up a list with "RecordSource" on it.

If this works, then your form objects are OK, and the problem may be in the SQL statement.

To debug the SQL statement, put a statement like this in your code before the line that tries to set the record source for the subform:

strSQLFinal = strSQL & " " & strWhere & " " & strOrder

Make this a break point (click in the grey margin running down the left-hand side of the code window - the line will be highlighted brown and a brown dot will appear in the margin).

Directly underneath the break-point line, put

Debug.Print strSQLFinal

Display the immediate window (View, Immediate Window Ctrl+G), and then go back to Access and run your code. Execution will stop on the strSQLFinal line and the code editor will appear. Press [F8] to step through the code one line at a time. When you step past the Debug line, the SQL statement stored in strSQLFinal will apppear in the Immediate Window at the bottom of the screen. Copy it, reset code execution (Run, Reset), return to Access, create a new query, then, View, SQL, delete any pre-set text - e.g. "Select;" and paste in what you copied from the Immediate Window. Now try running this query.

 

[gobogirl]

Thank you!!

Thank you very much! It's working.

Adam, thanks for calling my attention to manual-code-typing. Your code DID work. It's quite embarassing to realise what a dolt I can be.

Cheers.