Forms :: Calculating From A Query Based Subform
Oct 9, 2013
On my master form I have a subform called invoices this is tied by Site Number to the record on the form which is has the following important information, site code. group code, exp code an amount.
On another subform via a query I have a summary of all the invoices by exp code and group code.On another subform I need to create a Income an Exp.
I have two combo boxes 1 for the Group Code, and another for the expense code what I want it to now do is look up group code exp code = Total amount. There is currently only 1 line as you will see but there will be many rows of summary data...do i need a subform for the summary?
And I need to do it this way as not all exp appears on the income and exp and I want to be able to lay out the expenses for each site differently Attached is the zip file, of the database the only record that has data is site code 0289S
View Replies
ADVERTISEMENT
Nov 4, 2014
Doing a school project and need to add a count function to a sub form that is based on my query. The count function just needs to be displayed on the bottom of the sub form showing how many records are in the sub form.
When I do this, the function works all good.
When I add the function to a header or footer, so that it doesn't show a column and repeat itself each time.
View 2 Replies
View Related
Mar 20, 2014
I am trying to calculate the working days Based on all web searches I am unable to find the specific scenario I am working in Auto industry, which means auto industry usually close twice a year for any reasons, let say in July for one or sometime two weeks and in December depends upon the Christmas date usually from December 20 till Jan 01
Now my question is when i am enter the holiday details in table do I have to enter line by line date e.g. july 01, 02, 03 (I am able to understand, how this works but still not yet tried) OR july 01 to July 07 (which make sense, but unable to find how to use date range to calculate working days)...
View 1 Replies
View Related
Jan 2, 2015
I am trying to get a subform to be hidden when the value of a textbox (txtAllocationID) is empty but when a ID number appears I would like it to become visible again.This is what I have tried so far but doesn't seem to work:
Code:
Private Sub txtAllocationID_AfterUpdate()
If Me.txtAllocationID.Value >= 0 Then
Me.frmStockAllocated.Visible = True
Else
Me.frmStockAllocated.Visible = False
End If
End Sub
My allocation ID is a primary key so all auto number generated and only an integer.
Scenario: When I enter a stock ID of 121 and search I want the subform to remain hidden as there is no Allocation ID related to that Stock ID (see attachment tblAllocated). If I was to enter a stock ID of 122 I want the subform to become visible as the stock ID has a relation with the Allocation ID 6 and therefore the textbox txtAllocation ID would display '6' in it.
View 2 Replies
View Related
May 23, 2013
I have researched but couldn't find any working filter criteria for subform criteria.i have used the code, searching the web but still no luck.I have attached the picture and sample northwind database (accdb). I just want to filter customers in Orders navigation using text criteria.
View 9 Replies
View Related
Aug 30, 2014
I got a problem about form designing. i need to disable my Close Command button while my subform Price cell is null and enable the Delete button.
And while delete button is pressed the delete action occurred and close button enabled.
View 12 Replies
View Related
Mar 8, 2013
I have a Form named 'Opening_Screen' which consists of various objects - Combo Boxes, RadioButtons, Check Boxes which the user can select.There is a subform called 'Report' within the 'Opening_Screen'. The source object of the subform is a query (called 'QueryX').
There is a button called 'Generate Report' on the form which when clicked - an SQL must run on the backend, update the query which in turn should update the Subform 'Report'.For me, the QueryX gets updated, but the subform doesn't. When I manually switch the form to Design View and then back to Form View - the subform gets updated. I tried the same through the vba code instead, but it doesn't update the subform.
View 10 Replies
View Related
Jul 14, 2013
So, let's say I have a table named FLOWER_SHIPPING that has field:
Date [Date]
Flower_name [Short Text]
Amount [Long Integer]
Sender [Short Text]
Address [Long Text]
Then I created a form named MAINF with FLOWER_SHIPPING as its RecordSource. The form only has one TextBox: Date.
Then in MAINF, I created a subform named SUBF with FLOWER_SHIPPING as its RecordSource, in Datasheet View. It shows all fields in FLoWER_SHIPPING as it is.
The link between MAINF and SUBF is Date.
I want SUBF to only show record based on the Date chosen in Date TextBox in MAINF. So far, yes it did what I want. But with problems:
1) [SOLVED] SUBF does show record based on the Date I've chosen in MAINF's TextBox Date, but it ALWAYS ALSO shows the first record on the FLOWER_SHIPPING table. And the date in the first record always changed into the Date I just input in the TextBox Date in the MAINF. Automatically.
Example: I chose 6/22/2013, and there are 2 records with that date. the SUBF will show 3 records: those 2 records + the first row of FLOWER_SHIPPING with its Date automatically set to 6/22/2013.
2) When I chose a date in MAINF, I have to click everywhere in the SUBF so that it will refresh its content. Can I have it refresh automatically everytime after a date chosen?
View 9 Replies
View Related
Jun 18, 2015
I need to have a subform that shows up smack dab in the middle of my main screen when visible - which is what I want - but only based on a specific value of a control on the main form. Specifically, if a control's value is "See Spreadsheet", I want the subform visible front and center as it contains the link to the server location for that specific record. If the control's value is not "See Spreadsheet", I want the form to be invisible.
My code:
If Me.BudgetsFacultyAccountNumber = "SEE SPREADSHEET" Then
Me.frmMsgBoxUpdateSpreadsheet.Form.Visible = True
Me.BudgetsFacultyServerLocation.Visible = True
Else:
Me.frmMsgBoxUpdateSpreadsheet.Form.Visible = False
Me.BudgetsFacultyServerLocation.Visible = False
End If
The red lines result in an MS VBA error: "Compile error: Method or data member not found."
I've tried referencing that subform a hundred different ways, but I can't get it to work.
View 5 Replies
View Related
Sep 14, 2006
I would like the subform in the lower left corner to be blank when
I open the program. It is populated from my tblClients table.
How it works in general is you select "x" number of items from the
list box...hit the search button and the results go through a query
and then show up in the subform.
everything works ok but, like I stated I would like the subform to
be blank when the program is first started up.
It seems to be retaining information from the last start up in the
subform box.
Thanks for your help...it's coming along great so far...
see attached doc...
View 4 Replies
View Related
May 26, 2006
Because my tables were already large I split them up by environment to speed up data maintenence and retrieval. Thus I have table names which contain the environment as part of the table name (ex: tbl_TEST_MyTable, tbl_PROD_MyTable). Once the user sets the environment they want to use (by clicking on an option button) I update the form's recordsource to the appropriate table. My form has 5 subforms within it.
Even with the split loading the form was taking a long time because some of the queries the subforms are based on are joining tables so that they can be linked (parent/child) to the form. I created indexes to speed up the data access which helped a little but it was still running too slow. So, I attempted to change the subform's queries to only join the necessary data by re-creating the queries each time the user changes the form's current record.
For example, what I had originally was taking too long:
SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo
FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt
ON tb.Name = pt.TName
ORDER BY tb.Name;
and the parent/child link fields are CollId, ProgName, and QueryNo.
This is the change I made:
SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo
FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt
ON tb.Name = pt.TName
WHERE pt.CollId = 'BATCH_COLLECTION'
AND pt.ProgName = 'PROGRAM1
AND pt.QueryNo = 123
ORDER BY tb.Name;
where the pt fields are changed dynamically each time the user navigates to the next record in the form. This resulted in much faster access time; however I discovered that the form was always displaying the query that existed before the form was opened. I have not been able to figure out how to get the subforms to display the latest created query.
While debugging I discovered that the form's OnCurrent event was being executed 3 times before the form is opened. I don't know what I'm doing that's causing this.
I also discovered that the subform events are executed before the form's events, so I think that's why the latest query isn't being used. I attempted to requery the subform in the form's OnCurrent and OnOpen event, but to no avail.
View 4 Replies
View Related
Dec 13, 2013
I have a form which shows training events - these can take place over one or several days, and can be run by one trainer or several, so I have my basic Events Info in one table (EventID, EventType, Location, that kind of thing) and the 'Jobs' in a separate table (EventID, JobDate, TrainerID, etc)
The problem I'm having is that I want the List of Events to be sorted in order of their start date... which is on the subform, not the main form.
How can I go about it - and still leave both the main and subforms fully editable?
View 12 Replies
View Related
Nov 16, 2013
i have a subform name(SUBSAL)in this subform i get reocrds for some employee as select combobox Name(CMBEMP).some employee having 2 record or some having 6 record as their transaction
i have sum of transaction at subform footer field but when only 2 records shows in subform it shows a major gap or blank space in subform detail section.
View 2 Replies
View Related
Sep 2, 2014
i have some command button and some combo box on my form and also a subform. the subform contains some field. the fields used to enter numeric values.
i need to disable my command button on the form based on the subform fields if they r null.
View 1 Replies
View Related
Mar 19, 2013
I have form where I will show the details of one table(member details table). In same form I have subform where I can get the member family details (here I am using the relationship table), now my problem is when Member detail form open based on the member id, the below subform data should be fetched...
View 6 Replies
View Related
Jun 28, 2015
I have a form with 22 subforms (in about 11 tabs). Each one of these subforms has a controlling field in the main form (table) that dictates if the associated subform needs to comply with data entry rules or not.
For example:
Field in main form cboMed (Yes; No)
Subform: frmSubMeds has a list of medications
I want to be able to loop through all the records of the subform and determine if they are complete.
This check is run from a command button; that should also consider the value in the combo box. i.e. Check only if the combo has a value of "Yes"
Since i have 22 subforms and have to run the check on everyone of them, i have used the tag property to check if the record is blank or not. This is what i have so far
Code:
For Each ctl In Me.Controls
Select Case ctl.Properties("ControlType")
Case acSubform ' only look for subforms
If ctl.Enabled Then
If Not (IsNull(ctl.Properties("SourceObject"))) Then 'if it is a subform type
[Code] ....
The problem with this is that it only looks for the selected record in the subform and doesnt look for ALL the records.
View 5 Replies
View Related
Jul 4, 2013
i'm creating a database which holds all animals. i'm trying to make the parents selectable in a subform based on the same table as the main form - this doesn't work for whatever reason ("table is already opened exclusively by another user,..."). it can't just be one field or a query, it needs to be a subform because besides name and eartag i also want to display a small picture of the parent and only the filename is being stored.
i'd really like it to be selectable records in a continuous subform, so the risk of selecting a wrong id is minimized. is there a way around this not being able to use the same table for a subform?
View 14 Replies
View Related
Jun 12, 2013
When I started building forms to fill my junction tables, the example that I looked at based them all off of queries. That's what I've done for the most part. However, this latest subform that I've made and embedded into a main form is based off of a table. Will that cause problems for me down the road? I'm not so far with it that I can't redo it off of a query, I just don't want to!
View 3 Replies
View Related
Apr 24, 2014
I'm trying to find a subform record from a separate pop-up form based on its ContractID. When I click the button, I get the following error:
"A macro set to one of the current field's properties failed because of an error in a FindRecord action argument."
When I step through the code, it works just fine. Here is my code:
Code:
'Find contract
With Forms!frmContractForm.frmContractSub
.Form.ContractID.Enabled = True
.SetFocus
.Form.ContractID.SetFocus
DoCmd.FindRecord Me.ContractID
.Form.Business.SetFocus
.Form.ContractID.Enabled = False
End With
View 1 Replies
View Related
Jun 14, 2015
I have a problem printing a Subform that uses multiple criteria(in textboxes) as filters.
The search portion of the form works fine. The problem is I have created a report based on the subform and am using the following code to open/filter the report
Code:
Private Sub PrintBtn_Click()
Dim strCriterion As String
Dim strMsg As String, strTitle As String
[Code].....
View 3 Replies
View Related
Dec 6, 2013
What I have is a form that takes in information regarding test data. Each test run can record data for multiple requirements. I am able to pull all data fine, however in order to make it easier on the user i was hoping to populate the requirements subform based on a selection of "test group" in the main form.
Commonly run together requirements can fall in to groups. I have a selection box for these groups in the main form and a table that stores these group id's and group setup. Is there a way to autofill the tables records that the subform is linked to based on the selected test group?
View 3 Replies
View Related
Jul 31, 2014
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
View 3 Replies
View Related
Aug 4, 2014
All; using 2010. How can I get a subform to grow or shrink based on num of records returned? Its a continuous subform. Tried changing Vertical and Horizontal Anchors as well as some VBA suggestions but havent been able to make any of them work.
View 2 Replies
View Related
Mar 1, 2015
I have a parent form and connected to it is a subform. On the parent form I have a checkbox which enables and disables fields on the parent form and also hides the subform.
What I want to do is when the user unchecks the checkbox, this action also deletes the associated subform records, if there are any.I'm sure that this can be done with an SQL Delete query in VBA.
View 5 Replies
View Related
Oct 23, 2013
I have a multi select list box which I am trying to use to open another form and apply a filter based on the user's multiple selections.
List box - lstCatergories
Main Form - frmSuppliersSummaryCategories
Subform on Main Form - frmSuppliersSummaryCategoriesSubForm
The Subform contains the text box CategoryID
Here's my code which nearly works...
Code:
Private Sub cmdFilterSuppliers_Click()
On Error GoTo Err_cmdFilterSuppliers_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
[Code] .....
My problem is that the control "CategoryID" is on the subform and I'm having massive problems trying to reference it. This is the part of my code that won't work:
Code:
DoCmd.OpenForm "frmSuppliersSummaryCategories", acNormal, , "CategoryID IN(" & strWhere & ")"
However; if I open the SubForm directly it works perfectly. I.e.
Code:
DoCmd.OpenForm "frmSuppliersSummaryCategoriesSubform", acFormDS, , "CategoryID IN(" & strWhere & ")"
To summarise... How do I reference the control "CategoryID" on the subform "frmSuppliersSummaryCategoriesSubform"?
View 4 Replies
View Related
Feb 15, 2014
I am trying to lock records on a form and subform after a checkbox has been ticked, have used the code below from a previous post.
Private Sub Form_Current()
If Locked = -1 Then
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletions = False
[Code] .....
This is locking the Main form records is there a way to code this so that the fields on the subform are also locked when the checkbox is ticked?
View 7 Replies
View Related
