Forms In OneToMany Relationship
Dec 13, 2005
I have 2 tables: TblCustomers (one) [field: MblNo; CustName] and TblDetails (many) [field: MblNo; TalkPlan; BillingAddress] with (1 isto many) relationship.
The TblCustomers has primary key [MblNo] that is foreign key in TblDetails.
They have 2 bound forms frmTblCustomers and frmTblDetails. Now frmTblDetails is inserted as subform in the frmTblCustomers.
I would like to keep on adding [MblNo; CustName] in TblCustomers and customer details in TblDetails (ie Talkplan, etc). Now, if I try to enter an already entered [MblNo] in TblCustomers it will not allow and generate error. But a customer may appear more than once in TblDetails (as he might keep on changing his TalkPlan or BillingAddress).
Any idea on how should the forms be built so that whenever I attempt to add an already entered MblNo in TblCustomers it will alert me that "the MblNo that you are attempting is Duplicate" and the focus of the control will be set to frmTblDetails so that I can contnue adding details in the [TalkPlan]; [BillingAddress].
Any help is appreciated.
Thanks.
Prodigy...
View Replies
ADVERTISEMENT
Nov 18, 2014
I have started a new project where I have tables of people, organization, and roles, as well as one that is supposed to contain the relationships among them. In all four tables there is an AutoNumber ID field as the primary key. The fourth table, the relationship table, has as foreign keys, the primary keys from the first three.
I have created a form with a combo box for each of the three main tables. The intent is for the user to select a person, an organization, and a role, and then click on a button control that will create the relationship in the relationship table. What I am trying to do in the code is, using an AfterUpdate event procedure for each of the combo boxes, open the appropriate table as a recordset, find the selected record, and set a form-local variable to the value of the ID field for that record. So far, I can't get past the first combo box. Here is the code I have written, including both the form variable declarations and the AfterUpdate code:
Option Compare Database
Dim personID As Integer
Dim orgID As Integer
Dim roleID As Integer
Dim rsPerson As Recordset
Dim rsOrg As Recordset
Dim rsRole As Recordset
Dim rsRel As Recordset
[code]...
The error message I get is Compile error: Object required, with the personID reference in the last statement highlighted. (qryPerson is a query based on tblPeople that combines first and last names into LNFN.)
View 1 Replies
View Related
May 5, 2014
how to best implement a form for this very simple database (attached). I have a database that keeps track of just first names and colors. Not every first name needs a color assigned to it, and vice versa.
So, I have three tables:
tbFirstnames
First_ID (PK)
First_Name
tbColors
Color_ID (PK)
Color
jctbFirstColor (Junction Table)
First_ID (FK)
Color_ID (FK)
I want my continuous form (fmFirstColor) to show all first name records and have a drop-down to assign a color for each record individually. That's it -- two fields. With this form, I want to be able to create new first name records with or without colors assigned to them, and I want to be able to edit first names and change what colors are assigned for each name.
Again, very simple and small database, but I'm not sure where to run with this. I want to keep everything normalized and within best practice procedures. Is the junction table necessary or can it even be used in this instance?
View 11 Replies
View Related
Mar 28, 2014
I have a Workorder Form that holds all the fields that apply to the entire project (payment, customer, etc).
Then I have a subform for Stock. This allows there to be several different stock items (and production information relevant to that stock) assigned to a Workorder.
I have successfully linked the two so that when a Workorder is opened, it shows any related Stock in the subform that has already been created.
The problem is new stock entries. I need it to only allow Stock additions to the current open related Workorder.
I currently have it where you can type in the WorkorderID (autonumber) on the Stock subform new entry and it will work. But I do not trust employees to type in the correct number every time they need to add stock to a Workorder. I can't release it this way.
I mean, the whole point is for the database to promote as little error as possible, right?
View 5 Replies
View Related
Apr 8, 2013
I am creating a database for a claims management company. They want a system where one customer can have multiple claims assigned to them. So as far as i know this means i need one table for the customer and all their details (tblCustomer), and another for the case (tblCase). I need tblCase and tblCustomer to have a many to one relationship....with referential integrity?
Ideally i would like to search to find a customer then have a very brief list of the claims they've had. with each claim being clickable for full info/ editing.
View 7 Replies
View Related
Mar 27, 2014
Is it possible to create a relationship between subform and a table?
View 1 Replies
View Related
Jun 20, 2013
I have 2 relationships between 3 tables,
Customer Table > Customer ID (pk) 1
Issues Table > Customer ID (FK) Many
Complaints Table > Customer ID (FK) Many
Each table has Customer ID as you can see, I have linked the Customer ID From Issues & Complaints table to the Customer ID field to the Customer Table using a 1 to Many relationship.
Am creating a form on the customers table so an admin can add a customer issue against the customer this is working fine, The trouble is when I link the Complaints table as well. its asking for subdatasheets.
View 1 Replies
View Related
Apr 3, 2013
I have a relationship between two tables; the parent table is "Client Information" with a primary key of "Client_ID", the child table is "Event Information" and the foreign key is "Client_ID".
I know that within the Event Information table I can select which Client_ID to use as the link. I want to have that done within a form, however. I have a form for Client Information with a subform for Event Information. If I go into the table and manually set the Client_ID then everything displays fine.
How can I set the Client_ID in the subform to automatically match the Client_ID in the main form? I can only assume that these solutions require the parent/child relationship to already have been established.
View 4 Replies
View Related
Dec 26, 2013
Is it possible to link One2one relationship database to one form?
View 4 Replies
View Related
Apr 3, 2014
I have a "Main" form called frmProjects that sports multiple tabs. One of those tabs is labelled "Milestones", into which I inserted a datasheet subform called frmSubMilestones. The table that feeds the subform is tblMilestones. I established the parent / child relationship between frmProject and frmSubMilestones, and everything is working just fine...
To summarize: frmProject as main form -> Milestones Tab on main form -> frmSubMilestones as datasheet subform on the Milestones Tab (there are other tabs that are not subforms).
I have been asked, for each milestone in the frmSubMilestones subform's datasheet, to capture the number of days spent by employees on a monthly basis. The Milestones table and the PersonDays table have a primary key and a foreign key that are similar.
To summarize: frmSubMilestones as subform -> txtAggregatePersonDays as calculated Textbox fetching data from tblPersonDays -> OnClick event -> FormLoad of frmPersonDays as datasheet PopUp form.
DoCmd.FormLoad is supplied with the usual parameters to make the PopUp form appear, filtered on the currently highlighted milestone. Everything works fine so far... well almost.
Problem 1: When I introduce new rows in the frmPersonsDays datasheet, everything seems fine at the surface, BUT the primary key of the calling form (i.e. frmSubMilestones) does not appear in the tblPersonDays table; this is normal because there is no form-based parent / child relationship between the two forms. As one might expect, I am trying, from frmPersonDays, to get the primary key value from the "parent" frmSubMilestones using the Forms collection. No dice. I surfed and surfed, tried the bang and the dot operators, drank scotch, but nothing worked. I had to add foreing keys manually in tblPersonDays to further my tests. I can't figure out how to reach any control on the calling form, which is a subform.
Problem 2: The calculated txtAggregatePersonDays on the frmSubMilestones works wonders for existing data in tblPersonDays. However, when I introduce new rows in the PopUp form, I also realized that the calculated Textbox in frmSubMilestones is not updated when I close the frmPersonDays PopUp. I have to close the PopUp form and move the cursor in the Main form (frmProjects) to refresh its associated milestones.
View 3 Replies
View Related
Jul 8, 2013
I've been tasked with creating an Access database..We have a main table that includes information found on every lease, however there are 3 leases types. Each which lead to another table or two. Basically, I want to create a form that allows me to link the main table with the corresponding variable tables.
View 1 Replies
View Related
Sep 14, 2014
I am creating an access database for recording calls and follow ups for a company
relevant tables
mst_customers
card_call
ID from customers and customer_id from card_call have a one to many with all records of card_call
Although its more elaborate than this, lets get down to the problem
I created a form with a subform that should basically allow selection of customer and based on this, the creation of records in card_call as per customer selected. form and subform are linked by customer_id
Everything is working except a peculiar issue where if i select a customer_id it shows all relevant records + an additional record (usually 1 or 2) that just changes the customer_id for that record.
Example
customer_ID = 2 (combobox bound to column1 and showing name/column2)
output
call_ID | Customer_ID
1 | 2
2 | 2
3| 2
changing customer_id to 4 will change to
1 | 4
(new) |
View 1 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
Jan 14, 2006
Hi All,
I am new to access and this forum, so hello all.
I am trying to build a database to keep records of my bird sightings. As you might have surmised; I am a birder.
I have come to a holt on creating a complex relationship between the Bird Entry From and the table that contains a list of British birds.
In Bird Entry From two of the fields are for entry of the bird name, one field being the Common Name and the other being the Latin Name. They are both drop down lists that contain all the birds from the table, this being done by import data. The table that holds the bird list has two fields One is called Common Name and the other is called Latin Name, this being the same as the filed names in question in the Bird Entry From
This is where I am coming unstuck. What I would like to happen is that when I enter a bird in the Common Name field (either by typing until the bird is auto entered from the drop down or using the drop down to find the bird) it automatically enters the Latin name in the Latin Name field and visa-versa, but I have become very frustrated with trying to do this.
Am I wasting my time? Or is it possible? And if so would anyone be kind enough to tell me how?
Many thanks,
Simon.
View 8 Replies
View Related
Feb 28, 2007
Hi i need help with one of my relationships
I need help with linking Member 1: N Reservations
the entites are
Video (Vcode, VTitle, Date Made, Director, Genre)
Copy (CopyNum, Vcode, Due Date)
Loan (LoanMemNum, CopyNum, Ldate, In, Out)
Member (MemNum, MName, MAddress)
Reservation (VCode, resMemNum, Resdate, CollectDate. Collected Not Collected)
The keys are underlined
Cheers
View 1 Replies
View Related
May 16, 2007
Hi there!
I've three tables in my database, tblInfo, tblSavedInfo & tblCountry.
for tblInfo I have this fields - Ref: Cost: RefCountry:
for tblSavedInfo I have this fields - Ref: Cost: Country:
for tblcountry I have this fields - RefCountry: Country:
My problem is when I save from tblinfo to tblSavedInfo I want it from RefCountry on tblInfo check wich country is on tblCountry and save on tblSavedRef the country instead the refCountry.
As tblInfo has about 200.000 rows I need something to make it quick. Is there anyone that can help me please.
Thanks
View 2 Replies
View Related
Jul 25, 2005
Any advice? I've tried some things and no success with linkage
Rooms Table
RoomID
Room No
Type
Desc
Project Table
Project ID
Project
Desc
Faculty Assignment
Project Grant No
Sponsor
Project End Date
Student Table
Student ID
Students
Many rooms with many projects, many of those projects in many of those room, with many students working on those projects. Many rooms, many projects, many students. Argh...
I've already tried two junctions between rooms and projects // and projects and rooms
What is making this so difficult?
View 3 Replies
View Related
Aug 14, 2005
As you all know Im not a expert on databases but work in a volunteer basis in a small community area where we have no money for people to do anything for us.
Im proud because I did the other database and its up and working but this one has got me beat.
Im setting up one for the volunteers, which has computer knowledge, any basic training they need, when they are available and what tasks have been set for them.
I need to have a one form which has just their personal details but to be able to put in data on the other forms and it will link back to the volunteers name.
Ive attached what Ive done and Im sure your all going to laugh - but any help would be appreciated
Barnesy
View 5 Replies
View Related
Aug 19, 2005
Trying to set up a fairly simple DB. Here is the layout:
tblRoom(roomID, roomNumber, rackOne, rackTwo, rackThree)
tblStudent(studentID, name)
Each student will be assigned to one, and only one, rack. There will be 3 students to one room. One student can only have one room but a room can have many students... I store studentID in the rackOne, Two and Three fields. My problem is when I try and querry for the information so as to display actual names rather than ID's. I'm not sure If my querry is the problem or if I've set the tables up incorrectly. Any insight is much appreciated.
View 2 Replies
View Related
Aug 30, 2005
Hello,
I'm having some problems setting this up right.
I have 3 databases.
Volunteers
FIRST
LAST NAME both primary
Movie Data
MOVIE
DATEboth primary
Attendance
MOVIE
DATE
USHER 1 First
Usher 1 LAST
View 1 Replies
View Related
Aug 30, 2005
Hello,
I'm having some problems setting this up right.
I have 3 databases.
Volunteers
FIRST
LAST NAME both primary
Movie Data
MOVIE
DATEboth primary
Attendance
MOVIE
DATE
USHER 1 First
Usher 1 LAST
USHER 2 FIRST
USHER 2 LAST
AUTOKEY PRIMARY
I have a one to many relationship between the movie and date between the attendance and movie data table. I have tried linking the name to the usher fields but I"m not getting the result I want. I want to be able to open up the volunteer table and have it show me the movies they have worked on. The movie data table will list who worked it, but the volunteer tables are not.
Any ideas?
Thanks,
Cody
View 11 Replies
View Related
Sep 11, 2005
I have to tbls which have indeterminate relationships and I'm told that ther is no unique index found why is this?
i am using emplyID on both tblCommission and tblSales both are number type.
I might not need the commission table as I'm using this is calculate commission as said im my previous post
View 1 Replies
View Related
Nov 4, 2005
at one point in my database I have two tables and for everyone record in one table I may have either 1 or 0 records in the other. How do I express this in access design? Since it's an optional 1 to 1 relationship I guess?
View 1 Replies
View Related
Mar 3, 2006
I was wondering if anyone could help me with a query. Basically I am doing (or trying to do) a small database to track childrens progress in my mums primary school. I'm doing this for free as the budget is rubbish for this type of thing and i dont have enough knowledge to ask for payment anyway! I want to get it right from the start and I think if I get the relationships right initially then I can complete the rest of it on my own. I did this at university (normalisation etc) but ive not used it since i left in 2002 and so have practically forgotten everything i ever did.
Basically, the child comes into the school and is predicted a level in maths, reading and writing. Then in the october, feb and july of each of the 2 years they are there, they are given actual assessment levels. They are also given a prediction level at the beginning of year 2.
So far I have one table with student no, surname, first name, gender, ethnicity, year group and SEN(special educational needs) - with the primary key being student no.
This is where I get stuck - do I go for a maths, reading and writing tables and split it that way - or on an assessment basis, so prediction yr1, october yr1, feb yr 1 etc etc. Or is this completely wrong? Am sooooo confused, so any help would be greatly appreciated.
The outcome I want is to be able to query a childs progress, so for example: show children who achieved a 1a in yr1 maths, and then out of those children, who achieved a 2a in y2 maths (as this is the required shift in progress set by government). I really hope that makes sense.
Thanks in advance
Nicola
View 3 Replies
View Related
May 10, 2006
I'm rebuilding my database from scratch now that I've learned a bit from the forums about developing a proper database and I want to investigate whether I have the relationships set up correctly, as I seem to be using lot of one-to-one relationship, which most articles say is very rare, and whether I am formulating many to many relationship correctly. Here is a screenshot of my relationship.
If I've understood this correctly, one-on-one is desired if you have a subset of data that is applicable to the main table only occasionally and is dependent on the main table's data. Have I used that correctly?
As for many to many relationship, articles say this is formed by having two one to many relationship linked together in a table. In my screenshot, you can see that tblAdvocacyVisit is the linking table. However, I've set GuestID as a one-one relationship whereas ClientID is one to many relationship, reasoning that since I do need to relate the Guestl Logbook data to Advocacy table for some queries I will be performing later, this will save me some work. I'm afraid that this may compromise RI or something like that. I can simply add a PK autonumber to tblAdvocacy to revert the GuestID as a one-many relationship, but can't see how that would not create more work in queries or so...
Oh, are the lookup tables supposed to be related at all?
TIA.
Edit- Updated the relationship screenshot.
View 14 Replies
View Related
Jul 27, 2006
I’m building a database of protein mutants we use in the lab. A lot of analysis is done on these proteins by various people and it’s becoming a bit of a nightmare keeping track of all the information per protein. What we need is a database. I’m getting a reasonable grip on it all I think with the excellent examples I have found on the forum but there are obviously areas that I have great difficulty with. The main problem for now is that many-to-many relationships are involved and I just cannot get them to work properly.
On each protein up to 8 different types of test are performed. To keep things easy I’ll call them A1 (for Analysis1), A2, A3 etc. These are done by different people from a group of 7. I’ve called them O1 (Operator1), O2, O3 etc. Each of the operators can do multiple analysis and the operators overlap. So e.g. O1 might be particularly good at A1, A4 and A7, O2 might be good at A2, A4 and A5, and O3 predominantly does A1, A4 and A6. What it comes down to is that each operator can in principle do each of the analysis so I have to give that option in the database.
What does work (but is not the solution as it introduces 8x duplication of data) is if I have a separate table with operators for each analysis. The same tblOperators is linked 8 times. There must be a way to link each of the analyis to only 1 tblOperators, probably with the use of a linking but I just cannot work it out from the examples I've found here.
Can someone help me on my way please?
Thanks,
Walter
View 7 Replies
View Related
