473,320 Members | 1,921 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.

Two Subforms Entering Information to One Record

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!
Apr 21 '10 #1
9 2141
NeoPa
32,556 Expert Mod 16PB
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.
Apr 22 '10 #2
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.
Apr 22 '10 #3
NeoPa
32,556 Expert Mod 16PB
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.
Apr 22 '10 #4
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.
Apr 23 '10 #5
NeoPa
32,556 Expert Mod 16PB
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 ;)
Apr 23 '10 #6
NeoPa
32,556 Expert Mod 16PB
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...
Apr 26 '10 #7
NeoPa
32,556 Expert Mod 16PB
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.
Apr 26 '10 #8
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.
Apr 26 '10 #9
NeoPa
32,556 Expert Mod 16PB
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.
Apr 27 '10 #10

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

Similar topics

0
by: Jenni | last post by:
I currently have a query that is joining two distinct pools of information and turning them into one records with a unique identifier This query is dependent on criteria passed from a form in a...
1
by: M Wells | last post by:
Hi All, I am developing an Access 2003 project application with the back end in SQL Server 2003. I have a master form that tracks projects, and several subforms on it that track various...
0
by: Jack | last post by:
Gday everyone, I'm dearly hoping Stephen Lebans is going to update his masterpeice to stop the mouse wheel scrolling to work on subforms *he has indicated this to me but of course beggers can't...
3
by: Diana Gard | last post by:
Perhaps this is a design flaw, please let me know. I'm using Access 2000. I have a form with a tab control and 5 subforms within those tabs. The forms match with the tables: Client main,...
0
by: misscrf | last post by:
I am currently working on a database, in 3rd normal form, which is for candidates who apply for a job with the law firm that I workd for. My issue is with good form design. I have a main...
2
by: Reginald Bal | last post by:
Hello, I created a main form with 2 subforms. In an attempt to move to the next record (or new record) on the main form I get the error " Method 'requery' of object '_Subform' failed". The...
11
by: elaina | last post by:
Hello again. I have setup a master form linked to a subform (actually nested subforms, but the top level subform is the one giving me trouble.) When entering data, the forms all appear to work...
4
by: Harlequin | last post by:
I have a question concerning the need to trigger events within a "child" subform which is itself enbedded within a master "parent" form and which is accessible via a tab in the parent form. Becuase...
9
by: frys | last post by:
Access 2003 Windows XP I have a form that is for tracking attendance at programs. the main form is the program information, and the sub form is the attendance information. I have 5 homes...
1
by: isoquin | last post by:
Hello- I have one form frmProject1 (record source tblProject1), with two subforms within it: 1) frmPayroll (tblPayroll), and 2) frmTimes (tblTimes) Basically, there are multiple different...
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
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...
1
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
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.