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.
View RepliesI 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.
Any ideas would be great......
I have one form (Request Form) which has quite bit of information on it. So I decided that I would need the following tables:
tblRequests
tblCustomer
tblSalesperson
tblSupervisor
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 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?
Thanks in advance
David
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.
View 2 Replies View RelatedSorry for the newbie question but...
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!
DOWNLOAD DATABASE:
http://www.myfilestash.com/userfiles/sabotage1945/Student_Help.zip
Kindest regards,
Sab.
Hey Guys,
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.
Thanks ahead of time,
JVermast
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.
View 2 Replies View RelatedI 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.
TIA
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 RelatedI have 3 table each with the same columns.
1st table = current documents
2nd table = documents awaiting approval
3rd table = old documents no longer in use
I currently have a subform within my search form that I use to filter the results via a textbox on the form. I would like to have the contents of all three tables present in the subform.
Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?
Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...
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 making a master form for three tables which ultimately form a hierarchy. I am getting all the fields from each table onto one form. By default the record source would be made up of an INNER JOIN such as below
SELECT PERSON.first_name, PERSON.country, PERSON.last_name, PERSON.street, PERSON.area, PERSON.city, PERSON.telephone, CONTRIBUTOR.registration_date, CONTRIBUTOR.fav_category, MUJAHED.profession FROM (PERSON INNER JOIN CONTRIBUTOR ON PERSON.id=CONTRIBUTOR.id) INNER JOIN MUJAHED ON CONTRIBUTOR.id=MUJAHED.id;
I'm trying to change the record source to match using the where clause instead. However the following allows me to view but not add a new record
SELECT PERSON.first_name, PERSON.country, PERSON.last_name, PERSON.street, PERSON.area, PERSON.city, PERSON.telephone, CONTRIBUTOR.registration_date, CONTRIBUTOR.fav_category, MUJAHED.profession
FROM PERSON, CONTRIBUTOR, MUJAHED
WHERE (PERSON.id = CONTRIBUTOR.id) AND (CONTRIBUTOR.id = MUJAHED.id);
A tip or guideline on how I can correct this will be appreciated.
Regards
Hello, New to the board. I need your help/advice.
I have a: Movie Form. Table Actor and Table Director in my database.
on my Movie Form I have a combo box, where users can click on any movie on the list and display both Actors and Director information according to the Movie Title they select.
To accomplish this I use Subform/Subreport Wizard to add each Table to the Movie Form. Because When I try to bound the Specific Data to a textbox I use, the Field List is EMPTY.
My question is how can I make my Movie Form have acouple TEXTBOX's that change accordingly to the Combobox without having to use the Subform Wizard to import the info from my 2 tables.
I hope this make some sense, im just learning. Any help would be appreciated.
Thank YOU
My db has several tables tb1, tb2 tb3 tb4 ..... I have link tb1 to tb2 (tb1Id to tb2)and linked tb2 to tb3 and tb4 (tb2Id to tb3 and tb4)
I have created a form with several field from tb1 and tb2 and a single field for tb3 and tb4. All of the fields except one for tb1 are use to make a decision based on what they display. The only field that is updated on the form is a date conversion field from tb1. once you update this field it will auto populate a date field on tb1 with current date. you then have a choice of 4 commands to activate based on what is displayed form the rest of the form.
my problem none of the fields can be updated. can any one help
qry the form is based on
tb1 [Name]
tb1 [ID]
tb1 [date conversion] 'this is the only field that is manualy updated
tb1 [date] 'updated base on date conversion field being updated
tb2 [field]
tb3 [Yes/no]
tb4 [yes/no]
based on what is displayed on the form you have 4 choices of cmd buttons.
Hello,
Stuck on a problem and was wondering if anybody has a possible solution. I currently have the beginnings of a database setup that uses a search field on a form to find a reference listing using a part number input by the user. It finds the part number, and then displays all the information about it on another part of the form. My question/problem is, I now want to expand this database to multiple specialties, all with different parts in their respective listings, so they would have different tables. The tables would be the same format, just different information. No one table would ever contain the same info.
Q: How would I set up a form to search for a unique part number over multiple tables, and return that parts info?
It works great for one table, but I am stuck on the multiple tables.
Thanks in advance!
I have been trying to figure this one out for a while and I can't seem to find anything helpful. I'm going to present a simplified version of my problem to help keep it simple.
I have an "Activities" table that contains fields:
ActivityID
ActivityName
I have a table of 7 Risk areas which contains fields:
RiskID
RiskName
What I need to do is to create a form that will allow the user to select an activity and then, for each of the 7 risk areas, input a risk measurement.
This involves the creation of a 3rd table to collect the user input which would contain fields:
MeasurementID
RiskMeasurement
ActivityID
RiskID
I can do a dropdown of the Activities and have a subform that links to it but the problem I'm having is trying to get that subform to list the 7 Risk Areas AND collect the RiskMeasurement information from the user. How can I have a listing of these 7 areas from one table that are tied to inputs that go into another table? This seems so basic and simple yet I have tried numerous things and all have failed.
I hope this was clear. Any help is appreciated.
OK I'm totally lost again.
What I want to do is;
Use one form to add records into several tables.
I have seperated my data into seperate tables and set up one to many relationships.
tblincident
--------------
Key#
Report#
Time
Date
Location
tblPerson
----------------
Key#
Last Name
First Name
Middle Name
Address
etc.
tblItem
----------------
Key#
Make
Model
Serial#
etc.
What i want to do is use one form to enter all this data into the different tables.
There can only be one record in tblIncident that can match multiple records in the other tables.
i.e. for each record in tblIncident, I want to be able to have the capability to have as many persons related to that incident as I need.
I was thinking about setting up a tabctl for the data that has to be entered for tblIncident and then change the property setting for it to go away and then have the next tabctl appear in it's place for entering data into the next table. The problem I am having is that apparently I can only have one table as the control source for the form.
So next I tried using subforms, but that isn't working either. I can't figure out how to make the subform appear in a specific place on my form, sized in specific dimensions I want where I want.
I am just starting to learn about this relationships stuff and it's kind of difficult. How do you make it where you can do this from one form? Do I need to make a query of some sort?
Any help is appreciated.
I have a tabbed form that contains multiple rows of data just like the following: (it's a service checklist for a store where the user makes choices from the combo boxes: e.g. good, fair, poor, etc)
Paint (txtHrs), Action (cboPaintAction), Condition (cboPaintCondition)
Carpet (txtCarpetHrs), Action (cboCarpetAction), Condition (cboCarpetCondition)
Counter (txtCounterHrs), Action (cboCounterAction), Condition (cboCounterCondition)
etc.
Since the combo boxes describe data that relates only to that element I can't put all the data needed into a single table. There are 40 of these rows so does that mean I have to create 40 tables? If so, how do I add the data to them from the form? Any help with this problem would be greatly appreciated!
Hi all, I'm not an Access programmer by trade, but unfortunately got handed a project that nobody else wanted.
I have a small database taking shape which will track the auditing of paperwork/files. Each file has a unique ID associated with it, in this case, an account number. Audits will take place by selecting the job area (department), then the type of file (Line, Loan, Increase). This will be used to sort which type of audit questions are asked.
I have the following tables created:
tblQuestions: ID, Question, Product question applies to, Function question applies to, Comment field, and result field.
tblTeamMember: MemberID, TeamMember, Manager, PrimaryFunction
tblAuditResult: AuditID, MemberID, AccountNumber, Question, Result, Comment, Date of Audit
tblProduct: List of product types (line, loan, increase)
tblFunction: list of job functions
Currently a form is displayed so the manager can select the job function, then the file type. This will display:
frmAuditCreate - this form contains the fields: txtAccount, cmbManager, cmbTeamMember, txtAuditDate
Sub form on frmAuditCreate - frmQuestionSub - this form contains the fields: Question, Comment, Result
The frmQuestionSub is built off a query which uses the function and product values to determine which questions to filter and display.
Now, once the user is done flipping through each question in the sub form and input the data into the comment and result field associated with that question, I need that information along with the information on the frmAuditCreate to be stored in the table tblAuditResult. This is the problem I'm having difficulty with. I can't find a way to copy the question, comment, and result and insert it into the same record as the rest of the data.
Unfortunatlly I can't strip the database down enough to meet the 100k or less attachment limit.
Hi, I have a big problem. Here is the business rule, I am trying to track meeging request(MR) made. A meeting request is made by a manager,MR can include many analysts, and MR involves 1 project. I need to generate several reports from the MR information so I am trying to make sure the info is inputed correctly. I am trying to creat an input form for the MR table (tblMR) The input form is going to contain all the fields from the tblMR except the manager_ID and and the Project_ID, I am also going to have it track which analysts attended the meeting(1 or more). The problem I am having is in creating the form but having the analyst, manager, project values come from their own tables but after selecting them through combo box or list box, for values of Manager_ID and Project_ID to be entered in the tblMR. And for the analyst involved to be stored in a a composit table called tblMR-Analyst. this table will help me track how many meeting each analyst has been to. Any suggestions will help.
View 2 Replies View RelatedPlease be kind, i have little VB Knowledge, and wish to expand my learning on this topic.
I have a form that updates information on one table, and has a subform displaying info from another table.
the subform is filtered, and only shows data from what is specified from the filter of the main form.
If I update information on the main form for instance,
field 1, (the data on the subform has the same data so there is the relation), how do i update the subform by only updating the mainform? can this be done through some VB or something?
if you folks out there can give a Smidget of info on which VB codes i can use i can pretty much figure it out.
like Docmd.write something?
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 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 1 Replies View RelatedI 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 Related