Update Field On Separate Table If Result From Form Equals Certain Criteria

Mar 17, 2012

I have a form, which i use to book in products received. What i want to do is if the quantity received is equal to what was ordered then i'd like to update a yes/no field (Order Complete) on a separate table to true.

View Replies


ADVERTISEMENT

Queries :: Field Is Empty - Query To Update Table But No Result

Feb 26, 2015

I have a created a query which I want to update a table. The field is called DTP. I have the Workingdays2 module in place as well as the Holidays Table. The dates (date only) are called Time in and Transaction date. I want the table to update records only if the entry in Completed is Yes. I have attached screenshot of what I have in query. I am no good whatsoever at VBA so I used the Query design view. It shows how many records will be updated (which is a valid number) and shows me which field will be updated. However when I run the query and then opend the Table the DTP field is empty.

View 2 Replies View Related

Forms :: Display Field From Separate Table In A Form To Use As Reference

Mar 25, 2014

In my form (source tblJobs) i input a contract number from tblContracts. but i would also like it to show the contract address when the contract number is entered. Both fields belong to tblContracts but i only wish to enter the contract number, the address will just be used as a reference as it isnt something i would have to enter again.

So for example

112 - Main Road

If i enter 122 in my ContractNo field i would like it to show the contract address for that specific contract.

Can i do this? If So how?

View 5 Replies View Related

How To Set Field / Table And Criteria In Update Query

Jan 10, 2013

I have a form that run a query to select all the students taught by a given faculty member. Once the records are loaded, the faculty enters attendance data, selects a date from a combo box and hits a save button.I change my relationship and now need to be able to store the key for the field in the combobox and not the text.

How do I set the field, table and criteria in the update query to do this. The UD query uses the FacultyStudents query as its record source. Currently the FacultyStudents querry does not have the Key or the date fields since the date is selected by the faculty once their records are loaded. As I see it there is now way to make a join, so I think I either need VBA or SQL, which I am not very good (really bad) at wriitng. Can the necessary Sql be written in the query design view?

View 4 Replies View Related

Update Query Based On A Separate Table

Nov 24, 2005

Hi all,

This is quite a basic question and I'm sure that it has been posted here some time ago, although I couldn't find it on using the search.

I want to update a field in one table "FieldA" using an update query. I want the update to be based on matching Field B in the updated table, and Field C in a separate table (both tables have the same design structure).

That is, I'd like my query to search each record in the Destination Table to see if its Field B matches a Field C in a separate Table. If there is a match, I'd like to Update Field A to the corresponding field A of the reference table.

I have tried to do this with an Update Query based on both Tables in design view. Thus far, it hasn't yielded any results.

Can someone please post the solution asap. Thanks so much for your help.

Regards,

Mike J.

View 4 Replies View Related

Update Separate Table When Record Is Edited

Aug 5, 2015

I have a form where a user can change the scheduled start date for a job. On a sub form on the same screen is a list of notes relating to that job.

Any notes added, automatically have todays date and are locked when the user clicks off.

When the scheduled start date is changed I need a note to be made. Either forced, then entered by the user or automatically.

I was thinking of making the scheduled start appear in a small form and the button to make it come up could add a new note on the click event (possibly in a message).

Or even better (probably harder) any time the value is changed in the form a new note is added.

Other options could be a pop up form to add one note on a change.

View 4 Replies View Related

Modules & VBA :: Update Field With Calculated Result

Mar 19, 2014

I am trying to update a field with the result of what is in the field plus the contents of a unbound form field.

So if crm_order_det.Qty contained the number 5 and the form field (labelled qty) contained 7 I would like the field crm_order_det.Qty to end up with 12.

The code I am using

sql = " Update crm_order_det SET [Qty] = (" & _
frm!Qty + crm_order_det.Qty & ") where crm_order_det.part = '" & frm![SearchResults] & _
"' and order_id = " & frm![order_id] & ";"
DoCmd.RunSQL sql

However I get the following error

Run-time error '424': Object required.

View 3 Replies View Related

General :: IIF Statement With Ending Result - Default To 0 In Field If Meet Criteria

Oct 30, 2012

I have a text box with the following statement:

Summary detail: Sum(IIf([dacthc]=0,Null,Fix(100*[dqty]*([dstdhc]*[drate]/[dacthc]-[drate]))/100))

My question is, if it meets the criteria, is it possible to make it default to a "0" in the field.

View 1 Replies View Related

Get A Field Only If It Equals What I Asked

Sep 18, 2005

I have a table with data of people, in which table two fields contain two (not necessarily different) jobs.

I want to build a query (and later a report based on that query) which will return the name, address, phone of the person, plus only the field of the job that I asked for it.
when executing the query, the user should enter the asked job in a popup-box using the below formula.

