locked
Filter with current record parameter RRS feed

  • Question

  • Hi,

    I have a genealogy tree for animals. Each on have a father & a mother.

    The request for the combobox in Access works

    SELECT Animal.Id, Animal.MainName, Breeder.AffixName FROM Breeder INNER JOIN Animal ON Breeder.Id = Animal.BreederId WHERE Animal.Male=False ORDER BY Animal.MainName; 

    But it display all males or all females.

    Is it possible to exclude those whoose birth date > Animal.BirthDate for current record ?

    Thanks,

    Vincent

    Friday, August 3, 2018 5:35 PM

Answers

  • Forget about doing this in a table's datasheet; I see no way that it's possible.  It must be done in a bound form, as only forms have the necessary properties which enable you to achieve the desired functionality.  Also the form must be in continuous forms view, not datasheet view, for reasons which will become apparent below.

    Building a combo box's RowSource property to restrict its list is not a problem.  The SQL statement firstly needs to restrict the result set to those row with a gender of male or female, which is simple to do.  It also needs to restrict the result set to those rows where the animal's birth date is earlier than the birthdate in the current row in the form, which means referencing the birthdate control in the form as a parameter.

    As the user navigates to each row in the form the combo box's RowSource recordset, showing only those rows where the birthdate is earlier than that in the current row.  This is done by calling the control's Requery method in the form's Current event procedure.

    The above is relatively easy to implement, but there is a fly in the ointment.  Because the column to which the combo box is bound is a numeric foreign key value, which is then hidden by setting the control's ColumnWidths property to zero, when the control is requeried in the form's Current event procedure, in those rows where the mother and/or father's birthdate is not earlier than that of the animal in the current row, the two column positions will be blank.  No data is lost, just not visible in the control.  This is because the value of the hidden numeric key in the other rows no longer has a value in the second visible column to which it can map.

    The solution to this is to use a 'hybrid' control, where a text box is carefully positioned on top of the combo box, leaving only the arrow of the latter visible, to give the appearance of a single combo box control.  The form must be based on a query which, in addition to the rows from the Animal table returns the non-key text column from the referenced table.  As, in your case, the referenced table is also the Animal table, this means that three instances of the table are included in the form's query, the extra two instances being to return the mother's name and father's name respectively.

    You'll find an example of a hybrid control used in this way in ActiveEmployees.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file, the form is a subform within a projects form, but the methodology applies equally to a main form.  In this case the combo box's list is restricted to active employees only, but retains the visibility of inactive employees in the form.  The form's RecordSource is:

    SELECT ProjectEmployees.ProjectID, ProjectEmployees.EmployeeID,
    [LastName] & ", " & [FirstName] AS Employee, Employees.Active
    FROM Employees INNER JOIN ProjectEmployees
    ON Employees.EmployeeID = ProjectEmployees.EmployeeID
    ORDER BY Employees.LastName, Employees.FirstName;

    i.e. it includes the referenced Employees table as well as the ProjectEmployees table into which data is entered in the subform.  The text box on top of the combo box is bound to the computed Employee column returned by the query, while the combo box is bound to the ProjectEmployees.EmployeeID column.

    The combo box's RowSource property is:

    SELECT EmployeeID, LastName & ", " & Firstname
    FROM Employees WHERE Active
    OR EmployeeID = Form!EmployeeID
    ORDER BY LastName, FirstName;

    Note the use of the Form property to reference the current form.

    The form's Current event procedure is:

    Private Sub Form_Current()

        ' requery combo box to include current employee
        ' in list if not active
        Me.cboEmployee.Requery
        
    End Sub

    Ken Sheridan, Stafford, England

    Tuesday, August 7, 2018 11:28 AM

