Display an Arbitrary Query in Grid

[Oralloy]

Folks,

I'm old to programming, but a bit of a novice in Access as yet.

My problem is that I am building a tool in access to collect data from external databases, present it for review to the user, and finally output the data to a file as a CSV.

So, I can find external databases and query them, but how best to display the results in tabular form.

I've considered using an unbound sub-form and trying to bind the data into it, however I can't figure out how to get the thing to take a recordset, or just take the SQL source for my query.

It's straightforward enough to display an arbitrary table or form using code like:

Dim displayTarget As Access.SubForm

displayTarget = me.[Display: body]

''--displays a table
displayTarget.SourceObject = "Table." & "PCR Process Table"

''--displays a form
displayTarget.SourceObject = "PCR Database Extractor"

''--displays a query
displayTarget.SourceObject = "Query.Test Query"

So - three questions:
1) Am I on the right track?
2) How to bind arbitrary sql into my sub-form?
3) Is there a better way to go about this?

Thanks!
Oralloy

[NeoPa]

1. Possibly? That code looks a bit weird to me but that may be because we're used to different approaches.
2. Forms (and those forms used as subforms are included of course) have a property called .RecordSource which specifies the Table, QueryDef or SQL code that the form is Bound to. This can be changed in design view as well as in code at any time.
3. I would certainly consider using the answer to point #2 in preference to your current approach. Only you can tell us when you've tried it whether it suits you better or not. I imagine it would make for less complicated code when all is said and done.

[Oralloy]

@NeoPa,

Ok, that sort of works....

I created a trivial query, and I can bind it in at run-time.

However, when I change the record source of the query, I get an ugly result - a single column, all blank, but the correct number of records.

Should I be using something besides this code:

Ummmm....the code tags aren't working - not sure why.

Private Sub Zot_Click()
  Dim displayCaption As Object
  Dim displayTarget As Access.SubForm
  Dim query As String

  Set displayTarget = Me![Data Display: body]
  Set displayCaption = Me![Data Display: caption]

  query = ("Select [pcr].* FROM [PCR Process Table] [pcr] WHERE [pcr].[pcrTitle] <> ''")

  displayCaption.Caption = "Zot Bang!"

  'displayTarget.SourceObject = "Table.PCR Process Table"   '' works
  'displayTarget.SourceObject = "PCR Database Extractor"    '' works
  'displayTarget.SourceObject = "Query.Test Query"          '' works
  'displayTarget.SourceObject = "Test Form Subform"         '' works, but subform shows nothing
  displayTarget.SourceObject = "Query.Test Form query"

  displayTarget.Form.RecordSource = query

  Exit Sub
End Sub

[NeoPa]

I started looking at this then realised I wasn't sure what items you were dealing with.

[Data Display: body] is the name of your subform control?
[Data Display: caption] is what type of item?

I see you're using the .SourceObject property rather than the .RecordSource one.

.SourceObject is a property of the subForm control, whereas .RecordSource is a property of a form. For a SubForm form it is related to the form which is the .SourceObject of the main form.

[Oralloy]

[Data Display: body] is the sub-form control, yes.

[Data Display: caption] is the label that is provided for the sub-form.

So my code sets the .SourceObject property of the sub-form control to bind a query to it.

Then I set the .RecordSource property of the query.

Perhaps a bit more pedantic than you're used to. If I just bind the query as part of my form "design" and only set the RecordSource part of the query, I get the same result...

Private Sub Zot_Click() 
  Dim displayCaption As Object 
  Dim displayTarget As Access.SubForm 
  Dim query As String 
  
  Set displayTarget = Me![Data Display: body] 
  Set displayCaption = Me![Data Display: caption] 
  
  query = ("Select [pcr].* FROM [PCR Process Table] [pcr] WHERE [pcr].[pcrTitle] <> ''") 
  
  displayCaption.Caption = "Zot Bang!" 
  
  displayTarget.Form.RecordSource = query 
  
  Exit Sub 
End Sub

ALSO, FYI, The SQL query in the stored query is:

SELECT 1 AS One
FROM [PCR Process Table] AS pcr
WHERE (1<>1);

[NeoPa]

That seems to explain the problem. A SubForm control can only have a SourceObject that is either a Form or a Report.

It is the Form that is associated with the SubForm control that needs its RecordSource set up to either the QueryDef (saved query) or the SQL string.

[Oralloy]

@NeoPa,

Perhaps I'm being dense, but when I try to use a blank sub-form for the display, instead of a stored query, I get the expected result - I see nothing, as there are no fields on the form. ... and I see that there are 55 records, just as in the previous screen shot.

Part of this exercise is that I can't guarantee the number of fields in the query, so I'd like to be able to show a grid and have the query display "as expected".

Any thoughts on how I might get past this?

[NeoPa]

I must rush for now, but if you're interested I'll be around later this evening when I get back in (after 21:00 GMT). I'm happy to chat through it with you on Skype if you have it (or can get it installed by then). Let me know if this interests you and I'll PM you my details.

BTW. I can only work on versions <= 2003 atm, but I don't expect that'll be a great issue.