When user insert any other non the Default values to that comboboxes , and move to insert of other new record , the values of those combos don't saved correctly and returned to the Default values specified in the
Dflt_TBL table
When user insert any other non the Default values to that comboboxes , and move to insert of other new record , the values of those combos don't saved correctly and returned to the Default values specified in the
Dflt_TBL table
If you are storing the company, branch and employee in the form's table then the table is not normalized to Third Normal Form (3NF) Employee functionally determines branch, and branch functionally determines company. 3NF requires that all non-key columns are determined solely by the whole of the table's primary key. By storing all three values in a row in the table branch and company are transitively determined by the key, which introduces redundancy and the consequent risk of update anomalies.
The correct design would be to drop the branch and company columns from the table, retaining only the employee column. In the form the branch and company columns would be unbound, and populated in the form's Current event procedure on the basis of the employee value in the current record. The three combo boxes would be correlated in the usual way by means of a parameter in the RowSource property referencing the contol above in the hierarchy.
I've attached a little demo file which illustrates this. In the demo the three levels of the hierarchy are country, region and city, with the city combo box in a contacts form being the only bound control of the three.
When the user moves to an empty new record in the form the combo boxes are all Null, as set by the code in the form's Current event procedure:
Code:
Private Sub Form_Current()
' synchronize Go to Contact combo box with current record
Me.cboGotoContact = Me.ContactID
' if new record set unbound combo boxes to Null,
' for existing record call functions to return
' region and country for current value of CityID
If Me.NewRecord Then
Me!cboCountry = Null
Me!cboRegion = Null
Else
Me!cboRegion = GetRegion(Me!cboCity)
Me!cboCountry = GetCountry(Me!cboRegion)
End If
' requery unbound combo boxes to list
' only regions, cities and postcodes for current city, if any
Me!cboRegion.Requery
Me!cboCity.Requery
Me.cboPostCode.Requery
' move to firstname control in parent form
' and last record in employers subform
With Me.sfcEmployers
.SetFocus
DoCmd.RunCommand acCmdRecordsGoToLast
End With
Me.txtFirstName.SetFocus
End Sub
If I amend the code as follows to give the city combo box a default value of 1, which is the CityID value for Stafford:
Code:
Private Sub Form_Current()
' synchronize Go to Contact combo box with current record
Me.cboGotoContact = Me.ContactID
' set default CityID value to 1
Me!cboCity.DefaultValue = "1"
Me!cboRegion = GetRegion(Me!cboCity)
Me!cboCountry = GetCountry(Me!cboRegion)
' requery unbound combo boxes to list
' only regions, cities and postcodes for current city, if any
Me!cboRegion.Requery
Me!cboCity.Requery
Me.cboPostCode.Requery
' move to firstname control in parent form
' and last record in employers subform
With Me.sfcEmployers
.SetFocus
DoCmd.RunCommand acCmdRecordsGoToLast
End With
Me.txtFirstName.SetFocus
End Sub
Now, when the user moves to an empty new record, the city combo box will show Stafford by default, the region combo box will show Staffordshire, and the country combo box will show United Kingdom. The user can select another city if they wish, but if the city is not in Staffordsire they'd first need to select or enter a new value in the region combo box. and if the selected region is not in the UK they'd first need to select or enter another country. Only the value of the city combo box will be stored in the record, the region and country values are returned by the private GetRegion and GetCountry functions in the form's module.
BTW, if you are wondering why I've wrapped the 1 in quotes characters in the above code, the DefaultValue property is always a string expression, regardless of the data type of the column in question. In most cases, omitting the quotes won't make any difference, but in some contexts they are crucial, so it's best to always include them. In your case, if you were to set the DefaultValue property of the company control the code would be:
Unlike setting the Value property, as you are currently doing, setting the DefaultValue property does not Dirty the form, so, providing they haven't entered any other data, the user can abort the insertion of a new record simply by moving to an existing record or closing the form.
PS: If you need to select a branch and company, but not an employee, then be sure that, for each row in the Branches table, you have a row in the Employees table with a value of N/A or similar in the LastName column. You can then select this, after selecting the company and branch, rather than leaving the employee combo box Null.
as list separator instead of a comma:
