Add new Data through ComboBox and update Subform Automaticlly

[JoannaYang]

Hi Everbody,

I am new in this group and very new with Visual Basic. After searching
for two days, I still can not find a solution for my problem and
therefore decided to post it out. Apology if the same question has
been asked before.

The database I created is a simple one and is for our team to log
their work record. The tables are set as below.

TabBill – contains only 1 field - Billnumber (duplicate not allowed)
TabRelease – Contains multiple fields including Billnumber (lookup
from TabBill). One bill number could be logged many times in this
table.

Form is created for team to input data:

FormInput – Data is from TabBill. A combo box (combo1) is created for
staff to input bill number
A subform - FromSub (data is from TabRelease) is created on FormInput.
So, when a staff input a bill number in combo1
1) if the bill number is already on the list, then the detail show up
in FormSub
2) If the bill number is not on the list,
- A message window show up to ask staff if they want to add it.
- If confirm adding, the new bill number will be added into TabBill
and
- (supposedly) the FormSub will have a blank page with the bill number
for staff to put in detail. I have below written up on “not on list”
for comb1:

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new customer.
Msg = "Are you sure to log " & NewData & " " & " by bill?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then

' If the user chose Yes, add bill number onto TabBill
Set Db = CurrentDb
Set RS = Db.OpenRecordset("tabbill", dbOpenDynaset)

rs.AddNew
' Assign the New Bill to the BillNumber field.
rs![BillNumber] = NewData
rs.Update
Response = acDataErrAdded

‘requery the subform
Me.FormSub.Requery

End If

Exit_CustomerID_NotInList:

Exit Sub

Err_CustomerID_NotInList:
MsgBox Err.Description
Response = acDataErrContinue

End Sub


Problem:
When I first open FormInput and do not touch anything or change any
data, the database does what I want - Add new data and refresh with a
new page on the FormSub with the new bill number for staff to input
information. However, after that (or if I do any data change), it does
not work anymore (it will still add the new data onto TabBill and show
in the drop down list of combo1, but the new data will not show in
FormSub) unless I close the form and reopen it.

Could anyone give any suggestion of what the problem could be and how
to fix it?

Many thanks!
Joanna