Full Version: Working with a recordset
UtterAccess Forums > Microsoft® Access > Access Forms
I'm not really sure where this should be, so I'm posting it in multiple places here.
That I'm doing is have a console that generates queries on the fly, plugs in links, sets WHERE criteria, all that good stuff. Right now I have it setting a query definition and running the query which spits out a datasheet view.
What I want to know is if there's something that can hold a recordset on a form rather then launching a seperate query. I'm a VB man, so I'm used to having the FlexGrid or something similiar that I can load a recordset into or feed a SQL string to populate it with.
I was told there's nothing like that that can be used in Access/VBA, but there has to be something... no?
Would really appreciate any help! Thanks!!
Dom DXecutioner
Please refrain from cross-posting as this confuses members and it's also not allowed ...
Is far as your question, i'm sure you could create a temp recordset by declaring a module level object variable and manipulate it accordingly. I'm not sure I understand what you're trying to accomplish and apologize for not being able to further assist, but if you post more info i'm sure one of us will be able to help you ...
Oh, sorry about the multi-post. Will never happen again.
Ok, what I'm trying to do is create a custom report console. When you select criteria, fields, etc you click submit and it gives you your results. Right now I do that by launching a query and it comes up in a new screen in datasheet view.
What I want to do is have it returned to a control on the form itself so they can do other things to it. Maybe sort by certain columns, a button to export to excel, etc. Maybe they run a query on the top of the page, see the results at the bottom and notice they need something else, so they go back up to the top and modify their criteria and search again and it's all done right there on the one form.
Why I want this is because when you launch a custom query that's it, it ends the interaction. I can't put a button on the query datasheet view to return to the search page, to export to excel, etc.
You have cross posted this thread in multiple forums. Please post questions only to one forum. Double posting and cross posting does not improve your chances of finding a solution.
Double Posting" and "Cross Posting" are not allowed here at UA as they only create confusion with the people that may try to help you.
Please post your problem in only one forum and only once within that forum.
The correct forum for this post would be Access Forms since the main theme of your question relates to working with records on a form
UA Moderator
So I've learned. frown.gif I apologize for that.
Dom DXecutioner
it's usually never a good idea to show the user the actual queries or tables in you db (MPO). I would create a form with the required command buttons to do what's needed, then i would place a listbox control onto the form and name it "listQueryResults". You can then you the RowSource property to change the contents dynamically ...
You may have to do some extra coding to set the correct number of columns per query (if different), plus you'll have to come up with a way to select the correct query. For example you could add a combobox onto the form and populate it with your selections and on the OnClick event of your "View Results" command button have the procedure change the RowSource of the ListBox according to the combobox selection ...
Private Sub CmdViewResults_Click()

     [color="green"]'// populate listbox where the source could be from a Table Name,
     '// Query Name, or SQL statement [/color]
     Me.listQueryResults.RowSource = "NameOfQuery"

End Sub

I hope this helps a bit ...
The problem with the list rowsource is some query strings are too long. I get 'The setting for this property is too long.'
Dom DXecutioner
can you post an example of your SQL ...
Create a standalone query and use it as the rowsource.
XyonX, no I can't because it's different depending on what the pick.
Glenn, that won't work. There's over two dozen tables and over 150 fields. The user builds a custom search on the fly, so I can't know what they want in the order they want beforehand.
I, for one, believe it can done with Access and without the use of any ActiveX controls . . . .
There are methods in VBA that allow for the dynamic creation of fileds in a form. It will be a lot of work and problems to circumvent, but I believe it can bo done.
I would start by first looking up the code required to dynamically place controls in a form. Then test it to perfection.
Then I would add code to the existing code to count the number of fields used. That way you'll have the number of controls to generate. You can use the VarType() to determine what attributes to assign to the control as it is dynamically built.
Then inter-weave the dynamic control creation into your existing module/procedure.
I know it's a rough description, but if you can envision it, it may provide a possible solution.
hope this helps
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.