Results 1 to 13 of 13
  1. #1
    Join Date
    May 2010
    Posts
    6

    Unanswered: Limiting Form Records

    I presently have a query that returns multiple records (let's say 100). This is too many to display on one screen without scrolling, so I want to limit the return to say 20 records, and split it between 5 different screens (forms).

    I've figured out how to do this using Select Top 20 ... for the first batch, and then nulling this into the next screen to get the next 20, etc.

    The problem with this method, is that I'll have a dedicated form and query for each of these pages. I've got to do this with multiple queries, so that could mean a lot of pages to keep up with. Then, if I need to make a format change, add something, etc., it will take forever...

    I'd like to do this from within VBA to create the query and display it on a form dynamically. I use DoCmd.OpenForm Docname, , , strWhere regularly, but this where condition only covers part of what I need. Basically, I need to create the entire query on the fly using SQL, but how do I then attach this back to the form?

    Also, using the Select Top method to limit the records returned, this won't work with dynamically created queries because it needs to have a created query to null to get the next batch of records...

    Is there a way to do what I'm wanting to do?...

    Thanks,

    Danny

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I envision a list box that displays 20 records at a time with two command buttons for next and previous 20-record groups.
    Jerry

  3. #3
    Join Date
    May 2010
    Posts
    6
    No, I need each field value in a text box. It suits my formatting much better (conditional formatting, etc.).

    Other ideas? Thanks,

    Danny

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Give an example of the layout. If you are displaying 3 fields across, will you have 60 text boxes stacked up to look exactly like a list box, with the capability of formatting each one?
    Or just 20 text boxes?

  5. #5
    Join Date
    May 2010
    Posts
    6
    It is currently 17 boxes wide, and it looks like 20 high will fit with my current header/footer and text sizes. Yes it basically looks like a list box, but almost every box has formatting that corresponds to the value in it. I've attached a pic of the basic screen I'm using for development (without all the pretty header stuff) and one with it populated by the query.

    Thanks,

    Danny
    Attached Thumbnails Attached Thumbnails Layout.jpg   Layout2.jpg  

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Now that the details can be seen, I don't know how the pros would do this so I'll have to leave it to smarter minds on the forum.

    Is this were my job, I would number each record (all 100) sequentially when the form opened to be able to deal with each block of 20 records for display.
    Good luck.

  7. #7
    Join Date
    May 2010
    Posts
    6
    Anyone else have any input on this? I've been experimenting with using a sub query to get what I need. I'd just have to delete it and re-create it each time as needed for the different data groups.

    So far, I've haven't gotten the sub query to work quite right (I will...), but I'm open to other ideas as well...

    I've also tried to have a generic query, and then use the where clause to narrow it down, but I haven't been able to narrow it all the way down to where I need... Maybe have to try to nest two queries in the where statement once I get the sub queries figured out....

    Thanks,

    Danny

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If I'm understanding correctly,

    You could have your text boxes labeled like: Text1, Text2, Text3, etc...
    and then loop through the controls, testing the values and setting the colors (perhaps in the OnCurrent event) as such:
    For X = 1 to 20
    if me.controls("Text" & X).Value = "X" then me.controls("Text" & X).Forecolor = 255 else me.controls("Text" & X).ForeColor = 0 (and do the same for .backcolor colors)
    next X

    or you could utilize it with a select case statement
    For X = 1 to 20
    Select Case me.controls("Text" & X).value
    case "A"
    me.controls("Text" & X).backcolor = 255
    Case "B"
    me.controls("Text" & X).backcolor = 0
    end select
    next X
    Last edited by pkstormy; 05-11-10 at 00:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jan 2008
    Posts
    3
    I'm not having any trouble getting the text boxes to color correctly (as shown in the pictures I attached).

    What I need to know is how to dynamically create a query and attach it to a form. I've made some progress on this, but the load times are longer than I want. I'm now working on pre-loading pages, and that's making things even more complicated...

    Danny

  10. #10
    Join Date
    May 2010
    Posts
    6
    As I mentioned, I've figure out some of this. What I'm currently playing with is one form, that's tied to a query. In my code, I update the query with a new SQL string, and then close and re-open the form. This brings up the next batch of information, and works quite well. I've only got one form and query to contend with, so that's good.

    Here's how I'm doing that (abbreviated):

    Code:
    'Set up my values
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strQueryName As String
    Dim strFormName As String
    
    'Set initial values
    strQueryName = "Display_Query"
    strFormName = "Display_Form"
    
    'Point it to the database and query
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strQueryName)
    
    'SQL statement
    strSQL = "SELECT TOP 20 * FROM (SELECT TOP 20 * FROM...WHERE...) AS RESULT1 ORDER BY RESULT1.XXX..."
    
    'Write the SQL statement to the query
    qdf.SQL = strSQL
    
    'Open the form
    DoCmd.OpenForm strFormName
    
    'Wait 10 seconds to view the data - this is a pause routine I added
    Pause (10)
    
    'Close the form
    DoCmd.Close
    
    'Next SQL statement
    strSQL = "SELECT TOP 20 * FROM (SELECT TOP 40 * FROM...WHERE...) AS RESULT1 ORDER BY RESULT1.XXX..."
    
    'Write the SQL statement to the query
    qdf.SQL = strSQL
    
    'Open the form
    DoCmd.OpenForm strFormName
    
    'And so on...
    I don't actually have all this happening one at a time. I've got the SQL and form opening set in a loop that increments as needed to keep the code small and easier to update. It was just easier to display this way.

    My problem is that there's probably 3-4 seconds of dead time between when the form closes and the next one opens. I've helped this by re-arranging a couple things, but the delay is still longer than I need. Here's what I'm currently doing:

    Code:
    'SQL statement
    strSQL = "SELECT TOP 20 * FROM (SELECT TOP 20 * FROM...WHERE...) AS RESULT1 ORDER BY RESULT1.XXX..."
    
    'Write the SQL statement to the query
    qdf.SQL = strSQL
    
    'Open the form
    DoCmd.OpenForm strFormName
    
    'Next SQL statement
    strSQL = "SELECT TOP 20 * FROM (SELECT TOP 40 * FROM...WHERE...) AS RESULT1 ORDER BY RESULT1.XXX..."
    
    'Write the SQL statement to the query
    qdf.SQL = strSQL
    
    'Wait 10 seconds to view the data - this is a pause routine I added
    Pause (10)
    
    'Close the form
    DoCmd.Close
    
    'Open the form
    DoCmd.OpenForm strFormName
    
    'And so on...
    I really need the transition from form to form to be almost instantaneous. I've tried me.refresh, me.requery, etc. and they don't help. I don't think they're actually re-running the query because I checked it, and it's getting updated properly...

    I've thought about having two forms and toggling back and forth so one is loading while I've got my delay, but that would mean two to update unless I can have two instances of a form open at the same time and can keep track of them...

    Ideas?

    Thanks,

    Danny

  11. #11
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached database with Form1 shows how records can be displayed in individual text boxes, a block at a time on a form. I believe you want to display 20 rows by 17 columns. My example displays 10 rows by 5 columns, and that can be expanded unless someone on the forum can explain why my choice of methods might be unwise for this application.

    The demo is built on the idea that adding sequential numbers to your data allows you use those numbers to control the order and groupings of the display.

    Sequential numbers were added to the data in a recordset. Here is simplified code for generating the numbers:
    Code:
    SELECT Table1.*, (SELECT Count(*) FROM Table1 As T2 WHERE T2.[SEQ NO] <= Table1.[SEQ NO]) AS RowNumber
    FROM Table1
    ORDER BY Table1.[SEQ NO];
    The field SEQ NO is a key field with unique values that make it possible to generate sequential numbers for all records. So if there are 115 records in the table, this method generates record numbers 1 through 115.
    Each of the 10 rows on the form is identified with a number; my form label captions start with "1" though "10"; the values in the row label captions are used in a created query. I could have used the Tag properties of the first column of text boxes to store these numbers if I did not want to use labels.

    When the user clicks the button for next page, lbl1 Caption is increased by 10, so "1" becomes "11", and this is done for all 10 labels. A query is created for each row of text boxes using a loop. For example, in the first loop iteration for row #1 of the second page, where the label values run from "11" to "20", the query pulls in the record with rowNumber=11 (just one record) into the recordset, then code is run to populate the row, and the program loops through the same process for the other nine rows. Paging backwards on the form is done in a similar manner, except that the label values are decreased by 10 prior to running the code that populates the text boxes.

    The code prevents paging backward if records #1-10 are currently displayed, and it prevents paging beyond the last set of records. I also have a function that makes the final rows in the last set of records invisible if there is no data.

    Each column of text boxes are similarly named and numbered: txtSEQNO1, txtSEQNO2...txtSEQNO10. This makes it easier to work with the text boxes in a loop, as pkstormy has stated.

    I hope this might give you some ideas on working out your scheme to page through blocks of records. I have proven to myself that this can be done and multiple forms are not needed.
    Jerry
    Attached Files Attached Files
    Last edited by JerryDal; 05-12-10 at 14:12. Reason: clean up code, add command button enable/disable

  12. #12
    Join Date
    May 2010
    Posts
    6
    I can't open your database. I'm only running access 2000...

  13. #13
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Converted file to ACC 2000 is attached. File revised. Clean up code and add command buttons disable when page limits reached.
    Attached Files Attached Files
    Last edited by JerryDal; 05-12-10 at 14:15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •