Tables :: Auto Calculation In Sum Field After User Input (numbers) In Fields
Dec 28, 2012
I have a table, at the table I'v got these fields:
ID | num1 | num2 | sum
I want that the user put numbers at 'num1' and 'num2' fields and then the 'sum' field will calculate automatically the operator (sum=num1+num2).
I've tried to put any combination at 'Default value' of the sum field (all the fields are numbers , also tried to change the sum field to text...nothing works).
I'm getting a message that 'num1' field is not recognize at the table
(I tried =[num1]+[num2], without the '=', num1.table+num2.table , ...nothing works)
I also tried to do it with SQL command but it dosn't work.
There is any way to do it, is it possible? Or other way to do it at least at Form or at Report ?
I have list of candidates shown in report. I need each candidate to have a unique number based on user input. And this number increases by 1 for the rest of the candidates in the report.
Example is below:
If user enters 1132 in the user input box then the numbers shown in report should be as below:
Name Assignment Number
Felicity Thomas 1132 Andrew Sen 1133 Andy Luker 1134
Similarly if number entered is 2345 then the numbers shown in the report will be:
Name Assignment Number Felicity Thomas 2345 Andrew Sen 2346 Andy Luker 2347
I have a form where a user will enter a 11 digit number (12345043456). Using ASP (VB) this string of numbers queries an access database and returns the entry for that specific number to the user.
Unfortunately, the numbers in the access table are an 11 digit number with hyphens seperating the string like this 12345-04-3456 (the ndc # of a pharmaceutical).
So What i need is some code that monitors and modifies the form input so that the hyphens are added in to the string when the user is inputting the data.
If i cannot do this, then is there some VB that I can add which modifies the field in the text file when it is imported into access on a daily basis.
I would like to expand on this questions. I would like to generate a number based on a reference I use, a format like "14/000" where I use "14" with the last 2 digits of the year. However i want the auto number to generate the the last 2 digit automatically when the year changes i.e. when 2015 comes the field generates "15/000" without any prompt from the user.
I run a charity pre-school and have invoices to send out. Some of the parents cant afford to pay the whole amount in one go, so we give them a payment plan. e.g. If an invoice is for a 6 week term we let them pay weekly. So I have a check box on my form to say "are you on a payment plan".
Then - I have an free form input box..."How many payments..." and the answer may for instance be '6'.
What I want to do now is dynamically create/display 6 date fields, to record what the agreed payment dates are.
but maybe the answer is 4, or 7, etc. So I need to create/display the correct number of payment date fields.
I have a field for SSN. If the SSN has a 0 or 00 as the first numbers it auto deletes those numbers. Is there a way in access that when the record is visible the 0's are relpaced so that the SSN will read the 9 digit. Also i have another fiels EIN with 7 digits an i need for there to be 2 9's in the beginning. I was goingto use vba but when i go to yp it i only get .value <
Code: Me.SSN.Value
Value is tho only option to get. IDK y it does this or how to get it to shop if there is a ay.
I am using Access 2002.I am going to be producing a macro that imports a txt file. Once the text file is imported there will be a empty field for a date value. Nothing in the file that is being imported will have a date in it.
I would like to (if possible) to, once the file is in the table, open an input box asking the user for a date (formated as YYYY-MM-DD) and once the date has been entered and the ok button pressed it inputs that date in to all records in the date field.
Hi, im relatively new to access but my problem is fairly complex.
I'm creating a database for a school project, and it is focused on improving the way a flying club runs. I have been able to solve most problems myself, but this one truly has stumped me.
The basics: There are two tables - Instructor Details - Flight Completion
And one form - Flight Completion
On the instructor details table i have a field called 'Hours Flown' The purpose of this field is to show the total hours flown by an instructor. On the flight completion table i have a field called 'Flight Duration' This field is for the duration of the flight the instructor just flew.
I want it so that when the instructor presses a button on the flight completion form, the duration entered into the 'Flight Duration' field is added to the 'Hours Flown' field and then the 'Hours Flown' field is updated with the new total.
I hope that is clear to you, but if not please let me know and i will try and clarify it for you.
I have inherited an Access DB that is supposed to be kept "alive" but not made much better. On a weekly basis I get an Excel CSV that I'm supposed to import into one massive table; that works just fine.
However, there is another query that takes values from a table named "New Users From Import Table" and appends them to the current "User" table.
But before running that query I have to manually update a field called "Display Name ID" with the next highest number from the User table but only the highest value that isn't in the 9000 number range.
For example, my next highest "Display Name ID" is 1144, which of course does not include the records that range between 9000 - 9008.
I had to manually enter 89 "Display Name ID" values yesterday and I know there has to be a way to update that field when I pull the data over from the temp table without having to do it all manually.
Here's the query that pulls the data into the "User" table (after I've manually entered the incremented values).
INSERT INTO Users ( [Display Name ID], [User Type], Organization, [Display Name], [Alias Name] ) SELECT [New Users From Import Table].[Display Name ID], [New Users From Import Table].[User Type], [New Users From Import Table].Organization, [New Users From Import Table].[Display Name], [New Users From Import Table].[Alias Name] FROM [New Users From Import Table];
how to auto increment one field based on the value in another field. What I have is a table with six fields. There is a foreign key assigned to each record and linked to another table. Four fields in this table are number fields indicating accordingly a number for a category, a number for a subcategory, a number for a subject for a subcategory and a number for an instruction for each subject of subcategory. The last field is the text field for each instruction. Numbers are required to be included in the report. Also, number of categories, subcategories, subjects and instructions vary by each record in the the table which this table is linked to. What I'm trying to accomplish is to have numbers in the subcategory to auto increment starting with "1" for each number in the category field, numbers for each subject field would auto increment starting with "1" for each number in the subcategory field, and numbers in the instructions field to auto increment for each number in the subject field starting, again, with "1". I also need these numbers to restart with "1" for each new record in another table.
(if the record in another table is a new/next table)and so forth..I have been looking for anything that could remotely work for me. Every VBA code I've tried is either don't work the way I need it to work, other codes making the field to continue increment numbers if the number already exists in the other field.
I want to use a Form or Report to have the end user enter say a Customer # or the Customer Last Name and then have Access pull and display that record so that the end user can than print all the saved information from that record.
I have a field1 in table1 & field2 in table2. (field1&2 are numbers) After designing the tables I linked them with an ID (which is primary key in table1).
When I created a tabular form based on these two tables & tried to enter data, two problems.
1) Error come unable to update / enter & help says its locked while when I check the property of Locked, it is set as 'No' & Enabled 'Yes'. Why I can't input data.
2) The calculation field2/field1*1000 does not appear in a textbox (where it is defined).
I am building a form to create a user record and at the same time i have some yes/no options which are located in other tables but when i want add a user i cannot select any yes/no options they seem locked?
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
Started a new thread: Rebuilt database and still working with auto number for multi user form. The code I have works for single user but not multi user. Also if someone opens the form and closes without adding anything to the form the number is used with blank record.
This is what I have thus far:
Private Sub Form_Current() Me.[PO] = Nz(DMax("[PO]", "tblPurchaseOrder"), 0) + 1 End Sub
I have a search form that looks up a value in a field and lets the user know if it is there or not, they have now asked to put in multiple values to search... eg. searching containers number, I will put user input box where they can put these numbers
NYKU023561 TRLU102356 TCNU123023
This will search my container number field and show a pop up message box saying.. Please note below NYKU023561 - has been found you cannot use this container TRLU102356 - has not been found please check internal system TCNU123023 - has beeen found you cannot use this container
Below is the basic search I had before which was all I needed.. how can I adapt this?
Code: Private Sub Command256_Click() If (CntSearch & vbNullString) = vbNullString Then Exit Sub Dim rs As DAO.Recordset Dim CNT As String
I have a form in Access2000 with 5 text fields which get transferred to the table for each new record. Is there a way i can "LOCK" one field so that once the user has input that data it never changes until closed. I can already lock the field but once i create a new record the field then goes blank.
I would like a way to auto-generate a pdf when a user clicks a link based on available tables within my database.
Basically i have developed a pricelist database and our reps need a printable pdf version of that available items and there attributes.
I have done some research and it appears that visual studio and crystal reports may be what I am looking for. This is an access web database that is accessed housed in sharepoint.
I have created a query with the parameter for the Domain field. however on the form the user enters this information via a drop down menu. i was just wondering could the parameter box be set to a drop down box as well to save the user entering in the full Domain field name?
I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there.
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10 1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
I am building a form in access and I am trying to find a way where user input isn't possible in the associated textbox when "No" from on option box is selected.
