Forms :: Insert A Field That Sums Up / Aggregate All Values Of Field
Jul 25, 2014i want to add a control in that form that sums up and aggregate all the values of field called [amount] based on the value of [Name_Patient] as criteria
View Replies
ADVERTISEMENT
Insert Calculated Values In A Table Field?
Mar 17, 2014i have a subform which contains a lot of calculated values. fields which calculate values are located both in master form and subform. i use those calculated values in various reports where i have to insert formulas again. so i have decided to insert calculated values in table directly. i can use update query but i am unsure how to trigger it; and how to keep fields updated everytime a value is changed.
main form fields = [gsm] , [rate]
sub form fields = [pages], [qty], [gsm]*[rate]*[pages]*[qty]
General :: Passing A Field Value To Insert Query With Values
Jun 2, 2014I have a button on a subform that becomes visible if there is no records in the source of the subform. When clicked I want to run a query that will insert a record on to the source of the subform. There is one field in the query that I need to get from the parent form.The first part works OK - the button is visible when the source file to the subform for this main form record, is empty.
If I run the query against the source file it inserts the new record after it has asked for the value of the variable field.My problem is that when I try to run the query when the button is clicked It can't find form![ClientFileFrm]![ClientId]..This is my code on the subform
Private Sub Form_Load()
If Me.RecordSource <> "" Then
If Me.Recordset.RecordCount = 0 Then
Me.AddSettingsButton.Visible = True
[code]...
The ClientFileFrm is the main form.I can't seem to reference the clientId variable back to the main form.
Modules & VBA :: Insert Unbound Field Values Into A Table?
Mar 11, 2015I am working on a timesheet application which allows users to insert multiple timesheet entries in a grid style format. the first row is visible and to add another row users click on a command button which makes the next row of fields available and ready to fill in and so on and so forth. I need the fields to be unbound and then when the user clicks on a save button for example then it inserts those values into the relevant table. Table name is tbltimesheet, field names are id, companyname, project, activitydate, activityhours, activitynotes, username, userid
View 2 Replies View RelatedQueries :: Sums Up All Previous Lines In A Field
Feb 9, 2014I have a query that displays the difference between the number of scanned and processed invoices for each date (invoices scanned minus processed). Now I would like to make a query that for each date based on this shows the total backlog accumulated.
Example table:
The first column contains the date, and the second number of invoices scanned minus invoices processed.
2014-01-02 53
2014-01-03 -15
2014-01-04 24
Would it be possible somehow to make a query that for each lines shows the complete sum of all previous lines?
Based on the above data the result should be:
2014-01-02 53
2014-01-03 38
2014-01-04 62
Or would I have to write some VBA code to create some kind of temp table?
Forms :: Allowing Different Values In One Field Depending On Selected Values Of Another
Nov 22, 2013I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
[code]...
Forms :: Go To End Of Field And Insert New Line And Date
Jan 2, 2014I have set up a customer database for our small electronics company and i have built a form called contact history. In the form i show a few details about the customer (name phone number etc) but the bulk of the form is a large text box for a contact log.I would like to have a button on the form that takes me to the contact log field, inserts todays date and allows me to update whats been said to the customer etc. I have it working using setfocus and date functions however it erases any information already in the field. Is there a way to skip to the end of the field and insert a new line, then add the date and allow me to begin typing?I would like the end result to look like the following.
16/02/2013: Introduced Myself and the company, spoke to joe bloggs and agreed to call back on 28/02/2013.
28/02/2013: returned call to Joe Bloggs but was out of office, will call back 01/03/2013.
01/03/2013: Spoke to Joe Bloggs and have arranged for him to visit us on 10/03/2013.
Need Help: Memo Field And Aggregate Functions
Jun 21, 2005I know SQL well enough, but I cant seem to get my query to work. Basically, I am trying to display a series of posts, with the number of comments on the side.
I have two tables, Entries and Comments, which look like this:
Entries
---------
IDTitleContent (memo field)
1CatsSomething about cats here
2DogsSomething else about dogs here
3RodentsMy pet rat runs in a wheel
etc.
Comments
--------
IDPostIDComments
11My cat's name is mittens
21I taught my cat how to throw a curve ball
33Rats like squeak toys
42Nobody likes dogs
53Bunnies make good pets
61Cats are witches in disguise
Comments.PostID corresponds to Entries.ID on a many-to-one relationship.
I want to run a query that will join the number of comments (which is a Count of each row in the Comments table where Comments.PostID = Entries.ID) to the existing table, so that it will look like this:
Entries (with #ofcomments joined)
----------
IDTitleContent (memo field)#ofcomments
1CatsSomething about cats here3
2DogsSomething else about dogs here1
3RodentsMy pet rat runs in a wheel2
etc.
Usually, this would be a really simple SQL statement, but as I am using a Memo field, I am getting a "cannot use Memo field in aggregate function" error.
I'm not sure what to do, any help would be appreciated :)
Domain Aggregate Functions To Count Occurrences Of Specific Value In Field
Jul 8, 2012i need domain aggregate functions to count the occurrences of specific value in a field, and when it exceeds 1, display null. or 'group by' subqueries with count() to see if you should populate the field or not.
View 3 Replies View RelatedSum Field Values Only If Another Field Values Meet Criteria
Mar 20, 2015I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center
Job Type
Hours Paid
Pay Code
Earnings Amount
20530
Security Guard II
7.5
REG
190.37
[Code] .....
Forms :: Passing All Field Values From One Form To Another
May 28, 2015I have "LossForm" to record loss of inventory items due to damage, theft, etc. It has "Loss Subform" for input of multiple items. The row has a calculated field "TotalLoss" (from qty * itemcost). The footer of subform has unbound text field =Sum(nz([TotalLoss])). This all works fine. The problem I have is that I need to pass the total to another form. I want to have a pop-up form to use some of the field values from the Loss form. I have been able to pass all of the field values except for the TotalLoss.
LossForm Close Event: "DoCmd.OpenForm "Journal", , , , acFormAdd, , Me.LossID & ";" & Me.LossDate
pop up form:
Set frmPrevious = Screen.ActiveForm
Me.TransactionID = frmPrevious.LossID
Me.EntryType = "Loss"
Me.Date = frmPrevious.LossDate
{ Me.Amount = frmPrevious.TotalLoss doesn't work }
DoCmd.Save
End Sub
I also tried to setup a global, class, and module variable but keep getting error message of undefined variable.
Forms :: Values Change According To Selecting Value From Another Field
Apr 16, 2014I am working on a school database, today I am working for fee collection form and I am facing trouble.In attachment At the top right area the hidden field I would like here changes according to selecting Fee Type under Fee Detail. I want to achieve this,
If feetype is ABF then Hidden field = 1500110
If feetype is ASF then Hidden field = 1500120
If feetype is EXF then Hidden field = 1500130
If feetype is MTF then Hidden field = 1500140
If feetype is RBF then Hidden field = 1500150
How I can perform this ?
Table structure is
(tblstudents) Contain students data
(tblFee) Contain, Student Id linked with student table, month, year, amount, date, narratives and FeeType that looup values from FeeType tbl, this hidden field also in tbl fee.
The codes that are 1500100,1500120,1500130
These are necessary for GL entries that's why I must have it on my form. I had as well apply method of Default value but in that case I only present only a single value, but I want that in hidden field values take changes according to the fee type.
Limit A Field To User Defied Values Depending On The Input Of Another Field.
Nov 23, 2005Hey all,
I have two fields 1 & 2
field 1 is a simple combo list of user defined values ie A, B, C or D
Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, C and D. i.e
A could have a,b,c,d or e
B could have c,e,f,g or h
C could have a,g,h,i or j
D could have v,w,x,y or z
I would field 2 to have a combo box which only displays a,b,c,d, or e when A is chosen in field 1; c,e,f,g or h when B is chosen; etc
How do I do this?
Cheers all,
Matt :confused:
Tables :: Linking Multiple Field Values To A Field Selected From Combo Box
Feb 16, 2014I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)
Tables:
* Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player)
* Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box)
* Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box
* Venue - similar to Competition table
* Opponent - Similar to above two tables
* Match - MatchID, Competition, Venue, etc (form corresponding to table attached)
Forms:
* Player form
* Match form
Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).
Forms :: Change Display Of Field Values On Combo Box
Feb 17, 2015I'm trying to sort and filter a continuous form. I want to sort by descending and ascending, but I also want to have a combo box for filtering. For example, there a combo box for sorting with Value List as the RowSourceType, the list would be "Ascending" and "Descending", and there should be another combo box to choose the field to be sorted with RowSourceType as Field List from a query.
My field names are: "LastName", "FirstName", "MiddleName". But I want them to appear as "Last Name:, "First Name", "Middle Name". I want to be able to change the display of the field names within my combo box which should come from my query. How is that possible? I already tried to put caption in the properties but it didn't work.
Forms :: Limit Values Of Combo Box Based On Value Of Another Field
Aug 25, 2014Field B is a combo with the options of : 100% Availability, 75% Availability, 50% Availability, 25% Availability, OR Not Available
If I choose "Present" in Field A, I want the "Not Available" value to disappear as an option.
Forms :: Linking Form Operations To Values In A Field
Sep 8, 2014I have a combo box field called "investigations" on a patient database which can allow multiple values. Each of its values represent the different investigations a patient can do. Now those investigations are tables and forms in the database. how can i program the database to simultaneously open these forms "on update" of that field-investigations?
View 1 Replies View RelatedForms :: Search For Multiple Values In Single Field
Apr 21, 2013I have a table in which there are approximately 15k records. The name field has approximately 2500 unique values. I need to search the table for multiple names and return the results from the table. This would then be used as a report. Due to the users being very novice, a form is needed for the search.
Example:
Search for Smith, John; Wilson, Bill; Jones, Jeff
Return
Name Year Brand Make
Smith, John 1999 Oldsmobile 88
Smith, John 2001 Chrysler 300s
Wilson, Bill 1994 Porsche Carrera
Wilson, Bill 2007 Kia Sporty
Jones, Jeff 2004 Chevrolet Camaro
Jones, Jeff 2011 Chevrolet Impala
My knowledge of forms is basic, however, I can be a quick learner if on the right track.
Editing Field Values That Get Summed Up - Resets Total Field
Apr 7, 2015I have several fields that I fill in on a form and they all need to add up in the last field. The first way that I tried this worked good for the initial data entry on the form. However if I need to edit one of the fields in the form then it resets the TotalCost field and I have to enter all of the numbers again. Is there some way that I can make this work? Or is this an Access quirk I will have to live with?
I tried to do this two different ways.
Here is the code for the first method:
Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
[Code] .....
This way works great the first time that I tried it, I had to re-enter info in all the fields if I wanted to change one.
Here is my second method:
Which didn't work, i received an error message:
"The expression After Update you entered as the event property setting produced the following error: Invalid outside procedure."
And then nothing changes.
Code:
Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
[Code] ....
So I know this second method is incorrect.
Concatenating Multiple Field Values Into One Field Separated By Commas
Nov 18, 2014I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:
[Field1]&", "&[Field2]&", "&[Field3] and so on.
However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?
Code To Disable A Field Based On The Values Of Another Field
Nov 10, 2005If I have the following Code to disable a field based on the value of another field:
Private Sub lstAgreementType_AfterUpdate()
If Me.lstAgreementType.Value = "BN" Then
Me.txtSenateAandCDate.Enabled = False
Else
Me.txtSenateAandCDate.Enabled = True
End If
End Sub
But I also need it to disable the field is equal to "BA" or "BT" as well, how would I add that to my code?
Forms :: Passing Operator Values To Criteria Field In Query
Mar 11, 2014I have a query which contains figures. i have a search form based on this query and need to search using comparison operators such as <10000 or >500.
Is there a way to pass these value to criteria field in query ?
Forms :: Changing RecordSource Of A Form Based On Field Values?
Feb 16, 2014I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2.
They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).
But right now my form always adds to the table Box 1-1 - I can't get it to switch.
I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing. I'm running Access 2007.
Code:
Private Sub Switch_BeforeUpdate()
If Me.Rack.Value = "1" And Me.Box.Value = "1" Then
Form_Add.RecordSource = "Box 1-1"
ElseIf Me.Rack.Value = "1" And Me.Box.Value = "2" Then
Form_Add.RecordSource = "Box 1-2"
[Code] ...
Forms :: Null Field Values In Multi-select Listbox
Jun 15, 2014I have a multi-select listbox (called PotentialItems) which behaves perfectly unless one of the fields has a null value in it, in which case I get an error 13, "Type mismatch" in the following code. It is valid business-wise for column 9 to be null.
Code:
Dim varItem As Variant
Dim dblTargetPrice As Double
With Me.PotentialItems
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
dblTargetPrice = Nz(.Column(9, varItem), 0) '<----- Error here
End If
Next
End With
Forms :: Sort Values / Text Based On Active Field
Feb 5, 2014In a form is there a way to change sort order based on which field is active?
I'm using a Split Form which gives me the ability to sort by clicking on the header name ans selecting sort. This slowing things done since my workload is heavy.
Forms :: Change Button Enabled Status On Field Values
Jun 22, 2014I have several buttons on a form and I want to set enabled = false on one of them when I open the form - that's fine
I have two txt fields - ReceiptNo and BankPaymtId if the both have a value I want to make the button enabled = True
This is the code I have used on the BankPaymentId On Dirty event
Private Sub BankPaymentId_Dirty(Cancel As Integer)
If Me.[ReceiptNo] <> "" Then
If Me.[BankPaymtId] <> "" Then
Me.CreateOutlay.Enabled = True
End If
End If
End Sub
This is the one for ReceiptNum on Dirty event
Private Sub ReceiptNum_Dirty(Cancel As Integer)
If Me.[ReceiptNo] <> "" Then
If Me.[BankPaymtId] <> "" Then
Me.CreateOutlay.Enabled = True
End If
End If
End Sub
Nothing seems to be happening.
Even the Form on Dirty is not working now....