Modules & VBA :: Sum Of Field Of A Field In 2 Different Subform
Jul 20, 2015
I am trying to create a simple budget planner. So far, I have the following table:
tbl_account [id|acc_type|acc_name|balance|memo]
tbl_expense [id|exp_date|exp_amt|exp_acct|exp_payee|exp_type]
tbl_expense_cat [id|cat_name|cat_type]
tbl_income [id|inc_date|inc_amt|inc_acct|inc_payee|inc_type]
tbl_income_cat [id|cat_name|cat_type]
The relation now are:
[tbl_account:id] ---> [tbl_income:exp_acct] --> [tbl_income:id]
|_______> [tbl_expense:inc_acct] --> [tbl_income:id]
I have 3 subform on the main form:
[account_sf]
[expense_sf]
[income_sf]
I have set all them to continuous. Now I have added 3 account in the tbl_account.
I wonder how to SUM all the income of certain account, and minus all expense of certain account, and have them showed in the subform tbl_account.
Here is the attach pics: Untitled.jpg
View Replies
ADVERTISEMENT
Oct 24, 2013
I want to filter my subform data, to only show records where field A is a higher value than field B.
Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True
This way it doesn't find field B.
Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True
This way it seems to filter all record to the field B value of the first record.
View 5 Replies
View Related
Jul 11, 2013
A subform has 4 fields, State, Location ID, Total and District.For the first 3 fields ( State, LocationID, Total ) the data is obtained using a query 1. This is set up in the design mode..For the last field, District, data needs to be obtained by passing locationID to
Table 2.
E.g. select district from table 2 where ID = @locationID
How can I write a query where locationID is passed as a parameter in the design mode.
View 1 Replies
View Related
Sep 4, 2014
I have a form called frm002_PAF_MonitoringMAIN and on this form I have a subform called frm002_PAF_Monitoring. The subform has a button to another form for contact details called Contact_Details. The Contact_Details form opens with the details of the person who I have selected on frm002_PAF_Monitoring. There are some fields on frm002_PAF_Monitoring that I would like to be populated on Contact_Details when opened, like FirstName and LastName. How can I do it?I have tried on afterupdate event on Contact_Details below but those don't work:
Me!FirstName=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me!FirstName=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
View 11 Replies
View Related
Feb 27, 2015
I have an access 2010 database with an initial form for user login. After selecting their login, users go to the main tabbed navigation form. On the first tab, I have form with a dashboard as a subform containing a field that says "Welcome" with the user's name (obtained from the login). My problem is, users have to click on the subform for the name field to update. How to have the name automatically populate when the login form closes.
View 2 Replies
View Related
Jul 23, 2015
I have a main form and 2 subform. The first subform has a field for %. And the 2nd subform has series of checkbox (Checklist or error made by the student)
Because every checkbox will lower the mark from 100 to x points per checkbox.So, I want that, if the field is not set to 100 yet, then they will not be able to check any box in the subform.I tried, before update of the subform, etc, but none work properly.
View 4 Replies
View Related
Apr 19, 2015
How do I reference a subform DS field to open up a tabbed form on another subform.
Here's what I have.
field one on the Sub DS opens up tab one on another form, as does field two on DS open tab two on another form.
but..
how can I click on "any" field on the DS, run an IF statement, and it checks if that record on the Datasheet, contains information in regards to, IF field one OR field two contains any data, and opens the tabbed form respectively on the other form?
View 3 Replies
View Related
Dec 27, 2013
I have a form containing client demographics. One of the fields is the DateOfBirth. I would like to control the visibility of a subform based on this DateOfBirth field.
I have tried using the code below:
Private Sub Form_Current()
If DateOfBirth > 11 / 11 / 1994 Then subEligibility.Visible = False
End Sub
This works when the form is opened for the first record, but the visibility does not change as I scroll through the rest of the records.
View 4 Replies
View Related
Apr 28, 2015
I have a subform that I use in a few different ways on my database. It has a comment log with a memo field for people to insert their comments. The struggle is some can be long some can be short. Is there any way to have each record height adjust to the length of the memo?
If it cannot be done for the subform in regular use, my main concern is having it formatted for reports that are generated for clients.
I clipped and attached an example from a report. Its not the best example but the extra column height is adding additional pages to my report to accommodate one long comment.
View 4 Replies
View Related
Jun 15, 2015
How to add attachments to a email using a sub form were I store the attachment paths
E.g I have a main form called frmteaminfomer with various fields and continuous subform called attachmentssubform in the sub form I have a field call txtaddress . I have some code that I can pick a file then put its file path into txtaddress field and also have a check box call add to email. so what I trying to do is loop through all the records in the subform and if the check box is true add the files as attachment on the email.
View 14 Replies
View Related
Jun 13, 2013
I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".
The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.
Code:
Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer
[code]....
View 1 Replies
View Related
Mar 18, 2015
I have a form and a subform
Which method should I use if I need to update automaticaly a field in form based on an event in subform ?
Just to be more clear, have a look in the below example.
In first print screen, student "Johnson Nick" has not completed all tests in TestA subform, the form field "DateCompleteA" is blanc.
Once a student completes all tests in Period A, I need the form field DateCompleteA to show the date that he completed the last one. (18/3/2015).
If student has not yet completed all tests in TestA subform the Form field DateCompleteA must be blanc.
Student has not completed "literature"yet, DateCompletA field is blanc
Student completed ALL his test, subform is updated, Form field DateCompletA is updated with the maximum date.
View 3 Replies
View Related
Jun 2, 2014
I have placed an unbound subform on a form. I have the following code which loads in different datasheet to the subform:
'Loads in NewQuery
Me.data.SourceObject = "query.NewQuery"
'Loads in NewQuery2
Me.data.SourceObject = "query.NewQuery2"
etc.... this works fine to show these queries but i want to know how to reference the fields on the datasheet.
for example one of the fields in NewQuery is "ID"... When clicked i want to run code... normally on bound subform i could go for example ID_Click()... msgbox(me.ID)
how can i reference these other fields on unbound???
View 3 Replies
View Related
Jul 18, 2013
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
View 10 Replies
View Related
Aug 19, 2015
I am currently stuck on set focus property. I have a main from with nested subform. I am trying to move the focus from last field of the subform to another field on the main form.
Customers(mfrm)....>Addresses(sfrm)...>Orders(sfrm Add)......>OrdDetails(sfrmOrders)
Now I have a field name [Securedesign] in frmOrderdetails and I want the tab order to navigate to field [CustomerID] in frmAddresses which is a subform to frmCustomers.
View 2 Replies
View Related
Feb 12, 2015
I have a main form and a subform.
Both forms have the field called JobID in common.
Both forms have a field called JobStatus.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
View 3 Replies
View Related
Oct 24, 2005
hi, i would like to be able to prevent a user from being able to change a field (lock a field) in a main form if a field in a subform is complete. (the field in the subform is named: "new_weekly_base" if this is complete then i would like the field: "weekly base" to be locked on the main form. is this possible?, please help.
the main form is named: "SCREEN-MAIN"
the subform is named: "SCREEN-SUBFORM"
(the main form has a button on it which loads the subform.)
ive tried the below code but it doesnt work, any help would be excellent.
-------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If [NEW_WEEKLY_BASE] >= 0 Then
With Me.WEEKLY_BASE
.Visible = True
.Enabled = False
End With
End If
End Sub
------------------------------------------------
View 4 Replies
View Related
Jul 6, 2014
I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.
Something like:
Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function
View 5 Replies
View Related
Nov 26, 2013
I am doing this all as a bound form
My main form got a field named 'PersonID'.
My subform also got a field called 'PersonID'
The subform 'PersonID' populates however the mainform 'PersonID' does not.
I just need to write code, or maybe do something that will copy the number from subform and paste it in the mainform field, maybe at the click of my save button.
View 8 Replies
View Related
Nov 2, 2012
I need to choose a value, which will be a number from one field (TaxRate) and have it update a field on my subform which also is a number (TaxRate)
TaxRate set subform TaxRate to value chosen
main form is named AddProducts and subform is named AddProductsSubform
View 14 Replies
View Related
Jan 10, 2014
I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.
I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".
So far, my code looks as follows:
Code:
Private Sub Command16_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlString As String
Dim dataDay As Byte
'Open connection to current Access database
Set db = CurrentDb()
[Code]...
I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.
View 12 Replies
View Related
Jun 1, 2006
I have a mainform. I select from a couple of combo boxes. Based upon these selections, it pulls up appropriate info in subform #1.
There is a field in subform #1 which I want to populate a field in subform #2 with (automatically).
What kind of "event" is this? After/before? I tried everything, nothing works.
Let's try these naming conventions:
Main form = mainform
Subform #1 = subform1
Subform #2 = subform 2
Field Subform #1 = fieldsubform1
Field Subform #2 = fieldsubform2
Help me write the code and help me locate where to put that code. It's been troubling me for a long time.
Thanks, I hope someone can help me out.
View 2 Replies
View Related
Sep 11, 2013
I am trying create some code that checks if the contents of the status field in a table is "Authorised" where the ID = something specific for multiple records. If all records witht the specific ID are "Authorised" Then generate a new record in another table. Where to start, perhaps a Dlookup?
View 1 Replies
View Related
Sep 13, 2013
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
View 1 Replies
View Related
Aug 22, 2014
I have a form that users will use to add new records (customers).
There is a field named VAT_Registration_no
First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:
Private Sub VAT_registration_no_AfterUpdate()
Dim btest As Boolean
If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then
MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error"
Me.VAT_registration_no.SetFocus
Else
btest = True
End If
End Sub
and to all other fields:
Private Sub textfield_Enter()
If Not btest Then
Me.textfield.SetFocus
End If
End Sub
If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.
View 3 Replies
View Related
Apr 1, 2014
Is there a way to remove data from one or multiple fields if the value of a separate field IsNotNull?
View 2 Replies
View Related
