Unanswered: Closed form populates incomplete record
I am using Access 2002
I have a simple form with two combo boxes, and both fields in the destination table linked to the combo boxes are Required = Y, and Allow Zero Length = N.
The first combo box named productgroup gets its data from a Select Query S_F_PRODUCT_GROUP (table source is PRODUCT_GROUP) which returns one field called productgroup.
The second combo box named products gets its data from a Select Query S_F_PRODUCTS which returns one field called productdesc. The Select Query S_F_PRODUCTS has a join type 1 on the table PRODUCT_GROUP to the table PRODUCTS, and the tables are joined on the commom field productgroup. (Basically, if a user selects combo box productgroup = "Product Group1", I only want the product descriptions (field name productdesc) from table PRODUCTS with productgroup = "Product Group1" to be available in combo box product. To do that I used the following code:
Private Sub productgroup_BeforeUpdate(Cancel As Integer)
'Reset product combo box
Me!product = ""
Me!product.RowSource = "SELECT productdesc FROM S_F_PRODUCTS Where productgroup = '" & Me!productgroup & "' ORDER by [productdesc];"
I wanted to use Me!product = "" to make it obvious to the user that when a new productgroup in combo box productgroup is chosen, they will have to select a new product from combo box product. Also, if a user selects a productgroup and a product, and then goes back and selects a different productgroup, the product options do not reset so you could end up with Product Group1 and Product2B (from Product Group2) being entered into the table linked to the form. However, the Me!product = "" is conflicting with the fields in the destination table linked to the combo boxes since they are both Required = Y, and Allow Zero Length = N.
1. How can I reset the combobox product without conflicting with Required = Y, and Allow Zero Length = N.
Finally, if I have one combo box populated and then close the form, or go to design view, or even close Access, a new incomplete record (1 of two fields completed) is automatically inserted into the form's destination table.
2. How can i have the form simply close without saving the incomplete record?
Most reliable method to accomplish this and save your hair from being pulled out as you curse Bill Gates is to make the combo boxes unbound and use code to determine if the appropiate criteria has been met to insert the new record.
Your technique of setting the second box's rowsource is good. When a new value is entered into the first box, clear the contents of the second box in addition to what you are already doing.
Make the boxes unbound and add a "Save" button. When the save button is pressed, check to make sure a value is in each combo box and post a msgbox if not telling the user why you are not adding their new record if there isn't.
If your form is bound, there is a Cancel Update or something action you can insert in the on close event if the record is not complete. Check the help file - I hardly ever use bound forms, and this is one of the reasons why.