472,787 Members | 1,346 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,787 software developers and data experts.

Subform Wizard and number of primary key fields

I have two tables in a 1:M relationship- the parent has 5 fields in the
primary key and the child 6 (these are actually pretty far downstream in
a complicated ER model, but the problem is between these two). I set up
the relationship with no problem- referential integrity and left outer
join. I have created two forms- one for the parent and one for the
child table. I want to import the child table's form as a subform on
the parent table's form. Using the Subform Wizard, everything goes well
until it gets to the point where I need to specify the linking fields.
It shows a number of entries for these, all of which are single, key fields.

My problem is that Access allows only up to 3 fields for linking a
subform to a parent form, and that would force my having to enter
manually the other 2 fields in the primary key from the parent into the
child. Is there any way of getting around this? Thanks!
Feb 19 '06 #1
2 2191
"John" <jh********@yahoo.com> wrote in message
news:dt***********@netnews.upenn.edu...
I have two tables in a 1:M relationship- the parent has 5 fields in the
primary key and the child 6 (these are actually pretty far downstream in a
complicated ER model, but the problem is between these two). I set up the
relationship with no problem- referential integrity and left outer join. I
have created two forms- one for the parent and one for the child table. I
want to import the child table's form as a subform on the parent table's
form. Using the Subform Wizard, everything goes well until it gets to the
point where I need to specify the linking fields. It shows a number of
entries for these, all of which are single, key fields.

My problem is that Access allows only up to 3 fields for linking a subform
to a parent form, and that would force my having to enter manually the
other 2 fields in the primary key from the parent into the child. Is
there any way of getting around this? Thanks!

When you say 'enter manually' you do realise that you could use vba code to
do this. In other words, you do not use the subform wizard so that the
child/master fields are not filled in at design time. You then write code
in the form's current event which tells the subform which records to
display. Code will need to be written for when you add a new record,
automatically inserting the required values.

Having said all this, I would change the table design to use single keys -
that is autonumber/long integer fields. You do realise that you can set up
a unique index over these 6 fields without it needing to be primary. I
understand there may be arguments in favour of your approach, but in my
experience the single-key approach is preferable.
Feb 19 '06 #2
Anthony England wrote:
"John" <jh********@yahoo.com> wrote in message
news:dt***********@netnews.upenn.edu...
I have two tables in a 1:M relationship- the parent has 5 fields in the
primary key and the child 6 (these are actually pretty far downstream in a
complicated ER model, but the problem is between these two). I set up the
relationship with no problem- referential integrity and left outer join. I
have created two forms- one for the parent and one for the child table. I
want to import the child table's form as a subform on the parent table's
form. Using the Subform Wizard, everything goes well until it gets to the
point where I need to specify the linking fields. It shows a number of
entries for these, all of which are single, key fields.

My problem is that Access allows only up to 3 fields for linking a subform
to a parent form, and that would force my having to enter manually the
other 2 fields in the primary key from the parent into the child. Is
there any way of getting around this? Thanks!

When you say 'enter manually' you do realise that you could use vba code to
do this. In other words, you do not use the subform wizard so that the
child/master fields are not filled in at design time. You then write code
in the form's current event which tells the subform which records to
display. Code will need to be written for when you add a new record,
automatically inserting the required values.

Having said all this, I would change the table design to use single keys -
that is autonumber/long integer fields. You do realise that you can set up
a unique index over these 6 fields without it needing to be primary. I
understand there may be arguments in favour of your approach, but in my
experience the single-key approach is preferable.

Thanks! I do know about the VBA thing, but this is for a group of
people I'm teaching who don't know VBA at all. As to the autonumber,
that won't work, I'm afraid- the applications these people will be
creating (for medical research) don't do well at all with the autonumber
approach. Thanks anyway!

Feb 19 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: turtle | last post by:
I am creating a database that tracks the Techinical Skills of our engineers. There are about about 200 techinical skills that they will need to fill out by giving themselves a ranking. Some...
3
by: Uwe Range | last post by:
Hi to all, I am displaying a list of records in a subform which is embedded in a popup main form (in order to ensure that users close the form when leaving it). It seems to be impossible to...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
0
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary...
4
by: Peter Bailey | last post by:
I have a subform that I dont want to have a recordsource initially as it is locking the table that is created dynamically. I want to connect to the table after the data has been written by another...
0
by: Jean | last post by:
Hello everybody. Can someone point out please what is wrong with my work? What I have so far: People take part in experiments, and a record is made in tblExperimentInstances. There are a number...
4
by: Don Do | last post by:
Help I built a form/subform/subsubform setup using the access forms wizard. I have a table1 = parent, table2 = child, table3 = (grandchild?). There will be multiple records in table2 that tie...
8
by: Simon | last post by:
Dear reader, The syntax for the VBA code to change the RowSource of a Form is: Forms!FormName..RowSource = "TableOrQueryName"
7
by: PotatoChip | last post by:
Ok, I have a form and a subform. The form shows a document with it's pertinent information. The subform has some of the same fields as the form and shows where the document was sent. The main form...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

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.