Forms :: Subforms And Many To Many Relationships - Data Not Retained
Jun 4, 2013
I have created a form with two subforms. The context of this is a litigation database. The main form is for testimony. Among other things, it includes a field for a question and an answer. Any given piece of testimony may relate to one or more documents, and any document may be referenced in one or more pieces of testimony. So have two subforms, one for documents produced by the Plaintiff, and one for documents produced by the Defendant. It worked out easier that way because I originally created indices of those documents in separate Excel tables which I then imported to Access.
So the relationship for both document tables relative to the testimony table is many to many, which is established using two junction tables.
I also have a combo box in each subform where I can autofill fields based on selection of the document number.
My problem is that while the combo-box works great, once I select a document in the subform, that selection is carried over into the next entry of the master form, but also the selection is not retained in any of the master forms. So I can browse the document listing from any entry in the master form, but I can't get the document selection in the subform to stick and create a lasting relationship to a given record in the master form.
Is my problem that it is just impossible to have a subform based on a many to many relationship, or is it in the parent child relationship of the form and subform, or is it in the properties of the subform?
View Replies
ADVERTISEMENT
Mar 18, 2015
With a many to many relationship using a junction table, do I have to manually plug in the ID numbers from the related tables in the junction table for it to recognize the related data? It sounds silly and obvious I guess that you would have to, but when I go to create a form based on a many to many relationship, I would like the ability to enter data without having to enter the related ID numbers in multiple places on the form or subforms to make it work. I guess I envisioned access using auto numbers to automatically update the junction when I wanted to add data related between the "main" table and the distant table joined with a junction. Can this be fixed using a Parent/Child relationship set-up in some fashion?
View 1 Replies
View Related
Jan 20, 2014
I have a form and a subform.
When I click on the command button to create a New Record and then type the customer details in the Main Form and the Order Details in the Subform, the information is not retained
i.e. when I close the Form and then re-open the Form the orders details are not saved but the customer details are.
When I re-enter the order details the information is retained this time, everything seems work ok second time round!!
View 4 Replies
View Related
Nov 24, 2007
If someone can help solve this for me, I would be forever grateful. I have attached the database to which I am referring for clarity.
I am creating a database designed to input data for morbidity and mortality at my hospital. I have set up four main tables: tbldemographics (basic patient information), tbladmissions (information about each different admission a patient may have), tblmorbidity (information about each untoward event suffered by a patient, which I would like to have linked to the specific admission, not just the specific patient), and tblmortality (information about a patient's death, which I would also like to have linked to the specific entry in the admissions table rather than linked to the more general information in the demographics table).
I have created relationships which I thought would work--but with referentail integrity invoked (which it seems like it should be), the very first key stroke on the form that I created to input the data (frmmain) results in an error message that the record can not be added or chaged because related record is required in tbldemographics--and yet this is the table to which I am inputting the data! Turning off referential integrity stops the error, but I don't want to just blindly do that--seems risky.
Problem number two: when I disable referential integrity and enter data into the demographics section of the form, any entry at all results in an automatic entry into the admissions table--even if I haven't entered that part of the subform yet. So if I then exit the form and return later, ANOTHER entry into the admissions table is made--again, even if I have not ventured into that subform yet. So that table winds up with bunches of entries with little to no data, which are NOT cleared up with a compact and compare maneuver.
I'm at a loss on both of these issues. I apologize for the long post, but appreciate any ideas you all might have.
View 2 Replies
View Related
May 8, 2006
Hi there
I've got 3 tables related to one another with one to many relationships. They are:
21_Category
22_SubCategory
23_Indicator
where the 23_Indicator table has 22_SubCategory as its parent and 21_Category as its grandparent.
The forms wizard in Access has nicely developed a form with 2 subforms contained on it which does the trick for maintaining all 3 of these tables. The form itself displays 21_Category, subform1 displays 22_SubCategory and subform2 displays 23_Indicator. When I change the active record for either Category or Subcategory the subforms change their display as expected. All good.
My concern is that the form looks a mess and not very welcoming, too much info on one form, so I'd like to replace the 2 subforms (or even form plus 2 subforms) with one Tab Control. So far I can get the linking working correctly to the first page within the Tab Control but cannot link the second page child records to the active record in the first.
I'm using the Link Child Fields and Link Master Fields to do this. Even straight copying of the entries from my subforms which already work cannot get the Tab Control to behave in the same way.
Without wasting any more time on this, does anyone know whether it is easily possible or I'm missing something obvious? I suspect I've got something slightly wrong with either the Source, Link Child Fields, or Link Master Fields syntax, or referring to the wrong objects. Alternatively, Tab Controls just aren't designed to show one to many type records??
I'm not a VB Developer so if this isn't possible without writing a whole heap of code then I'll just have to cut my losses and use the crowded form I already have, though I'm happy to insert a few lines to get the Tab Control to work.
Thanks for your help in this. Apologies if this has already been posted loads before. I'm doing this as voluntary work for a charity and I'm a fairly new user of Access and VB (though I'm familiar with databases and SQL).
View 1 Replies
View Related
Dec 6, 2013
I need to take data from a form with many sub-forms. The letter has to be in word to allow for edits. Its an appointment letter, however is has a table with 1-100 lines.I need to be able to add the name, address, date of appointment and table with however many lines are needed for that client.I can get the table to work by itself or the name, address and date of appointment but not both.
View 5 Replies
View Related
Nov 22, 2013
I have several Suppliers, each of whom holds several SalesEvents. At each SalesEvent I might buy none, one or more Bundles. Each Bundle then contains one or more Items. These relationships are all reflected in my Access 2013 desktop db as one-many relationships with cascaded updates.
My form structure for data entry reflects the Relationships, in that I have a Supplier form with a SalesEvents subform. On that subform I can enter data about each of that Supplier's SalesEvents, and each SalesEvent row has a button to call up a SalesEvent form which has a Bundles subform. Then each row of that subform has a button which calls up a Bundle form containing an Items subform. That Bundle form identifies the Supplier, Sales Event and Bundle number, and within its Items subform I can then enter the data for each Item.
Problem : All used to work fine, but I've clearly changed something because now when I select the Items button on the Items subform in the Bundle form, the system gives me the data for the first record in the SalesEvents table, rather than the one selected. I have checked the raw data and that's as it should be. I have tried to undo all the steps I took since it last worked properly, but to no avail. Restoring the last backup (taken when I'm sure it worked properly) still has the problem.how do I find what data is actually being passed between forms and subforms ?
View 3 Replies
View Related
Jun 18, 2006
Hi Guys,
The asset managment system I have been developing is due to go live next week and I am really struggling with the data entry and search forms.
I have attached a picture of the main form (and outlined each subform) to give you an idea of my project.
The attached form is actually a Main Equipment Type Detail form with a child subform AssetMain which has a number of child subforms i.e. Asset Details, Purchase Details etc.
When a user clicks to add a new asset a popup form asks them to select an equipment type i.e. PC. When they click it opens the attached form. Their selection filters the equipment type detail values in the combo i.e. Laptop, Tower, Server etc.
When a value is then selected in the combo you can then add an asset for that equipment type detail. (One Equip Type > Many Equip Type Detail > Many Assets)
1st Problem)
I have set the Asset Main subform to allow additions only. When the whole form opens, the Equipment Type combo (at the top) does not display a value but does actually contain the first value.
How do I force my user's to select a value in the combo before it actually filters the child subform?
2nd Problem) The Asset Main Subform (filtered by selected Equip Type) should only be used for data entry. I set the data entry property of the form to True but when the value in the Equip type combo changes, it displays all the relating records in the subform. My only solution so far is to stick
Subform.DataEntry = True
in the Equip Type combo's after update event. However, this doesnt look to good as it flashes up with the data and then displays a blank record.
Any ideas?
3rd problem) Due to the fact that I am using a number of subforms, my tab order is such that values are entered for the main form (i.e. ID, Condition) then subforms (i.e. manufacturer) then back to the main form (i.e. allocated to). This means that:
a) I am having problems validating the record beforeinsert as I have had to change the required property to "no"
b) Once the user has tabbed through all the forms what is the best way to go to a new record? Alternatively if I add a new record button I need to check that the necessary fields have been filled in (on all of the forms/subforms).
and finally the 4th)
I have a search form that queries the assets. The search results are displayed in a list box. When you dblclick the list box I need to show the asset details.
Question: Can I use the above form to do this? My only problem is that I need to open the AssetMain form which is a sub form in the above. If I try and use the existing form it will not open as many of the controls refer to the form as a subform. Do you think I need to create a separate form for showing the data to entering it?
Would really appreciate some help (not that you havent heard that before :) )
My head is going to pop in a minute!!!
Thanks
Ben
View 5 Replies
View Related
Mar 24, 2014
I have a form with several subforms within (tabbed subforms). I've used the code:
In the Current event of the parent form -
Code:
Me.AllowEdits = False
Me.AllowDeletions = False
Me.AllowAdditions = False
In the Current event of each of the subforms -
Code: (THIS NEVER WORKS FOR ME - RUNTIME ERROR)
Me.AllowEdits = Me.Parent.AllowEdits
Me.AllowDeletions = Me.Parent.AllowDeletions
Me.AllowAdditions = Me.Parent.AllowAdditions
In the Click event of the button -
Code:
Me.AllowEdits = True
Me.AllowDeletions = True
Me.AllowAdditions = True
So the main form is locked upon opening and unlocked with the click of an unlock button. How to apply this to the subforms as well. They just stayed unlocked the whole time.
View 14 Replies
View Related
Jun 22, 2006
Hello! I am new to your board!
Can someone please tell me how to keep the value typed in a field (Text field and Combo Box) on a Access Form the same until changed (always retaining the value of the last typed value until changed)?
Thank You!
View 8 Replies
View Related
May 3, 2013
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
View 14 Replies
View Related
May 6, 2013
I'm trying to clean up a form a bit and have it only show certain subforms/graphs if the data exists. I already have columns in a combobox query to show an "X" for if certain data appears:
Now, I know I could build another query and have some system go through and identify these things, but the easiest thing would be to reference the "X" in the columns of the combobox. Is there an easy way to reference values in the other (non-primary) columns? Or can you think of an easy way to make these subforms only be visible when the data exists? Maybe have an on load event for the subform?
View 2 Replies
View Related
Sep 9, 2005
I have two subforms on a tabbed page. Both subforms read/write data from the same table. The first subform is in dataset view and consists of 5-6 fields on the line containing class information. The second subform consists of two memo-type fields where users can enter comments. The fields from both subforms should be written to the same record in the table. The problem is that
the information from the second subform is being written to a different table record; it is creating a new autonumber. The data is being written to two separate records instead of being combined into one.
How do I link the two subforms so that the data gets written to the same record in the table so that the class information and it's related comments stay together?
View 1 Replies
View Related
Jul 5, 2006
I have a form with two tabs... Its record source is Person and Account.
The main tab has a load of fields relating to the person and the other tab is meant to show the bank accounts that the person has in a subform. However if there is no bank account for the person then nothing will show in either the subform or on the person tab.
I have checked throughly to see if there is anything saying
SELECT Person.*, Account.* WHERE PersonName = etc
which would only get the account and person details if personname was there.
The form is accessed by means of a search form which opens the form like so.
DoCmd.openForm "PersonAccount", , , "[PersonName] = '" & personName.Value & "'"
So any ideas why nothing at all shows not even the Person details?
View 3 Replies
View Related
Mar 19, 2008
You know the scenario where people keep working in an old version of a database, while you work on an update... leaving you with a new database, but old data?
This concerns a database, containing about 20-25 tables all linked together.
I was asked to make an update. In this update 3 new tables were included and here and there a few of the old tables got news fields, nothing crucial and a lot of forms were changed.
The current situation:
Database 1: contains new data, misses a few tables, is missing some fields, forms and queries
Database 1.1: contains old data, but some new extra tables, added fields, forms and queries
I would like to know if it is possible to import all data from 'database 1' into 'database 1.1', so that all linked information between the data is preserved, all database relations are maintained.
If I import tables from Database 1 into database 1.1, tables just get added, not replaced. If I delete or rename tables, I loose my relationships.
I would like to know what is a common and effective way to get all the new data of an old version of a database (where people keep working in while you work on an update), into the new version of the database, that currently contains old data.
View 3 Replies
View Related
Jan 31, 2006
Hi everyone,
I have a relationship between Market data table and Trade table which is One to Many. Market on the One and Trade on the Many.
However I just found out that I am unable to enter information into the Trade Form without entering information into the Market Form first.
I would like to enter info. into Trade Form/tables independent of whether I enter info. into the Market Form/table, but I still need to have the relationship of One Market data to Many Trade date.
Thanks
dfuas
View 5 Replies
View Related
Aug 20, 2007
I have 2 excel sheets that I have imported as external data. I created a relationship between the 2 tables on part# field that is common to both. The relationship is to take all info from A table and only those from B table that match. The part# field is set to text in both tables. When I run a report that merges the 2 together a significant # of parts in my field change to a negative number. For instance the part# in both tables are 12345678 it is changed to a random -3452345 number..
Any ideas?
Please help
View 1 Replies
View Related
Sep 6, 2005
I'm new to form design and do not know any VBA (but that won't stop me from messing with the code when need be).
I'm creating a form that will serve as the user-interface for viewing, adding, and deleteing data.
The product I want is a form with 3 items: a subform that displays data in a table (so users can see all the data), a button that links to a second form for adding data, and a button that allows users to delete data.
I've gotten close to accomplishing this with the exception of the delete option. The hang-up is due to the fact that the subform that displays all the data is the output of a query (can't delete data through the query). A friend who used to work with Access years back was trying to solve this issue by writing code that would identify the primary key of the record selected in the subform/query and then adding code that would use this extracted primary key to delete the record in the original table.
This seems like a slightly convoluted approach. Any suggestions how I can get the product I'm looking for?
Thanks,
Phil
View 14 Replies
View Related
Jul 8, 2004
Picking up on some previous conversations with people, I have a form (Interconnect Register) within a database that I am trying to add a "cancel" button to which would close the form without saving. I can do this now, but the problem is there is a subform (Sheetsubfrom) within the main form. I have struggled to get the subform data to clear (was trying to use the DIRTY property but it wouldn't work - believe me, spent ages on it!) but have now got the following in place which I thought would work.
Private Sub Command41_Click()
Dim retval As Integer
'Check main form
On Error GoTo Sub_Form:
Forms![Interconnect Register].SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Sub_Form:
On Error GoTo Error_Handle:
Forms![Interconnect Register]![SheetSubform].SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close
Error_Handle:
DoCmd.Close
End Sub
The problem has come full circle, in that when nothing has been changed ie nothing to cancel, an error occurs saying "nothing to undo". I believe this is something to do with having an error handling procedure nested within another error handling procedure, as from what I can make out from the help files Access backtracks to the level above. So when I have an error in the Sub_Form part, the whole thing crashes.
Any ideas guys and gals? I could try posting then DB on here if that would help, providing I can zip it up ok
Thanks
Ian
View 11 Replies
View Related
Sep 27, 2013
I have a field showing who equipment is being distributed to pulling data from my US Employee table. If I make it to only the last name from my lookup
(SELECT [US Employees].[Last Name], [US Employees].[First Name], [US Employees].[Dept] FROM [US Employees] ORDER BY [Last Name], [First Name], [Dept])
I can also view the equipment listed under the individual it is assigned to on a subform on my US Employee table. If I try to have the first and last name displayed on my Equipment table, however,
(SELECT [US Employees].[Last Name]&", "& [US Employees].[First Name], [US Employees].[Dept] FROM [US Employees] ORDER BY [Last Name], [First Name], [Dept]; )
it will not display under the individual on the US Employee table. Is there a way around this without having to combine the fields on the employee table or separate them on the equipment table?
View 8 Replies
View Related
Mar 2, 2015
I am new to access but I am familiar with SQL databases. I have two tables: one for project data and another for project leader data. These tables are linked by a third table that relates projectIDs to leaderIDs. I am currently working on making a form that will go through each project and display the people involved in each project in a subform. I pulling the data from the the leader table that matches the the projectID with a query.
I would like to be able to update the information in this subform directly (which I can currently do) and be able to add new project leaders to a project. What is the best way to add the new leader to this? If a person is already in the database how do I add them with the form without re-adding new information to the table?
View 3 Replies
View Related
May 31, 2005
Hi, I want to create an access app that will allow a user to see the number of pages printed by various printers.
These are the data that I have:
Model Type (various are available)
Printer's name (unique to one printer)
IP addr (unique to printer)
Quantity printed (such as Single Legal, Double Legal, Single Letter,etc)
This is what I have (2 tabels with 1-many relationship):
Printers
PrinterID
Model
Name
IP
ModelTypes
ModelID
Model
Now, where do I place the quanties of paper printed? Do I just leave it in Printers?
Thanks.
View 9 Replies
View Related
Nov 22, 2006
Hi All
I've worked with Access in the past but haven't touched it for 6 years now so I've forgot a lot of the things i've learnt. Bare with me while I try and explain it as clearly as possible
I'm creating a Database for work which will register all our Commerical Clients, which projects the clients are related to and which students worked on them projects. So I have 3 main Tables:
1.Commercial Clients
Client ID
Company Name
Contact
Title
2. Commercial Projects
Project ID
Nature of Project (Graphic Design, Multimedia, Media)
Project Name
Project Information
Staff Member Responsible
Start Date
End Date
3. Students/Graduates
Student ID
Student Name
Area of Expertise
Contact Details
Last Project worked on
Right here we go.....
Each commercial Client can have more then one project to their name. Each Project can have more than one student working on it and each student can be working on more then one project.
My relationships are set up like this:
Commercial Clients <One to Many> Projects
Projects <Many to Many>Students
What I have done is create forms with sub forms in to enter to extra data. I dont know if this is the right way to do it but it seems to easiest way. Please show me a way that will work better if you know it.
Anyway... the way the tables are now I can on cascade the information down through the tables.... it will not cascade upwards. I have ticked everything you can possibly tick for all relationships. So when I can enter projects in the Subform on the Commerical Clients Form and I can enter students on the subform in the Projects form. But on the Student form the projects the students at on does not show up in that subform and entering the day only tries duplicating it (which i dont want to do). If I had a form that showed the projects and the related commercial clients in the subform it wont find the commerical clients. So i.e.
It shows fields when the cascaded down like:
Commercial Clients
V
V
Projects
V
V
Students/Graduates
But it wont show if its:
Commercial Clients
^
^
Projects
^
^
Students/Graduates
Anyone have any ideas how I get get them to cascade upwards?
Thanks
Pete
View 9 Replies
View Related
Aug 15, 2006
K,
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never).
I have a form with several tabs, linked to a table with employee information, works like a charm.
Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings.
Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab.
I definately don't want any relationships with the employee table.
Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here.
Much appreciated,
View 6 Replies
View Related
Aug 26, 2014
My problem is displaying data in many-to-many relationships.
There are three main tables: tblShops, tblOwners and tblMarketingEvents.
These are linked through two junction tables creating two many-to-many relationships:
1) Each Shop may have more than one Owner and each Owner may have more than one Shop.
2) Each Marketing Event may reach more than one Owner and each Owner may be reached by more than one Event.
Now I want to display for each Marketing Event:
1) the data of that event, (tblMarketingEvents)
2) plus the owners reached in that event, (tblOwners)
3) plus all the shops owned by the owners reached in that event. (tblShops)
Preferably, I want to display more than one record at a time without repeating any information.
Is there a way to display the data like this?
I tried a query: it gives the right data but does not display it as I need. It repeats the data from tblMarketingEvents and tblOwners for each shop.
I tried a form with subform: it can only display one record at a time and is hard to work with (printing etc)
I tried a report with grouping levels based on the query: Again, right information, wrong display. I need to group according to the records of the MarketingEvents table and not just according to a single field.
View 3 Replies
View Related
Aug 5, 2013
I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.
tblLangInfo: LangID (PK), language name, language family, etc.
tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in
tblSegmentLangJoin: LangID (PK), SegmentID(PK)
I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:
tblProcesses: ProcessID (PK), Process name
tblProcessLangJoin: LangID (PK), ProcessID (PK)
Here's the fun part...
I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:
Process1_triggers
Process1_targets
Process2_triggers
...
This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.
The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.
E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.
I've attached a picture to illustrate the relationship I need, since that's likely clearer.
One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.
View 3 Replies
View Related
