I want to create a form that has several parts(5), that will direct to 5 different tables(they will all be connected). I want to keep it as one form because if I'm successful it will be easier for people to transition from a paper to electronic form(if the format is similar). I don't want any subforms, I just want to be able to direct the information to different tables.
View RepliesI have a form that shows information on a person (think first name and last name) at the top and contains tab controls that hold subforms each containing a different set of info about that particular person. Ex: for person 1 the main form will display the persons first and last name as well as show the subject tabs. as you click on each tab you should see the information that pertains to that person. I would like to step through each ppeson one at a time using the horizontal record arrows (eventually I will change this process to using forward and back buttons) on the main form with the persons names changing as well as the information in the subforms changing to match the person on the main form. I can step through each separately (main form and each subform) but I cant get the subforms to change information when the main form changes persons. Seems simple enough but I cant get the subform to change when the main form changes. They are not connected. This is for an Access class that I am taking and we use Access 2013.
View 2 Replies View RelatedI have a form based on tblContact with a subform based on table tblCategories, and another subform based on tblCompany. I have a one-to-many relationship between tblCategories and tblCompany, with the pk in tblCategory linking with a fk in tblCompany. All of the fields in tblCategories/ sbfrm Categories are yes/no fields, not sure if that plays into my problem at all.
When a user checks one or more of the check boxes in the categories subform, closes the form and re-opens it, none of the check boxes are checked for that person. I know this has something to do with the relationships and how the categories are linked to the contact... but I can't get it straight in my mind as to how that should be set up.
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.
View 4 Replies View RelatedHey Access people...
Does anyone know if it is better as a general rule (server performance wise anyway) to connect to a single database with many tables or multiple databases with fewer tables??
In this case I have a small calendar database and large navigation/metadata database -- both are MS Access & connected to the same ASP page.
Can anybody make a recommendation here??
Many Thanks!!
I am trying to create an input form for input to tables.There is a main TblContacts table with fields such as: LastName, FirstName, Address, etc. There are also ID fields for the Company and Country and joins to the Company and Country tables, each with just ID and name fields.I want to have all the fields from the TblContacts table and the Name fields from the Company and Country tables on the form. I want to be able to select the Name fields from the Company and Country tables from combo boxes and for these also to populate the Company and Country ID fields (from the TblContacts table).
To create the form, I selected Create Form whilst the TblContacts was open. Then, I added the combo boxes pulling the names from the Company and Country tables.My problem is that the selection of the Company and Country names above is NOT populating the Company and Country ID fields on the form (taken from the TblContacts table. I have checked in relationships that the joins have been created.
I have data that needs to be entered. It is the same data across 3 tables but only for the first three columns, then it is different. I can create a form for one of these but not for all three.
View 4 Replies View RelatedI am currently working on a form that involves several linked tables. Since our personnel got so used to a certain form format, I have scanned the form and imported into an access form as a background and have laid corresponding fields on top.(I have attached a snapshot of the form). This works great for a view only form, but I can't seem to add/edit records using this form because of the table relationship. I have done some research, and found that one way to get around this problem is to use subforms, but that would kill the form format. Another way I have seen (which I am not sure how to do) is to have a temporary field and once you hit the submit button it will then fill out the tables.
View 1 Replies View RelatedI am fairly new to Access and am having some trouble creating a search form. I have numerous tables, which contain information regarding clients and contracts, that are all connected by relationships. I would like to create a form in which I can type in the name of the client and pull up all the information regarding it from the various tables (i.e. when it was signed, the amount, and etc).
View 2 Replies View Relatedproblem is that i have 2 tables - Consolidated Orders, Customers.
Customers has 2 fields with a record inside one field with the clients name , but it also has an address in another field in the same table which i want to link to the name of the client because in my invoice i show the companies name and to the left of it i show their address.
i wish to link the two fields together so that when the name shows up, the address of the client will be to the left side .
I'm having a problem in Access where I'm trying to connect 2 tables together.
On one table is all the information of the person, the other table is a list from 1-50. That list is a drawing of all the peoples ID number for a drawing. When I type in their ID next to what order they got picked in is there any way where All of their information comes with that ID number they have? I really need help on this.
So, here's my problem: I'm doing a database on my DVDs, and I wanted to add as much info as I can about them. But I have some problems with "actors", as you know one actor can be in several movies and one movie has several actors.
I want to put the actors in their own table and movies in their own. I know how to link them together, but I don't know how to link them together so that actors could be in more than one movie and movie could have more than one actor.
Hope that even someone understands my question since my english ain't so good..
I work in an office where we do testing with clients . I want to create a database that can create a unique report for each client on the testing results.I envision a database with at least three tables. The first table would be client demographic information with a unique ID field (CID). The second table would be the Appointment information (date, referral source, etc.). The third table will have the test results (although I'm wondering if I should have a table for each test).
Sometimes, we see clients more than once, and so need the ability to have more than one appointment record for each client. For each appointment, we would record test results.I have created a one-to-many relationship between the Client Demographics and the Appointment tables. When I created the form for the Client Demographics, I inserted a subform for the Appointment. That works great. The CID automatically transfers to the Appointment record and instantly connects the two.How do I connect the Test Results table so that the CID automatically transfers to the Test Results record as well as the Appointment Date field from the Appointment Table.
I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:
Adult Banding Records
Nestling Banding Records
Historical Banding Records
The specific issue:
I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.
I'm working to create a staffing database that houses changes to staffing week over week.
I have one primary table, the "empMaster" table, that stores the employee's name, contact information, etc. I have other individual tables for noting which employee reports to which manager, what their business title is, what group they're in, their training history, etc.
Once I've populated the empMaster table with employee information, I want a form that allows me to update each of the other tables IF there as a change. Some weeks will have a manager change, some only a business title change, some a group change, some a training change, some all of the above. The problem I run into is that I will sometimes process hundreds of changes a week, sometimes only 10-20 so I almost have to use datasheet view for mass edits. I'm relatively new to Access and I'm having a hard time getting my form to allow this level of flexibility and to update all fields needed.
When I build a form that includes more than two tables (let's say I want to update a Manager and a Business Title), the form will not work and populates nothing. I believe it's because Access wants there to be an existing record to match to across all three tables and there will not always be.
How do you create a form that is not tied to one single record source? In other words, I want to be able to select the record source that it updates. I have a bunch of tables that have the same data structure but are separated due to geographical nature among other reasons. Is there a way to do this?
View 10 Replies View Relatedis there a possibility to connect two different databases??? I mean is, I have a table called PERSON. under PERSON, there are fields called PERSON NAME, BIRTHDAY and ADDRESS. another table is called SEMINARS, under it are DATES FROM, DATES TO and TITLE.
I want to combine one of the records in PERSON to the 5 records in the SEMINARS. is there a possibility to do that???
to include in the information, there are 10 records in the PERSON and there are 75 records in the SEMINARS. and I want to add more records in SEMINARS in any of the records in PERSON in the near future.
I currently have a pharmaceutical lot database set up in the following format:
MFGData (table w/Manufacturing Info)
QAData (table w/ Quality Assurance Info)
QCData (table w/ Quality Control Info)
PASData (table w/ Process & Analytical Science Info)
SCData (table w/ Supply Chain Info)
[Code] ....
Each table has a corresponding form for data entry in each area. The tables were subdivided in this way in order to limit each department's ability to edit the data of other departments. The only field common to each table is the drug lot number, or "Lot #" (which is the primary key of each table).
I wanted to make it so that when Manufacturing enters a new lot number on frmMFGData, it automatically creates that lot number in the other 4 tables. This process mirrors our actual real world business process, where drugs are manufactured and assigned new lot numbers by our manufacturing team, and then other departments simply reference those numbers when doing their part.
To accomplish this, I went ahead and set up 1 to 1 relationships between the various tables using their "Lot #" fields, establishing referential integrity and enabling cascading updates. However, when I attempted to enter a new lot number into frmMFGData (the manufacturing form), it didn't seem to appear in any of the other tables. If I edit an existing lot number and change it to something else, the change does carry over to the other tables, so I know that the cascading updates are working in some capacity.
If cascading updates cannot "cascade" new records, then is there any other way to accomplish this?
My database allows you to log issues (see attached)
An issue will belong to the project, the project may have multiple test plans.
The issue will also be the primary responsibility of a company, and that company may have multiple contacts (people)
Now, i could create a form over the top of each table but then if someone wanted to add a new project, testplans for that project, suppliers (companies) for that project AND new contacts for those companies, they are going to end up going through loads, and loads of forms.
So my question is, what approach would you recommend to create a control panel where they could add these items with the fewest number of page switches possible? (preferably none.
In addition to my Intro to Access class, I am taking a Web Programming class, which has the same general assignment, but for Web pages, instead of an Access database; create at least 3 pages, 1 each for financial data, customer relations and product/service info.
I would like to create a small Access database connected to a Web page. I won't have a server, but I can run everything off a computer. I have a book that tells me the steps to do all this, but I'm supposed to install MS SQL Server (I chose to download MS SQL Express Server), and I can't get Access to talk to it. Among other things, I noticed that I could not stop SQL Server, nor could I start SQL Browser. I did enable TCP/IP (I think). I uninstalled the whole thing, thinking I could start from scratch.
How to publish my database on a Web page.
I am building a form to create a user record and at the same time i have some yes/no options which are located in other tables but when i want add a user i cannot select any yes/no options they seem locked?
View 1 Replies View RelatedI had an existing database with 2329 records entered into it. All of the fields (220ish) were all in one table. Myself and my co-workers wanted to rebuild the database without losing the data. We wrote queries to transfer the data from the original database to the new database and split the data from the original 1 giant table to 9 smaller tables.
The transfer of data worked so I went to start making forms. When I went to add fields from different tables I had to built a relationship, which I did. All of the data that transferred over from the new database is in the form (now multiple forms linked by button) but I can't add new information. I get an error stating, "You cannot add or change a record because a related record is required in table ..." and the table referenced keeps changing.
I have a form "frm_PatientNew" based on table "tbl_patients", this form contains a button "cmd_NewVisit" which is supposed to do the following: opens the form "frm_NewVisit" for recording a new visit for the last recorded patient in "tbl_Patients", I found many approaches depending on DMax and Dlookup and they worked fine just if "frm_NewVisit" is bound to "tbl_Patients", but "frm_NewVisit" is bound to "tbl_Main" which acts as a container for all information (patient data, visit data,service done and service provider), so the form "frm_NewVisit" contains fields from different tables. I wonder if I should create "frm_NewVisit" as unbound form, then adding fields from different tables to it and using vba to populate "tbl_Main",
View 2 Replies View RelatedI'm trying to create a form using a combo box to populate multiple fields and tables.
I've created a text field to display the added information using this format:
=Comboboxname.Column(x)
in the text box control source field, and this works for display purposes.However, I need it to populate this data into a field on a table.
For example:
My combo box looks up data that has 2 columns, Part Number and Description.
The control source for the combo box is "Part Number". And that populates the part number in the "Main" table no problem.
The text box I created using the above format in the control source populates the field in the form, but not the "Main" table.
Is there a way for the other (description) field to also populate the "Main" table as well?
I have a club member registration application consisting of a program database and a data database. They reside in the same directory. There is an autoexec macro which runs at the beginning. This macro has to find the DATAdb and make sure that the required tables are linked (Not all tables). If it cannot find the DATAdb and then has to make a call to the user to use a different procedure.
Now to find the DATAdb I have used the following code that works. The function GetPathAndName parses the string db.name and outputs the path and name of the PROGdb. Up to here no problems.
Code:
GetPathAndName db.Name, FrontPath, dName
Then I use the following code to get the fullpath of the last database used and thereby find the path and name of the last DATAdb. The path of PROGdb is used in place of the existing path of DATAdb to check whether the file exists.
Code:
Dim rs As Recordset, cPath As String, cName As String
Set rs = CurrentDb.OpenRecordset("SELECT Database, Type " & _
"FROM MSysObjects " & _
"WHERE ((MsysObjects.Type) = 6) AND ((MsysObjects.Name) = 'MembersTbl') " & _
"ORDER BY MsysObjects.DateUpdate DESC;")
[Code] .....
As the final step I use the table definitions to refresh links that already exist and connect tables that are not linked The code is below. But I can not get it to work. It links 7 tables instead of 16 and when tested in different folder says invalid operation and so on. There is fundamental error some place but I could not figure it out.
Code:
On Error GoTo Error_Linking
Debug.Print "TableDefs.Count="; db.TableDefs.Count
For I = 0 To db.TableDefs.Count - 1
Debug.Print "TableDefs("; I; ").Connect="; db.TableDefs(I).Connect
Debug.Print "TableDefs("; I; ").Name="; db.TableDefs(I).Name
[Code] .....
I have two tables: "Tbl_CM_Project_Details" and "Tbl_CM_Inventory"
that have the data that I am trying to connect with a third table: Tbl_CM_Proj_CMI_Connector.
Tbl_CM_Project_Details connecting field is PK_Project_Num to Tbl_CM_Proj_CMI_Connector field Connecting_Project_Num
Tbl_CM_Inventory connecting field is CMI_ID to Tbl_CM_Proj_CMI_Connector field Connecting_CMI_ID
On the form I have a SQL Query where I enter the PK_Project_Num and the CMI_ID Auto enters. What I need is for this information to automatically fill the connecting table so that the information is connected.