I am sure that in earlier versions it was easy to write the result of a calculated field on a form, back to a table, but I can't find the method in the help file for the current version of Access.
Would appreciate any help.
Hi all.
I have a TableA with 3 fields: FieldA, FieldB, Result. A Form1 based on these fields. User will put data in FieldA, FieldB and calculate in Result. Problem is how to keep result of calculation in TableA?
Thanks
Hi there,
I've read through the forums on saving the results of a calculation into a field is a bad idea. I somewhat understand the reasoning for it. But I don't think such situations arise for everyone.
But I have gone with the suggestion and created an updatequery, which simply goes through and updates the calculated value into a table. Now each time the updatequery is run its asking for permission as to the fact if I'm sure I want it to be run. How do I turn this option off. And should I call this updatequery afterupdate or beforeupdate?
Thanks
I am trying to update a memofield from a form.My file.. sign_midi.asp has this partial code.First I pick up the record I want to update.
And then present it in a form.
"
<tr>
<td align="right" height=10 valign="top"><b>Beskrivning :</b></td>
<td align="left" height=10 valign="top" width=250><TEXTAREA WRAP="soft" name="M1" cols="65" rows="8"><% = rs("text")%>
Here's the form I'm trying to Create:
VEH POS NAME
A21: TC: CPT Somebody
G: SGT Someoneelse
D: PVT Noone
A22: TC: SFC Smith
G: SGT Jones
D: PVT Doe
and so on and so forth.
The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?
Dim strSQL as string
strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME
FROM tblPERSONNEL
WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"
Hi ..
I have an issue I do not know how to tackel. I have a select query that selects from three tables to calculate the commission for each transaction. The query is working just fine.
My problem is that I need an additional function ... What if I need to charge a special commission for that specific trade ?
I need to ammend the commission based on a figure I input in a text box from the form view.
Example:
- Commission (calculated by the query) is 100.00
- Special Commission (which is a text box on the form) is 80.00
Then Commission field = 80.00
PLEASE HELP .. Please let me know if you have any other suggestion in tackling this problem. Thanks
Query
Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",IIf([Blotter]![Quantity]*[Clients]![USD_2]<[Clients]![USD_1],[Clients]![USD_1],[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or [Shares]![Currency]="EUR",IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))
Here's what I have so far...and it works fine; however, some results have decimal places; i.e., 2.1666666.
I just need the result to be the whole number. I can't seem to get it to come out that way...I've used "round"...but I must not be putting it in the right part of the formula.
TEST: IIf(Year([EndTerm])>Year(Date()),0,IIf((Year(Date())-Year([EndTerm]))/[Renew]<1,1,(Year(Date())-Year([EndTerm]))/[Renew]))
Thank you for any help!
Carol
I have a formula box in my form (textbox) called InventLeft that would calculate product in my inventory. I'd like to make this box flash if the result in it is Less than 2..This way it would catch attention to my user to order more product..Is this possible? Thanks in advance..
View 5 Replies View RelatedI am creating a driving school database and have four different tables. Student, Instructor, Lesson and Lesson Type.
In the footer of my subform which works out what lesson type the student has taken and from which instructor, I have created a calculation to multiply the number of hours a student does to what type of lesson they take.
=([LengthOfLesson]*[cost]) (This works ok)
I then want to add all of these options together. I have tried:
=sum([OverallTotal]) in the footer (This doesn't seem to work)
and then
=[Booking].[Form]![OverallTotal] to show it in the form
The name of the subform is correct (booking) and name OverallTotal is also correct but I keep getting #error message.
1. I created a table that contains information about people and their details (mainly numerical info).
2. I created a form containing a command button and a label.
3. I have written a VBA script under the button so that when the button is pressed, the result of the calculation appears as the caption on the label.
My problem is...How do I get the script to run so it does the calculation for every record and places the result as a field in a query.
Hi
I have a small database written in access 2000 which has be compiled as a mdb, this resides on a file server and can be run from a number of workstations on the network .
It has been running ok for 2 years until a couple of weeks ago, on one particular pc the database opens and you can do reports etc view exist data as normal ,but you cannot write to a table ( these are contained in a second table only data base )
if you run the program on any other pc workstaion it works fine as it has for 2 years on this problimatic PC.
it must be something on the particular pc , we have reinstalled Access 2000 to no advail
Any Ideas on this
Regards
Sam
a have a form with a combo box that displays 3 values (columns)
in a bound form i can display the second value in a text box with the following command
=Forms!MAIN!field_2.COLUMN(1)
but i can't write it to the table
my main table has 3 fields
field_1
field_2
field_3
i have a secondary table that holds some values and is been used as a lookup list to field_2
my secondary table has 3 fields
field_a
field_b
field_c
the field_2 of main table takes value from field_a (the lookup list shows all the fields (a,b,c) and i want in text field lets say field_3 to take the value of field_b with this code
Forms!MAIN!field_2.COLUMN(1)
The problem is that i want the value of Forms!MAIN!field_2.COLUMN(1)
to be written to a field of the main table :( and i cant
Thanks in advance
please help me
:eek:
I have a form based on a query where I want to document the status of certain records. The original data is on a SQL server so I am linking to it in my query. I have a local table with the added fields to document my review and status. My query has both tables in it and are linked by the report number. So I have join properties to show all records from the SQL database and only the records in the local table that match. So initially the data in my local table will be blank. I want to use the form to add comments on the status. The problem is, when I type in the form fields nothing happens. It's like the query is confused and won't let the form write back to the local table.
So my question is, do I have my query set up properly so that I can read the fields from the SQL database and yet write in my comments to the local table. I am using a select query.
Thanks for the help.
Jim
I'm having trouble with some code. I have two combo boxes on my form. When a user selects a value in the first combo box, I want it to find the information for the corresponding record in the table. Then, in the second combo box, if the user selects one of two values (two full names), I would like that value to be written to the table for the corresponding record of the first combo box. I'm not sure how to transfer data from what the user selects in the second combo box to the table. I've inserted a command button called "Update Table". When user clicks on this button, the table should be updated with the 2nd combo box's value. Please help!
View 1 Replies View RelatedI have the requirement to write EACH record from a table to its own CSV file with name of the file being combination of 2 fields
So let's say I have Table1 with 3 columns (Field1, Field2, Field3) with following content :
Field1 Field2 Field3
AA 1 ABC
AA 2 DEF
AA 3 GHI
I should get 3 files with names AA1.CSV, AA2.CSV and AA3.CSV and each file contains its respective row from the table.
I tried to do it with DAO Recordset, but I do not find a way to write only the current record from recordset while looping.
See below the code I was using, but issue is that code does succesfull creates the 3 CSV files as per above example, but in each file it writes ALL 3 ROWS instead only the respective ROW.
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strFilename As String
[Code] ....
I have a form which has an unbound text field which is a calculated filed showing a date which is x months after a separate field on the form.
I need to write this field back to a table in the database when an "add" command button is clicked.
The table is called Assets
The form is called Assets List
The text field on the form is called txtWED and needs to be written back to the table "Assets" in the field "Warranty_End_Date"
If possible can I add this as an expression on an "add" command button?
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
View 13 Replies View RelatedI am the first to admit I struggle with looping through records.
I am trying to loop through a table and collect external income data and write it to a new table.
My issue may be the way the table is laid out. ???
The attached pictures show the tables. The tblExtInc is where the data are. I am trying to create a table that looks like tblExtIncYr that shows each year and separate entries by item.
How would I loop through tblExtInc and grab ex:$2000 every year for upto 100 years but only get the $1000 for year 6,7,8 only?
see below the code . The select statement searches the Printpoolno value from the top to bottom in table tblmaster. As in my table tblmaster there are thousands of records and it takes long to search for that Printpoolno from the table . Is there anyway we can write a query that will search the table from bottom to top as the Printpoolno will always be in the bottom records and not in the top records.
Code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
[Code].....
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.
Here is my challenge:My TableI has the columns A, B, C and D which are populated, for example, as follows:
TableI
A B C D
1 2
2 6 4 3
1 7 5 9
1 2
2 5 8 5
etc.
I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields C and D (same as above). When saving data entry thru the save button of the FormII, fields C and D will be naturally saved on the TableII. Well, I also want C and D info updated into Table I as well, but only when field A=1 and B=2.So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it with the values of the fields C and D in every record WHERE A=1 AND B=2.For instance, assuming C=& and D=%, the desired result should be as follows:
TableI
A B C D
1 2 & %
2 6 4 3
1 7 5 9
1 2 & %
2 5 8 5
I did not find any examples in the net including multiple criteria..Here is what I wrote unsuccesfully:
Private Sub BtSalvarFrmII_Click()
CurrentDb.execute "UPDATE TableI"
Set FieldC = Forms!FrmII!FieldC.value AND Set FieldD = Forms!FrmII!FieldD.value WHERE FieldA = 1 AND FieldB = 2
Docmd.save
Docmd.close
End Sub
What would be the correct syntax?
Does a (the) .ldb file have to be closed for another user on a network (separate FE linked to network drive BE) to read/write info to a table? Or even select info from a table?
View 6 Replies View RelatedHi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
Actually I have a small form of customer details, that i made in excel, the field name mention below,
Customer Details Table
First Name
Last Name
Contact Detail
Address Detail
Postal Code
Last Purchasing Date
Remark
Now i want to make a search form like this
Search Form
Contact Details
& the result is show which I insert the contact number.......
I have two tables
1) has email address, and Store ID
2) has multiple records per store
I need to write a procedure to send a separate email to each store that contains the records found in table 2 ( excel format).
I split my db to front-end and back-end. 20 users are using the database. But now I have the problem: In my admin front-end db I created a new table and some reports to the table, but this new table I have only in my front-end db and it doesnot appeares in back-end db - so that other users has no data in hte report for the new table. How I can create new table to be in back-and table too?
View 5 Replies View Related