none
Do not load subform. Sort the query and load only filtered data into Datasheet. RRS feed

  • Question

  • Hello,

    I've been struggling for awhile to find some info on how or if it is possible to load subform's datasheet at run time and filtered. There are a lot of records in the database which are old, hence no need for them to be loaded into datasheet and also to speed up the process. 

    On a Form I have a button, 4 TextBoxes and a datetimepicker that are used to sort the 5 columns in the sunform.

    When I limit the query to lets say todays date, only a few records are displayed but then when I try to sort the data, it produces an error and when I debug, it shows that the Recordset is empty. Is it possible in Access to sort the query, then load datasheet?

    Any help or suggestion is appreciated.

    Savalan

    Wednesday, July 18, 2018 9:40 PM

Answers

  • Hi Savalan,
    From your message I now understand that you did not actually use the sub form for which it was intended. Actually, you simply want a data sheet for which you can determine the displayed data and where you can search. There are countless possibilities for this but they do require some knowledge. A few tips:

    - In the Event Open of the form you can set the RecordSource on the desired set of records. This can be a query or even more dynamic, a VBA Function String (see next).

    - You can call a procedure that allows you to reset the entire RecordSource. This allows users to change the source while the form is open. To do this you need to create a VBA Function String that represents the SQL of the RecordSource. As a tool you can make a temporary query so that you can easily put together the SQL. In the query grid, click View> SQL View. You create a VBA Function of the type String from that SQL.

    I also work with the Ribbon UI (User Interface) so that users can choose which recordset they want. That requires extra knowledge, but you can also call a procedure differently (as you say by double-clicking in a field). Because on a data sheet you can not place a button.

    Perhaps too complicated for you: I often let users make settings via a dialog. Users then only have to select what they want to see and the SQL for the RecordSource is then built and set. This gives even more possibilities because with the same selection you can also print a report, create a PDF, export to Excel and so on.

    - The most convenient for you may be to set the Filter property of the form with code. Type in the VB editor Me.Filter, select Filter and press F1 for more info to learn more about it.

    - You can also have the user filter manually on any field.

    - I have made a search function myself. If you create assignments that allow you to set different filters, you may not need that.

    I wish you good luck with your project.


    • Marked as answer by Savalan Monday, July 23, 2018 9:01 PM
    Friday, July 20, 2018 9:09 PM
  • Hi DBgun,

    I have to admit I learned a lot from your posts on here or older posts online(from 7, 8, 10 years ago) :) and if I am guessing right on YouTube(wow, you're very active).

    Anyways, My problem is that I don't know what is the syntax to check whether the cell or the row on datasheet is empty so I can say something like: if the condition is right and no data then Exit Sub.

    Best regards,

    Savalan

    Hi Savalan,

    Thank you for the nice comment. In this case, I was saying, don't even bother checking if the cell is empty or not. Rather, let Access tell you there's an error. Your code then traps this error, before the user sees it.

    For example, if the user double-clicks on an empty cell or datasheet, what is the error message and number you get? Once we know it, we can set an error handler to trap it.

    Can you post a copy of the code for the Dbl_Click event and the error number you get if the datasheet is empty?

    Cheers!

    • Marked as answer by Savalan Monday, July 23, 2018 9:01 PM
    Monday, July 23, 2018 8:39 PM

  • Hi,

    Here's how you can trap error #2498...

    At the top of your procedure, enter the following line:

    On Error GoTo errHandler

    For example, your code might start out looking like this:

    Private Sub SubformName_DblClick()
    On Error GoTo errHandler
    ...

    Then, just before the End Sub line, enter something like the following:

    ...
    errExit:
        Exit Sub
    
    errHandler:
        If err.Number = 2498 Then
            MsgBox "Sorry, no data to show...", vbInformation,"Error"
        Else
            MsgBox err.Number & ". " & err.Description, vbInformation, "Error"
        End If
        Resume errExit
    
    End Sub

    Hope it helps...

    • Marked as answer by Savalan Monday, July 23, 2018 9:01 PM
    • Unmarked as answer by Savalan Monday, July 23, 2018 9:05 PM
    • Marked as answer by Savalan Monday, July 23, 2018 9:05 PM
    Monday, July 23, 2018 8:53 PM

All replies

  • Hi,

    Not sure I understand your question but you can open the form empty at first and only load the data after the user selects the filter, or you could refresh the subform with a new recordset you created each time you apply a new filter.

    Just my 2 cents...

    Thursday, July 19, 2018 1:11 AM
  • Hello DBGuy,

    Thank you for the reply. I will try it in a basic app. Sorry, my explanation was a bit confusing. 

    Regards,

    Savalan

    Thursday, July 19, 2018 4:15 AM
  • Hi Savalan,

    You're welcome. Please let us know how it goes or if you need more help.

    Cheers!

    Thursday, July 19, 2018 3:48 PM
  • Hi Savalan,

    I assume that the subform is linked to the data of the main form. Make sure the link is set correctly. I.e. that the subform displays only those records that are actually linked to the record that is displayed in the main form. So every time a different record is taken, the subform is updated. If too many records are displayed, you can set a criterion for the source of the subform. Perhaps that is the easiest for you. Or do you want to set a filter for the subform via the main form? There are several possibilities.


    Thursday, July 19, 2018 9:25 PM
  • Hi Peter,

    Thank you the reply. Actually it is a fairly large database with thousands of records. They asked me to add a Search form to it. They don't want the subform to never be filled with all the records. So subform should start empty when the form is opened.

    I put 2 Textboxes (1 for searching 4 columns, 1 is for Date field), 2 Buttons(1 for each Textbox) and 5 Radiobuttons. Radiobuttons decide which column has to be searched. As for your question, there is no connection between main form and subform. The purpose of this form is to search for records and then when the first column of each record is double clicked, another form opens and subforms clicked row id is passed to it (which I have implemented and is working).

    The requirements are:

    1. When the form opens subform should be empty

    2. Subform should display 1 or a few records depending on the search criteria

    3. When the Textbox is cleared with keyboard backspace key, Subform should be cleared again (which displays all records).

    Best regards,

    Savalan


    • Edited by Savalan Thursday, July 19, 2018 9:58 PM
    Thursday, July 19, 2018 9:56 PM
  • Dear Savalan,
    You want to use the subform for which it is not intended. A sub form should always (in principle) be linked to the main form. If you want fewer records to be displayed, you have to arrange that based on the source of the main form. This is the basis for the direction that you will have to go.
    Friday, July 20, 2018 6:39 AM
  • Hi Peter,

    Thank you. Following your advice I tried other ways to implement this. I found out that using Split Form is very easy to search the datasheet. Now I have to figure out how to open the form with an empty datasheet and how to apply Double Click event to the first column because datasheet is not visible until you run the app. 

    Regards,

    Savalan

    Friday, July 20, 2018 6:47 PM
  • Hi Savalan,
    From your message I now understand that you did not actually use the sub form for which it was intended. Actually, you simply want a data sheet for which you can determine the displayed data and where you can search. There are countless possibilities for this but they do require some knowledge. A few tips:

    - In the Event Open of the form you can set the RecordSource on the desired set of records. This can be a query or even more dynamic, a VBA Function String (see next).

    - You can call a procedure that allows you to reset the entire RecordSource. This allows users to change the source while the form is open. To do this you need to create a VBA Function String that represents the SQL of the RecordSource. As a tool you can make a temporary query so that you can easily put together the SQL. In the query grid, click View> SQL View. You create a VBA Function of the type String from that SQL.

    I also work with the Ribbon UI (User Interface) so that users can choose which recordset they want. That requires extra knowledge, but you can also call a procedure differently (as you say by double-clicking in a field). Because on a data sheet you can not place a button.

    Perhaps too complicated for you: I often let users make settings via a dialog. Users then only have to select what they want to see and the SQL for the RecordSource is then built and set. This gives even more possibilities because with the same selection you can also print a report, create a PDF, export to Excel and so on.

    - The most convenient for you may be to set the Filter property of the form with code. Type in the VB editor Me.Filter, select Filter and press F1 for more info to learn more about it.

    - You can also have the user filter manually on any field.

    - I have made a search function myself. If you create assignments that allow you to set different filters, you may not need that.

    I wish you good luck with your project.


    • Marked as answer by Savalan Monday, July 23, 2018 9:01 PM
    Friday, July 20, 2018 9:09 PM
  • Hi Peter,

    Thank you so much for thorough explanation. You and DBguy helped me a lot to finish this task. 

    I created a sub (vba) to display empty datasheet that has RecordSource =  where date column's value is equal to the next day. This will always produce an empty datasheet and when needed I can call this sub.

    "WHERE [Date Column] =  Date() + 1"

    For the selection of Radiobuttons, I used Select Case, which depending on selected Radiobutton sets RecordSource accordingly. 

    Select Case True
       Case FrameOptions.Value = 1
          strRecordSource = "SELECT ..."
       ...
    End Select

    As I mentioned when first column is double clicked a new form opens and Form's cell value is passed through OpenArgs to the other form. 

    Private Sub FirstColumn_DblClick(Cancel As Interger)
    
       DoCmd.OpenForm "frmTheOtherForm", View:=acNormal, OpenArgs:=Me.myVal
    
    End Sub

    I have implemented validations for empty and/or valid Textboxes' and Radiobutton's values. There is only one thing left. When datasheet is emply, how can I check and prevent the new form from opening when the first column is double clicked (which throws an exeption).

    Best Regards,

    Savalan


    Monday, July 23, 2018 3:56 PM
  • Hi Savalan,

    One approach is to try using an Error Handler. With it, you can trap the exception and exit the routine gracefully when the user double clicks on an empty datasheet.

    Just a thought...

    Monday, July 23, 2018 4:04 PM
  • Hi Savalan,

    I do not know exactly what you mean. Do you want to count records first before a form is opened and then cancel it if there are no records?

    Monday, July 23, 2018 4:14 PM
  • Hi DBgun,

    I have to admit I learned a lot from your posts on here or older posts online(from 7, 8, 10 years ago) :) and if I am guessing right on YouTube(wow, you're very active).

    Anyways, My problem is that I don't know what is the syntax to check whether the cell or the row on datasheet is empty so I can say something like: if the condition is right and no data then Exit Sub.

    Best regards,

    Savalan

    Monday, July 23, 2018 8:34 PM
  • Hi Peter,

    When the form loads or no search options selected, the datasheet is blank and no data is shown. There is only one empty data row and when I double click in the first column that has DblClick event implemented, then the second form opens and because the cell does not have data, I get an error.

    Regards,

    Savalan

    Monday, July 23, 2018 8:39 PM
  • Hi DBgun,

    I have to admit I learned a lot from your posts on here or older posts online(from 7, 8, 10 years ago) :) and if I am guessing right on YouTube(wow, you're very active).

    Anyways, My problem is that I don't know what is the syntax to check whether the cell or the row on datasheet is empty so I can say something like: if the condition is right and no data then Exit Sub.

    Best regards,

    Savalan

    Hi Savalan,

    Thank you for the nice comment. In this case, I was saying, don't even bother checking if the cell is empty or not. Rather, let Access tell you there's an error. Your code then traps this error, before the user sees it.

    For example, if the user double-clicks on an empty cell or datasheet, what is the error message and number you get? Once we know it, we can set an error handler to trap it.

    Can you post a copy of the code for the Dbl_Click event and the error number you get if the datasheet is empty?

    Cheers!

    • Marked as answer by Savalan Monday, July 23, 2018 9:01 PM
    Monday, July 23, 2018 8:39 PM

  • When [Full Name] column double clicked, the second for opens and id of current form with datasheet is passed to the second form through OpenArgs. 
    • Edited by Savalan Monday, July 23, 2018 8:48 PM
    Monday, July 23, 2018 8:45 PM
  • Hi Savalan,

    I would like to help you but I can't follow your story. I hope theDBguy can help you. I will folow this post.

    Monday, July 23, 2018 8:49 PM

  • Hi,

    Here's how you can trap error #2498...

    At the top of your procedure, enter the following line:

    On Error GoTo errHandler

    For example, your code might start out looking like this:

    Private Sub SubformName_DblClick()
    On Error GoTo errHandler
    ...

    Then, just before the End Sub line, enter something like the following:

    ...
    errExit:
        Exit Sub
    
    errHandler:
        If err.Number = 2498 Then
            MsgBox "Sorry, no data to show...", vbInformation,"Error"
        Else
            MsgBox err.Number & ". " & err.Description, vbInformation, "Error"
        End If
        Resume errExit
    
    End Sub

    Hope it helps...

    • Marked as answer by Savalan Monday, July 23, 2018 9:01 PM
    • Unmarked as answer by Savalan Monday, July 23, 2018 9:05 PM
    • Marked as answer by Savalan Monday, July 23, 2018 9:05 PM
    Monday, July 23, 2018 8:53 PM
  • Hi,

    Perfect. Thank you so much. The project is completed.

    Best Regards,

    Savalan

    Monday, July 23, 2018 9:00 PM
  • Peter, thank you so much. I was able to finish the project with your and DBguy's help.

    Best regards,

    Savalan

    Monday, July 23, 2018 9:03 PM
  • Hi,

    Perfect. Thank you so much. The project is completed.

    Best Regards,

    Savalan

    Hi Savalan,

    You're welcome. Peter and I were happy to assist. Good luck with your project.

    Monday, July 23, 2018 9:03 PM
  • Yes, always willing to help. I wish you a lot of succes.
    Monday, July 23, 2018 9:08 PM