473,320 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access 2010 run-time error 3201 cannot add or change record

77 64KB
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:-
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCountry.[Country Code], tblCountry.Country FROM tblCountry; 
The Data Row Source code for the Trainer Name combo box:-
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTrainers.TrainerName FROM tblTrainers WHERE (((tblTrainers.TrainerCountry)=[Forms]![frmOrders].[Controls]![CustomerCountry].[Value]));
The Data Row Source code for the SportsCentreName combo box: -
Expand|Select|Wrap|Line Numbers
  1. 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 17 of following VBA code is highlighted in yellow:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. ' function that checks form is closed or open.
  5. Function fIsLoaded(ByVal strFormName As String) As Boolean
  6.     fIsLoaded = False
  7.     If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
  8.         If Forms(strFormName).CurrentView <> 0 Then
  9.             fIsLoaded = True
  10.         End If
  11.     End If
  12. End Function
  13.  
  14. ' refresh our form (CheckBox for Trainers)
  15. Private Sub Form_Close()
  16.     If fIsLoaded("frmOrders") Then
  17.         Forms![frmOrders].Refresh
  18.     End If
  19. 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?
May 11 '16 #1

✓ answered by wirejp

Hi zmbd, thank you for the clarification about the terms called "controls" and "fields". I updated my post#2 to say "I had to add the country name and sportscentre name controls to the trainers form". Yes, there were more steps involved in fixing the problem.

As the SportsCentre, Trainers and Customers are in the same country, the field called [CountryCode_pk] in the tblCountry table is linked to the respective country field of SportsCentre, Trainer and Customer table by a one:many relationship . Originally the [TrainerCountry_fk] text 255 (in the Trainers table) was linked to [CountryCode_pk] text 3 (in the Country table). Similar situation for the SportsCentre table and Customer table. So I had to change the naming of the [TrainerCountry_fk] text 255 to [CountryCode_fk] text 3 in the SportsCentre table. I performed similar changes for the [SportsCentreCountry_fk] text 255 and [CustomerCountry_fk] text 255 in the SportsCentre table and Customer table respectively.

In the frmOrders form, I had to update the Data Row Source code for the Trainer Name combo box: -
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTrainers.TrainerName FROM tblTrainers WHERE (((tblTrainers.CountryCode)=[Forms]![frmOrders].[Controls]![CountryCode].[Value]));
I also had to update the Data Row Source code for the SportsCentre combo box: -
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSportsCentre.SportsCentreName FROM tblSportsCentre INNER JOIN tblTrainers ON tblSportsCentre.SportsCentreName = tblTrainers.SportsCentreName WHERE (((tblTrainers.CountryCode)=Forms!frmOrders.Controls!CountryCode.Value) And ((tblTrainers.TrainerName)=Forms!frmOrders.Controls!TrainerName.Value)); 
In the frmTrainers form, I had to change the Trainer Country Code control from "TrainerCountry" to "CountryCode". The SportscentreName control was added to the frmTrainers form. The TrainerName control was already present in this frmTrainers form.

And Voila! the problem was solved.

3 1527
wirejp
77 64KB
I have managed to resolve the issue with the database. I had to add the country name and sportscentre name controls to the trainers form.
May 11 '16 #2
zmbd
5,501 Expert Mod 4TB
Good to hear that you were able to solve the issue. That particular error is quite common when the related field isn't properly set either via a master/child-sub-form relationship, VBA, Form-Control or default value in the table.

Just to clarify things though... Controls are on forms, fields are in tables/queries... what was it exactly that you did to solve the issue?
May 11 '16 #3
wirejp
77 64KB
Hi zmbd, thank you for the clarification about the terms called "controls" and "fields". I updated my post#2 to say "I had to add the country name and sportscentre name controls to the trainers form". Yes, there were more steps involved in fixing the problem.

As the SportsCentre, Trainers and Customers are in the same country, the field called [CountryCode_pk] in the tblCountry table is linked to the respective country field of SportsCentre, Trainer and Customer table by a one:many relationship . Originally the [TrainerCountry_fk] text 255 (in the Trainers table) was linked to [CountryCode_pk] text 3 (in the Country table). Similar situation for the SportsCentre table and Customer table. So I had to change the naming of the [TrainerCountry_fk] text 255 to [CountryCode_fk] text 3 in the SportsCentre table. I performed similar changes for the [SportsCentreCountry_fk] text 255 and [CustomerCountry_fk] text 255 in the SportsCentre table and Customer table respectively.

In the frmOrders form, I had to update the Data Row Source code for the Trainer Name combo box: -
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTrainers.TrainerName FROM tblTrainers WHERE (((tblTrainers.CountryCode)=[Forms]![frmOrders].[Controls]![CountryCode].[Value]));
I also had to update the Data Row Source code for the SportsCentre combo box: -
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSportsCentre.SportsCentreName FROM tblSportsCentre INNER JOIN tblTrainers ON tblSportsCentre.SportsCentreName = tblTrainers.SportsCentreName WHERE (((tblTrainers.CountryCode)=Forms!frmOrders.Controls!CountryCode.Value) And ((tblTrainers.TrainerName)=Forms!frmOrders.Controls!TrainerName.Value)); 
In the frmTrainers form, I had to change the Trainer Country Code control from "TrainerCountry" to "CountryCode". The SportscentreName control was added to the frmTrainers form. The TrainerName control was already present in this frmTrainers form.

And Voila! the problem was solved.
May 12 '16 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: JMCN | last post by:
hello i receive a runtime error '2465' whenever i run my module in access 97. it says 'Run-time error '2465' OOB Reports can't find the field "DuplicatePayments' referred to in your...
2
by: Polly | last post by:
I'm trying to write the results of a query, a name, ID number, and date out to a notepad .txt file to print on a "legacy" printer. I get the output from the first 2 "write" lines over the...
4
by: Aliza Klein | last post by:
Apologies in advance as I am SURE this has been asked 100 times - but I haven't got a simple answer: I have MS Office XP Developer and have created an installation package (with the runtime) for...
4
by: peashoe | last post by:
I get the following error: ADODB.Field error '80020009' Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. This is my code: ' Get...
0
by: Yelena Varshal via AccessMonster.com | last post by:
Hello We have a shortcut for our MS ACCESS application that uses a /Runtime switch because we may have some users that use Runtime. The application worked fine in Access 2000 and was tested with...
4
by: indhu | last post by:
Hi i am getting this kind of error while saving the records this error comes. runtime error: you cannot add or change a record because a related record is required in table scene Private Sub...
5
by: Tony | last post by:
I am continuing to develop an Access 2007 application which was originally converted from Access 2003. In Access 2003 I was able to disable the Access Close button in the top righthand corner of...
0
by: Tom Gibbons | last post by:
I have an application in Access 2007. I have split the files and created a distributable package that includes Access 2007 Runtime. The front end is linked to tables in an .mdb file from Access...
2
by: Rinis | last post by:
Keep in mind that I'm new to VBA with access. so try to break it down as much as possible. I just got some fantastic help here earlier so I hope for the same luck. I'm doing a basic timeclock...
5
Seth Schrock
by: Seth Schrock | last post by:
I have created a navigation form in Access 2010 using the horizontal tabs, 2 levels style. This form is my startup form. In the top row, I have to options. Under each option are several sub-tabs...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.