Linking Subforms In A One To Many Relationship
Aug 4, 2005
Hello,
We are a theatre company attempting to link our bookings and contacts tables in a form - subform which can be viewed in chronological date order. We are having trouble establishing a one (contacts) to many (events) relationship and setting up the forms as such. What are some easy steps to ensure that both forms are visible, functional, and appear in the order we require? As of now, we only have the two separate forms built (along with the corresponding tables). The relationship field, we presume, is the organization contact itself, and the main form would be the events with the contact appearing as a subform at the bottom of the page.
Thank you for your help!
View Replies
ADVERTISEMENT
Apr 30, 2005
I’m truly sorry about posting a question that probably should be found by searching this forum. However, despite some ambitious, but not very fruitful, efforts to locate answers to it, I’ve decided to start this thread anyway.
I’ve used a sample database to create a contact database. The sample database contains a table called “Samtal” (which in English should translate into something like “calls”). The idea is that every contact in the database has a 1:N relation to the samtal table. It means that the user continuously can update the Samtal table for every phone call made to different contacts.
Every contact has a unique contact ID, which links the two mentioned tables together. Every entry in the Samtal table has a unique Samtal-ID (called “samtalsnummer”) as a primary key.
In order to easily access, administer, create and modify different entries in the Samtal table, the form “Kontakter” contains a button called “Anrop”. Clicking on this button, a new form, “Samtal” opens (taking the contact number as input from the active contact in the form from which the button was pressed).
The Samtal form contains two subforms (“underformulär” in Swedish):
1. Listing all the entries that are found in the Samtal table for the active contact’s contact number.(samtaslista underformulär)
2. Stating the content of the phone call. (samtal underformulär)
Very conveniently, the user can scroll through the list (in the first subform) and by picking/marking a certain entry, be able to see the content of the particular call (in the second subform).
My idea was basically to copy the Samtal table’s and form’s architectures to create a table for addresses, and in this way be able to assign every contact an arbitrary amount of addresses (and in this way keep track of old addresses as well).
After have done this, and even added an “Adress”-button in the Kontakter form. (Note that every address entry is to be assigned an “address type” collected from a predefined table called adresstyper.) Somehow, however, I can’t get it work at all. Gatuadress (Street address), Husnummer (Street number), Postnummer (Zip code) and the other fields found in the second subform (“adresser underformulär”) aren’t working.
My simple question is why it isn’t.
Any ideas, literature suggestions or comments are highly appreciated as I’ve been struggling with this for over two weeks. I’m attaching the database to this post.
Thanks a lot in advance!
View 12 Replies
View Related
May 6, 2005
I am sorry if similar questions have been asked by others but my understanding of subforms isn't good enough to apply other answers to my problems.
Essentially I have two problems. My situation is that I have a form with 4 tabs. Two of these are just straightforward tabs which have certain fields that must be completed. Originally I only had two tabs and then you would click on a button to open a different form. Before opening it qould test that the key fields had been completed and then open the form. I have now added this to the original form as a subform for the third tab. I suspect it may be rather obvious but I don't know how to apply the same logic so that I cannot switch to this tab until the key fields in the previous two tabs have been completed.
My second problem has to do with my understanding of how subforms link I think. My fourth tab is also a linked subform. When I have data in the first two tabs this form is visible but when there is not data this form is not visible. Why might this happen with this subform but not with the subform on the third tab which is currently always visible regardless of the data?
I hope this is clear but if it's too abstract please let me know.
Many thanks
View 1 Replies
View Related
Oct 25, 2006
Hi, all--
What is the simplest way of linking two subforms so that selecting a record in one subform will result in specific related records in the other subform?
THe subform where the initial record will be selected is linked to the main form but the other is not and has no fields in common with the main form.
I do have some VBA experience, although it has been a while.
Thanks!
Paul
View 6 Replies
View Related
May 6, 2005
Hi,
I tried searching for an answer, but with no luck.
My problem is with linking master/child forms.
What I want the subform to do is to show me a list of records that have the same "GroupID" as the master form. To do that I set the link properties to:
Link Master Fields = "GroupID"
Link Child Fields = "GroupID"
This works perfectly fine! Now I wanted to take it a step further and add another link so now I have this:
Link Master Fields = "GroupID;LastName"
Link Child Fields = "GroupID;LastName"
Now what (I figured) Access does is show me the records where the "GroupID fields match AND where the LastName fields match". But what I really wanted was that it displays the records where the "GroupID fields match OR where the LastName fields match".
Can anybody suggest something?
Thanks
Luke
View 3 Replies
View Related
Aug 6, 2006
I have found a lot of answers here to my multitude of questions. However, I am having two final issues. Both happen to be in my subforms.
Issue 1
I have a subform called Student addClass Subform and in the ON CLICK event of the save buttom it runs the following code
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
[Forms]![Student_Classes_Subform].Requery
It Should requery a subform called Student_Classes_Subform which is in a main form called Students however it constantly tells me that is can't find that subform. I had this working before and made a few changes while working on issue 2 and some how messed it up and can't figured out what I did. Please Help!!!
Issue 2
In my main form called Students I have a 2 subforms that I need to link in the ON CURRENT EVENT I want to link Student_Classes_Subform to
Student ClassNTinfo Subform. Currently there is a relationship between the fields NT_ID. I assume that I need to pass a variable out of one subform to the main form and then back into my other subform. For the life of me I can't figure out how this works since the data is coming from two different tables. All of the examples I have scene pull from one table. Any help is great!
P.S. I am attaching the database and linked tables that will help where my explanation didn't.
Cheers
-andy
View 10 Replies
View Related
Feb 5, 2014
I am trying to link two unrelated sub forms to a main form so I am able to query data all at once and make a report that displays all this data at once. I do not know if this is possible. I will tell you to the best of my ability about what I have going on.
My main form is a shift report. The primary key is a auto number ID. The rest of the fields are date, name, shift, vehicle. etc.
The first sub form is area attendance. Field are as follows auto number ID (primary key), report ID(which comes from the main form, linked), the area, and the area attendance.
The second sub form is the event log. Fields are as follows auto number ID (primary key), report ID(which comes from the main form, linked), time in, and events.
My relationship now is simply primary key from the shift report (the autonumber) going to the first and second subforms report ID's.
Problem is I can not query two distinct subforms like this (I realized).
View 1 Replies
View Related
Nov 12, 2013
i am making a database from which i can calculate fuel required from one point to other in any particular type of vehicle.the problem is fuel from Pt A to Pt B is not same as from Pt B to Pt A.A sample table is attached as zip. qp.bmpis there any way i can cross refer field values with that of rows .
View 9 Replies
View Related
Aug 28, 2014
Users are too lazy to check whether a patient record exists or not. Consequently, we have a number of duplicate records that I would like to merge.
My problem is finding a way of linking 2 subforms to a parent that is not based upon a query.
On the parent form, I want two Comboboxes called 'Patient_to_Keep' and 'Patient_to_Discard'.
The two subforms called Frm_DUP_Patient1 and Frm_DUP_Patient2 should then display the data of the respective combo boxes.
Once I have this working, I can write the necessary code to compare and update the data as required.
View 5 Replies
View Related
Feb 4, 2006
I forum could someone tell me:
I have an unbound form on that form I want to put three sub forms one on a products table the other on a course start dates table and the link table that joins the other two together. all three are related to each other with Pk/FK links.
When I try to link them it says you cannot link items on an unbound form.
regards in advance
Peter:eek:
View 1 Replies
View Related
Jun 28, 2007
I have a form with 5 subforms in it.
I am using recordsets to step through a table that contains a field with the names of the subforms.
Depending on the code, I want to make visible or hide the subform for that particular record.
ie Table has 5 records with a field called subForm. the values are as follows:
Record1: Field1: Value="Yes" Field2: Value="subForm1"
Record2: Field1: Value="Yes" Field2: Value="subForm2"
Record3: Field1: Value="Yes" Field2: Value="subForm3"
Record4: Field1: Value="No" Field2: Value="subForm4"
Record5: Field1: Value="Yes" Field2: Value="subForm5"
When I step through the table if the code finds "No" in Field1 then id makes the subform referred to in Field2 invisible and if it finds a "Yes" it makes it visible.
Can anyone help me with this?
View 2 Replies
View Related
Dec 13, 2005
Does anyone know how to have a datasheet view in a subform fill the top of the main form?
I want to be able to see the records in a datasheet view and when the record in the datasheet view is selected it prefills the top of a form ?
View 1 Replies
View Related
Feb 18, 2005
This is a question from yesterday that a member had helped me out with.
I have additional questions while I am waiting for response from him. If anyone of you can help, I would really appreciate it.
1. How can I make a form that gathers information from multiple tables?
2. Lets say it is possible, how does this info get saved to these multiple tables when the user clicks on the next record button on the record navigator.
Thanks for the assistance!
View 2 Replies
View Related
Aug 12, 2005
is it possible to have more then 2 subforms?
Lynda
View 1 Replies
View Related
Sep 18, 2005
Heya --
So if I haven't scared everyone away with the post's title, maybe someone out there can help me. I need it.
I have a many-to-many relationship with the following tables:
Companies
CompaniesProjects
Projects
I also have a query with the following:
CompaniesProjects.CompanyId
CompaniesProjects.ProjectId
Projects.*
Pretty straightforward. Now, I have a form to view Companies, inside of which is a subform listing Projects associated with the Company using the above query.
The issue that is stumping is that I would love to be able to add a new project from the subform. I thought it would be pretty easy, but I'm totally dry. I guess I need to grab the CompanyId from the parent form, and assign it to the new record before I can add any more data to the new record. But I have no idea how to do this, or even if it is the right thing to do.
Any help would be _greatly_ appreciated. I've been slamming my head against a wall with this for two weeks.
-Lucas
View 2 Replies
View Related
Nov 29, 2005
Hi,
I have 3 tables : Products, Description and ProductDescription. Products can have more then 1 description, Descriptions can be used by more then 1 product, that is why the third tabel ProductDescription exists (normalizing..:) ). The Productdescription table has 3 fields: id, productid, descriptionid.
I want a form in which you choose a product, then you see all the descriptions along with it. In this same form I want to be able to add a description, e.g. by pushing a button NEW. After that user enters description form etc. After returning to the mainform, a new record must be automatically added to the ProductDescription table and ofcourse a new record has been added to the Description table. I thought I would use subforms to realize this but it isn't working..:( . I have tried so many things that I dont know anymore what to do, i am totally confused. I know how to program in VB and ACCESS, but for now i don't see it anymore. Can someone please help me with this?
I just need to know how to start, what to use as subform and what as masterform, which links between fields, etc
nelleh:rolleyes:
View 2 Replies
View Related
Jan 17, 2005
Just a quicky.
I am trying to design a form with three subforms.
I keep getting tblXXX not in recordset.
Is there perhaps something wrong with my relationships?
Thanks,
Brian.
Zimbabwe.
View 3 Replies
View Related
Nov 22, 2006
Hello,
I hope someone could come up with a solution. I have a form and 2 subforms. The form contains the person id, address and phone number. The subform1 contains a list of skills that person has which is derived from a query. I have to be able to delete any of the skills that person has. I am not sure how to delete the skills but I know when I type a skill, it automatically adds (can grow = yes). In subform2, the skills that the person DOES NOT have are listed derived from another query again. We should be able to click on any of those skills so that it will be an addition in subform1. I do not know how to do this.
Thank you for anyone who comes up with the solutions.
View 1 Replies
View Related
Oct 17, 2007
I am designing a student registration system in MSAccess. It consists of tables such as Applicant,Courses,Department,Qualification,Employe r details..the applicant has to choose only 3 courses. i have already designed all the tables and relationships. I need to create an application form which allows the user to imput the personal, qualification and employment details of the applicant. i have used tabs for this part. now i have to create a form where the user input the 3 choices of the applicant. i have used a subform in my application form. my problem is how do i restrict the user to input only 3 courses and how do i link the subform with the main?how do i make sure that when i select 3 courses, it is linked to that specific applicant?
View 8 Replies
View Related
Nov 22, 2005
Hi there.
I have a form with two sub forms on it. The form loads with a reference number that I pass to a function that in turn requeries the first sub form. The function call is in the on load event of the main form.
This part works fine.
The second sub form uses a similar requery function to the first one. The only difference is that the query for the second sub form is based on a reference number from the selected record in the first sub form.
If I put the function call for the second sub form in the on click of the first one all is well and the second sub form displays all records with a matching reference number.
The problem is that when the main form loads the first sub form will display the correct data but the second remains blank or displays the wrong data.
I know this is happening because the first sub form has not loaded fully and so the second sub form cannot get the reference number. I just can’t find a way to make the second sub form display the correct data when the main form loads.
I have tried calling the requery function for the second sub form from most of the events on the main form and many of the events on the first sub form but to no avail.
I also tried putting a meaningless sub form at the bottom of the main form and putting the requery function for the second sub form in the on load event but that was no good either. This surprised me as until that point I was assuming that access was loading the sub forms sequentially from top to bottom.
Can anyone tell me how I might determine the point at which the first sub form has been loaded so that I can get access to the reference numbers needed for the second sub form query. I would prefer not to use a timer for this as there is no way to determine the maximum time it will take access to load the rest of the data but at present it looks like the only option left.
Any comments would be much appreciated and sorry for the lengthy explanation.
View 6 Replies
View Related
Aug 2, 2006
Please help !
I have been struggling to create forms with these relationships(see attached file) . I would like to create a subform of the product list and the form must have Title TitleID and Artist and Artist ID ...these also need to populate the Product list table... oh how do I get around this ! What am I doing wrong
View 7 Replies
View Related
Feb 11, 2008
Hi, I have a main form with a subform inside it. The subform contains two records but only one is shown during form view, is there any way to show both records at the same time?
I am using continuous forms view for my subforms as i have to.
View 3 Replies
View Related
Sep 7, 2006
Hi guys
The question i am looking for help with is.....
i have a database with a form(form1) and a subform(sub1), when i query this it will only desplay information that has all boxes filled in. i want it so that if there is info in form 1 but no info in sub1 i will still display the info from form 1. At the moment it just ignores the whole thing and does not give certain records from form 1.
any ideas?
James
View 2 Replies
View Related
Feb 10, 2005
First I'm an average access user, but don't know much about VB. I've created a subform with a querydef that works fine standalone, but as soon as I move my subform onto my main form the the querydef no longer runs.
Am I missing something simple?
View 3 Replies
View Related
Mar 11, 2005
I have a main form with 4 subforms. The subforms a setup in columns kinda like excel. Eack subform is displaying data. What I want to do in compare the data in subform 2, 3, 4 to the data in subform 1 and if different using the conditional format change the color of the fields. How do I code a conditional format from subform to subform?
Jim
View 1 Replies
View Related
Mar 21, 2005
i have a main form, in which i have included 2 subforms on it. Now when i am at the last control on the main form, the focus moves to the first control of one of the subforms. However, when i am at the last control of this subform, on hitting the tab key, the focus does not move to the first control of the other subform.
How can i achieve this please?
Thank you!
View 1 Replies
View Related