Subform data entry is set to YES but it displays all records on refresh

[tasawer]

Hi,

On a default startup form (switchboard) I have a combobox in which one selects the username (bound to StaffNum) and clicks OK to open up the mainform.

On the mainform I have a data entry form that has its DATA ENTRY option set to YES.
for StaffNum field, I set the Default Value as

=[Forms]![Switchboard]![cboStaffNum]

initially the form opens up as data entry, but after inserting new records, and hitting the refresh button, all records are displayed in this subform.

Refresh Button Code:

Private Sub cmdRefresh_Click()
 
    Me.Accounts_Totals.Requery
    Me.MMQ_DataEntry.Requery
    Me.lstTotals.Requery
    Me.lstCashinHand.Requery
    Me.lstAccountBalance.Requery
    Call CalculateTotals
 
End Sub
---
This routine adds up figure from a column in listbox
Public Sub CalculateTotals()
 
    Dim varOutTotal As Currency
    Dim varInTotal As Currency
    Dim varRow As Integer
 
    For varRow = 1 To (Forms!Totals_By_Date!lstTotals.ListCount - 1)
        varInTotal = varInTotal + Forms!Totals_By_Date!lstTotals.Column(1, varRow)
        varOutTotal = varOutTotal + Forms!Totals_By_Date!lstTotals.Column(2, varRow)
        Next
 
        Forms!Totals_By_Date!txtPaidOutTotal = varOutTotal
        Forms!Totals_By_Date!txtPaidInTotal = varInTotal
 
        Forms!Totals_By_Date!txtBalance = varInTotal - varOutTotal
 
End Sub
 

for the STAFFNUM Field, if leave the Default value as blank or I set it to number as 1,2 or 3 etc, then the database works fine.

I am not sure as to why I am facing this problem.

Your help is appreciated.

[twinnyfo]

tasawer,

After your Refresh, do you want to only display the records that relate to cboStaffNum? If this is the case, you might want to simply add the following code at the end of your cmdRefresh Sub:

    Me.Requery
    Me.Filter = "StaffNum = " & [Forms]![Switchboard]![cboStaffNum]
    Me.FilterOn = True
 

That assumes Switchboard is still open. If not, you would have to save the StaffNum in a variable, then filter by that variable.

Hope this works. If this is not what you are trying to do, please explain.

[tasawer]

after reresh, The dataentry subform should go to blank, ready to take more records, but instead it displays all the records related to cboStaffNum ID.

If I force the field StaffNum to e.g.'2', then the whole system works fine.