Multiple Tables, One Form With 3 Subforms Problems
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.
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?
I have a tabbed form. The main form is titles ContractDtlsFRM. There are 3 other subforms in separate tabs. The first field in the ContractDtlsFRM is Contract No. I would like the form to sort in ascending order by this number.
I tried entering the following code in the Forms Order By event but it didn't work
Private Sub Form_Open(Cancel As Integer) Me.OrderByOn = True Me.OrderBy = [Contract No] End Sub
I have a main form with multpile tabs - each tab containing a different subform.
Link Master Field: ClientID (field in Master Form) Link Child Field: Client ID (field in all subforms)
There is another field that all the subforms (continuous type) have: ObligorName. All the subforms are based off of a huge table (subtable) with fields: Obligor Name, Address, Zipcode, City, DOcuments Required, Bank Account Number, etc......
Essentially, the user will fill in the 10 obligor names associated to ONE client on the first subform on the first tab + address + zip code + city.
When the user clicks on the second tab to fill out the next subform, I want all 10 obligor names to be there already, so then they can fill out Documents Required + BAnk Account Number.
If I fill out the first subform and then exit out of the form and then reopen it, the other subforms autofill.
HOWEVER, I dont want to have the user have to do this. It wastes a lot of time. What can I do about this so that the table is automatically updated right away. They are all based on the same table so I do not see why there is so many issues.
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.
I'm new to programing so if this is not possible please tell me! Because this is going to be hard to explain I attached an example.
I have a Main form that a user will open, this is the only window that will ever pop up in front of them so it has a subform that will open differnt froms by picking buttons on the left.
One of the sub forms is the Member Information form, this form will show member information than at the bottom has another subform that will allow a user to pick a button above it to open another subform (this is so member info is always show and a member can be registered, pay...all different subforms)
on the Event Register subform the "Class" pulldown is based on the event that was pick just above it.
Issue: when I am in the main form, I can not get the class pulldown to work it always prompts me for "enter Parameter Value". I have tried the following SQL statements in the row source and noting work.. what am I missing???
SELECT SubCatList.SubCatName, SubCatList.EventName FROM SubCatList WHERE (((SubCatList.EventName)=[Forms].[MainFRM]![MemberInfoFRM].Form]![EventRegFRM].[Form]![EventName]));
and
SELECT SubCatList.SubCatName, SubCatList.EventName FROM SubCatList WHERE (((SubCatList.EventName)=[Forms]![MainFRM]![MainSubFRM]![Form]![memberinfoFRM]![MemberSubFRM].[Form].[EventName]));
I am trying to add multiple subforms (or subtables?) on an existing form. The underlying table is a very detailed descriptive table for art in a collection, I am already using one subtable to manage the movement in and out of inventory. When I look at the table in the datasheet view, I see the "+" sign next to each record, and when I click on that, I see the subtable I already have setup - looking at that, it seems impossible to have more than one subtable... is this true? This does not seem right.
My question is this: Can I have more than one subform in a given form? What I would like to have is a subform to keep track of provenance for any given piece (history of ownership), and another to track appraisals (values, dates, appraiser, etc) of any given piece in the collection.
how to add times together from multiple subforms and display the data on the main form. Just for backround, I am creating a timesheet to be used by our workshop employees. The form is set up in such a way that there is a combo box where they can select their name which rotates the record so all the data is specific to their id, and then there is a tab box with a tab for each day of the week each containing its own subform with combo boxes to select the project they worked on, text boxes to enter the start time and stop time for that project, and then 2 other text boxes: one that calculates time worked on that particular project and then one that calculates the total time worked for the day since they typically work on more than one project in a day. So what I need to do is to take the values from the total daily hours boxes and add them all together and show the sum in a box showing the total hours worked for the week.
My most recent attempt was to simply make an expression in the control source as follows:
I'm v.new to Access so do excuse my ignorance. I have a form with a combo-box with the following values: Trust, Course, Overseas, GP When Trusts is selected I'ld like frmtrust_sub to appear. When anything else is selected I'ld like frmnontrust_sub to appear Once the data has been entered in the subform the user would continue back to the main form. I'ld like the subforms to remain hidden until needed, if possible so as not to confuse users.
I’ve copied this piece of code and have tried pasting it into the “after update” event procedure but I get this error message
“the link masterfields property section has produced this error “Ambiguous name detected: Type_afterupdate”
Private Sub Type_AfterUpdate()
Option Compare Database
Sub ShowSubform()
'Save unsaved changes to currently open subform DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Display appropriate subform based on Type chosen If Type = "Trust" Then frmtrust_sub.Visible = True frmnontrust_sub.Visible = False
ElseIf Type = "GP" Then frmnontrust_sub.Visible = True frmtrust_sub.Visible = False
ElseIf Type = "Course" Then frmnontrust_sub.Visible = True frmtrust_sub.Visible = False
ElseIf Type = "Overseas" Then frmnontrust_sub.Visible = True frmtrust_sub.Visible = False
ElseIf Type = "Other" Then frmnontrust_sub.Visible = True frmtrust_sub.Visible = False
End If
End Sub
Private Sub cmdClose_Click()
'Close form DoCmd.Close
End Sub
Private Sub Form_Current()
'Call subroutine to display appropriate subform based on template type ShowSubform
End Sub
Private Sub Type_AfterUpdate()
'Call subroutine to display appropriate subform based on template type ShowSubform
Combo boxes cboTN and cboSB are on my main form. cboTN is a search combo box that updates combo box cboSB with one of 3 values (ID, IDC, or MC).
I would like to do the following:
1: If the after-update value of cboSB is ID, subform sf1 will be visible. If the after-update value of cboSB is IDC, subform sf2 will be visible. If the after-update value of cboSB is MC, subform sf3 will be visible. (only one subform visible at a time since they are stacked in one place on my main form)
2: Once the appropriate subform is displayed, is it possible to use option buttons to set the property of the subform to either Add-only mode or Edit-only mode? If so, how? (perhaps some sort of Case A, Case B scenario with the option buttons?)
If you can help with either of the above; example codes would help my novice skills greatly!
I have a form for timesheet entry that has 2 subforms.
The main form has a combo box for selecting staff name and another for WkEnding date. The form is based on table "SELECT StaffRef, WkEnding, Status FROM tTimesheets".
SubForm sfTimesheets is based on table tTimesheets. The subform is linked by StaffRef and WkEnding. (This has been working for ages.)
Recently added second subform sfOnCall based on table tOnCall. The subform is also linked by StaffRef and WkEnding.
If the user has a timesheet entry for the date, then sfOnCall displays and operates ok. But if there is no entry in the timesheet table, the sfOnCall form doesn't show the appropriate entry from the OnCall table. - I did try basing the main form on a UNION of the 2 tables, but whilst the query showed the correct data when I ran it - all the controls on the form disappeared!
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
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.
I have 6 tables that have 1 to many relationships. I would like to have all 6 tables on 1 form for data entry. When I use the “Form wizard” and select the tables it wants to group the last 3 tables in 1 subform and it won’t let me add records to the last 2 tables. Same thing happens when I create a query and run the form wizard selecting the query.
I know I’m doing something wrong but I can’t see it……….
Thanks Paul
Here's an add on question:
When the form wizard adds a subform to a subform the link child and master are filled out and the form updates. But when I add another subform and set the links it will not update. Example: form Book has text fiels and 2 sub forms "Chapter" and "Section" subform section child link is ChapterID, master link is [Chapter Subform].Form![ChapterID] --- This updates as expected.
I create a new subform "para" based on a table and set the Child link as SectID, master link [SectionID].Form![Section Subform].Form![SectionID] --- Will not update the subform "para" when the next record in subform "section" is selected.
I think I have created the relationship fairly well. Most of them one-to-many.
I created the form but now I whenever person is making entry I want the information from one form to go into multiples tables. What's the best to go about this?
I have tried couple different ways, Made a query from the tables and then using that as Record Source for the form as well. It doesn't seem to work properly.
I am trying to take two tables (one is customers and the other suppliers) and turn it into a form to pull up the data from the tables. I need to be able to enter new data. This is for a corrective action form so after pulling up the customer and supplier info, I need to input the problems and corrective action. This information will then be turned into a report that I can print out.
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?
I am currently designing a database to solve our organisation's training requirements. The db needs to be able to identify an individual member of staff at any particular site and list all the medical devices that they have training on and to what level of competency. So far I have five separate tables:
Staff details Site Devices Competency/Risk Equipment
By using unique identifiers within each table, we can, in theory, comply with the requirements.
Now here is the problem. Having talked it through with a colleague, we have two possible options.
A. Using a form based on the staff member, combine the salient details and, where needed, create additional fields (such as dates and tick boxes) to record odd items (this is my preference)
or
B. Based on the site, create a make-table query and form a table, sorted by site and then staff, listing all the equipment and competency levels. Given that some staff will work at multiple sites, the table will probably contain over 6500 records with lots of duplication (you can see my bias!)
Which solution is probably the best or should we be looking at solution that combines elements of both?
Hi, I am designing a database for a basic payroll function. So far everything was going well until i started to design forms. I cannot delete multiple table entries from one table that connects two tables. This situation is as such:Employee Table: contains entries as follows:ID: Autonumber (PK)First Name Last Name PayCategory (Salary or Hourly) (List Box)PayInfo Table: contains entries as follows:ID: Autonumber (PK)Employee Name (Text)Pay RateThe relationship of these 2 tables is a one to many relationship; where the ID from Employee Table is connecting to Employee Name in PayInfo Table.When i created a form using these 2 tables and added a delete button to it, the entries from Payinfo table get deleted when i click the delete button, but not from the employee table.I have tried everything i can think of :( . What am i doing wrong? Can someone please guide me in the right direction. Any help would be greatly appreciated.
I've got a form called 'frmStudent' which is linked to 'tblStudent'. I have designed it as a tabbed form, the first tab has "Student name", "Student Number", etc. The second tab is linked to visits by the student to a mentor (such as a tutor etc). Here I'd like to have "Date of visit", "Mentor Name", etc.
So far I've been able to get the first tab to work (I'm guessing because it's linked to 'tblStudent'), but can't get my 2nd tab to work as I have no clue on how to link it with 'tblVisits'. Is there a VBA methor or perhaps a nother method to do this?
I'm attaching a link to the sample of what I'm doing (please shift-open into frmStudent to view what I'm talking about). Any ideas and suggestions would be greatly appreciated!
I have 2 tables (tblSET, tblPROJECT) that have a relationship between them in the form of strPROJID (Project ID)
I need to make a form that has all empty fields in it, and when filled in distributes the entered data to the corresponding field in the corresponding table. I have tried using the design wizard and just putting the fields from all tables onto one form, but the "new record" button is greyed out when I do that. I figure this will include coding of some sort but I am far from a coder.
I have 2 tables, Test Cases, Test Case Details. I have a tabbed form to display the data. How do I open the form and retreive the data from both tables? Tables are related by ID.
I have a combo box with a list of names in, and a table for each of those people.
When one of these names is selected, i want the contents of the corresponding table to display in a form. Easy Enough.
But...how do I get these tables displaying in one form depending on which name is selected, to save me having 7 copies of the same form just with different sources. All the field names are the same.
One solution I thought of is to put all the data in one table, but would really rather keep it separate.
I currently have 1 form linked to 1 table which data from another table is appended to for editing, re-appended to original table and then rows are deleted. However, this process will need to be done by 4 other users soon and I needed to know if I need to create 4 separate tables and 4 separate forms or is there another way to implement this? The append query has clauses that will display only a specific user data for editing, however, I do believe that if 1 user is in the editing table, then when 1 of the other 4 users attempt to run the query to append their data from the original table, they will get an error message that this table is already in use. I just need to know if there is a way to set up an alternative to re-creating 4 tables/4 forms again.
