Several Issues with forms

victorsales

Registered User.
Local time
Yesterday, 17:28
Joined
Aug 2, 2013
Messages
14
Hi All,
I have recently started using Access in order to create some standard in the way we keep our records and to move several different excel files into a single database.
So I started with the headings on the 2 spreadsheets we use: Supplier + Product Information and then applied the normalisation. I indentified all the relationships, created extra tables to manage many-to-many relationships and once finished I end up with 13 tables.
I was quite happy with the result and decided it was the time to start building the 2 only forms that I want people to be filling in (adding of course all the sub forms).
Here is the problem:
When I try to input a record on the supplier table I get the following error:
“Cannot add record(s); joint key of table “SupplierInfo” not in recordset” :confused:

And when I try to input a record on the “Product Table” I get the following error: “The value cannot be added to this new row until the row has been committed. Commit the row first, and then try adding the value."
:confused:
None of the fields in both forms work they all show the same error. :banghead: :banghead: :banghead:



Considering I’m new to Access could someone point me on the right direction?


I can send a copy of the database if requested.

Thanks,
[FONT=&quot]Victor[/FONT]
 
Welcome aboard:)
Sounds like you have created some type of pathological relationship where you can't insert a record in tblA unless there is a related record in tblB and you can't insert a record in tblB unless there is a related record in tblA.

Take a look at all your relationships and make sure they make sense. You don't have enough posts to upload files yet so you'll have to describe the relationships for now.
 
Hi Pat,

thank you for your response. I have named my pathological DB "little_frankestein.accdb" for the time being!!!
Right, there is nothing better than trial and error (and google) and trial and error (and google) and... you know.

So this is how I solved my problem:

First of all I've just started using Access so I've made (still do) lots of mistakes. The first one was to drop all the fields from the several tables onto only two forms without creating subforms (I only want people to populate 2 forms). Here is why I've got all the errors reported before.

So I started creating subforms and linking them on the main form. I've got almost everything working now (after a whole weekend in front of the PC)!

I'm missing a final link that maybe you can help me with.
I've got the "Transport table" that links to the "Suppliers table" (1 to many relationship) = one supplier can have only one transport co but one transport company can be associated to several suppliers. So I created a foreign key fkTransportID in the supplier table.
I would like to have a section of the supplier form where people can select the transport co and maybe create new ones directly from there.
The problem is that I can't get the transport subform to work. It seems that it's not linked to the supplierID and there is not option to change the child / master properties.
This is how I did it: created the transport form from the transport table. Dragged it onto the supplier form. I can create new transport co but they are not "connected" to my supplier. What am I doing wrong?

Thanks again,
Victor
 
This is what is often called a "lookup" relationship. You don't manage them with subforms since the main record can only reference ONE instance. Use a combobox to select the transport company on the supplier form. You can use the NotInList event to open a form to add a new transportation company at that point. This is designed for a one-time use. It is not a way to manage transportation companies. There should be a separate form for that.
 
Hi Pat,

I will make some research on the lookup relationship.

Thanks,
Victor
 
Hi Victor ! Welcome to the forum !

I've read somewhere (I don't remember the exact place) that you can upload anything, even if you have not yet 10 posts, by using ZIP format.
So, ZIP your database and try to upload.
Better is to convert in A2003 or 2007 format before to ZIP)
 

Users who are viewing this thread

Back
Top Bottom