Like "*" & [job] & "*"

Note, that there may be records containing in both job fields the same job. I want to get the field only if it contains the asked job.

Any ideas?

Thanks

View 12 Replies View Related

Forms :: Combo Box To Be Selected If Another Field Equals 0

Mar 11, 2014

I have a form (PostProductionForm) which is used to input data about finished goods which come off a production line. There are three possible outcomes which can occur after the production: Passed, Reprocessed, Failed and the numbers for these are entered in 3 text boxes on the form (QtyReprocessedTxt, QtyFailedTxt, QtyPassedTxt).

These text boxes are auto populated with a 0 from the table (PostProductionTbl) so there aren't blanks.

What I am trying to do is use an IF function (I think) which says that if Quantity Reprocessed is equal to zero when the submit button is pressed, the "Reason for reprocessing" combo (RPReasonCombo) must have something selected (i.e. can't be blank) or it won't go to a new record.

View 5 Replies View Related

Update OnHandQty Table Field Based On Qty Field From Another Table / Form

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

Code To Store A Value In Table Where ID Equals To X

Mar 13, 2014

I have my Jobs form which has a sub-form for all the different parts used on that job which then calculates the total charge and displays this on the main jobs form. Now how would I get this value to be stored in table tblJobs on the record where the ID = JobID on Jobs form. I need to do this to display the total on a report.

Looked around and couldn't find what I was looking for, is there a visual basic command to set it then I can use docmd.saverecord after or what would I need to do?

Or is there an SQL statement to do this I could execute on a button click or something similar?

View 9 Replies View Related

Update Form Text Box With SQL Result?

Sep 24, 2012

I am creating a form for 3rd-party service contracts, and I have two tables called tblServiceProviders and tblServiceDescriptions. tblServiceProviders has a 1-to-many relationship with tblServiceDescriptions; i.e., each Provider can have multiple Descriptions (real world: product offerings). So for example, ServiceProvider "Acme Corp" might have ServiceDescriptions "AcmeCare", "AcmePremium" and "AcmeInstall". My database is normalized, so tblServiceProviders has an ID field that is related to a field called ProviderID in tblServiceDescriptions.

Each ServiceContract includes, in addition to customer name and some other stuff, one and only one ServiceDescription. The ServiceDescription field in the ServiceContract form is a drop down box called comboSvcDesc and it contains all of the ServiceDescriptions in the database. I also have a non-editable text field on the form called txtSvcProvider.

What I want is to have the name of the ServiceProvider ("AcmeCorp") automatically populate in txtSvcProvider whenever the comboSvcDesc box is changed. So for example, if I change comboSvcDesc box from "WidgetCare" to "AcmeCare", I want txtSvcProvider to change from "WidgetCorp" to "AcmeCorp".My thought is that I should be running a SQL join query to get the related value "AcmeCorp",but I can't figure out how to make this work in VBA.

View 3 Replies View Related

Form Event (Before And After Update) Copy Form/Field Value To Another Table/Field

Aug 17, 2006

Group,

I thank you in advance for considering this inquiry.

From within a Form.field (based on a master table query), I desire to trigger two events; one before update and one after update.

This function will serve to document specific form.field value changes to a "log" table for review prior to being committed back to the master table.

What I'm looking for are functions, which I can use in a Macro or VBA code to facilitate a field read and copy before change and a read and copy after change triggering.

I'm sure this is absurdly simple.

View 2 Replies View Related

Queries :: Create Separate Columns From Same Field And Table

Apr 9, 2013

I have 1 table that I duplicated to make 3 tables total. I did this b/c I am trying to create separate columns from the same field and table. The field is Workorder. Each workorder list the workorder number followed by a dash and then code. I am wanting to put all of the workorders with the same code in it's own column.

I have 5 codes that i am searching for. The first column list the workorder and a code (123456789-AD). The second column (123456789-BC). I'm good to this point but my problem occurs next.

The third column i am trying to put 3 types of workorder and it's code in the same column. As follows, (123456789-CD, 123456789-TC, and 123456789-PTC. However, when I do this it takes the results from the 3rd column and applies it to the 3rd column but also the 1st and 2nd column. I tried a UNION query and unless I am doing it incorrectly it does not work.

View 3 Replies View Related

Queries :: DSum - Update A Field In Specific Table With Info Form Another Table

Aug 5, 2014

I have a table products with a field "id_product" and "total" (Total items in stock)

I have a query with the fields "id_product" and also the field "total in stock"

I want an update query to update the field 'total' in table 'products' with infos from that query

For each id-product in table products, replace the field total with the field 'total in stock' from the query

So I want to update a filed in a specific table with infos form another table.

View 2 Replies View Related

Forms :: Using Data From Another Table As Validation Criteria For A Field In Form?

Mar 24, 2015

I've two tables, QA (Quality Assurance) and Instruments. I'm using form to add data to QA.

There's a field in QA, named InstrumentUsed. The criteria is that InstrmentUsed should only accept value when 'status' field in Instruments table shows 'working'. If status is 'faulty' or 'need repair', it should pop up the related error message and cursor stays on the same field.

I've not starting learning VBA yet, so I'm trying to make use of Macro's.

How to use data from another table as a validation criteria for a field in form?

Pops up a MessageBox and stays on the same field, on the form, unless error is resolved?

View 3 Replies View Related

Tables :: Extract Data From Memo Field And Put Into Separate Fields Of New Table

Jun 15, 2014

I have a notes field in the customer table that is a memo field. An example of one customer's notes field data :

<div>20.3.14 Ordered 2 cartons</div>
<div>4.3.14 Ordered 2 cartons</div>
<div>18.2.14 ordered 1 carton</div>
<div>30.1.14 ordered 3 cartons SCENTED wipes</div>

[Code] ....

I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?

View 2 Replies View Related

How To Automatically Update A Table Field From A Form?

Feb 19, 2006

hi there
I am having some difficulty with using Access (aren't we all?) ;-(
I have a table with a the following fields: START_DATE, END_DATE, RENTAL_PERIOD, DAILY_COST & TOTAL_COST.

What I am tring to achieve is that when someone fills in the START_DATE, END_DATE & DAILY_COST fields (which update the table behind the form) that the form will take the END from the START and put the result into the RENTAL_PERIOD field and then multiply this by the DAILY_COST value to automatically fill in and display the TOTAL_COST field.

It must be simple but I just can't get it to work and it's stopping me from sleeping :mad: I have found myself doing Access in my dreams which is not good!!

Any tips much appreciated - Thanks
Mike

PS I have written 2 queries which will calculate this for me but dont seem to be able to action the queries from within the form (if that helps!)

View 3 Replies View Related

Insert Into Another Table When A Field In Form Update

Apr 6, 2006

i have a form, which is based on query from multiple tables.. in this form i want to add a unbound textbox in the detail section.. whenever a user leaves this records, the system should insert a record in another table with values from this record including this field... how to do this?

the record is from a query where as the field is unbound, still we need to insert the values from record and this field at the same time into another table..

example: i have item master and item production table.... from joining these table i get a query which will give me item A and prod qty .. when i use this query as form datasource, the detail section has these two fields.. now i want to have an unbound field budget.. so i have the followig in detail sectin

item A production qty budget qty

when users enter values in budgetqty field and moves to next recod,, the system should insert into another table xyz values (itemA, production qty, budget Qty)

how to do this..which event will be good.

urgent reply is expected.

View 2 Replies View Related

Forms :: Update Table Field Which Is Not In Form

Dec 2, 2014

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?

View 1 Replies View Related

Tables :: Export Table In Excel Workbook With Separate Sheet Based On Value Of Field

Nov 7, 2012

How to export ms access table into excel workbook with separate sheet based on a value of field?

For Example:

I have One Table with three fields

Name Address Company
Steve a Apple
John b Apple
Josh c Dell
Pete d Dell
Pat e HP
Jacob f HP

Output in Excel(list for Employee by company):

Sheet 1 Sheet 2 Sheet 3
Apple Dell HP
Name Address Name Address Name Address
Steve a Josh c Pat e
John b Pete d Jacob f

View 3 Replies View Related

Queries :: Parameter In Query By Two Separate Form Field

Oct 31, 2013

I want to set parameter in my select query with or function as in detail.i have two form with same field

1-with field A
2-With Field A

now i want to select data for a table .with a parameter select with Field A in form 1 or Field A in Form 2.How can i do it

View 5 Replies View Related

Queries :: Update In Table From Same Table Based On Criteria Column In Same Table

Sep 29, 2013

How can I update (some columns) in a table from the same table based on a Criteria column in the same table.

View 2 Replies View Related

General :: Separate Image Per ID On Table / Form

Sep 24, 2014

I have the need to put a separate image per ID on my table/form or a separate link to an image I can put an image on my form but its does not change when I change my clients its the sam e all the time, I have tried linking to an excel book but for some reason it will only show 10 fields, (and it is linked).

I don't mind creating a standard windows folder with my images in but would need to be able to link to each one from my form.

View 8 Replies View Related

Forms :: Search Form With Each Field In Separate Text Boxes

Dec 8, 2014

i want to search a phone number in contact information table with column names - number, name, address, dob, gender, f-name, m-name etc and show them in a form with each field in separate text boxes in access 2010.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved