Problem with many side of table (related record)

[JimJones]

Hi,

I've uploaded a graphic of my database form to illustrate a better idea of my issue.

In the Service Table (subform), I have just included some extra fields: SupplierID , Parts, and Part Number.

Of course, I also created a new table "Suppliers", with SupplierID as primary key.

I can't open a new service ticket (in the subform), AND upldate the service ticket, without getting the error about a related record is required in the Supplier table.

Well, the form is actually much more cluttered than the graphic shows. I can only add a Supplier subform as a popup or something. In the graphic, such a pop up is not yet added.

I have several test records, and I don't want to delete them just to start everything from 0. How can I have the Supplier form included as a one to many (to the Service table), and just start from there, without getting the related record error?

Thanks,
Jim

 

[Rob.Mills]

Do you have the child and master links setup correctly. Everytime you add a record in the subform it needs to place the correct foreign key into the subform record.

 

[JimJones]

I have the understanding that child/master links apply to subform/mainform setups.

I was hoping not to have to set up another subform and instead make this a pop up. In my table relationships, supplier is the PK, with a many in Service.

Do I need to put at least the one field from Suppliers somewhere on the form?

Thanks,
Jim

 

[Rob.Mills]

I gotcha. In order to make a popup form work you'll need to create a hidden textbox on the popup form that is bound to the foreign key field. In its default value property type this:

=Forms![Form bound to one side]![Primary key]

This way it will automatically add the foreign key for you.

Now one issue is if you just added a new record in the form on the one side you'll need to add in your code to save the record first before opening the popup. Otherwise the primary key won't officially exist yet and you'll get the same error.