Hi Guys, I don't know Access very well but I know enough to generate a 2 dimensional database. The problem I have is that I have been asked to look at a travel agents database to create some additional letters. The problem is that they use a form which has an underlying data table called customers. When they create a record in the form all fields in the customers table are filled in except those on the form which derive their data by calculating two or more fields i.e Date Due is [Date of Travel]-70 (days), Balance Owing is [Cost]-[Deposit]. None of these derived fields fill in their corresponding fields in the customers data sheet.
I would welcome any ideas please. Thanks in anticipation
I need to figure out how to have two calculated fields written to the database table instead of just being displayed in the form.
The user selects a number from 1 to 5 in the first field, and a number from 1 to 7 in the second field. The first calculating field multiplies these two numbers and displays the result. The second calculating field determines where the resulting number fits in three ranges of numbers and displays the result as Low, Medium, Or High.
My problem is the calculated results are be displayed in the form but not being written to the table. I cannot create a report to display all the High results, etc.
How do I update the calculation field formula to write the results into the table so I can create queries/reports on the calculated data?
Can anyone tell me what's wrong with the below statement ? I'm trying to pull the contents from a text box in a form. The text box is supposed write the contents to a field (memo data type) in a table. Thanks ! ! !
Hi all ... been awhile since I have had to create a database so I have gotten a little rusty. :eek: I have a form where some of the fields I have formulated to calculate an amount. Example... =[GrossAmt]*[FeePercent] This is calculating into the form correctly but not writing to my table for that field. What am I doing wrong or missing here?
I've read over and over that calculated data is bad, that's fine with me... but here is my dillema...
I have four fields that interact with my calculation in a single form. There is only one table in the database.
AmountF (The stored data in the table) (amount financed) FC (calculated by =[amountf]*.25) (finance charge) Amount Paid (used in calculation of total due) total due (calculated by =[amountf]+[fc]-[amount paid])
The math already works, everything works. I can choose to print the form and it looks great. However, I would like for it to (when I'm done printing the form) somehow save the total due data to the AmountF column in the table. AmountF is in the same line as all the other information, so I would like it to not create a new record just update the old one.
All four of these fields have a text box in the form for themselves.
I am creating a production line database for use on a touch screen (no keyboard or mouse) which has to be really easy to use. At the start of the week i got to the point were i thought i would test what i had done to date and have come across a serious issue.
I thought that the data from the forms i am using to input the data to the main table tblProductionDetails would write into individual records but although new records are created at the appropriate point e.g. when the comand button labelled START on frmDetailsCorrect is clicked the data is not writing to the new record that is created. I thought it would be an easy thing to solve but 20 hours later and much mucking about with DoCmd.Goto's etc i am no further forward. I have attached what i have done in the hope it is something obvious and somebody will be able to point me in the right direction.
The way the db is set up is that frmDayStart needs to be opened the command button clicked and then each form opens once the OK comand button in the top right of each opened form is pressed.
It is still a work in progress so a bit rough and ready but i need to get this fundimental problem sorted before moving on.
Any help appreciated (i am not looking for somebody to do the work on the DB just point me in the right direction or point out what the error might be).
I hope this makes sense? I have a main form with a button that opens a new form. On the new form I have 5 fields. 1 of those fields is the Employee ID which I have the form getting from the main form (IE the Control Source is =Forms!Search_Employee!Employee_ID )and the other fields are bound to my table for the new form. I am unable to get the Employee ID to show in my table, it is not updating or writing the value to that field. Not sure if there is code or how I should or can get this field to write to my table. Any Ideas or be very appreciated. Thanks anyways guys I igured this one out. I was able to set the dafault value to point ot my main form so that my Control Source could point to my table and it worked.
I'm trying to write code which writes text into text boxes on a form depending of certain content of other text boxes. The names of the text boxes are all very similar
F.i. R1, R2, R3 ...... R12 if the content of these boxes are empty then the content of the corresponding text boxes VR1, IR1, VR2, IR2, VR3, IR3.......VR12, IR12 should also be empty.
In fact I am trying to write something like this
DO UNTIL i=12 if me.R(i).value = "" then me.VR(i).value = "" me.IR(i).value = "" endif LOOP
But this isn't working. The solution below works but isn't a very nice one, writing 12 times the same code
if me.R1.value = "" then me.VR1.value = "" me.IR1.value = "" endif
if me.R2.value = "" then me.VR2.value = "" me.IR2.value = "" endif
I need to create a table with names of controls on my forms.
I'll use that table to apply user rules.
Any solution to write the table automatically using VBA code ?
I know how to populate a combobox...
DoCmd.OpenForm YourFormName, acNormal, , , , acHidden Dim c As Control For Each c In YourFormName.Controls [Control-Name].RowSource = [Control-Name].RowSource & c.Name & ";" Next c
I am very inexperinced with Access, please be kind. My problem is that from the form view the simple division of 2 #'s are placed in the correct text box I selected, but I am unable to get the value to enter into the data table. Please help.
I have a textbox on a form that displays a calculated number with regards to input from other text boxes.
I need this calculated number to update to a corresponding field within a table.
I have a button that updates the information, but the calculated field will not update.
I have an "=" expression written that does all of the calculating. When I put this in the 'control source' it calculates and displays on the form perfectly, but will not update the table. If I change the control source to the field within the table and then move the "=" statement/expression someplace else....the field is blank on the form, but I can manually input a number and it will update to the table.
i have a total in an invoice with is a tabulated form with totals in the detail footer I want to post the calculated total (control) to a table. How ia this done.
What I'm trying to do: I have created an unbound field within a subform's footer to calculate the average of the displayed record values. The subform is in datasheet view. The records are returned based on a query with a relationship between two tables. I need the calculated data (which I currently have displayed on the main form) to populate within the master table.
I can't figure out how to automatically do this. I created a simple command to get it there, but I'd like the user not to need a button to display a calculation.
Main form based on TableA; subform based on TableB.
Here's how I have it set up (Btw, I suck at SQL I just figure this is easiest to read):
Query SQL looks like this: SELECT tblB.Field1, tblB.Field2 FROM tblB.Field1 INNER JOIN tblA ON tblB.Field1 = tblA.Field1
Main form: unbound txtbox = [Forms]![frmA]![subfrmA].Form.[txtAvg]
The field that is averaged is tblB.Field2. There are potentially a bunch of records displayed in the subform.
I need that [txtAvg] field to save to TableA. My command button is simply Me.tblA.FieldName = Me.unboundtxtbox. I'd like to do away with that.
I tried to use the on current or on load event for the main form; but the calculated field value is 0 until it calculates it. It seems like there is a short delay before the value shows up on the form at which point the on current or on load events don't pick up the calculated value; just the 0 that is initially there.
I am trying to change the data type of a field in a table from calculated to something else. It gives me the error "this data type cannot be changed once the field has been saved"
OK, I've tried everything I can think of with this combo box. I've searched the internet for days, even bought some big fat Access reference books and read them cover-to-cover, but I still can't get my database to do what I want. This newbie would *really* appreciate some help...
I am trying to create a database which I can use to store client’s advertising expenses. I currently have three tables: tblListings (which stores basic information about each client, with the field ListingID as the primary key); Advertising Rates (which stores the different types of advertising products and their prices, with the field ProductNumber as the primary key); and tblAdvertisingExpenses (which stores each “order” of an advertising product, including the client’s ListingID as a foreign key, date, the type of advertising, and the total cost - the primary key is an auto-numbered field called OrderNumber).
I have a main form called frmListings, which displays the client’s information from the tblListings table, and includes a subform. I wanted the subform to show only the advertising expenses corresponding to the ListingID (ie. client) displayed on the main form, so I based the subform on a query which displays all records from tblAdvertisingExpenses with a ListingID that matches the ListingID displayed from tblListings. So far so good - the records displayed in the subform change correctly when the record in the main form changes.
The subform returns info from the following fields in the tblAdvertisingExpenses table: Date, ProductNumber, and TotalPrice. I want users to be able to select a type of advertising from a combo box on the subform, which looks up the ProductNumber and Cost from another table (tblAdvertisingRates), and then writes the ProductNumber for that type of advertising to the ProductNumber field of tblAdvertisingExpenses, and writes the corresponding Cost to the TotalPrice field of tblAdvertisingExpenses (and the corresponding controls on the subform). The reason I want to write the Cost from tblAdvertisingRates to tblAdvertisingExpenses is so that I can update rates for types of advertising in the future, without having the change cascade through records where the old price has already been paid for the advertising.
My problem is: a) how to get the combo box to write these various values to other controls on the form, and b) how to write the same values to fields on the subform’s underlying record source.
I’ve tried having the combo box look up all the fields from the underlying table (hiding all except the description of the advertising) and setting the bound column to the field with the price. However, the price is not the uniquely identifying field, and the combo box only writes to the TotalPrice control if all the advertising options in the list have a different price.
Sorry if this is terribly confusing. I can provide more specific details (and screen captures) of the tables, queries, forms, and relationships (if I haven’t provided enough specific details already), and would really appreciate any help that anyone can provide. Help?!
I have been reading various threads about this subject and I know that one shouldn't store a calculated field in a table.
I believe this may be an exception to the rule (but I could be wrong). I am making a pricing database that will use a few manual inputs and the rest of the fields will be calculations (gross/profit margin, expenses) based off these inputs. We need to have these calcuated fields stored because they are essential to the pricing decisions and we will have to go back and access these fields at a later time. Another reason for this is because there will be at least one report that will be used to compare prices and calculated fields associated to a certain product by company, program, item type, ect...
I have a series of dates over several months, and I want to write a parameter query which will list only those from the month of August. I know how to write a general parameter query, but I can't figure out how to write one that specific.
I'm running pro bono a linked file database for a wildlife hospital in Australia. I have a Form in which there are several fields for vets to enter data about a wildlife patient. One of these is the Collection Plan (for the patient). This is a memo field from the TBL_Accession. The Collection Plan is written in straight English on the input form, but the data entered into the table is completely reversed, reading "etinuer ot ovra siht pu/p ot ronod". Translation for the line here is "donor to p/up this arvo to reunite".
I am fairly new to msaccess programming and am trying to build a stock control system for my business.
I am happy with everything I have done so far but there is one thing I cannot figure out.
I have set up so that the user can click a button and remove the relevant number from the stock level. The buttons are for removing 1, 5, 10 or 20 (there are reasons for these numbers). However I would also like the button to take information from the form (which is referenced to a query) and write that information, along with an autogenerated id number and the current date/time to another table. This is for our record keeping, so that we can record what is selling well etc and when the last one was sold.
How do I do this?
I have gone through the help files and my books, but I think the problem is I don't know where to start looking.
I also want to add a button that will add items to an order table. Again by taking the information from the form and writing it to another table. With this one a message will appear asking for the quantity to be entered. It will also check the order table to see if the item is already there and advise if it is.
I have a form created from a table and I had to use calculations in some of the fields to automate certain processes. Now, if I pull a report based on the fields in the table - I am getting all except for the fields with the calculations. Is there any way to update the main table - since most of the calculations were done with text boxes?
Is there a way to make some of the fields from my form that contain calculated values carry to the table? Right now the values calculate correctly in the form but when I look at the table the field is blank...
I was hoping to use one form to calculate a value, insert it in the table, and then use that value in another form.
I have a form which calculates alot of numbers. Im trying to figure out how to save the calculation to a table field. Is this possible? Can someone help me with a solution please
I have a check box that gets ticked when 'work complete', it adds the parts/service totals and fills in the 'parts total', 'vat' and 'total' fields.
This works fine but I need it to write the values to the table as well rather than just displaying the values in the fields so reports can be produced etc.
i want to insert a calculated value of a e.g field6 (field4 * field5) in forms into the database table. by using expression builders in my forms. i have made that feild and on click it shows the calculated value but on filling in the rest of the form and clicking on Insert button does not update this calculated field but iserts all other fields.
I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are
ID- number Dispensed Date- date with dd/mm/yyyy format Dispensed Type- text Quantity- no. of days Next Collection Date- date with dd/mm/yyyy format
The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.
Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.
