By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

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

P: 106

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
Expand|Select|Wrap|Line Numbers
  1. =[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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRefresh_Click()
  3.     Me.Accounts_Totals.Requery
  4.     Me.MMQ_DataEntry.Requery
  5.     Me.lstTotals.Requery
  6.     Me.lstCashinHand.Requery
  7.     Me.lstAccountBalance.Requery
  8.     Call CalculateTotals
  10. End Sub
  11. ---
  12. This routine adds up figure from a column in listbox
  13. Public Sub CalculateTotals()
  15.     Dim varOutTotal As Currency
  16.     Dim varInTotal As Currency
  17.     Dim varRow As Integer
  19.     For varRow = 1 To (Forms!Totals_By_Date!lstTotals.ListCount - 1)
  20.         varInTotal = varInTotal + Forms!Totals_By_Date!lstTotals.Column(1, varRow)
  21.         varOutTotal = varOutTotal + Forms!Totals_By_Date!lstTotals.Column(2, varRow)
  22.         Next
  24.         Forms!Totals_By_Date!txtPaidOutTotal = varOutTotal
  25.         Forms!Totals_By_Date!txtPaidInTotal = varInTotal
  27.         Forms!Totals_By_Date!txtBalance = varInTotal - varOutTotal
  29. 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.
Aug 3 '12 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 3,284

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:

Expand|Select|Wrap|Line Numbers
  1.     Me.Requery
  2.     Me.Filter = "StaffNum = " & [Forms]![Switchboard]![cboStaffNum]
  3.     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.
Aug 3 '12 #2

P: 106
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.
Aug 3 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.