none
use SQL to filter a form's contents RRS feed

  • Question

  • I'd like to keep my database small.  I'd like to reuse the forms and queries if I can. 

    I have 2 forms, Form A is fmCustomerEnterInfo and Form B is fmEmployeeViewInfo.  Each form has a text box, txtID (contains record ID) and will have a button that opens another form, fmDetails, which needs to be filtered on the record number.  The fmDetails is filtered by qryDetails, which contains 2 fields ID and Details. 

    I figure I somehow I can launch the fmDetails and use a SQL query to filter it by the record ID.  strSQL = SELECT * from tblInfo WHERE ID = (form name).txtID.  But I'm not sure how this is accomplished.  Obviously it would be in the button's onClick property but how would I tell it to launch the form using that query?


    • Edited by Carl_S_S Sunday, September 23, 2018 10:20 PM
    Sunday, September 23, 2018 10:19 PM

Answers

  • You can easily open the same form, based on the same query, from multiple forms by filtering the form in the WhereCondition argument of the OpenForm method, rather than by restricting the form's recordset by referencing a control in the calling form as  a parameter, e.g.

        Dim strCriteria As String

        strCriteria = "ID = " & Me.txtID

        If Not IsNull(Me.txtID) Then
            DoCmd.OpenForm "frmDetails", WhereCondition:=strCriteria
        End If

    I would recommend against the use of the generic 'ID' as a column name however.  It is semantically meaningless and can be confusing in complex code or SQL statements.  Using a name which clearly identifies the column as the key of the entity type modelled by the table is better, e.g. EmployeeID for an Employees table, ProductID for a products table etc.


    Ken Sheridan, Stafford, England

    • Marked as answer by Carl_S_S Tuesday, September 25, 2018 3:12 PM
    Tuesday, September 25, 2018 11:24 AM

All replies

  • > I'd like to reuse the forms and queries if I can.
    That is good practice, but do not take it to extremes. The database size is a secondary concern well below clarity and maintainability.
    For example you should not create a SINGLE form frmData to display both Customer and Employee info. Although it is possible, it is very highly not recommended. It could get you fired in some places.

    Also do not use ID for each table's PK. Rather use more meaningful CustomerID and EmployeeID. This will become important when joining tables.

    You lost me with the Details form. frmCustomer and frmEmployee already show details for one customer or employee. What other details are presumably in tblInfo?
    Maybe you can post a Relationship diagram to help the conversation.


    -Tom. Microsoft Access MVP

    Sunday, September 23, 2018 10:29 PM
  • I'd like to keep my database small.  I'd like to reuse the forms and queries if I can. 

    Hi Carl,

    To keep the database small, thus save disk space, should not be the goal. You can better delete some pictures to have the same effect.

    But reuse of code is in my opinion very important. It can yield very powerful and robust routines, which can lead to faster development of new applications, or modify existing ones.

    To generalize, you have to study the similarities and differences in the code you want to combine. The similarities will be placed in the combined code, the differences can be supplied in different ways: with parameters, with global variables, with definition tables. If you follow this path step by step, times after times, you can reach the situation that almost all code is generalized. My code is in that stage on this moment.

    Back to your situation, it is not clear to me if you want the same single-record-form fmDetails opened from two different forms  fmCustomerEnterInfo and fmEmployeeView, or that you have only one single-record-form, that can display the details of a Customer-related record or an Employe-related record.

    Imb. 

    Monday, September 24, 2018 1:17 PM
  • Sorry for not being more clear.  fmCustomerEnterInfo and fmEmployeeView both look at tblInfo.  There's a field in tblInfo, Details, that I want to open in a stand alone form and show a large text box for paragraph of info.  Because of the size, I don't want it on the 2 main forms and it's only needed occasionally.  If the customer answers yes to several questions, the Add Details button will appear.  The Form (fmDetails) and Query (qryDetails) will be identical for both Customer and Employee.  The customer enters the details, the employee views the details.  So since both forms need to do the exact same thing, I figure why have 2 forms and 2 queries 100% identical except for the form name in the query filter.  I'm hoping that there's a way to launch the query/form by passing the form variable when clicking the button.  If form = fmCustomerEnterInfo then query = Like [Forms]![fmCustomerEnterInfo]![txtID] else Like [Forms]![fmEmployeeView]![txtID].  Could this be done with an iif statement?
    Monday, September 24, 2018 2:45 PM
  • If form = fmCustomerEnterInfo then query = Like [Forms]![fmCustomerEnterInfo]![txtID] else Like [Forms]![fmEmployeeView]![txtID].  Could this be done with an iif statement?

    Hi Carl,

    One of the possibilities could be this.

    Make a Sub in a general module, that look like:

    Sub Open_details_form (cur_id As Long)
        'the lines to do the job
        DoCmd,OpenForm ...
    End Sub

    On the fmCustomerEnterInfo you have a button, with in the Click event:  Open_details_form Me!txtID

    On the fmEmployeeView you have a button, with in the Clcik event:  Open_details_form Me!txtID

    The commands are the same, but the values come from different forms (different Me's).

    Inside the Open_details_form you write the lines for the opening of fmDetails. Most simple way is to set the filter argument of DoCmd.OpenForm using the passed parameter cur_id (Where clause).

    Success!

    Imb.

    Monday, September 24, 2018 5:04 PM
  • You can easily open the same form, based on the same query, from multiple forms by filtering the form in the WhereCondition argument of the OpenForm method, rather than by restricting the form's recordset by referencing a control in the calling form as  a parameter, e.g.

        Dim strCriteria As String

        strCriteria = "ID = " & Me.txtID

        If Not IsNull(Me.txtID) Then
            DoCmd.OpenForm "frmDetails", WhereCondition:=strCriteria
        End If

    I would recommend against the use of the generic 'ID' as a column name however.  It is semantically meaningless and can be confusing in complex code or SQL statements.  Using a name which clearly identifies the column as the key of the entity type modelled by the table is better, e.g. EmployeeID for an Employees table, ProductID for a products table etc.


    Ken Sheridan, Stafford, England

    • Marked as answer by Carl_S_S Tuesday, September 25, 2018 3:12 PM
    Tuesday, September 25, 2018 11:24 AM
  • Thank you Ken (and everyone else for trying to assist).  I knew there just had to be some way.

    I did run into the issue you're speaking of with this and the ID.  The info is actually coming from a query based on a couple tables, and I had to use txtID to specify the ID of the table I wanted.  Using the table name with ID would probably be a better idea, since txtID doesn't really say which table it's coming from.
    • Edited by Carl_S_S Tuesday, September 25, 2018 3:14 PM
    Tuesday, September 25, 2018 3:11 PM
  • I'd like to reuse the forms and queries if I can.

    Hi Carl,

    Ken's solution is sure the easiest way to implement what you want.

    But you can also think a little bit different, if you want to go to generalization and reuse of code. In this respect it is good to exaggerate a little bit.

    Suppose you have 10 forms in which you can zoom in to display the Details form, in the way Ken proposed. Then you have on 10 forms the information that ID-field of the Details table is "ID". If you want to change ID to the more meaningful Details_id, then you have to change that on all the 10 forms where you used it (You did not forget one?).

    On the other hand, if you pass only the ID-value as parameter to the sub that opens the Detail form, in which you construct the filter using ID, then you only have to change ID in Details_id in ONE place, and all functions immediately. The field name "ID" is only used in the sub (in the neighbourhood) in which the Details form is opened.

    For generalization and reuse of code these kind of considerations are important to make a dynamical, but very robust reusable code.

    Just a little different thinking.

    Imb.

    Tuesday, September 25, 2018 9:22 PM