Do not save data from the main form to the table associated with the sub form

[mkc80]

I have a main form and a sub form. There are 3 fields in the main form. The sub form is associated with a table (this sub form is bound to a table)
When I select the data from the 3 combo boxes in the main form and close the form without entering data in any of the fields in the sub form, these 3 fields are getting saved in the table associated with the sub form. I don't want that. If data is entered in at least one of the controls in the sub form, the data in the 3 fields of the main form along with the data in the sub form should be saved in the table. How do I accomplish this?

[June7]

The main form and sub form are bound to the same table? Why? This doesn't make sense to me. Want to provide project for analysis? Follow instructions at bottom of my post.

[mkc80]

They are not bound to the same table. Only the sub form is bound to the table. That's the mystery. May be I have incorrect options in the properties window or need to set additional options?
Basically, in the main form, you select State and Customer ID (together they form a primary key in a table A). So for every State and Customer ID selected, other information such as order number, customer information, customer satisfaction survey questionnaire are entered in the sub form, which are saved in the table B, along with the State and Customer ID. The problem I am facing is, when I close the form without even entering information in at least one text box or combo box in the sub form, still the State and Customer ID are getting saved in the table B bound to the sub form. I don't want that. I only want to save the information to the table B when at least one text box or combo box in the sub form is filled.

Please Advise.

[orange]

I think you are too far down in the details for us to give a quick answer.
Can you tell us in plain English what the data base is about? That is what type of business does it support; what are the "things" involved?
Your description

you select State and Customer ID (together they form a primary key in a table A)

suggests that Customers are numbered by State, which seems strange. Do you really have tables named A and B?

Can you post a jpg of your tables and relationships?

[June7]

Obviously, something about the form/subform setup is causing record to commit. Is there an autonumber field in table B? Do any fields have a Default value? Are you creating a new record on the main form? The main form is bound and the State and CustomerID fields are bound? I really need to analyse the db to figure this one out.

[mkc80]

So this is the form I was talking about.
Description of what the form is supposed to do: When the user selects the Site from a list (the list of sites is populated by running a query against the table 'Sites'), the patients enrolled at that site get populated in the patient combo box. Similarly when the Patient is selected, the associated admit dates for that patient get populated in the admit date combo box.
The main form is designed to have Site, Patient and Admit Date (controls above the label DATA ENTRY FORMS). The sub form (lets say 1) has all the controls except for line number, REV.CD, Description and total charges, which is a sub form (lets say sub form 2) in itself under sub form1. The sub form 1 and sub form 2 are bound to two individual tables. When the user selects the Site, Patient and Admit Date from the main form, all the fields in the sub form1 and sub form2 get automatically populated. (There are no duplicate records for same Site, Patient, Admit Date, Discharge Date and Type of Bill, meaning there cannot be two bills of type 100 associated for same Site, Patient, Admit Date and Discharge Date). The tables bound with sub form 1 and sub form 2 are linked by Site, Patient, Admit Date and Type Of Bill (These fields are listed as Master Child links in the property window of sub form2 in sub form 1).

The problem: After selecting the Site, Patient, Admit Date in the main form and when the fields in the Sub Form1 and Sub Form 2 get populated, I am not able to add a new record to the associated tables by selecting a different type of bill from the Type_Of_Bill combo box. When I select a different bill type (which is not already present in the table, meaning adding a new bill)
the already existing record in the table (s) is getting updated (all the fields in the sub form are not being reset to blank, they have the values from the bill first displayed). What I want the form to do is, when I select a different bill type which is not present (that is I am entering it for the first time) I should be able to add a new record to the tables.

As you can see from the way I have explained, I am new to access, never worked on it before, its not my forte. It's just something I have to do apart from my regular work.
Forgive me if this is still not clear.

The form and back end code is attached to this message.

Please advise.

[June7]

If you want to add new record, must move to a new (blank) record before selecting/entering data such as bill type.

What is a .cls file?

[mkc80]

How do I move to a new (blank) record?
The .cls file is a class file containing the source code for one class. We have different events for the controls on the form (if set) defined in the .cls files.

[June7]

I have never use cls, no idea how to implement it.

Move to new record with the Navigation bar at bottom of form or if it is disabled use code (VBA or macro) behind button Click event.

DoCmd.GoToRecord , , acNewRec

[mkc80]

The above statement does add a new record but there are couple of problems here:
1) The type of bill gets updated in the current record to the bill type selected for the new record (the bill type should remain the same!)
2) The type of bill is missing in the new record
3) The line number for the new record is not reset to 1 for the new record in the subform2. It starts from the line number from where the last line number for the current record.
(subform2 is bound to table2)
4) The total charges from the subform1 are being carried over to the new record, instead of just being blank

BTW, I have added this code to the OnChange event of the combo box for Type_Of_Bill

[June7]

I don't have your db to analyse so can't determine cause of these issues but sounds like things are happening in wrong order.
1. Why would bill type be selected before moving to new record?
2. see number 1
3. Why assign line numbers? Might be able to handle this with report design.
4. No idea what is going on.