Update Field Based On Other Form
Oct 9, 2006
I have two tables:
Table1 - show details of customer payment (full payment / with credit terms up to a maximum of six (6) months)
ColID - (PK/Autonumber)
Col1
Col2
Col3
Col4
Col5
Col6
Table2 - shows insurance agent's prorata commission (based on cleared payment).
ComID - (PK/Autonumber)
ColID
Com1
Com2
Com3
Com4
Com5
Com6
Scenario: If a customer was given 6-month-credit term to pay for his insurance premium, then the insurance agent's commission will also be given in six monthly terms.
Example Computation:
Amount of premium is 12,000 (payable in 6 months = 2,000/monthly)
Agents Commission is 10% of Premium (1,200 = 200/monthly)
What i need is a code to automatically update Table2 if Table1 is updated. If the dbase user updates Col2 based on the amount the customer pays, the field Com2 must also be updated.
I hope this is feasible.
Thanks!
Sheila
View Replies
ADVERTISEMENT
Oct 30, 2012
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.
View 5 Replies
View Related
Jul 13, 2013
I have a table that has been converted into a form for an input. Now suppose I have two columns. Say I enter value in first column. Now the second column is a lookup to a data from another table.
What I want is to see only those data in 2nd Column which have the value filled in Column 1. This is should be in run time mode. I fill in the value and then in the 2nd column I directly get to see only common values.
View 2 Replies
View Related
Jan 9, 2015
I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form
Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.
I know I am duplicating the data by having customer name in both tables which is bad database design!
View 3 Replies
View Related
Mar 3, 2008
Ok. I've got a table named SIPATable and some of the fields contain summed values. Let's say for instance I have a field named "GaDRaw", which represents a raw score. now...I need to update a field called "GaDPer", based upon the value in "GaDRaw". So...If "GaDRaw" <=16, then I would want "GaDPer" to be updated to = 35. Further, if "GaDRaw" = 17. then I want "GaDPer" to = 37. and so on....
I think it's an If Then Else statement, but not sure how or where to place it. Id like it to be as code under a command button, and update the table all at once.
Any help would be appreciated.
Thanks in advance,
Freud52
View 7 Replies
View Related
Aug 24, 2014
I have a form used to track attendance of employees:
I want to update a value in Field B to a default value if a specific criteria is selected in Field A. How? I cannot seem to find a answer...
Specifically: If the combo box value "Not Present" is selected from Field A, I want to value in Field B to automatically update to "Not Available".
View 11 Replies
View Related
May 11, 2015
I have the following fields in an A2010 form
PaymentMethod
Total
InstalmentAmounts
OurFee
InstalmentAmounts field is populated based on the results of PaymentMethod which is a combo box
Here is the current code
Select Case Me.Payment_Method.Value
Case "Three post dated Cheques"
'If three payments by cheque
Me.InstalmentAmounts.Value = Round(Me.Total.Value / 3, 2)
In some cases there will be a few pennies that the client "overpays" because the instalment amounts will not add up to the Total. I need to add up the TotalInstalmentPayments to find out what the client actually pays and then add the difference between TotalInstalmentPayments and Total to the OurFee field
So
Total = 500.00
Payment Method = 3 instalments so
IstalmentAmounts = 166.67 (rounded) so client pays 500.01 so the 0.01 needs to add to the OurFee field.
View 4 Replies
View Related
Jan 5, 2006
drvRegion either contains "EUROPE", "AMERICA" , "ASIA" or is NULL.
if drvOrderSource="Whatever" THEN update drvRegion with "EUROPE", "AMERICA" , "ASIA"
Right now I'm getting a circular reference error :/
please advise. Thank you.
View 5 Replies
View Related
Jul 25, 2006
I have the following situation.
Table 1:
------------------------
Primary Key
Name
Secondary key
Table 2:
------------------------
Primary Key
Age
Secondary Key
*****************************
This is just an example. The first table is totally populated. The second table does not have the secondary key assigned, but otherwise is populated. In additon, some of the records in Table 2 do not have a matching entry in table 2.
I would like to be able to update table2, populating the Secondary Key field in order to allow me to delete the Primary Key fields. The Primary key was assigned by somone else who created the db, and makes no logical sense, i'm trying to replace it with a key that can be used more easily. Any help would be greatly appreciated.
Kevin S. Jones
View 1 Replies
View Related
Jul 6, 2006
i have set up a database to help track the vehicles my company owns. However on the form i have created when i type in the license plate number want the work area that is responsible for the vehicle to pop up so i can inspect it.the form also has the different things i would look at but i don't want to continue to change the work area that has the possession of the vehicle or have to look it up every time i have an inspection come due. Any help would be appreciated
View 1 Replies
View Related
Jul 12, 2007
I want to create an query that will update a field value for records where the length of the field value is less than a given number (if LEN of UniqueID is less than 6 for example), but I can't figure out how to write a SELECT query, much less an UPDATE query for this condition....can someone help me, please?
View 1 Replies
View Related
Mar 31, 2008
I have a combobox that selects the customer and shows related information on that customer such as phone number, cc#, etc..
now the trick is i need to allow the selected member to be added to the order information.
For instance, the user selects the customer "Bob" and bobs information is displayed to check for accuracy. After the info is approved the user will continue to process his order. In order to do this I need the customer ID to be the same as the selected customer in the combobox.
Then the user will proceed to fill out the order information, location, date, time, etc.
How can I go about doing this?
Thanks in advance
View 2 Replies
View Related
Mar 15, 2006
I would like to update two fields [Category] and [ProdType] in tblAccum based on a reference table.
The reference table is tblReference and contains the fields [Code], [Category] and [ProdType].
tblReference example of field values:
Code Category ProdType
A Blank Accessory
BS Blank Blank Stock
O Printed Offset
So if the Code field in tblAccum has a value of O then based on the tblReference table the Category value would be Printed and the ProdType value would be Offset.
Any help is greatly appreciated.
Thanks,
View 7 Replies
View Related
Jan 25, 2015
I have a calculated field in the form footer which adds up the number of boxes that have been ticked for the received field
=Sum(IIf([Recieved]=Yes,1,0))
If the ticks equal to 3 then I want to update the status field in another table to "Active".I am trying this VBA code but it won't work.
Code:
If Text9 = "3" And custNumber = tblCustomers.custNumber Then
tblCustomers.Status = "Active"
View 3 Replies
View Related
Oct 30, 2012
After testing it, the database got corrupted. I had backed it up just prior to using this.
It was something like = Sum(Abs[AmountPaid], [Paid] = "X")
I have a continuous subform with an "AmountPaid" column. The total is displayed in the subform footer. I need it to display the total for only the fields with an X in them denoting that they were paid. This total should match the statement we receive.
After clicking the button to put the X in the Paid field, then I used the formula to update the AmountPaid field.
Joe..........10..... X
Al..............5.....X
Flo.......... 25
.....Total = 15
View 3 Replies
View Related
Jul 26, 2006
I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...
What is the code to have multiple fields updaterd based on what is input into a field?
---
Example:
A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)
---
I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.
Thanks a million!!!
View 1 Replies
View Related
Nov 16, 2013
I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.
I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.
I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.
I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"
I'd attach my version ....
View 2 Replies
View Related
Aug 8, 2013
I have a field in a table that is to be concatenated from two other fields, PolPrefix and PolNum. On a form I got it to show the full field 'PolicyNumber' by making the control source =[PolPrefix] & [PolNum].
But this doesn't update the field PolicyNumber in the respective table, and only shows it on the form. How do I get a concatenated field defined by the user into a table so that I can call that field other places in the database?
View 3 Replies
View Related
Sep 7, 2012
I have 2 tables like this
TableA
FromDate
ToDate
BatchType
TotalBatches
TableB
Date
BatchType
NumBatches
Is their a way to update the TotalBatches in TableA with the sum of NumBatches from tableB that have the same BatchType and falls between the FromDate and ToDate of TableA?
View 14 Replies
View Related
Aug 25, 2006
I am trying to update a field to say "Yes" or "No" based on whether it is more than thirty days out from the value in another field. I am having trouble doing this for some reason...totally stuck!
Please help :-)
View 5 Replies
View Related
Jun 3, 2013
I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.
View 1 Replies
View Related
Mar 12, 2013
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.
PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1
View 6 Replies
View Related
Jan 14, 2013
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.
View 1 Replies
View Related
Jun 13, 2006
I am very new at Microsoft Access. I have two forms, A and B, that are filled out by two different users. Form A gets filled out first than form B gets filled out. Each has a name box as well as a birthdate box and a few other similar text fields. However, each form also has a few distinctive fields. I was wondering how I could input the common information in Form A to Form B so the user of Form B does not have to spend time retyping the name and birthdates again. In other words, I want to synchronize the similar records between the two forms. I would like the values to appear in a table as well. I would greatly appreciate it if someone could help me with the visual basic code. Thank You.
View 1 Replies
View Related
Aug 2, 2013
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.'
View 7 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
