Access 2010 run-time error 3201 cannot add or change record (1 Viewer)

[wire_jp]

When the user opens the Orders form he can manually update the 2 controls:- CustomerFirstName (textbox), CustomerLastName (textbox) and he can also select one of the options of the following 3 combo box controls:- CustomerCountry (combo box), TrainerName (combo box), SportsCentreName (combo box). The user can also add new Trainers and new SportCentres using the following 2 command buttons: Add cmdNewTrainer and Add cmdNewSportsCentre.

Updating the CustomerCountry combo box, will filter the results in the TrainerName combo box and similarly when the user updates the TrainerName combo box, the results in the SportsCentre combo box are filtered.

The Data Row Source code for the CustomerCountry combo box:-

SELECT tblCountry.[Country Code], tblCountry.Country FROM tblCountry;

The Data Row Source code for the Trainer Name combo box:-

SELECT tblTrainers.TrainerName FROM tblTrainers WHERE (((tblTrainers.TrainerCountry)=[Forms]![frmOrders].[Controls]![CustomerCountry].[Value]));

The Data Row Source code for the SportsCentreName combo box: -

SELECT tblSportsCentre.SportsCentreName FROM tblSportsCentre INNER JOIN tblTrainers ON tblSportsCentre.SportsCentreName = tblTrainers.SportsCentreName WHERE (((tblTrainers.TrainerName)=[Forms]![frmOrders].[Controls]![TrainerName].[Value]));

If an user updates the pop-up dialog box of the cmdNewTrainer command button and close this dialog box, the following debugging error message occurs: the error Run-time error '3201':
You cannot add or change a record because a related record is required in table 'tblTrainers'.
The line 15 of following VBA code is highlighted in yellow:

Option Compare Database
 Option Explicit
 
 ' function that checks form is closed or open.
 Function fIsLoaded(ByVal strFormName As String) As Boolean
     fIsLoaded = False
     If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
         If Forms(strFormName).CurrentView <> 0 Then
             fIsLoaded = True
         End If
     End If
 End Function
 
 ' refresh our form (CheckBox for Trainers)
 Private Sub Form_Close()
     If fIsLoaded("frmOrders") Then
         Forms![frmOrders].Refresh
     End If
 End Sub

If the user cancels the debugging error, and the user can still be able to select all of the available trainers (including the newly added trainer) in the TrainerName combo box control.

Is there a problem with the VBA code?

 

[arnelgp]

when you add the trainer, do you also save the country name and sport center on trainer table?

 

[wire_jp]

The tblTrainers table has a control for the SportsCentrename and this control is saved before closing the tblTrainers table. However, the tblTrainers table do not have a control for the CustomerCountry (instead it has a label called Trainer Country Code and this label is associated with the control for TrainerCountry).

The tblTrainers table also has a label called Trainer Country with the control source:

=[Forms]![frmTrainers].[Controls]![TrainerCountry].[Column](1)

 

[arnelgp]

you must save it in the table since you are using it as a join in your trainer combobox.

 

[wire_jp]

You have help me to resolve the problem. Thank you for your help.