Split form with filters

[mba_110]

Hi

I have a query about split form and its filter to the record I select in combo box, I have fields on frmBankDetails all employee bank account details are there.

Also I have cboEmpID and CboRecordID which filter the record by group on cboEmpID and requery with cboRecordID.

For example if any employee have more than one bank details it will be listed in cboRecordID and I can select it after that all the fields on form will show the data for that row source.

But now I want to give a touch to my form where employee able to see in botton with split form how many bank details he is having.

Now the problems are when I select cboEmpID with any number the upper portion of form is working good, but in bottom of form which is datasheet how many I have rows in tblBankDetails lets say 15 it is showing 15 times same details with same empID which I select in cboEmpID, please note cboEmpID and CboRecordID are not tblBankdetails fields it is placed on form to filter the number of records that employee have but it does reference to tblBankdetails with following rowsource.

cboEmpID  = tblBankdetails.[EmpID] which is FK

rowsource SELECT tblBankDetails.EmpID, tblEmployees.FullName FROM tblEmployees INNER JOIN tblBankDetails ON tblEmployees.EmpID = tblBankDetails.EmpID GROUP BY tblBankDetails.EmpID, tblEmployees.FullName;

cboRecordID = tblBankdetails.[BankID] which is PK

rowsource SELECT tblBankDetails.BankID, tblBankDetails.BankName, tblBankDetails.EmpID FROM tblBankDetails WHERE (((tblBankDetails.EmpID)=[cboEmpID]));

I try to set filter with code but unable to achieve the below goals.

Private Sub SetFilter()
  Dim TheFilter As String
  
  If Not IsNull(Me.cboEmpID.Column(0)) Then
    TheFilter = "[BankID]= '" & Me.cboEmpID.Column(0) & "'"
  End If
  If Not IsNull(Me.cboEmpID.Column(0)) And Not IsNull(Me.cboRecordID.Column(0)) Then
    TheFilter = TheFilter & " AND [BankID]='" & Me.cboRecordID.Column(0) & "'"
  ElseIf Not IsNull(Me.cboRecordID.Column(0)) Then
    TheFilter = "[BankID]='" & Me.cboRecordID.Column(0) & "'"
Elseif not isnull(me.chkShowAllRecords ) then
	me.filter = “” me.filterOn = false
me.cboEmpID = null
me.cboRecordID = null
  End If
  Me.Filter = TheFilter
  Me.FilterOn = True
End Sub

Firstly I need each selection in cboEmpID should show only records associated to that employee and do not repeat in datasheet one record one row if someone have 5 records than five rows only.

secondly I need what fields I have on split form’s upper portion other than actual table fields should not show or listed as a column in bottom of datasheet which is (cboEmpID and CboRecordID, tckShowAllRecords) and datasheet only show the fields for tblBankDetails.

Thirdly I have tick named “Show all records” if I select it than in bottom of split form should show all records from tblBankdetails, upper portion can be blank, but if I double click on any of datasheet record it should open/select that row on upper portion of form with whatever fields are there.

I know it sounds tricky in same form to do so, but I believe there should be options in filter for these activities to perform.

 

[Dreamweaver]

If your accessing a combo you don't need this ".Column(0)" if trying to get at column 0 as it will be available just by doing Me![ComboBox]

 

[mba_110]

The code is ok ? the only issue of column?

It does exactly what I am looking for ? Or not ?

Please give reply on whole function and required result and not on part of it.

 

[Deleted member 147267]

please note cboEmpID and CboRecordID are not tblBankdetails fields

I think you can fix your problem if you make it so. AFAIK, a split form is supposed to include the same fields top and bottom. You filter the form based on selections in the header controls, much like you would by applying filters in a table or query when you're looking at those in datasheet view. Then you wouldn't need so much code.

Also, you haven't shown the form recordsource. Open that in datasheet view, and if you're getting too many records, your table joins are incorrect, or the criteria is wrong. You may have an outer join that's causing too many detail records.