Child/Master Problem
Feb 10, 2005
HI everyone, I have a Sales Invoice form with a Receipts subform showing what receipts have been lodged against that invoice. It works fine as long as there is one receipt or more.
The problem is when no receipts have been lodged and there are no receipts to show in the subform. When I open the Sales invoice form I get a "you entered an expression with no value" error, then the Sales invoice form opens. I tried suppressing the warning with DoCmd.SetWarning False, but that hasn't worked.
Anyone know a workaround?
View Replies
ADVERTISEMENT
May 3, 2005
Hi,
We all know we're not supposed to duplicate data right? So was wondering how people deal with the deletion of Master records.
For instance let's say I have 3 tables.
tblSales
tblSalesSub
tblProducts
tblSales - lists the sales summary - stuff like SaleId, SaleDate, SaleTotal
tblSalesSub - lists the products contained within the sale.
tblProducts - is the Master list of products storing the product specifics
Now to prevent duplication of data I should just store the productId in tblSalesSub then reference that to tblProducts with an innner JOIN of some kind.
But what if the Master record gets deleted? The reference is broken and NULL values are returned.
How do you deal with this?
Don't allow deletion of these records?But just hide them when deleted?
Duplicate data by storing the product title and description within tblSalesSub?
Tell the user - tough cheese you shouldn't have deleted it should you?
Any other ideas?
Dan
View 3 Replies
View Related
Apr 9, 2015
I am building a user creation form. I have built it with 3 subforms
Subform 1 is voor general information about the user.
Subform 2 is some more specific information about the user
Subform 3 is more info about the same user.
Now when I create a user (record) in subform 1 it gets the primary key lets say 14. Then when i add information in subform 2. I want it to grab the Primary key 14 from subform 1. So that the information which is inputted in subform 2 will be related to information from subform 1. I get it working if I make a Main form and then put subform 2 and 3 in a subform and then let them grab the primary key from the main form. But how does this work with 3 subforms and no mainform.
View 3 Replies
View Related
Aug 28, 2013
When trying to link parent to child in master and subform, I am getting a pop-up:
Subform Field Linker
"Object variable or With block variable not set"
View 3 Replies
View Related
Oct 14, 2013
How to structure some MS Access 2007 Forms for the following scenario.
My table structure is as follows:
Product
++++++
ID (pk)
ProductName
...
Tag
++++
ID (pk)
TagName
ProductTag
+++++++++
ProductID (fk)
TagID (fk)
I want to have a main form that allows me to search for and displays a list of all products (and the dozen or so attributes for each product) that meet that criteria (I'm thinking a continuous form view of products).
So far so good. No problems.
Now I want a "tagging" form to the right of my continuous forms product list that is linked to the product which currently has focus. The tag panel should always show a complete list of all Tags. I want a checkbox next to each tag that I can switch on and off whether that tag applies to the currently selected Product. I also want to be able to add tags on the fly (without leaving the main form).
I think I have three forms in play :
Main Form
Product Form (subform to Main) my product search criteria is probably on this form header.
Tag Form (also subform to Main)
But with this approach I'm have trouble linking the two sub forms to each other. Actually, I'm having huge trouble figuring out how to display the list of all tags with a check box that when checked on and off creates / deletes a record in the ProductTag table and I really want Access to do as much of the CRUD as possible. I would prefer to avoid writing screeds of SQL INSERTS/DELETES on events. Am I asking too much of Access?
View 1 Replies
View Related
Oct 7, 2013
I have a form which I am using child/master fields to create a filter (see attached pic)
For the date ranges I need these to be
>= Start Date and <= Actual Finish Date
How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.
View 14 Replies
View Related
Jun 17, 2015
I have a form, frmProjects, with a continuous subform, sfrPeopleOnThisProject. They have a master-child relationship. The form shows one Project record at a time, while the subform shows all the People records for that project. This works as expected - the correct records show up in the subform as you move through the form records.
To make it easier to glance through the list of subform records (as they can sometimes be quite long), I've added a navigation combobox to the header of the subform. If I understand correctly, as the combobox is within the subform, it should obey the master-child relationship: it should only show the subform records associated with the main form's current record. It does not. Instead, the combo box collects *all* of the records from the table that the subform is based on. So, choose the record for Project 1 in the main form. Up pops the 2 people on Project 1, but the combo box lists both the 2 people on Project 1 and the 3 people on Project 2.
The row source for the subform combo box is:
SELECT tblPersonOnProject.PersonOnProjectID, tblPerson.Fullname FROM tblPersonOnProject INNER JOIN tblPerson ON tblPersonOnProject.PersonFK=tblPerson.PersonID ORDER BY Fullname;
I can make the combo box perform as required by adding "WHERE Project=Forms!frmProject!ProjectID" to the row source and placing a hidden textbox on the main form for ProjectID (the autogenerated primary key) to sit. Have I misunderstood the point of the master-child relationship in the subform? This hidden textbox thing can't be correct.
View 2 Replies
View Related
Jun 13, 2005
Hello,
I have a form and a subform in MS Access 2003. I have made some changes to database structure, so I decided to change the subform also. When I changed the Link child and link master fields, the controls of the subdatasheet dissapear- they show only in design view. If I clear the contents of Link child and link master fields they appear again, but the records are not binded.
Is there a setting on the parent form that also has to be changed, to make the new binding?
Thanks,
Aleksander
View 1 Replies
View Related
Jul 1, 2013
I have 2 tables, master & child. with a one to many relationship.
On one of my forms I want to display some of the fields from the master table and only the last entry from my child table.
How would i accomplish this?
View 5 Replies
View Related
Dec 21, 2012
I have a master table with about 40 fields. In addition, I created eight child tables with read only datasheet views of limited fields from the master table. The child table views are based on certain critera in the master table and are being refreshed by using delete and append queries tied to the On Current property of a form. By double-clicking a specific child record, a form is opened to allow editing of the master record. Also, I am using Referential Integrity and Cascade Update Related Fields to update the child table when a change is made to the master. However, I do not know how to add a record to one or more child tables when a record is added to the master table. The new child record would have to be based on the same critera that the append queries use so it gets added to the correct child table or tables.
View 5 Replies
View Related
Aug 13, 2006
Hello All,
I am trying to make a small database that just help users to enter there data. I am using a switchboard here. User should click on his name (Captions)and the switchboard should close and automatically user name should appear in a text box in child form.
I hope I will get an amicable responce.
With Best Regards
Yqureshi80
View 1 Replies
View Related
Oct 12, 2006
Hi all
I seem to be having a problem with acess 2003 in that when ever I set the parent/child relationships and go back to the front end the simple disappear?
any thoughts would be grate
Thanks
James
View 1 Replies
View Related
Nov 9, 2006
Parents can have one or more children. I want to return the youngest child of those parents with more than one child.I have [QryParent&children] that returns all the parents and all the children. I have used this as a sub query in [QryParents>1child]. This uses "In (SELECT [ParentsId] FROM [QryParent&children] As Tmp GROUP BY [ParentsId] HAVING Count(*)>1 )" as the criteria in the ParentId. This returns all parents with more than one child and gives the name and DOB of the child.I now want to return just the youngest child for each parent.An ideas?
View 1 Replies
View Related
Feb 10, 2008
Dear all,
I have a table "Project Master Code", contains: Project code, Cost Code, Description, Budget Amount, Total Purchase, Left to Complete.
For each project, we enter all above field except Total Purchase. Total Purchase to be taken from Transaction table.
What I want is that, I want to creat a query based on Project Master Code table. I will call all field from Project Master Code and in Total Purchase column I want all Purchase from Transaction table.on based of Project Code & Cost Code.
thanks
mithani
View 14 Replies
View Related
Aug 4, 2005
Hi there,
Is there an event for a form object, which is triggered whenever any control in that form is changed/altered?
I have tried the suspect looking ones, but to no avail.
Thanks!
View 1 Replies
View Related
Nov 4, 2007
I have a simple Family table:
PersonID
Name
ParentID
where, in the case of a child, its ParentID contains the PersonID of its parent.
In the Relataionships window I put two such tables, Family and Family_1 and I joined Family.ParentID to Family_1.PersonID. I set referential integrity, cascading updates and cascade deletes.
I made a Parents form (by setting the ParentID criteria to Is Null), and added a Children subform (linking the ParentID of the subform to the PersonID of the main form).
The cascading updates works when I add a new child but when I try to delete a parent I get the error: Could not update; currently locked.
Why doesn't the cascade delete work?
View 3 Replies
View Related
Dec 24, 2005
I have a main form with 2 subforms. Subform 1 shows the results of query 1, subform 2 shows the results of query 2.
Query 2 uses query 1 as a starting point. Query 1 is updated with an option group on the main form. In other words, when I select an option on the main form, qdef for query 1 is updated and query 2 will also be affected.
If I select an option the subforms don’t update. If I close and reopen the main form, the subforms show the correct information so the queries are being updated but the subforms are not showing the updated information.
I’ve been trying to requery the subforms using the following code in the OptionGroup_Click() event:
Forms![MainForm].[Subform1].Requery
Forms![MainForm].[Subform1].Requery
By the way, I don’t have any parent/child relationships.
Am I missing something simple.
View 12 Replies
View Related
Jan 26, 2006
Hi,
I've got a parent/sub-form set up, both of which derive their data from linked tables connecting to a central database. As a result I've had to add coding to the NewRecord events to fetch the next primary keys from the database.
Unfortunately I'm finding that one loading the new record, the child form seems to be loading before the parent, ie the child form_Current event occurs before the parent form_Current event. Is this how it should be? It seems intuitive that the parent should load first.
I'm sure I can develop some cunning logic to get around this problem but I'm sure there must be a better way. Has anyone any experience of this parent/child set-up with linked, central database tables?
thanks!
Dave
View 5 Replies
View Related
Oct 21, 2006
I hv a relationship as follows:
Person 1 ------- N Assignment
Person
(PersonID, lastName, firstName...)
Assignment
(AssignmentID, PersonID, AssignmentDate, Score)
I would like to show the Score of the latest assignment in the Person Form, how can I do it?
Thx in advance!!
View 1 Replies
View Related
Jun 17, 2013
I am trying to open a child recordset but keep getting Error 424 "Object required".
Dim rstEmails As DAO.Recordset2, DBS As DAO.Database, rstFile As DAO.Recordset2
Set DBS = CurrentDb
Set rstEmails = DBS.OpenRecordset("Beldenemails")
Set rstFile = rstEmails.Fields("email").Value 'Error 424 here
End Sub
I've tried everything I can think of.
View 4 Replies
View Related
Dec 16, 2013
I have done this by linking the relationship between a person and a company (company-personID) with the table "alternates". In a subform for the information about the company itself, it shows the people associated with the company, as well as their alternates (assuming they're a director). It works just fine when I view a director's alternate, as John Tong here has his alternate as the person with relationship number 7. However, the whole thing is made useless when I view it from an alternate director's point of view, like Peter Chan, where he's displayed as the director, and the alternate is null. After poking around for a bit, it turns out that the child field is ALWAYS director.
What I want is for it to show the alternate or director based on who's being shown in the subform, i.e. Peter Chan is filled as alternate with the director being shown as "1". I've tried setting up expressions and putting up multiple master and child fields, but they just mess up Access.how I could display the names instead of the company-personID in the "alternates" subform,.
View 1 Replies
View Related
Jul 7, 2015
I have two tables in my access database with Parent Child relationship.Then i have below query based on which i have a datasheet form.
SELECT MOC_EMERGENCY.[MOC NUMBER], MOC_EMERGENCY.PERMANENT_MOC, MOC_DATA.TITLE
FROM MOC_DATA INNER JOIN MOC_EMERGENCY
ON MOC_DATA.[MOC NUMBER] = MOC_EMERGENCY.[MOC NUMBER]
When i delete a record in this form, my parent table record (in MOC_DATA table) is automatically deleted.
View 2 Replies
View Related
May 30, 2012
I have a Parent Record "Events" which has data already entered into it. I now want to assign workers to work the "event". The child table is "Assigned Officer". They have a one (event) to many(employees) relationship. Both the parent and the child have the key "Event ID". Of course Event ID is a subkey in the employee's table.
Every time I enter an employee it creates a new blank event.
My query looks like:
Code:
UPDATE Event INNER JOIN [Assigned Officer] ON Event.[Event ID] = [Assigned Officer].[Event ID] SET [Assigned Officer].[Name Assigned] = [Assigned Officer].[forms]![Assigned Officer Subform].[name assigned], [Assigned Officer].[Employee ID] = [Assigned Officer].[forms]![Assigned Officer Subform].[Employee ID];
View 7 Replies
View Related
Jul 14, 2005
I have created a master & replicas and sorted out synchronisation across a VPN and overall l am very happy with what l have achieved (lots of thanks to to help l have received todate from this forum that has made that posssible).
I now have a couple of questions which are niggles rather than full blown problems.
THe first is my master keeps reverting to a replica version of itself, l press the reinstate master and it keeps it as the master for a period but it keeps dropping the master status. Firstly any ideas why ? and secondly how do l stop it keep changing?
Next every now and again synchronisation slows a relicants version down to the point that it VERY slow to open (almost like it is on a VPN itself not a local copy) No amount of synchronation from their end fixes it, however it would appear that a synchronisation from my end solves the problem.
Agani the two questions - any idea why ?
and how do l fix it ?
Hope someone can help me
AndyB
View 1 Replies
View Related
Nov 27, 2006
I have a problem retrieving my Design Master. I regularly backup my DB onto a separate hard drive. And in doing so have lost my designing capabilities. I have tried replicating and retrieving the DM per the help section, but I receive numerous error messages telling me Error accessing file, network connection lost rollback error, etc. Any ideas would be greatly appreciated.
FT
View 1 Replies
View Related
Jun 14, 2005
There are total three tables tblInternal, tblExternal, tblCourses. Table
tblInternal and tblCourses is one to many relationship by reference foreign
key InternalID from tblCourses to tblInternal. If I just use these two table
to set up Master form and subform in ACCESS, you can in subform Field Linker
window of subform property Data tab select primary and foreign key to
generate link between Master and subform. So if you select user name from
tblInternal in Master form ComboBox, you can see the detailed information the
user relates to.
Same relation between tblExternal and tblCourses.
Now I want to merger tblInternal, tblExternal to list user name in ComboBox
by Union statement. But in SubForm Field Linked window I just see one primary
key from one of these two tables instead of two primary keys from each table.
How to solve this issue?
View 1 Replies
View Related
