General :: Update A Field In One Table From Another Table Via Forms
Apr 26, 2013
I'm trying to update one table's field, via a Form, with certain data from another existing table in my DB when I enter key data in this first form. Example:
Table Equipment ... Some columns... Year, Make, Model, LIcPlate, etc.
Table Fuel ... Some columns... Year, Make, Model, LicPlate, Fuel Dispensed, Milage, etc.
Form for Fuel has Year, Make, Model, LicPlate, Fuel Dispensed, Milage, etc. BTW, it will take Year, Make and Model to fully qualify the search/lookup as there may be more than one occurrence of a Year and Make in the Equipment table, so Model is necessary to fully qualify. . Yes, something like VIN would be a simpler lookup but remembering a VIN is much harder than entering a Year, Make and Model.
In a Form over Table Fuel, I want to have the LicPlate field (possibly other fields as well) automatically updated from Equipment Table when I enter the Year, Make and Model in that form.
I'm assuming the solution involves creating VB code, of some such, via an Event (AfterUpdate) or some such built through the LicPlate field in the Fuel form. A mass Update via SQL is not appropriate.
I have a simple MyAddress Project. It consists of one main table, a couple of queries, some forms and reports. Most fields in the table are typical text fields. The project allows me to select records from the table and print labels for those selected records. One field is a "yes/no" field. If the field value is true, the label prints; otherwise it does not. I have a form which allows me to scan the list of records and mark those I wish to print. So far so good. I am trying to add two buttons to the form to enable me to 1. Clear All and 2. Check All. I am trying to use an UPDATE statement to activate when I click the appropriate button. However, when I execute, I get the error message "Compile error: Sub or Function not defined".
The name of the table is tblMyAddresses.The firld I am trying to UPDATE is PrtLbl.The update value will be true or false depending on the button clicked. Since I want to change the values in all records, there is no WHERE required. The code I am trying is as follows:
Option Compare Database Public Sub CheckAll_Click() Update tblmyaddresses Set PrtLbl = False End Sub
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.
When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.
I am working on a shared database. There are two tables in the database.Table1 have all the employee personal information like employee ID, name, nationality, date of birth etc whereas Table2 have the salary information like Basic, HRA etc. The two tables are joined by Employee_ID field.
I have created two forms, Form1 is only based on Table1 whereas Form2 have fields from both Table1 & Table2. The issue is that if I use to enter the data using Form1, the employee_ID is not automatically updated in table2. On the other side If i use to enter a data by using Form2 then everything is working fine. Is there a way to update a field value which is not in form?
I have created a table for installer invoicing, and have a field for invoice amount. I have created a user input form that allows a user to fill in certain pay rates for different aspects of installation, and would like to know how to make the invoice amount a calculated control that will auto update the field InvoiceAmt in my table. I can't figure out how to do this in the property sheet.
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
I have a combo box that get its values from another table the problem i am having is when a user don't see the info they have in the combo box then enter the new data into the combo box field but it don't update my table with the entered values. How can i fix this to update my table if the user add new data in the combo box field.
I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
I have a table called Inventory (table1 - PK=INVID) that contains all inventory ID numbers and descriptions. I have another table called Inventory Transactions (table2 - FK=INVID) that gets updated through a form. When a new transaction is made in table2, I want the transaction amount to get added or subtracted to an OnHandQty field in table1. I tried having table2 (transaction table) as the main form and then table2 as the subform, but I couldn't get table2 to update.
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria: ID - primary key DiscountID - only when the DiscountID = 92 Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria: ID - primary key code = text field with a code like "einstein01", "einstein02" Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number) Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor" Amount:Number
Table B have two Fileds Sl Number: Number Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl numberstatusamount 1Honor5222 2Honor855 3Honor988 4Honor7777 5Honor777 6Honor9999
[code]...
Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following
sl numberstatus 5Dishonor
Now how I can get the result as following by using query:sl numberstatusamount 1Honor5222 2Honor855 3Honor988 4Honor7777 5Dishonor777 6Honor9999 7Honor6666 8Honor7777 9Honor666
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
I have a form that contains the following: Combobox, (Lists BadgeNum from tblPersonnel)
2 Textboxes (LastName, FirstName) populated via code from the combobox using info from the same tblPersonnel. 2 labels (one containing Date, another containing Time) ToggleOnButton (Valueof 1) ToggleOffButton (Value of 0) Savebutton
This form is basically used as a cheap police timeclock. All Im trying to do is when a user chooses their name from the combobox, clicks ON or OFF and then save, is just write the much of the same info to a table. Specifically, BadgeNum, DateIn, TimeIn, DateOut, TimeOut.
Members has all the data specific to a member and contains just one record per member. Promotions contains information on member promotions and contains multiple records per member. Ranks is a table of ranks and data specific to each rank such as name, description, title, fee.
I have a Promotion Detail form used for adding new promotions. I use some VBA that auto completes other fields on the form with information from the Ranks table based on the rank selected. The user has the ability to modify any of the data.
What I would like to do is update the Member's title (in the Members table) when a new promotion is added to the Promotions table. Their title is determined by the information from their latest promotion.
I'm trying to automate as many processes as possible.
Still working on the registration and badge print system for their upcoming exhbition the company I do a bit of work for. Managed to solve most of my problems but have got stuck here.
Basically each of the badges have a barcode (code 39) on them which is in the following format... *1000000123$M*
(*'s required for the barcode to work, the numbers are the badge number and the $M is the enter command (i think))
Basically we need a form that allows us to scan the barcode (the number of the barcode is the Primary Key in the tbl_Attendees), and for this to update the "Attended" (Yes/No) field to yes. This bit I can do with a simple update query, but we need some form of confirmation, something simple like a line of text showing up on the form saying
I would like to use the "Collect and update via email" function within MS Access 2010 which sends out and collects emails. I know how to use this function, but what I would like to do is something slightly different. Instead of emailing out a data entry "form" that has the employee/team member enter information for data collection purposes about their project, status, start date, end date, priority, tasks, etc. Instead, I would like send an Excel/Access table with all of the current project information displayed in the email data collection form and then have the employee reply to the email and overtype edits to displayed information and then have those changes automatically entered into my table. For example, one project table can have up to 50 rows and then column headers such as project status, start date, end date, priority, task description, etc.
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
I need to update a field within one table based upon a value from another. The problem is after i have setup the relationships the field i need to update will not work.
I have enclosed a screen shot of the relationship and field layout.
I need to update the field" registered with"(tblhistory) using the data within "clubname"(tblplayers) ,this field is updated from clubname(tblclubs) via a relationship.
Please help at a loss.I am not sure whether i need to change the design and relationship side of the database.
