Forms :: Display Subform As Datasheet And Allow Filtering
Feb 9, 2015
I am using Access 2010. I have a subform(continuous form) that I display as a datasheet and allow filtering. I display the record count by using Me.Recordset.RecordCount in the subform's form_current event. This value updates properly based on the column filtering except when the filtering displays 0 records.
When there are 0 records, the form_current event does not fire and I cannot figure out how to trap when this occurs so that I can display '0 records found'. How I can tell when the column filtering causes 0 records to display in the datasheet? Is there an event that fires when the user filters using the column headings arrow?
View Replies
ADVERTISEMENT
Dec 2, 2014
In Access 2010, if you have a form with a subform in datasheet view, and you move the focus away from the subform, how can you tell which record(s) on the subform are selected?
When the subform has the focus, the selected row is shown with a blue/grey tint. But as soon as the subform loses the focus, this disappears and all rows look identical.
Its an issue as I have 2 datasheet subforms on my main form, with the 2nd showing records related to the record selected in the 1st. But as soon as the 2nd subform receives the focus, it is impossible to see which record in subform 1 was selected.
I have tried conditional formatting, but that does not work.
View 1 Replies
View Related
Mar 27, 2014
I filter my subform (datasheet) by fields, everything works great except "date" column. MS Access shows that record does not exist but it isn't true.
Code:
Me![frmAktPD].Form.Filter = "[Data przyjęcia] = " & Me.DataPrzyjecia
Me![frmAktPD].Form.FilterOn = True
Where is the problem?
View 1 Replies
View Related
Sep 10, 2014
I have a subform that is displayed as a datasheet, and the source of this data is from another database. Essentially, it shows someone's name and status, but the status is displayed as a number. The people using the database don't know what this value means, so I was asked to display the label instead.
The values still need to remain in the database where the data resides, but can I use a lookup table or some other mechanism so that the label is displayed in the database the users are viewing? How exactly do I do this?
i.e...the data below resides in Database A but is displayed in Database B:
Col1 | Col2 | Col3
John | Smith | 2
Jane | Doe | 1
And in Database B only, it should show as:
Col1 | Col2 | Col3
John | Smith | Inactive
Jane | Doe | Active
Note that the users of Database B will not be updating Col3 data, they will just be viewing it.
View 1 Replies
View Related
Apr 29, 2015
I'm using a datasheet that can be filtered for certain fields. I have a macro that opens a form to the record that was selected (aka "current") in the datasheet. It filters this opened form to "1 of 1" which makes sense. How would I go about filtering that form for all of the records left (after filtering) in the datasheet?
I hate macros, but I'm doing client-specific work. The primary key is a field (autonumber) called "ListingNumber".
View 3 Replies
View Related
Jul 31, 2015
Have created a form to display/change table info in Datasheet View and added the form to a switchboard.When selected from switchboard, it displays a single record. When executed from the Forms (objects), it displays in Datasheet view.? How can I get it to display in Datasheet View from Switchboard?
Default view = Datasheet
Allow Form View = Yes
Allow Datasheet View = Yes
View 1 Replies
View Related
May 30, 2015
I have a header/detail form working well. I use a Dsum to update a TOTAL field in the header - based on all the detail records for that Header.
However, I want to 'filter' or condition the records which the form will initially display from the Header file. I have a conditioning field (Invoice date) in the header which should 'block' it from display on the form. I may also desire to SORT the selected Headers record into a different sequence before display...
Looks like any "filtering" I try on the header table makes it difficult to go back and update the TOTAL in the Header?
View 2 Replies
View Related
Aug 8, 2014
I have a split form on my database where I have my combo boxes bound to a primary key but they display the description field so it's much easier to understand. Is there a way to do this with option groups? In other words, I would like the datasheet part to display the option group labels rather than the values so it is easier to understand.
View 1 Replies
View Related
Jul 29, 2014
The main form has fields for Record ID, Position, and Revision. The subform has fields for ID, Position, and Revision. There is also a field for "task". I have over 15 positions and 495 tasks. Each position has a set number (around 15 or so) tasks associated with that position.
I have the Position in the main form and the subform linked so when I select a position on the main form, the subform loads the same position. The question field (drop down) is "fed" from a query which contains all positions and all tasks. What I want the subform to do is when I select a position the drop down ONLY displays the tasks associated with that position.
I tried using an event procedure "On Click" and "After Update" to no avail. The embedded macro was:
Apply Filter Where condition= [tbl_Detail Cost Information]![Position]=[qry_attempt 1]![Position].
I also tried the reverse Where condition =[qry_attempt 1]![Position]=[tbl_Detail Cost Information]![Position]
The result is a fully loaded (all tasks) drop down.
View 4 Replies
View Related
Oct 31, 2013
I have two related tables
1 Donor and
2 Donations with fields date, donor, fund and amount
I want a form to display donation details in datasheet view of fund and amount for a selected date and donor...
View 14 Replies
View Related
May 15, 2013
I have been handed an Access database (Access 2002-2003 file format) to clean up and update as necessary. Currently I am trying to add new rows to a datasheet that is in a subform. I have had no trouble in the past adding rows and for some reason now I can't.
In some instances I can update an existing row but never add a new one. Also, the records options are greyed out in the ribbon except for a select few that are not useful (refresh all, totals, more). Can't add new record from their either. I am in the main form that contins the subform which contains the datasheet, in form view.
View 5 Replies
View Related
Oct 31, 2014
I have recently added a subform to a form and wish to display the subform in Datasheet view. Whilst I can get the proper data and headings to display, I cannot figure out how to get the column widths to persist. I can adjust the column widths while in (main) form view right on the subfirm columns themselves but after I save and reload the main & subform form, the widths revert to some sort of default value.
I have also tried adjusting the columns in the subform's associated data table itself as well as adjusting the text box widths when the subform is displayed in Form view but it does not affect the column widths when the subform displays Datasheet view.
What is more annoying is that I have another subform in which I was able to make the Datasheet view column widths persist but I don't seem to be able to properly recall how I did it.
View 2 Replies
View Related
Oct 3, 2014
Sum Function in Unbound text on Datasheet subform..How to make a sum formula without count orange color rows Amount. Because that orange color rows is product return items so no need to count in SubtotalRs. Actually I have used below normal sum formula so it is ok but need to change the formula. how to give a formula without count orange Amounts?
View 1 Replies
View Related
Nov 5, 2014
I have not used access since Office 97 so, I see many changes. A while back I used Access to create some minor tracking databases. Now, with 2010 I'd like to modify the database that I am currently using. I have a main form that tracks daily trips or tasking(s). Within the form are two sub forms that track:
1. People assigned to the trip through a combo box; and
2. Itinerary for this trip.
I have been asked to add a Trip justification(s) through a combo box which will have several selections avail that can be chosen and would apply to each trip. These can be added to by double clicking the justification (figured that one out). What I would like the sub form to do for each new trip that is entered is to display only these values as a single selection rather than 12 times (example) if there are 12 trips that have the reason justification as part of the trip/tasking.
I just can't seem to make this work. The personnel one and itinerary one work just fine (previously existing). I just can't seem to get the new one to work. I have checked that the new table is connected through the relationships. In the exact same manner. The new form has the ID (Key) OPID (to link each trip separately) and justification text field.
View 6 Replies
View Related
Sep 25, 2014
I have a tab control at the bottom of my main form, and some of the tab pages contain subforms. On one of the subforms I have created a filter to divide the content into "complete" and "incomplete." I have also added a toggle button option group to the subform to toggle the filter (one button for "complete" and one for "incomplete"). I'm using a macro to apply the filter, but it isn't working. The filter does work properly if I use the "Toggle Filter" button on the Ribbon, but I want to create something a little more user friendly.
I have :
Main form: Search_by_name
Subform: Action_Items
Underlying table: tbl_Action_Items
Subform control: sfctlActionItems
Toggle button option group: tgl_Complete_Incomplete
Here's the macro as it stands right now:
If [tgl_Complete_Incomplete]=-1 Then
ApplyFilter
Filter Name
Where Condition =(([tbl_Action_Items].[Completed])=True)
Control Name [Forms]![Search_by_name]![sfctlActionItems].[Form]![tgl_Complete_Incomplete]
Else
RunMenuCommand
Command RemoveFilterSort
End If
I get the error message "The 'ApplyFilter' action requires a valid control name that corresponds to a subform or subreport."
If I remove the content of the Control Name field and open the subform independently (i.e. not as a subform), everything works fine.
View 6 Replies
View Related
May 6, 2014
After having no luck with the standard datasheet E-Totals, i'm now trying to have a textbox on the main form that totals a subforms column called lineTotal.Line Total is a calculated query field that works out the Qty*cost.So say if they put in 5 lines on the subform, the textbox field will sum all of there linetotal and shows a rolling grand total.
View 10 Replies
View Related
Dec 23, 2013
How can I obtain the value of a record from my subform in datasheet view?
Ideally I could double click in the "cell", for lack of a better word, and grab that value to pass through to another function.
On double click event? What then?
Edit: I suppose the user could select the row that the value is in as well. As long as I could then find the field I need from within the row. That might make things easier.
Edit 2: Sometimes I post before doing proper research. I've figured it out. Here is a quick example. The below code references an unbound subform called "Main_AU" where the field I want is "Description". The MsgBox is used to confirm that the code is grabbing what I want it to.
Code:
Dim test As String
test = Main_AU!Description
MsgBox test
View 3 Replies
View Related
Feb 8, 2014
I'm trying to sum a couple of columns from a subform datasheet and carry them onto the main form.
My main form, [frm_tours_details], holds subform [sbfTours] which is a datasheet.
On [sbfTours], I've got a couple calculated fields within the datasheet, one, called "txtStudentSum", which calculates number of people total ([students]+[adults]+[numfreeteachers]) and one, called "txtptotal", which calculates fee for the program based on the following formula:
Code:
=IIf([txtMonth]>=10 Or [txtMonth]<=4,([Students]+[Adults])*7,([Students]+[Adults])*8)
(The fee is $8/person in season, and $7/person out of season.)
I've been following online directions I found to put a text box in the form footer which then sums each of these calculated fields ("txtStudentSum" & "txtptotal"), and then reference these text boxes from my main form.
However, the main form text boxes just come up as #Name? or #Error when I try to reference these boxes on the subform. Or, sometimes, the one calculating total students will show the total of ALL students, adults in the database, not just those for this particular record-but only, it seems, if I have the subform open separately first, then load the main form. I also tried putting the whole original calculations into text boxes on the main form, but they will only reflect either the current record selected in the subform, or they will only show the first record, regardless of what you've selected, depending on which way I tried.
I've attached a copy of the database ... click on the tab 'tours', then click on, say, tour #6 (since this one is multiple programs and needs to calculate) to open the tour details form.
View 1 Replies
View Related
Feb 7, 2014
I have subform (datasheet) with a column for gender indicating "Male" or "Female" I wish to place the totals for each gender (and total overall) on the main form footer. I have tried objects of every type with calculated controls using conditional count formulas such as
=Count(IIf([Gender]="Male",0))
But I get #error on everything.
View 12 Replies
View Related
May 30, 2013
I have these 3 combo boxes filtering results into a subform.
Code:
Private Sub Combo5_AfterUpdate()
If Len(Nz(Combo5, "")) > 0 Then
FindRFQsubform.Form.Filter = "[RFQ Title] = '" & Combo5 & "'"
FindRFQsubform.Form.FilterOn = True
[code]...
View 1 Replies
View Related
Sep 11, 2013
I am trying to filter a subform on a tab control based on an input on a second form.
Mainform: frm Index Page 2
Tab Control on Mainform: tabctrlMain
Subform: Customer Details
Tab Name: tabCustomerDetails
The second form is basically a separate Search Form where users can look for a customer ID and returns the ID back to Customer Details form.So far I have managed to redirect focus to the correct tab on the tab control using the following code:
DoCmd.OpenForm "frm Index Page 2"
With Forms("frm Index Page 2")
!tabctrlMain.Value = !tabCustomerDetails.PageIndex
End With
However, I am stuck with the filtering part. Everything was working fine before I began putting the forms together into one big tab control (I tried using Navigation Pane but there were too many referencing problems so I gave up on that one).
View 2 Replies
View Related
May 6, 2013
I have faced with a problem while i was filtering my main form.....my problem is when i filter the main or parent form the related sub-form also gets filtered how to manage the main form so that when i filter it the sub-form should not be filtered so that i can get the related record to my filtered one in the sub-form... as for the time being when i filter the main form the related record in the sub-form is not shown and when i press unfiltered on the sub-form its data gets appeared....
View 11 Replies
View Related
Jun 28, 2013
I have a pivotchart subform who's Filter Property I've set to:
Code:
[EEIC_ID] In ([Forms]![MainDataControl].[EEICBuffer],0) And
IIf([Forms]![MainDataControl].[TypeIDBuffer]="",[AG_TYPE_ID] Like "*",[AG_TYPE_ID] In ([Forms]![MainDataControl].[TypeIDBuffer],"")) And
IIf([Forms]![MainDataControl].[AgencyBuffer]="",[AGENCY_ID] Like "*",[AGENCY_ID] In ([Forms]![MainDataControl].[AgencyBuffer],"")) And
IIf([Forms]![MainDataControl].[FacilityBuffer]="",[FACILITY_ID] Like "*",[FACILITY_ID] In ([Forms]![MainDataControl].[FacilityBuffer],""))
The issue is that none of the parameters are recognizing the textbox controls even though the references appear to be correct. Per access.mvps.org/ access/forms/frm0031.htm, I've attempted several versions of the syntax.
I believe that I've done this successfully in several other forms, though none have been been via a pivotchart. What's more, this exact same filter string worked when the pivotchart was its own popup and the referenced form was a separate window. Once I embedded it (being the pivotchart) as a subform, however, I began to be presented with the "Enter Parameter Value" dialog, even though the control's address had not changed. Note: the form with the embedded pivotchart is separate from the control's parent form.
In essence: the filter property of a pivotchart subform has a parameter that references a control on a seperate popup form, and every one of the syntactual statements I've tried returns an "Enter Parameter Value" dialog (where they did not when the pivotchart was not a subform). I've also attempted to isolate this issue by putting the text box controls on the parent form (rather than the separate popup form) and referencing them via the pivotchart subform, but this provided no resolution (the same thing happened).
View 8 Replies
View Related
Jun 20, 2013
im having problems getting a subform to calculate another subform's total and display it as it keeps coming up with the #NAME?.Basically my database is like this
tblCustomer
- tblOrders
- tblItems
and this is displayed on a form. so you can flick through clients, then flick through the orders and its broken down into the items inside each order.i have seen many answers that are only about calculating something on a subform and displaying on a form but this does not work when displaying on a 'parent' subform. the form structure looks like this
frmCustomers
- sFrmCustomerItems
- sFrmCustomerOrders
i have a calculated control textbox in the footer of sFrmCustomerOrders with sums up all the prices using =sum([Price])and in my sFrmCustomerItems, i have a field called Amount (the total amount of all the items) and the Control Source formula i was using was =[Forms]![sFrmCustomerOrders]![sFrmCustomerItems]![txtTotalPrice] but i get a #NAME? error.
View 5 Replies
View Related
May 1, 2014
I m having a subform in datasheet view. whn i use a join query as the record source for the sub form, i am not able to add or update any record in the datasheet. Is there anyway to use a linked query as record source of the datasheet with editable property.
View 2 Replies
View Related
Oct 10, 2014
Is there anyway that we can do cascading combobox in the same datasheet view of subform? Basically I'd like to filter "Account Name" based on "Plan#" selected in the same record of subform.
View 3 Replies
View Related