All replies

  • Hi Vincent,

    You can refer to the current record of a form in the combobox row source by using an absolute reference. For example:

    Forms!FormName.ControlName

    Hope it helps...

    Friday, August 3, 2018 5:58 PM
  • the combobox is in the table not in a form.
    Friday, August 3, 2018 7:11 PM
  • the combobox is in the table not in a form.

    Oh, I suppose you could try just adding the name of the field then. For example:

    ...AND LookupTableFieldName = [ThisTableFieldName]

    Just a thought...

    Friday, August 3, 2018 7:23 PM
  • I tried something like this but the combo is empty :

    SELECT Animal.Id, Animal.MainName, Animal.BirthDate FROM Animal WHERE (( (Animal.BirthDate)<[BirthDate])); 

    Friday, August 3, 2018 8:57 PM
  • If you think about it the following SQL statement can never return any rows as the BirthDate value can never be less than itself.

    SELECT Animal.Id, Animal.MainName, Animal.BirthDate
    FROM Animal
    WHERE Animal.BirthDate < [BirthDate];

    I don't follow what you are trying to do here, but whatever it is, you will have to do it in a form.  You need to be able to reference a parameter, i.e. a control in a form.  If you can explain in formal terms exactly what you are attempting then we might be able to help you, but at present it's not clear what your intentions are.

    Ken Sheridan, Stafford, England

    Saturday, August 4, 2018 11:42 AM
  • Hi,

    the goal was to have something like 'this.' to select date older that current record without using a form.

    Vincent

    Monday, August 6, 2018 9:40 AM
  • the goal was to have something like 'this.' to select date older that current record without using a form.
    I haven't a clue what that means.  Where do you want to 'select' a date?   Current record where?

    Please give an example using real or dummy data.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, August 6, 2018 10:28 AM Typo corrected.
    Monday, August 6, 2018 10:25 AM
  • Here's a sample.

    For selecting the animal mother I'm using this request :

    SELECT Animal.Id, Animal.MainName, Breeder.AffixName FROM Breeder INNER JOIN Animal ON Breeder.Id = Animal.BreederId WHERE Animal.Male=False ORDER BY Animal.MainName; 
    But it present all female including those who birthday is newer than animal (if newer, it can't be her mother).

    Tuesday, August 7, 2018 6:45 AM
  • Forget about doing this in a table's datasheet; I see no way that it's possible.  It must be done in a bound form, as only forms have the necessary properties which enable you to achieve the desired functionality.  Also the form must be in continuous forms view, not datasheet view, for reasons which will become apparent below.

    Building a combo box's RowSource property to restrict its list is not a problem.  The SQL statement firstly needs to restrict the result set to those row with a gender of male or female, which is simple to do.  It also needs to restrict the result set to those rows where the animal's birth date is earlier than the birthdate in the current row in the form, which means referencing the birthdate control in the form as a parameter.

    As the user navigates to each row in the form the combo box's RowSource recordset, showing only those rows where the birthdate is earlier than that in the current row.  This is done by calling the control's Requery method in the form's Current event procedure.

    The above is relatively easy to implement, but there is a fly in the ointment.  Because the column to which the combo box is bound is a numeric foreign key value, which is then hidden by setting the control's ColumnWidths property to zero, when the control is requeried in the form's Current event procedure, in those rows where the mother and/or father's birthdate is not earlier than that of the animal in the current row, the two column positions will be blank.  No data is lost, just not visible in the control.  This is because the value of the hidden numeric key in the other rows no longer has a value in the second visible column to which it can map.

    The solution to this is to use a 'hybrid' control, where a text box is carefully positioned on top of the combo box, leaving only the arrow of the latter visible, to give the appearance of a single combo box control.  The form must be based on a query which, in addition to the rows from the Animal table returns the non-key text column from the referenced table.  As, in your case, the referenced table is also the Animal table, this means that three instances of the table are included in the form's query, the extra two instances being to return the mother's name and father's name respectively.

    You'll find an example of a hybrid control used in this way in ActiveEmployees.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file, the form is a subform within a projects form, but the methodology applies equally to a main form.  In this case the combo box's list is restricted to active employees only, but retains the visibility of inactive employees in the form.  The form's RecordSource is:

    SELECT ProjectEmployees.ProjectID, ProjectEmployees.EmployeeID,
    [LastName] & ", " & [FirstName] AS Employee, Employees.Active
    FROM Employees INNER JOIN ProjectEmployees
    ON Employees.EmployeeID = ProjectEmployees.EmployeeID
    ORDER BY Employees.LastName, Employees.FirstName;

    i.e. it includes the referenced Employees table as well as the ProjectEmployees table into which data is entered in the subform.  The text box on top of the combo box is bound to the computed Employee column returned by the query, while the combo box is bound to the ProjectEmployees.EmployeeID column.

    The combo box's RowSource property is:

    SELECT EmployeeID, LastName & ", " & Firstname
    FROM Employees WHERE Active
    OR EmployeeID = Form!EmployeeID
    ORDER BY LastName, FirstName;

    Note the use of the Form property to reference the current form.

    The form's Current event procedure is:

    Private Sub Form_Current()

        ' requery combo box to include current employee
        ' in list if not active
        Me.cboEmployee.Requery
        
    End Sub

    Ken Sheridan, Stafford, England

    Tuesday, August 7, 2018 11:28 AM
  • thanks for this long answer.
    Thursday, August 9, 2018 7:52 PM