Modules & VBA :: Data Filtering By Form / Subform Using Same Record Set
Feb 26, 2015
I have a form and subform where i have table data in the subform and 03 Combo box in the main form header. I need to do some filtering using combox box1 and out of that filtered records i need to do one more filtering using combobox2. then again another filtering by Combobox3. ( same way we are doing in Excel)
Subform is running on a query where i have following fields;
Vessel
Voyage,
POL
POD
MLO
by Combo box 1 i need to filter Voyage
by Combo box 2 i need to filter POL ( out of the data filtered by above )
by combo box 3 i need to filter POD ( out of the data filtered by both above )
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
I want it to filter out any matches between the 7 fields. Here is my code:
Code: Private Sub Command19_Click() Dim strFilter As Variant, _ strSDate As String, _ strEDate As String 'check Text13 Text13.SetFocus 'set focus to Text13 to be checked
[code].....
It will only filter for Build Date 1 and not for any of the other fields.
Is there a way to search for a term within a form and goto a record that matches that term assuming there was only one matching result (Unique ID, for example).
But NOT filter the results so that user can still navigate as usual after the search has been carried out.
I have a tbl_product with PK_productID as primary key and frm_search with a subForm in it, How do I filter my subForm in a form with a single click button?? well... at first i did it with open new form with a specific data to display.
and from now on i want with a single click in my search form the result will show up in my subform but i don't know how to do it T_T
I'm working on a database that will be used when maintenance needs to be performed on our test equipment. So far, I have a form set up for the associates to track when a maintenance action has been performed. The top section of the form contains basic data about the part and there is a subform that lists the actions that need to be taken on the piece of equipment. In the subform, the associate can mark whether the action is complete or not.
If the action is marked incomplete, I wanted a separate form to appear for them to track why an action cannot be performed. If possible, I would like this separate form to automatically show the equipment ID and the action that was missed. However, I'm having difficulty getting the data to copy from the subform to the Exception form. Is this something that is possible to do?
I have a main form that has some sub forms on it linked by master child on PK. When I select a new record on the sub forms I want to take a date value from the main form and populate the value into a field in the sub form. How do I do this as I cannot see a new record event on the sub form?
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).
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....
I'm trying to make a form to filter a table and open a data entry form with the filtered data ready for editing. I've got it working when filtering a 1 field but I'd like to filter the data by 3 fields and i can't get it right. the code I'm trying to use is :
Code: DoCmd.OpenForm "Main", , , "[Admin District]='Corby'" And "[AgeRange]='31 - 50'" And "[Gender]='Male'"
it works fine when just filtering one field like this:-
I have a modal form which has a single record which is then linked to a sub-form on the same modal form. This all works fine and shows the relevant record and sub records but I want the modal form main record to change dependant on the record selected in the subform (which is basically order item history.
I found some code on another site which seemed to work for others but for me I am getting Run-time error 2465 and it doesn't like my reference to 'Me' - is this because it is a modal form?The code I am using is:
Public Function GotoRecord(RecordID As Long) Dim rst As DAO.Recordset Dim strCriteria As String Set rst = Me.RecordsetClone strCriteria = "ID = " & RecordID rst.FindFirst strCriteria If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
I have a form with a subform in a navigation menu. Something like:
Navigation Form NavigationSubform ParentForm (header data and some unbound calculation fields) Subform (Multiple lines tied to query)
I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).
My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.
Parent Form PF1=10 PF2=3 Subform QTY ADJ SF1 SF2 Line 1 5 .05 10.6 112.89 Line 2 8 .14 10.38 105.15 ... Line N SF1=PF1+(PF2/QTY) SF2=SF1*(SF1+ADJ)
So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.
I have an unbound mainform and an unbound subform(datasheet). The source of the subform is a query which is dynamic(I have many queries with diffrent columns). the subform source is change using a combo box selection. I have text boxes in my mainform..how do I get the data from subform to the text boxes of the mainform?
Can I look up and verify data on a "second" form based on a selected record from first (still open) form.
I am trying to allow users to select a User Name from a combo box list and then open "Change Password" form when they select "Change Password" for that selected user name.
My problem is that I can't figure out how to associate and verify the data tied to the user name selected on the previous (Login) form ( I am trying to validate the old password tied to that selected record).
I have the first login form created, and it's working just fine. I also have the change password form created (and it's displaying the user name selected from the first form using:
Code: Private Sub Form_Load() With Forms![frmLogin]![cboUserName] Me.txtPwdChgUserID = .Column(2, .ListIndex) End With EndSub
I also have the code written to validate and confirm old password, new password and validate new password (when the save button is clicked). I have yet to update the password with the new password (still trying to figure that out).
Attached zip file has screen shots of the two forms.
I'm trying to add data about a form to a table when the On_Current event fires. The data I want to add is: windows login username
date and time the On_Current event occurred name of the form that was opened that the form was opened the number of the record (ID) in the table the form is based on
I have the following code
Private Sub Form_Current() If Me.NewRecord Then Call FlimOpen("OR IR No", "OPENED") Else Call FlimOpen("OR IR No", "OPENED")
[Code] ....
Excuse the use of 'FlimOpen' but I needed a unique word that I would remember (inspired by FlimFlam the cat on the children's channel CBeebies here in the UK)
I try to compile and I get an error about the wrong number of arguments or invalid property assignment. However, the event does fire correctly when I open the form or move from one record to another so something's working!
I am using the following code to filter my form with a search box:
Code: Me.RecordSource = "qryCompanies" Me.Filter = "CompanyName Like '*" & Me.SearchTxt & "*' Or webpage Like '*" & Me.SearchTxt & "*' Or PriorName Like '*" & Me.SearchTxt & "*'" Me.FilterOn = True
[Code] ....
It works great with one minor issue: if I try to search for a name containing an apostrophe, I get a syntax error. So, it won't find Children's Hospital for example.
I've got a data entry form bound to one table. The form has four buttons:
- Clear Fields - Cancel - Save and exit - Save and add another (which should save the user input to the subform/table, clear the input fields, and allow the user to add another record)
I can't quite seem to get the "Save and add another" button to work. When I put some information in the input fields and click the button, it saves it to the subform/table perfectly, but when I try to do it again, it just edits the last record (the one just created).
How can I get that button to place the information from the input fields in a new record every time?
The _Click event for the button looks like this:
Code: If Len(Me.field1 & Me.field2 & Me.field3) > 0 Then Me.Refresh btnClear_Click DoCmd.Save End If
I'm using 4 combo-boxes in order to filter a tabular form in access.
One combo box is for a company name the second is for a report type and the third and forth is for choosing years. I used VBA in order to apply the filter:
Each line works by itself but I cant figure out how to combine them together, or how to use "AND" between each filter so the filtering will refer all of them when I run the code.
I'm having a bit of trouble with a piece of VBA code I have. What it's supposed to do is filter and sort a report using a form. But there are two lines, which both I need, in it that are not compatible with each other. When both are in and I apply the filter with only a sort, it gives me a "Run-time error '5': Invalid procedure call or argument."
But the code runs beautifully when only one of the pieces, which I've highlighted in red below, is in it. The debugger highlights strFilter = Left$(strFilter, lngLen) after I have received the error.
Code: lngLen = Len(strFilter) - 5 If (lngLen <= 0) And ([cboSort1] = "Not Sorted") Then MsgBox "Insert criteria before filtering.", vbInformation, "No Criteria" Else strFilter = Left$(strFilter, lngLen)
I'm attempting to filter my form with combo boxes. I've added the below code, but the if statement that checks if one of the combo boxes is null won't work. If the combo box is null or "", it's supposed to assign a wildcard to the variable. But it doesn't pass the test and goes straight to the Else statement. I've highlighted the if statement that keeps failing in red.
Code: Private Sub btn_Search_Click() 'create variables to store the combo box values Dim str_Country As String Dim str_Vendor As String Dim str_Survey As String
I've search around the forum and haven't come up with the answer yet.
So here goes...
I have a main form (GRCSearch) that is tied to a table (StoredSearches). When users enter data in this form it saves it to the table. It is a single record form. The data entered by the user is used to search a big second unbound table via some queries.
The search data entered is then recorded to the table (StoredSearches) as a way for the user to track searches and to recall them if they want to run the same search again.
I've created a pop-up form that displays a continuous record layout of the StoredSearches table and a command button at the left of each record.
I want the user to be able to click the command button to the left of each record in the pop-up and that in turn set the focus of the main form to that record. It recalls the search data, repopulating the search fields of the main form. And in the onclick event I'll also have it close the pop-up window.
I however cannot find any references to setting the focus from one form to another form this way. I've found plenty that changes the focus of a form from an embedded subform.
I have a form with 3 combo boxes that filter another form/report. The first combo (cboByCategory) contains options from 2 different fields within the same table. Before I added this add'l piece of code, all 3 combos worked fine. I am not getting error messages, it just does not filter on the other 2 combo boxes - cboDiv and cboGender.
Code: Private Sub cmdModifyRecords_Click() On Error GoTo Err_cmdModifyRecords_Click Dim stDocName As String Dim strFilter As String Dim stLinkCriteria As String stDocName = "Modify_OpenItems"
i have a calender of which show's records on date box's i m having a issue when i double click on a datebox it opens a form call update which is filtered on open to show only records for this date but on date boxs on 1st to the 11th i have add the code to do this but when the form open's the form is blank and shows no records but the code works fine for 12th to 31st which i can't understand why? as im using the same code that does dateboxs 12th to 31st on datebox 1st to 11th but doesnt work i'm pulling my hair out on this one !!
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ******** Public Function GetTargetType() As Variant GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]") End Function ************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ******** Private Sub Form_BeforeInsert(Cancel As Integer) Me!target_group = GetTargetType() 'Forms!frmNmsConsumptionEntry!target_group = GetTargetType() '[tblNmsConsumption.target_group] = GetTargetType() End Sub ************************************************** ********
making sure I can insert this value once retrieved.
