Two Subforms Entering Information to One Record

[chetoche34]

I have created a database for a storage company which will track pallets of goods from the time they are received until the time they are shipped off again. On my receive order form, I have a subform in which I can create new records for pallets received. On my outbound shipping form, I have another subform on which I want to be able to list PalletIDs, and have the outbound order number be added to the record for the pallets listed.

My problem is that on this subform, it will not allow me to put in existing palletIDs, because it wants to create a new record with only the outbound order number field populated. How do I add this the order number to the open field for existing pallets?

Thanks!

[NeoPa]

It's hard to guess what you may have done wrong, as you give us no details at all to work with. Remember we only know what you share with us about your project. Most of us know Access quite well, but that cannot help us to identify what is wrong in your particular project if we have no knowledge of it.

[chetoche34]

I would be more than happy to provide more details, but I am not sure what would help. I would attach my database if I could, but it apparently is not an acceptable file type for this web site. Please let me know what kinds of details would help you.

Thanks.

[NeoPa]

A logic response which engages my points directly. Faultless.

Unfortunately I can't answer this fully for you. In view of your response though, I will do what I can :
First identify where your problem is showing, and what may be the associated items of interest. I would guess this might be the forms and the tables referenced therein. If both forms reference the same table then this will clearly be important information. Names of objects are rarely wasted information.

I can say that having two forms open concurrently, that both access the same recordset or table, is liable to cause problems as, in most cases, they will tend to lock each other out.

[chetoche34]

OK, so my Pallet table has a field for the inbound order number and a field for the outbound order number. Both of these fields are linked to the order number fields in the the respective Inbound/Outbound Order tables. They are both linked in one-to-many relationships (with enforced referential integrity).

In my Inbound Order form (which works perfectly), I have the order number field, as well as some other fields which are only pertinent to the order in the main form. In the sub form, I have the PalletID field as well as all other fields pertinent to the pallet (with the exception of the Outbound Order Number, which should be added when we ship the pallet out). All of the fields on the subform, as well as the inbound order number all populate perfectly to the Pallet table.

The problem is on my outbound order form, on which the main form is set up almost identically to the inbound order form. The subform, however, only has one field: PalletID, which I have set up as a combobox, to show all available pallets. The combobox works fine, as far as selecting the pallets is concerned, but after I select a pallet in the combobox, I cannot move to the next record.

The error that I am getting says that the changes I requested were not successful because they would create duplicate values in the index, primary key, or relationship. Basically, it is trying to create a new record (row) in my pallet table, instead of simply adding the outbound order number to the selected pallet number - which is what I would like it to do.

When I remove the combobox, I can enter in a new palletID (not already existing in my Pallet table) and it will add a new record with only that palletID and the outbound order number. So I know that my outbound order number does go correctly to the pallet reverenced, but my problem is that my subform is wanting to create new palletIDs rather than referencing existing palletID numbers.

I hope that this helps...it certainly is a lot of words. Hopefully you can sort through the mess and pick out anything that might give you a clue as to what is going on. Thanks a bunch for your help with this, I really appreciate it.

[NeoPa]

chetoche34 - Hopefully you can sort through the mess and pick out anything that might give you a clue as to what is going on. Thanks a bunch for your help with this, I really appreciate it.

I hope I can. I'll certainly see what I can do, then you can worry about thanking me ;)

[NeoPa]

Sorry for the delay. I spent most of the weekend riding around on my new Pan-Euro. I just finished it's 600 mile run-in and wanted to loosen up the throttle a little to see how much fun it could be. It can handle fun just fine it seems :)

Anyway. Monday again so I'd better get down to things...

[NeoPa]

Actually, nothing obvious springs to mind here. What I can say though is that relevant information would be the RecordSource values of both the main form and the subform of the form where you're having these difficulties, as well as for the order number ComboBox.

I think you're saying (correct me if I go awry) that a pallet can be associated with with a single input order as well as a single output order. Presumably orders can only be associated with a single pallet.

If we cannot make progress after this next exchange then we may look at getting you to post a copy of your database in 2003 or earlier format so I can look at it in all its detail. Let's see what we can do before that though.

[chetoche34]

Well I'm glad that you had such a good weekend...I mowed my lawn and studied for the GMAT...so you definitely had more fun. :-)

To address your relationships question: Each pallet can relate to one inbound, and one outbound order. Each order (both inbound and outbound) can relate to many pallets.

The RecordSource of the main form (Outbound Shipping) is just my ShipOrder tabel, and for my Pallet-Shipping Subform, it is my Pallet table.

The PalletID ComboBox on the Pallet-Shipping Subform is set up as follows:
Control Source: PalletID (this is the primary key, ID field of my Pallet table)
Row Source: tblPallet
Row Source Type: Table/Query
Bound Column: 1

I hope that this helps. Let me know if I can provide any further information that would make this more solvable for you.

[NeoPa]

Ah. So the form is not designed to reflect pallets, with their input and output orders showing. It is designed to show the orders, including which pallets are linked to each order. You have a subform bound to the Pallet table. This makes sense. Having a ComboBox bound to the same table, and responsible for populating that table, seems a bit weird, but may work. You'd need to ensure the Link Child Fields and Link Master Fields properties were set up correctly on the forms of course.

You say the Input Order version of this construct is working correctly. We'd need to see where the setup is different when comparing the two. Possibly check through all the property settings for differences.