Error code 3201??????

[smelly]

Hello, I have two tables which are linked together by the Client ID. One is a Clients Information table and the other is a Project Details table. When I try to save a new record to the Project Details table it gives me this error:
You cannot add or change a record because a related record is required in table 'Clients Information'. (Error 3201)
I know it is because I have the referential integrity set but what "new record" would I add to the Clients Information table, since the only thing I am storing there are Clients?
Thanks!

 

[Jack Cowley]

Create a form/subform where Clients is the Main form and Project Details is the Subform. Selecting a Client from the Main form will then allow you to add data to the subform.

hth,
Jack

 

[smelly]

Thanks

I will do that but is there another way also for future reference. (I have many forms which are linked together and I am curious if this may happen again if there is an alternative.)
Thanks Again!

 

[Jack Cowley]

If you have other One to Many relationships the a form/subform is probably the best way to go. You can open the One form, select a Client then open the Many form being sure you pass the ID to the Many form so you have maintained the relationship. Here is an article that may prove helpful.

Jack

 

[smelly]

Thanks for the help. I made a form and a subform and it took care of the error code problem but now it is not saving the information from the subform into the projects table. Thank you for your patience and help! I will go look through the article you sent and see if there is anything about it there.

 

[Jack Cowley]

If the subform is based on the projects table then it should save the data without a hitch. Your tables might look something like this:

tblClient
ClientID (Primary Key)
LastName
FirstName
...more fields..

tblProjectDetails
ProjectDetailsID (Primary Key)
ClientID (Foreign Key)
ProjectName
Description
...other fields...

If the subform is based on tblProjectDetails then selecting a Client in the main form should allow you to save as many records as you like for that client in the subform. I think you just have a small hitch somewhere as this is pretty straight forward.

Good luck.

Jack

 

[smelly]

It is saving to the projects table but when I look at the record in the clients table and click on the plus box it shows the projects table with nothing saved in it.
THank you!

 

[Jack Cowley]

You may need to do a Refresh for the answer to show by clicking on the plus box. I learned Access with Version 2 so this Datasheet view is something that I never use. I could be wrong about this, but the main thing is that the record is saved and that is what is important. Clicking on the Plus is just a tool, in my opinion, that has little value in the world of your client. If the record is saved then you have lots of ways of letting your client know that and displaying the data they have just entered...

I hope that makes sense and that I have not gone off on an irrelevant tangent.

Jack

 

[smelly]

Thanks for your help. I do want it to show in the datasheet view so they can see an overall picture of what projects are working for the clients. I will keep working on it and see if I can find an answer to my problem.
Thanks again!

 

[Jack Cowley]

I am not sure what is going on for you. If you have a Clients form with a Project Details form and the Project Details form is a subform of the Client form then selecting a Client in the Main form should show you all the Project Details for that Client in the subform.

Sorry that I was not able to help you solve your problem...

Jack

 

[smelly]

Great, I finally figured it out. There was a glitch in my linking fields. Thanks for your help!!

 

[Jack Cowley]

Congratulations! Glad you got it sorted....

Jack

 

[smelly]

Hi again,
I have one more question. I have a combo box on a form where the user selects a company name. After selecting the comapany name I would like to have a text box on the form displaying the company number corresponding to the comapany name the user selected from the combo box. Do you know how I could do this? (The form is based on a different table from where the company name and number comes from.)
THanks again!!

 

[Jack Cowley]

Assuming the combo box has two columns, CompanyID and CompanyName and CompanyID is the first column in the Combo box. In the After Update event of the combo box put code like this:

Me.NameOfTextBoxOnForm = Me.ComboBoxName.Column(0)

hth,
Jack

 

[smelly]

Nope, it only has the company name column in the combo box. Is there a way I can get the company number from the table without having it in the combo box?

 

[Jack Cowley]

Sure, but why not added it to the combo box as you can add it and not display it. It is easier and quicker to do that than to write code or use DLookup() to get the number. Just create the combo box using the Wizard and when asked hide the CompanyID. No one will be the wiser...

Jack

 

[smelly]

Ok, I will do that-thanks!

 

[Jack Cowley]

You are welcome... Good luck with your project....

Jack

 

[smelly]

I tried this code....

Me.Text52 = Me.Combo6.Column(0)

and I get this in the text box...

#Name?

 

[Jack Cowley]

Is the CompanyID the first physical column in the combo box? What version of Access are you using?

Jack