Update Two Tables From One Form
Oct 5, 2005
I am in the process of developing a program for work. I am stuck on a form that I have developed to enter information on an injury report.
This is what I would like to do but stuck on how to get it completed. I have attached a stripped down version of my DB.
Injuries are entered in the "frmAccidentInvestigations." Once you have entered the employees information the form would automatically check to see if this employee is already in the "tblPersonnel." If the employee is already entered into the tblPersonnel you would continue with entering the information without any further prompts. This information is saved in the tblAccident.
If you are adding a new employee that is not in the "tblPersonnel" then a pop up window would open asking if you would like to add this record. If you hit "yes" it automatically saves the employee information into the "tblPersonnel" and the tblAccidents. This would allow you to check to see if you may have made a typo in the record. If you hit "No" then it continues with the Accident report.
The tblAccidents is filled with all of the injuries and you may have multiple records (Primary Key is the associate#). The tblPersonnel contains all of the information for employees and there are no duplicate records.
I have removed the information on the injury report to reduce the size of the attachment. The original DB has approximately 30 text boxes that you would have to enter information into.
Well this is one of my last bugs that I need to finish my DB and would appreciate any help you might provide.
Thanks in advance.
View Replies
ADVERTISEMENT
Aug 27, 2004
I have two tables "Clients" and "F/S tracking". The one commone field between
the two is "Client Name". What I want to do is have one form that is used for data
entry, that will update both tables with the "Client Name".
Is that possible? and if so how? I have had no luck with it yet.
Thanks
Mitch
View 1 Replies
View Related
Jul 26, 2007
I was just assigned this database task couple weeeks ago, I know VERY LITTLE. I have all my tables and forms set up. My company builds custom cars....very few per year. I have dozens of tables for all the different componetns of the car....and each table has the same column "Unit" in it. I have a form that when a new car is began being built, the the first thing it does is ask the user to type in the Unit for the new car, but that only puts it in the one table i referenced when creating this form. I want/need this Unit information to be placed in every single table (20+) that has the column "Unit" in it. How can i do this, any information is greatly helped.....im getting so frusturated, i feel like i've learned a lot in the past 2 weeks since starting but i stil really know hardly anything.
View 3 Replies
View Related
May 22, 2015
I wrote a database for my daughter's dance school. There are two tables, one for personal details, the other for exam results. The data is separated this way because when I was given it all, I got it from two people in an Excel file.
When I programmed it, I had the Primary Key in the Master table auto-generated (an Integer) which I also used as the Primary Key in the ExamResults table. All the data manipulation is handled in code by Event Procedures.This is the code to update the Master Table when a new pupil's details has been entered and the 'Submit' button on the form clicked:
Code:
Private Sub btnSubmit_Click()
On Error GoTo btnSubmit_Error
Dim curDatabase As Database
Dim rstNameAddress As Recordset
[code]....
The woman who uses it told me the other day that she can't update the exam results for any pupil she has put into the database since I gave it her - records of pupils entered by me are OK.When I checked the code I realised that when a new record is created, there isn't a corresponding one in ExamResults. when is the Primary Key first generated and how do I program the form to update the new pupil's details in the ExamResults form when the Master table is first written to?
View 12 Replies
View Related
Feb 9, 2006
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.
View 1 Replies
View Related
Jul 31, 2015
I have two tables that I want to update from data in an unbound form. How do I capture the ID of the new record created in table 1 in order to write it to a field in the new record being created in table 2.
Table 1
id
f1
f2
f3 and so on
Table 2
ID
Table 1 id
F2
F3 and so on
View 5 Replies
View Related
Feb 10, 2014
I have 2 main tables. One with Data that I get from an outside source and cannot be changed. The other is one that needs to be updated as needed. I have a few look up tables so I can identify the codes with the descriptions. In 2003 I was able to make a big query and link my forms to the query to make modifications. I am not able to edit any information in the 2010 query. The people want it to work the way it did in the past but I am not skilled enough in access to do that. Is there an easy way to use a query when updating the form? I used to be able to edit the query itself and it would feed back into the existing tables.
View 1 Replies
View Related
Aug 24, 2011
Ok So I've created a Database to track phone calls coming into a call center.
(Originally created in Open Office, but then we switched to Access)
I've got a form that has buttons to run multiple reports.
The Reports pull data from a table based off dates found in another table
(Something I used in Open Office)
Table 1 has all the phone calls
Table 2 has a From date and a to date
The report pulls the phone calls in table 1 between the dates in table 2. In open office I used Subforms (Which were seemless) and combo boxes to update the dates in Table 2 before running the report.
Is there an easier way to accomplish this in Access? If so How? If Not, is there a way to make a seemless subform in Access?
View 3 Replies
View Related
Jul 19, 2006
Hello,
My first post is on something that is troubling me. I have a Form acting as the display and entry point for data for a contact list, which is composed of two Tables as follows:
Contact - (text fields including: first name, last name, phone number home, phone number work, etc)
Industry Role - (yes/no tick boxes including: film, photographer, audio engineer, producer, reporter, etc)
The two Tables have a one to one relationship based on the URN field which is an autonumber. My problem is that when someone enters say a name, and then ticks a box, the autonumber will add two entries because it seems to see the first table then the second tables as sequential, and not the same thing. How do i go about making a form that can enter new records the same autonumber for two connected Tables?
View 3 Replies
View Related
Jan 9, 2015
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.
View 4 Replies
View Related
Jan 31, 2014
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 Related
Jun 27, 2013
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.
View 1 Replies
View Related
Apr 24, 2013
I have a form that I need to turn into a table. The table will be updated each day with new information but I dont know what to do to keep the previous days info. I have attached the form ....
View 3 Replies
View Related
Jul 30, 2012
I'm using Access and Excel 2007.... I know how to import an Excel spreadsheet as a table.
I have several supplier price lists in Excel. I want to keep my vendor price lists up to date.
When one of my vendors tell me that a price has changed on a particular item, I figure that I could have a form that I could use to enter the changes.
I believe the form would look like:
Field: "Vendor" (drop down list to choose from. Name of the Supplier price lists) Required.
Field: "OEM" (Key Field found in each table) Required.
Field: "Brand" (Field found in each table) Not required.
Field: "Price" (Field found in each table) Required.
OEM would be the unique key field.
If I enter the Vendor name and then the OEM number it would show if there is already that number in the Vendor price list and I could make changes. Or I could enter new data in that vendor price list.
View 10 Replies
View Related
Jun 27, 2012
I have a form with two tables referenced. I am using the form only to update one of the tables. I am using the other table to pull a reference field. When I add my second table using the query builder, it makes it to where I can't edit/add in my form. I assume its because of the SQL insert statement, but I don't see this statement and can't find where it is to edit it. How to have the form only update one of the tables, while just using the second table as a reference for a field?
View 1 Replies
View Related
Jun 28, 2005
Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
View 9 Replies
View Related
Apr 19, 2007
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
View 1 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
Oct 11, 2007
Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.
View 1 Replies
View Related
Apr 30, 2015
How to Update ODBC linked tables on ms access?
View 5 Replies
View Related
Oct 22, 2014
I have to make a Costing System but for that I need to enter our Expense details in database according to Fiscal year and months.
I need a table for Fixed expenses and one for Variable Expenses and then I need one or more Forms to update data in those tables. Now I've created a table with Fixed Expenses. I have to update Year and Amount in that. Now it is only letting me one entry per Expense.
I want multiple entries for one Expense say 'Advertising' for different years. I'm thinking may be I need to make more than one Table, may be one for Expenses with ExpenseID and other for Years with Year and YearID and the third one for Amount with columns Amount, Method of Payment, Date and Notes. I did tried this but I think I'm not creating proper relation may be because its only updating for one year.
I'm using Access 2003.
View 2 Replies
View Related
Aug 9, 2006
Hi,
I have a client who wants to work from home on two tables in the database. What would be the best way to update his changes to the live database at his work? He is planning on doing changes for a few days at a time before updating the two tables at work.
Thanks for any help.
Geno
View 4 Replies
View Related
Jan 25, 2007
I've been creating a new Access application and I've run into an issue. The form I created has 2 subforms on it. The data is stored on 3 different tables related by the RMAID. The data gathered from this form needs to go into our MRP application. I have linked the necessary MRP tables to my access application. How do I get the date from the form to the tables in our MRP application? The data needs to go to 3 tables from the MRP application. Do I create an append query that's run after the user completes entering the data on the form? Thanks for any help
View 1 Replies
View Related
Apr 10, 2005
Hi,
I have two tables as follows:
___________________________
tableA:
projectid capital
1 200
2 200
3 300
4 400
tableB:
projectid capital
1 100
2 200
3 300
____________________________
I want to create tableC that is a merge of tableA over tableB as follows:
projectid capital
1 200
2 200
3 300
4 400
Can someone tell me how I can get this done please?
I have been struggling with query syntax on this for about 2 days now not knowing how to get this done. Thanks for responding.
View 6 Replies
View Related
Dec 20, 2005
Does anyone know how to use an update query however, rather than update the field with a specific value, the field is updated from another tables value of the same field name?
This is the sql I attempted to use however, when I execute the statement i get a enter parameter value when it gets to the Where statement?
Any advise would be greatly appreciated, I'm lost!
Update PendingPolicies
Set PendingPolicies.PolicyStatus = PolicyTable.PolicyStatus
Where PendingPolicies.PolicyNumber=PolicyTable.PolicyNum ber----Iknow there appears to be a space here but there is not in my SQL?
View 2 Replies
View Related
Jan 9, 2013
I want to update fields B and C in a table based upon another field (A) in the same table but I want:Field B to update when the original field A is changed in the table (I have managed to get this working in the form but I am trying to get the fields in the table to do the same as sometimes we batch update via the tables and this doesn't seem to work so we have to go into each record individually in form view to update the fields) Field C to only use part of the original field. where to start on this?
View 9 Replies
View Related