By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,611 Members | 1,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,611 IT Pros & Developers. It's quick & easy.

Display an Arbitrary Query in Grid

Oralloy
Expert 100+
P: 983
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:
Expand|Select|Wrap|Line Numbers
  1. Dim displayTarget As Access.SubForm
  2.  
  3. displayTarget = me.[Display: body]
  4.  
  5. ''--displays a table
  6. displayTarget.SourceObject = "Table." & "PCR Process Table"
  7.  
  8. ''--displays a form
  9. displayTarget.SourceObject = "PCR Database Extractor"
  10.  
  11. ''--displays a query
  12. 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
Sep 9 '10 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,662
  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.
Sep 10 '10 #2

Oralloy
Expert 100+
P: 983
@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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Zot_Click()
  2.   Dim displayCaption As Object
  3.   Dim displayTarget As Access.SubForm
  4.   Dim query As String
  5.  
  6.   Set displayTarget = Me![Data Display: body]
  7.   Set displayCaption = Me![Data Display: caption]
  8.  
  9.   query = ("Select [pcr].* FROM [PCR Process Table] [pcr] WHERE [pcr].[pcrTitle] <> ''")
  10.  
  11.   displayCaption.Caption = "Zot Bang!"
  12.  
  13.   'displayTarget.SourceObject = "Table.PCR Process Table"   '' works
  14.   'displayTarget.SourceObject = "PCR Database Extractor"    '' works
  15.   'displayTarget.SourceObject = "Query.Test Query"          '' works
  16.   'displayTarget.SourceObject = "Test Form Subform"         '' works, but subform shows nothing
  17.   displayTarget.SourceObject = "Query.Test Form query"
  18.  
  19.   displayTarget.Form.RecordSource = query
  20.  
  21.   Exit Sub
  22. End Sub
  23.  
Sep 10 '10 #3

NeoPa
Expert Mod 15k+
P: 31,662
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.
Sep 10 '10 #4

Oralloy
Expert 100+
P: 983
[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...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Zot_Click() 
  2.   Dim displayCaption As Object 
  3.   Dim displayTarget As Access.SubForm 
  4.   Dim query As String 
  5.  
  6.   Set displayTarget = Me![Data Display: body] 
  7.   Set displayCaption = Me![Data Display: caption] 
  8.  
  9.   query = ("Select [pcr].* FROM [PCR Process Table] [pcr] WHERE [pcr].[pcrTitle] <> ''") 
  10.  
  11.   displayCaption.Caption = "Zot Bang!" 
  12.  
  13.   displayTarget.Form.RecordSource = query 
  14.  
  15.   Exit Sub 
  16. End Sub 
ALSO, FYI, The SQL query in the stored query is:
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 AS One
  2. FROM [PCR Process Table] AS pcr
  3. WHERE (1<>1);
Attached Images
File Type: jpg Access Form As Displayed.jpg (38.2 KB, 416 views)
Sep 10 '10 #5

NeoPa
Expert Mod 15k+
P: 31,662
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.
Sep 10 '10 #6

Oralloy
Expert 100+
P: 983
@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?
Attached Images
File Type: jpg Access Form As Displayed.001.jpg (34.9 KB, 401 views)
Sep 10 '10 #7

NeoPa
Expert Mod 15k+
P: 31,662
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.
Sep 10 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.