One To One Tables In Subforms
Jun 28, 2006
hello,
we collect around 200 pieces of data for each client in our database. the data has been split up into five tables which are in a one to one relationship with eachother.
my main form has a combo box which selects the client to work with, which changes the current record in my main client table. i have a tab control with five subforms for entering data. these subforms have their navigation buttons turned off, so that the user can only browse records using the navigation buttons of the main form. so i am not using the main form for any data entry, just navigation.
i'm having a weird problem that crops up sometimes while trying to add records. i will try to enter data in the first field in my first subform, and i get the error that i've tried to assign a null value into a variable that isn't variant. sometimes it adds the record, sometimes it doesn't. i will look into that a little more closely.
now my main form has a combo box record selector. i think my problem might be when i enter data into the subform, it makes the new record and then it's not able to link back up with the combo box record selector in my main form because it doesn't exist in the main form yet. how do i get the thing to refresh once a new field has been entered via the subform?
i will also point out that the main form gets its data from my main client table, and so does my first tabbed subform.
does anyone see what my problem is?
thanks!
harry
View Replies
ADVERTISEMENT
May 7, 2008
Hello.
I'm upsizing an Access 2000 application to Access 2003 + SQL Server 2005, and I've found something I cannot get through.
The original application was a file 'app.mdb' with all tables, and another file 'app_exec.mdb' with all forms (tables in 'app_exec.mdb' were linked to 'app.mdb').
There was a table called T_Order (I will call it A) and another table called T_Order_Line (I will call it B).
* Table A fields: Id (Primary Key) and Customer_Id
* Table B fields: Id, Order_Id (Both are Primary Key) and Article_Id.
There was one form (called frm_A) that includes a subform (called frm_B). The form frm_A allows navigation, adding, deleting and updating 'A' table. The subform frm_B shows data related to current 'A' record.
The subform frm_B allows you to add/delete/update records to the table 'B'.
Now I only have one file 'app.mdb' with forms and tables are linked to a SQL Server 2005 database (I use the Access export wizard).
frm_A and frm_B are related using "Link master field" and "Link child field".
The problem is that I cannot add new data to the frm_B. Navigation in the form works. In the subform frm_B I see data related to the current record. I can delete/edit data from frm_B. But I cannot insert new data on it.
When I click on "Add New Order" button, I execute the code "DoCmd.GoToRecord , , acNewRec". In the old application, frm_B was cleared and ready for appending new data. But with linked tables to SQL Server, I cannot add new data!
I have checked that if table 'B' is stored locally in Access, everything works fine. But if I use table 'B' linked in the SQL Server, I cannot add data.
So, I am sure that the problem is only with table 'B' and the subform, but I cannot understand what happens.
Any idea?
Thanks !!!!
View 2 Replies
View Related
Dec 16, 2007
Hi Guys & Gals, I have an Access 2007 database that I am fighting with. I have a friend who needs a data entry database. So far I have created 4 tables: Cases, Customers, Data1, Data2.
Cases_tbl has following fields:ID(autonumber), Customers_ID(number), Data1_ID(number), Data2_ID(number).
The Customers, Data1 and Data2 Tables are all similiar, they all have the ID(autonumber) Field followed be fields that need data inputed.
I would like to have the Cases table as the master table that is linked to the other 3 tables, so when I delete a customer, there case, and all of there data is deleted with it.
I have one form with 3 subforms. Each subform is used for inputing data into the customer, data1, and data2 tables. However, I cannot for the life of me link all these tables together. I have tried all different relationships and have search the web high and low and I cannot find an answer anywhere. So if somebody could help I would be much appreciative.
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
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
Apr 14, 2005
Hi
Is anyone aware of a method to have two subforms on a form and then set the sub forms up like this:
Form1 is the main form
Subform1 is dependent upon the Key field in Form1 (can get this far easy)
Subform2 is dependent upon the Key field in Form1 + the Key value in Subform1?
In my example I have Attendees (main)
who can belong to a long list of Agencies (and Agencies make up a table on this form with a whole bunch of fields)
then for each Attendee, depending upong which Agency they belong to, I need to list a table of data about the Sessions they are attending.
It may seem trivial, but I have been racking my brains and bandwidth trying to work it out.
Thanks.
View 1 Replies
View Related
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
Jun 5, 2005
I'm looking for if possible a simple solution for my cascading combo boxes that do not work in a subform.
I have provided a link to my DB and the form i'm having issues with is frm_Projects any suggestions woul dbe great i know that this subeject has been looked at several times but i still seem to not figure it out.
Database (http://www.cyruspostings.com/hold/db.zip)
View 3 Replies
View Related
