Hi! thank you for the wonderful tutorial i just had one issue i wonder if you can help me out.. when i click open report btn without any filters it open the form but when i chose something from the drop down menu i got a highlited error on DoCmd.OpenReport
@@seanmackenziedataengineering worked thank youu! also one thing that some program records duplicates in the report because each program have more than one trainer.. so the record duplicate in the report but with different trainer name.. is there is a way to keep one record only with all the names instead of this? the two tables program, trainer related through compiste table program_trainer..
Great video! I'm trying to do this exact scenario but to open in access (like a table?). I need a user to select 2 of the filters and then create a table so that we can add data entry. Project is more like a checklist with a series of questions to be answered. Is it possible to open in a table instead of a report?
Good question! Yes you can do this. In this case, you'll make a subform: ua-cam.com/video/tQfAol75j_I/v-deo.html you can do data entry like a table and it will organize according to your dropdown selections on the master form. If you really want it to look like a table, on the subform go to properties > Format > Datasheet view.
You could do something like make a text box, then load the text box each time the user selects from the combo. Select once, the box says 'Canada'. Select again and the box says 'Canada', 'Spain'. Then, in your criteria you can just change the VBA line: strFilter = strFilter & "[Country name] In (" & frm!txtCountries & ")" Note the brackets as we are using an IN clause. Your challenge is to load the text box txtCountries 🛠
Hey! Is there a way to do the dropdown in the report? So I would never have to go back to form and so on. I did this hole form selection in a criteria with queries. That's more simple. But I want to choose smtg from a dropdown in myreport, and then auto refresh. :)
You can do this by putting a subreport on your form! So, when you change selections on the form, the report will change for the new selection. Make a big window for your subreport so it looks good.
How does this work with queries? I want to filter the report based on the form inputs from a report built off a query. Does this only work when the Report is built on a table.
Hello Sean, I am trying your code but can't get the second filter to work. It askes me in a message box for the second filter but Item even though I have the code exactly like you have with my Combo List. When it gets to the report the items from the second list are still there. How do I fix this? My combo boxes work and the first filter works but not the second one. I don't know why it brings up the message box. If I get this to work that would be great! I am new to this as well.
I would check your spelling of the second combo box in your code. Make sure it matches exactly to how you named the combo on the form. This is the most common reason for the problem. Access can't find the name you put for the second combo in your code. If it has a space in it, surround the name with [ ] If it still is broken, go ahead and post your code here and I will look!
@@seanmackenziedataengineering If Len("" & frm!cboWeekEnd) > 0 Then If Len(strFilter) > 0 Then DateSel = strFilter & " And " End If strFilter = DateSel & "[Week Ending] = Forms!Creat_Report!cboWeekEnd" End If I am trying to filter a date so I am not sure if that makes a difference or not, but the combo box is cboWeekEnd (This is Copy and pasted) Thank you so much for the help
@@harshmancegroupllc4517 Nice! Ok, our goal is to continue building strFilter in this block, so you can change the two instances of DateSel to strFilter so that the condition for the second combo is added to the string.
@@seanmackenziedataengineering I had it like that before but it did not work. I changed it thinking it got to complicated. I just changed it back and it is still doing it. Is it because it is using a date or could it be something else? It also wont filter the date when it asks to the date.
Sir, I have two tables with two separate forms. The first table based on the first table has a calculated text box. I want to show the textbox data to a field to another form. How to do it. Thanx and regards.
Great video . i have a question for my project. So i have a continuous dataentry form that show all the data in my table. And there's no querry between them. How can i make a report that print only the filter record that i filtered in my form? Thank you in advance ❤
You certainly can do that! In the report's On Open event: Me.Filter = Forms!MyForm.Form.Filter Me.FilterOn = True Your form has to be open and filtered how you want when the report opens. Good luck on your project!
I have a drop down menu that selects a product and the price auto. populates in the price field, I am trying to get the total field to update as soon as I enter each product
Great question. You can just add one more line in your after update; something like Me!txtTotal = Me!txtPrice * Me!txtQuantity Are you using a continuous (list) form or a columnar form?
I appreciate the tutorial. I have an additional question. How would you write the code for a second filter for the same field? Say I wanted the reports for Canada AND Sweden to show up?
You can, just copy and paste your country drop-down onto the same form, then rename it to cboCountry2. In your code, you can say: 'Country If Len("" & frm!cboCountry) > Then If Len(strFilter) > 0 Then strFilter = strFilter & " And " End If If Len("" & frm!cboCountry2) > 0 Then strFilter = strFilter & "[Country name] In ('" & frm!cboCountry & "', '" & frm!cboCountry2 & "')" Else strFilter = strFilter & "[Country name] = '" & frm!cboCountry & "'" End if End if
Very helpful video, thank you! Is there a way to do this with multiple selections? Using the example you provide, what if I wanted to report the data for, say, 12 out of 50 countries? Thank you!
Hi, can you help me? How i can launch and filter report for more than one filed in the table with one combo box in the form. All three fields some time have same values in different date entry
In your criteria entry, just make it like "FieldA = '" & cboSeries & "' Or FieldB = '" & cboSeries & "' Or FieldC = '" & cboSeries & "'" Something like that. If you want, you can use And instead of Or if all three dates need to be the same.
Hi sean. Thanks for your Great Tutorial but I have question I hope You Can answer my question. I create 2 tables(1-main , 2-money resource) , second table is combo box field in another table (combo box for money resource) but when I want filter report by option in combo box (money resource) and I press Print button It show nothing just shows empty report . What should I do ? I create relationship from money resource id to combo box filed in second table . and also I create report from main table and create combo box from money resource table?????
You can check what value was actually selected in the combo. Sometimes you see a text description but the combo "value" is actually an ID number. Open your form and select values, then go Ctrl+g to open immediate window, then in the white area type in: ?Forms!MyFormName!MyComboName. Did you get the expected value? Would that value work in your query?
Sir, Satyaban from India. I want to preview a report based on the item selected by me on a form having parameter. For example, I have a form with a drop down combobox and report will only show the data of the particular item selected from the combo box. Is it possible Sir. Regards
This is awesome! One question I do have is could I include it to where I have created multiple reports based on specific queries? So when I select an id (in my specific database), it tells me which reports are associated with it and then generates a filtered report?
You can! You would use the On Current event of the form to see which ID is selected on your form, then update the possible report list from there. You could use a Select Case or something to update the possible reports. This would be done in VBA.
Hi, thank you for a great video. I am brand new to coding, so I wonder if you could help me out a bit. When I use the drop down menu and press open report, I get a new dialog box that ask me to ad parameters. ?. When I do, I get the report with all content, no filtrations. I guess there is a typing error, but I can’t find it Thank you again for a greate series of videos Rune
Hi Rune, that's really cool that you're starting to code. It is a cool journey! The issue you are having is almost certainly because you made a slight spelling error in your code. The parameter pop up in this case tells you that it is looking for a value, or the spelling of the field name does not match the name you put into the code. If your field name has spaces in it, you might need to put it like this: [field name] in your code.
That is really a great video, but I faced a problem, when I introduce the fields with text format it gives me a positive result, it means that my report get filtered as my wish but when I introduce a numerical field or date field the result is negative and gives me an error message. would you tell me if there is any other code for numerical and date fields to filter?
@@seanmackenziedataengineering thanks a lot for getting back to me. 'I apologize sir but I did not get your point, what you describe me using ‘ apostrophe and # in the bellow code: 'Date_enrol If Len("" & Frm!cbodate_enrol) > 0 Then strFilter = strFilter & " And " End If strFilter = strFilter & "[Date_of_enrolling] = '" & frm!cbodate_enrol & "'" or in this code 'Date_enrol If Len("" & Frm!cbodate_enrol) > 0 Then strFilter = strFilter & " And " End If strFilter = strFilter & "[Date_of_enrolling] = forms!Filter_Form!cbodate_enrol" thank you in advance
@@seanmackenziedataengineering That's great, thank you sir. As the last question can I add filter range in this coding? I want to filter this single date field of within a desired interval. like I want to filter that from 1/1/2022 to 5/13/2022 how many enrollments have I had in the position of Engineering in Canada? Thank you in advance
I have made a columnar report. But the report is displaying more than 1 page. Sometimes, it displays more than 20 pages. But I want to display one record in one page at a time. How to handle it Sir.
You need to open the design view, then check the width. For example, A4, or Letter size. Make sure that you move and resize all of the fields to fit within your width minus margins. So, if you use letter size 8 1/2"x11" with margins of 1", then your items must fit the width of 6 1/2". You can use the ruler bar at the top of the report to check it. THEN make sure to also decrease the size of the form where the text controls are placed, by grabbing the edge of the form and resizing the width to be less than or equal to 6 1/2" (in this example). Rerun your report and it should display with less extra pages.
Great video, thank you very much I want to search between two dates as well and i don't want to use the query like putting criteria under date ( between text box 1 and 2 in the form) I want it the same way you did you said you can put dates Can you help me please
Great question! You can do it. Make sure to set your text box formats to a date format. Then, in the filter string I mention in the video, you will use # around the value instead of '. ie. strFilter = "MyDateField Between #" & date1 & "# And #" & date2 & "#" date1 and date2 might also be like Forms!myform!date1 etc.
@@seanmackenziedataengineering Ok, it works 👍 but with one problem. No matter i put in the input mask in date field in the form, the report read it as if it is mm/dd/year If i put 01_ sep_21 for example, The report give me records for 09/ 01/21 (( 09_ jan_21)). I'm really sorry for asking again, but can you help me Thank you
How could you filter a sub report when you are sending the main report directly to the printer? I have been able to do it in print preview by setting it from the main report load event, but that event does not seem fire when sending directly to the printer.
Great question! You could try using Report Open for this, but if you're still having trouble, just set your subreport RecordSource to a query, then just change the query sql dynamically at run-time. 1. Subreport record source was TableA 2. Create query QueryA as Select * From TableA 3. Set Subreport record source to QueryA 4. At run-time set QueryA SQL As Select * From TableA Where .... 5. When report runs, subreport is already filtered.
Something like: Dim qdf as dao.QueryDef Set qdf = currentdb.QueryDefs("QueryA") qdf.SQL = "Select * From TableA Where.." qdf.Close You might have a button that starts the report. You can do it there before opening the report, or just put it in ReportOpen
@@seanmackenziedataengineering An unrelated question I have been wanting to ask someone. Do you have any advice or recommendations for taking an existing MS Access app and converting it into a web app? Are there any tools for this or do you just have to rebuild it?
Hello Sean, this is great! One question, what if one of the two filters is a numeric value? I am getting an error message when filtering the second field which I think might be related to the data type. The first filter that I am using is a text string and the second filter is a numeric value, if I run it only with the first combo box it works great but as soon as I input the second value it breaks, any thoughts on it? I am new in vba by the way lol. Thanks a lot!
Great question! In your filter string, when you use a number, you don't need to put the ' symbol around the value. So, you can go: "[MyField] = " & frm!MyControl Instead of "[MyField] = '" & frm!MyControl & "'" If you have a date in a control: "[MyField] = #" & frm!MyControl & "#"
@@seanmackenziedataengineering That is awesome! not getting an error message anymore now but for some reason the second filter is not working along with the first one, it kinda offsets it and I only get the second value filtered instead of both any thoughts on what could be missing? Thanks for your patience and support here Sean!
Usually this is because you have a typo in your field name. Check it over very carefully to make sure your field names in your code match those in the tables. If you have spaces, put [ ] around the field name.
Hello, thank you for the video! I have run into a snag trying to execute the filter code. When I try to open my report I am receiving an error that says Access is unable to find the referenced form in this line: Set frm = Forms!Filter_Form. I have checked that the name is accurately spelled and am unsure of how to proceed. Do you have any advice for a situation like this? Thank you
How i can launch and filter report for more than one filed in the table with one combo box in the form. All three fields some time have same values in different date entry
In your criteria entry, just make it like "FieldA = '" & cboSeries & "' Or FieldB = '" & cboSeries & "' Or FieldC = '" & cboSeries & "'" Something like that. If you want, you can use And instead of Or if all three dates need to be the same.
Thank you for your help, I am Grateful but I followed your instructions in your video and it worked very will for one filed, then I wrote what you sent me but it give me empty report without results. Strfilter = "test1 = '" & cbotest_name & "' and test2 = '" & cbotest_name & "' and test3 = '" & cbotest_name & "'" Can you help please. And how I can connect the search with Specific date. And can we save pdf in the access and make print order button in access to print the saved pdf. I will be appreciated to you My best regards
Great video, very helpful! I just have one issue. When I go back and click on the Open Report button on the form, nothing happens. I didn't set the combo boxes using Sql. Is that what's causing this to not work?
You may need to "Enable" functionality if you see a yellow bar at the top of the screen. This can stop code from running. If you didn't specify acViewPreview in the code for your button, it will default to send it to the printer (or queue if you don't have one set up). Hope it works!
@@seanmackenziedataengineering Actually I completely skipped the button coding, omg. Once I added the coding for the button, it worked! :D Thank you!!!
Hi, I have a Question. I followed all your step but when i select any number and click on print button , it print all the data in the report not shown the result of one value which i selected. It showed all the information in the report. Here is the code Dim frm As Form Dim strFilter As String Set frm = Forms!Filter_Form 'must be open strFilter = "" 'If both are empty, show everything If Len("" & frm!cboDescription) = 0 And Len("" & frm!cboTest) = 0 Then Me.filter = "" Me.FilterOn = False Exit Sub End If 'Description If Len("" & frm!cboDescription) > 0 Then strFilter = "[Descriptionname] = " & frm!cboDescription End If 'Test If Len("" & frm!cboTest) > 0 Then If Len(strFilter) > 0 Then strFilter = strFilter & " And" End If strFilter = strFilter & " [Testname] = " & frm!cboTest End If 'Add filter Me.filter = strFilter Me.FilterOn = True End Sub
That's interesting. If frm!cboDescription and frm!cboTest are id number values then it will be fine. Otherwise, you should put apostrophes around the line, like: strFilter = "[Descriptionname] = '" & frm!cboDescription & "'" Another thing to check is that the "bound column" is correct in your properties of the combo box. You can select something and it looks like it is selected, but if the bound column is null then your logic will unset the filter and show everything. ie. 'if both are empty..
Nice to know how to do this in MS Access. Considering it's just 1 table with a couple of parameters I would prefer do this in MS Excel using a pivot table. Much faster cause no programming, many users are able to do the job themselves without the need to know any VBA, and far more flexible in case someone wants to change the report or alter the filters. I would rather use an Access report if the report is based on more than 1 table, data on which the report is based frequently changes while the report format remains the same, etc. Or perhaps that's just me;) As just an example how to do this in Access using VBA it's ok of cause, don't get me wrong.
You've hit the nail on the head! If my query has one table or many tables, it will not matter, since this technique will work for all cases. Developers can use this technique to give the filtering ability to many concurrent end users on the same data who don't have advanced abilities in Excel. Thanks for checking it out!
Great Video Sean I would like to use this method, using an "Unbound TxtBox" on the search form, tried using this Set frm = Forms!Search_Form strFilter = "" If Len("" & frm!txtKeywords) > 0 Then strFilter = "(txtKeywords) = '" If Len(strFilter) > 0 Then strFilter = strFilter & "" End If Me.Filter = strFilter Me.FilterOn = True Can you advise a solution, Geoff
Hey Geoff, for this you might want to change "(txtKeywords) =" to "[txtKeywords] In (" & strFilter & ")" That will use In so that you can give a list of values delimited with commas. If they are numbers, this is fine; if they are alpanumeric, then make sure to put ' around each one. The second block is redundant "If Len(strFilter) > 0 Then. You can remove that line and the line just after. Make sure to check that you have "End If" for each If statement. Cheers
Hi Sean, thanks for the great video. I'm not a developer, but I was able to get two dropdowns working. How can I build on this to get five dropdowns working? Adding another section as below returns all entries. 'Country If Len("" & frm!cboCountry) > 0 Then If Len(strFilter) > 0 Then strFilter = strFilter & " And " End If strFilter = strFilter & "[Country name] = Forms!Filter_Form!cboCountry" End If Thank you!
@@seanmackenziedataengineering Thanks! This includes the 3rd dropdown (Priority)...the one that is not working: Private Sub Report_Open(Cancel As Integer) 'Add a filter Dim frm As Form Dim strFilter As String Set frm = Forms!Filter_Form 'must be open strFilter = "" 'If both are empty, show everything If Len("" & frm!cbo_TechOwner) = 0 And Len("" & frm!cbo_Phase) = 0 Then Me.Filter = "" Me.FilterOn = False Exit Sub End If 'TechOwner If Len("" & frm!cbo_TechOwner) > 0 Then strFilter = "[Technical Owner] = Forms!Filter_Form!cbo_TechOwner" End If 'Phase If Len("" & frm!cbo_Phase) > 0 Then If Len(strFilter) > 0 Then strFilter = strFilter & " And " End If strFilter = strFilter & "[Project Phase] = Forms!Filter_Form!cbo_Phase" End If 'Priority If Len("" & frm!cbo_Priority) > 0 Then If Len(strFilter) > 0 Then strFilter = strFilter & " And " End If strFilter = strFilter & "[Priority] = Forms!Filter_Form!cbo_Priority" End If 'Add filter Me.Filter = strFilter Me.FilterOn = True End Sub
Hi Geoff, very good video but i have a question similar to what someone wrote a year ago: "Great Video Sean I would like to use this method, using an "Unbound TxtBox" on the search form, tried using this Set frm = Forms!Search_Form strFilter = "" If Len("" & frm!txtKeywords) > 0 Then strFilter = "(txtKeywords) = '" If Len(strFilter) > 0 Then strFilter = strFilter & "" End If Me.Filter = strFilter Me.FilterOn = True" i am having a difficult time getting this to work on the most simplest of levels using a test form with one unbound text field and one simple report searching one specific field. The report field is called 'CASEREF'; in the form, the field is called 'RE". I want the user to be able to enter more than one criteria separated by commands so that the report will filter based on those entries. Do you have a simple spreadsheed that shows how to connect those two (form and report) to filter correctly? Your help would be most appreciated, thank you
Good question. For example, say you have a date range. On your form, create txtDateFrom and txtDateTo text boxes. Under the format tab in properties, change the format to a date format. Then, in your filter you will say something like: MyDateField Between Forms!MyForm!txtDateFrom AND Forms!MyForm!txtDateTo Good luck!
Great video. I'm trying to add a date filter to it and am really struggling, I want my customer to be able to filter on Course Name as well as a date range. I am new to VBA and still have difficulty with syntax. Can you help? Thank You! Option Compare Database Private Sub Report_Open(Cancel As Integer) 'Add a filter Dim frm As Form Dim strFilter As String Set frm = Forms!ClassDateFilterForm2 'must be open strFilter = "" 'If all fields are empty, show everything If Len("" & frm!cboCourseName) = 0 And Len("" & frm!cboStartDate) = 0 And Len("" & frm!cboEndDate) = 0 Then Me.Filter = "" Me.FilterOn = False Exit Sub End If 'Course Name with No Dates If Len("" & frm!cboCourseName) > 0 And Len("" & frm!cboStartDate) = 0 And Len("" & frm!cboEndDate) = 0 Then strFilter = "[SCO_Course_Name] = '" & frm!cboCourseName & "'" End If If Len("" & frm!cboCourseName) > 0 And Len("" & frm!cboStartDate) > 0 And Len("" & frm!cboEndDate) > 0 Then strFilter = "[SCO_Course_Name] = '" & frm!cboCourseName & "'" & "SCL_Date Between #" & frm!cboStartDate & "# & #" frm!cboEndDate & "# End If 'Add filter Me.Filter = strFilter Me.FilterOn = True End Sub
Lol at the train whistle at 19:13, I couldn't hear it with my noise cancellation headphones on :-D
Thought it was a ship >_
Hello Sean, I tried the code =Count(*) in a textbox on the from and it worked perfectly. So I say you are the world's best Data Engineer. Thank you.
Thanks! Glad that worked for you.
This worked perfectly for me. Thank you!
Glad it helped!
Thanks for explain it so perfectly. i'm have no IT or a coder background and i'm building a mini database, so this video is helping me so much.
Glad it helped! Good luck on your project :-)
Hi! thank you for the wonderful tutorial i just had one issue i wonder if you can help me out.. when i click open report btn without any filters it open the form but when i chose something from the drop down menu i got a highlited error on DoCmd.OpenReport
No problem! Make sure you put arguments after DoCmd.OpenReport, and make sure you spell the report exactly.
@@seanmackenziedataengineering worked thank youu! also one thing that some program records duplicates in the report because each program have more than one trainer.. so the record duplicate in the report but with different trainer name.. is there is a way to keep one record only with all the names instead of this? the two tables program, trainer related through compiste table program_trainer..
Great video! I'm trying to do this exact scenario but to open in access (like a table?). I need a user to select 2 of the filters and then create a table so that we can add data entry. Project is more like a checklist with a series of questions to be answered. Is it possible to open in a table instead of a report?
Good question! Yes you can do this. In this case, you'll make a subform:
ua-cam.com/video/tQfAol75j_I/v-deo.html
you can do data entry like a table and it will organize according to your dropdown selections on the master form.
If you really want it to look like a table, on the subform go to properties > Format > Datasheet view.
is there anyway to select multiple countries, say you want to sort by canada and spain together?
You could do something like make a text box, then load the text box each time the user selects from the combo. Select once, the box says 'Canada'. Select again and the box says 'Canada', 'Spain'. Then, in your criteria you can just change the VBA line:
strFilter = strFilter & "[Country name] In (" & frm!txtCountries & ")"
Note the brackets as we are using an IN clause. Your challenge is to load the text box txtCountries 🛠
Thank you very much
This video helped me fix a problem
Great to hear! Happy to help.
Thank you Sean for the videos, they have been a big help. One question I have is how would I be able to use multiple filters to filter a report?
Good question! You can add more to your report filter, in a similar way to how I do it here on a form:
ua-cam.com/video/uq3cgaHF6fc/v-deo.html
Anyway this can be done without code? I recall the Northwind database having a quick setup, and I need to do this pretty quickly.
Yes, you can do a simple version of this with Macros, though it may not be as flexible.
@@seanmackenziedataengineering I’m still getting an error in the form of a parameter. Please let me know if you have any time for online session.
Hey! Is there a way to do the dropdown in the report? So I would never have to go back to form and so on. I did this hole form selection in a criteria with queries. That's more simple. But I want to choose smtg from a dropdown in myreport, and then auto refresh. :)
You can do this by putting a subreport on your form! So, when you change selections on the form, the report will change for the new selection. Make a big window for your subreport so it looks good.
How does this work with queries? I want to filter the report based on the form inputs from a report built off a query. Does this only work when the Report is built on a table.
You can set the RecordSource of your report to your query instead of table and then it will work!
Hello Sean, I am trying your code but can't get the second filter to work. It askes me in a message box for the second filter but Item even though I have the code exactly like you have with my Combo List. When it gets to the report the items from the second list are still there. How do I fix this? My combo boxes work and the first filter works but not the second one. I don't know why it brings up the message box. If I get this to work that would be great! I am new to this as well.
I would check your spelling of the second combo box in your code. Make sure it matches exactly to how you named the combo on the form. This is the most common reason for the problem. Access can't find the name you put for the second combo in your code. If it has a space in it, surround the name with [ ] If it still is broken, go ahead and post your code here and I will look!
@@seanmackenziedataengineering
If Len("" & frm!cboWeekEnd) > 0 Then
If Len(strFilter) > 0 Then
DateSel = strFilter & " And "
End If
strFilter = DateSel & "[Week Ending] = Forms!Creat_Report!cboWeekEnd"
End If
I am trying to filter a date so I am not sure if that makes a difference or not, but the combo box is cboWeekEnd (This is Copy and pasted)
Thank you so much for the help
@@harshmancegroupllc4517 Nice! Ok, our goal is to continue building strFilter in this block, so you can change the two instances of DateSel to strFilter so that the condition for the second combo is added to the string.
@@seanmackenziedataengineering I had it like that before but it did not work. I changed it thinking it got to complicated. I just changed it back and it is still doing it. Is it because it is using a date or could it be something else? It also wont filter the date when it asks to the date.
@@harshmancegroupllc4517 is there an e in the last line for Create? The date will be fine since we’re using parameters.
Sir, I have two tables with two separate forms. The first table based on the
first table has a calculated text box. I want to show the textbox data
to a field to another form. How to do it. Thanx and regards.
You might be able to reference it, using an expression. Check it out:
ua-cam.com/video/I2JU95lnz_w/v-deo.html
Great video .
i have a question for my project.
So i have a continuous dataentry form that show all the data in my table. And there's no querry between them.
How can i make a report that print only the filter record that i filtered in my form?
Thank you in advance ❤
You certainly can do that! In the report's On Open event:
Me.Filter = Forms!MyForm.Form.Filter
Me.FilterOn = True
Your form has to be open and filtered how you want when the report opens.
Good luck on your project!
I have a drop down menu that selects a product and the price auto. populates in the price field, I am trying to get the total field to update as soon as I enter each product
Great question. You can just add one more line in your after update; something like Me!txtTotal = Me!txtPrice * Me!txtQuantity
Are you using a continuous (list) form or a columnar form?
I appreciate the tutorial. I have an additional question. How would you write the code for a second filter for the same field? Say I wanted the reports for Canada AND Sweden to show up?
You can, just copy and paste your country drop-down onto the same form, then rename it to cboCountry2. In your code, you can say:
'Country
If Len("" & frm!cboCountry) > Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " And "
End If
If Len("" & frm!cboCountry2) > 0 Then
strFilter = strFilter & "[Country name] In ('" & frm!cboCountry & "', '" & frm!cboCountry2 & "')"
Else
strFilter = strFilter & "[Country name] = '" & frm!cboCountry & "'"
End if
End if
@@seanmackenziedataengineering Thank you for the help. It was very appreciated.
Very helpful video, thank you! Is there a way to do this with multiple selections? Using the example you provide, what if I wanted to report the data for, say, 12 out of 50 countries? Thank you!
Hi, can you help me?
How i can launch and filter report for more than one filed in the table with one combo box in the form.
All three fields some time have same values in different date entry
In your criteria entry, just make it like "FieldA = '" & cboSeries & "' Or FieldB = '" & cboSeries & "' Or FieldC = '" & cboSeries & "'"
Something like that. If you want, you can use And instead of Or if all three dates need to be the same.
Hi sean. Thanks for your Great Tutorial but I have question I hope You Can answer my question. I create 2 tables(1-main , 2-money resource) , second table is combo box field in another table (combo box for money resource) but when I want filter report by option in combo box (money resource) and I press Print button It show nothing just shows empty report . What should I do ? I create relationship from money resource id to combo box filed in second table . and also I create report from main table and create combo box from money resource table?????
You can check what value was actually selected in the combo. Sometimes you see a text description but the combo "value" is actually an ID number. Open your form and select values, then go Ctrl+g to open immediate window, then in the white area type in:
?Forms!MyFormName!MyComboName. Did you get the expected value? Would that value work in your query?
🛠
Sir, Satyaban from India. I want to preview a report based on the item selected by me on a form having parameter. For example, I have a form with a drop down combobox and report will only show the data of the particular item selected from the combo box. Is it possible Sir. Regards
Thanks for your question! You can do this using the method in this video. Cheers!
This is awesome! One question I do have is could I include it to where I have created multiple reports based on specific queries? So when I select an id (in my specific database), it tells me which reports are associated with it and then generates a filtered report?
You can! You would use the On Current event of the form to see which ID is selected on your form, then update the possible report list from there. You could use a Select Case or something to update the possible reports. This would be done in VBA.
Hi, thank you for a great video. I am brand new to coding, so I wonder if you could help me out a bit.
When I use the drop down menu and press open report, I get a new dialog box that ask me to ad parameters. ?. When I do, I get the report with all content, no filtrations.
I guess there is a typing error, but I can’t find it
Thank you again for a greate series of videos
Rune
Hi Rune, that's really cool that you're starting to code. It is a cool journey!
The issue you are having is almost certainly because you made a slight spelling error in your code. The parameter pop up in this case tells you that it is looking for a value, or the spelling of the field name does not match the name you put into the code. If your field name has spaces in it, you might need to put it like this: [field name] in your code.
That is really a great video, but I faced a problem, when I introduce the fields with text format it gives me a positive result, it means that my report get filtered as my wish but when I introduce a numerical field or date field the result is negative and gives me an error message. would you tell me if there is any other code for numerical and date fields to filter?
Remove the ‘ apostrophe from around numbers. Use # around dates in your code. ie. Basically substitute ‘ for # in code for dates. Great question!
@@seanmackenziedataengineering
thanks a lot for getting back to me.
'I apologize sir but I did not get your point, what you describe me using ‘ apostrophe and # in the bellow code:
'Date_enrol
If Len("" & Frm!cbodate_enrol) > 0 Then
strFilter = strFilter & " And "
End If
strFilter = strFilter & "[Date_of_enrolling] = '" & frm!cbodate_enrol & "'"
or in this code
'Date_enrol
If Len("" & Frm!cbodate_enrol) > 0 Then
strFilter = strFilter & " And "
End If
strFilter = strFilter & "[Date_of_enrolling] = forms!Filter_Form!cbodate_enrol"
thank you in advance
@@jaberdawran2525 if it is a date then just change:
strFilter = strFilter & "[Date_of_enrolling] = #" & Forms!Filter_Form!cbodate_enrol & "#"
@@seanmackenziedataengineering
That's great, thank you sir.
As the last question can I add filter range in this coding?
I want to filter this single date field of within a desired interval.
like I want to filter that from 1/1/2022 to 5/13/2022 how many enrollments have I had in the position of Engineering in Canada?
Thank you in advance
Sean, please how do i create counts for filtered form
Good question. You can use a textbox with =Count(*) in the control source.
I have made a columnar report. But the report is displaying more than 1 page. Sometimes, it displays more than 20 pages. But I want to display one record in one page at a time. How to handle it Sir.
You need to open the design view, then check the width. For example, A4, or Letter size. Make sure that you move and resize all of the fields to fit within your width minus margins. So, if you use letter size 8 1/2"x11" with margins of 1", then your items must fit the width of 6 1/2". You can use the ruler bar at the top of the report to check it. THEN make sure to also decrease the size of the form where the text controls are placed, by grabbing the edge of the form and resizing the width to be less than or equal to 6 1/2" (in this example). Rerun your report and it should display with less extra pages.
Great video, thank you very much
I want to search between two dates as well and i don't want to use the query like putting criteria under date ( between text box 1 and 2 in the form)
I want it the same way you did you said you can put dates
Can you help me please
Great question! You can do it. Make sure to set your text box formats to a date format. Then, in the filter string I mention in the video, you will use # around the value instead of '.
ie. strFilter = "MyDateField Between #" & date1 & "# And #" & date2 & "#"
date1 and date2 might also be like Forms!myform!date1 etc.
@@seanmackenziedataengineering Thank you very much
@@seanmackenziedataengineering Ok, it works 👍 but with one problem.
No matter i put in the input mask in date field in the form, the report read it as if it is mm/dd/year
If i put 01_ sep_21 for example,
The report give me records for 09/ 01/21 (( 09_ jan_21)).
I'm really sorry for asking again, but can you help me
Thank you
@@HatemEsmat Returning records for 01-sep-21 looks like how it should work. It returned 09_jan_21? Is your file .mdb or .accdb?
@@seanmackenziedataengineering my file is .mdb
How could you filter a sub report when you are sending the main report directly to the printer? I have been able to do it in print preview by setting it from the main report load event, but that event does not seem fire when sending directly to the printer.
Great question! You could try using Report Open for this, but if you're still having trouble, just set your subreport RecordSource to a query, then just change the query sql dynamically at run-time.
1. Subreport record source was TableA
2. Create query QueryA as Select * From TableA
3. Set Subreport record source to QueryA
4. At run-time set QueryA SQL As Select * From TableA Where ....
5. When report runs, subreport is already filtered.
@@seanmackenziedataengineering Thanks, can you elaborate on step 4. How do you set the SQL of a saved query at runtime?
Something like:
Dim qdf as dao.QueryDef
Set qdf = currentdb.QueryDefs("QueryA")
qdf.SQL = "Select * From TableA Where.."
qdf.Close
You might have a button that starts the report. You can do it there before opening the report, or just put it in ReportOpen
@@seanmackenziedataengineering Thank you, I will give it a try...
@@seanmackenziedataengineering An unrelated question I have been wanting to ask someone. Do you have any advice or recommendations for taking an existing MS Access app and converting it into a web app? Are there any tools for this or do you just have to rebuild it?
Hello Sean, this is great!
One question, what if one of the two filters is a numeric value? I am getting an error message when filtering the second field which I think might be related to the data type.
The first filter that I am using is a text string and the second filter is a numeric value, if I run it only with the first combo box it works great but as soon as I input the second value it breaks, any thoughts on it?
I am new in vba by the way lol.
Thanks a lot!
Great question! In your filter string, when you use a number, you don't need to put the ' symbol around the value. So, you can go:
"[MyField] = " & frm!MyControl
Instead of
"[MyField] = '" & frm!MyControl & "'"
If you have a date in a control:
"[MyField] = #" & frm!MyControl & "#"
@@seanmackenziedataengineering That is awesome! not getting an error message anymore now but for some reason the second filter is not working along with the first one, it kinda offsets it and I only get the second value filtered instead of both any thoughts on what could be missing?
Thanks for your patience and support here Sean!
@@raulsansores6271 you can post a few lines of code and maybe I can see if where the problem is.
When I hit the open report button, a box pops up asking me to "Enter Parameter Value". Any idea why it is asking for this?
Usually this is because you have a typo in your field name. Check it over very carefully to make sure your field names in your code match those in the tables. If you have spaces, put [ ] around the field name.
Hello, thank you for the video! I have run into a snag trying to execute the filter code. When I try to open my report I am receiving an error that says Access is unable to find the referenced form in this line: Set frm = Forms!Filter_Form. I have checked that the name is accurately spelled and am unsure of how to proceed. Do you have any advice for a situation like this? Thank you
Try:
Set frm = Forms("Filter_Form")
and make sure that Filter_Form is open!
@@seanmackenziedataengineering It worked! Thank you!
@@maaikebos2570 Great! Glad that worked
How i can launch and filter report for more than one filed in the table with one combo box in the form.
All three fields some time have same values in different date entry
In your criteria entry, just make it like "FieldA = '" & cboSeries & "' Or FieldB = '" & cboSeries & "' Or FieldC = '" & cboSeries & "'"
Something like that. If you want, you can use And instead of Or if all three dates need to be the same.
Thank you for your help, I am Grateful but
I followed your instructions in your video and it worked very will for one filed, then I wrote what you sent me but it give me empty report without results.
Strfilter = "test1 = '" & cbotest_name & "' and test2 = '" & cbotest_name & "' and test3 = '" & cbotest_name & "'"
Can you help please.
And how I can connect the search with Specific date.
And can we save pdf in the access and make print order button in access to print the saved pdf.
I will be appreciated to you
My best regards
Hi Sean,
I have tried but when i hit run it shows Error (Object require). please help
I am also facing similar kinds of issues
Make sure the form stays open when you open the report! If you still have issues, please post your code here. Cheers!
🛠
Great video, very helpful!
I just have one issue. When I go back and click on the Open Report button on the form, nothing happens. I didn't set the combo boxes using Sql. Is that what's causing this to not work?
You may need to "Enable" functionality if you see a yellow bar at the top of the screen. This can stop code from running. If you didn't specify acViewPreview in the code for your button, it will default to send it to the printer (or queue if you don't have one set up). Hope it works!
@@seanmackenziedataengineering Actually I completely skipped the button coding, omg. Once I added the coding for the button, it worked! :D Thank you!!!
@@jamesbeard8855 Glad it worked!
Hi, I have a Question. I followed all your step but when i select any number and click on print button , it print all the data in the report not shown the result of one value which i selected. It showed all the information in the report. Here is the code
Dim frm As Form
Dim strFilter As String
Set frm = Forms!Filter_Form 'must be open
strFilter = ""
'If both are empty, show everything
If Len("" & frm!cboDescription) = 0 And Len("" & frm!cboTest) = 0 Then
Me.filter = ""
Me.FilterOn = False
Exit Sub
End If
'Description
If Len("" & frm!cboDescription) > 0 Then
strFilter = "[Descriptionname] = " & frm!cboDescription
End If
'Test
If Len("" & frm!cboTest) > 0 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " And"
End If
strFilter = strFilter & " [Testname] = " & frm!cboTest
End If
'Add filter
Me.filter = strFilter
Me.FilterOn = True
End Sub
That's interesting. If frm!cboDescription and frm!cboTest are id number values then it will be fine. Otherwise, you should put apostrophes around the line, like:
strFilter = "[Descriptionname] = '" & frm!cboDescription & "'"
Another thing to check is that the "bound column" is correct in your properties of the combo box. You can select something and it looks like it is selected, but if the bound column is null then your logic will unset the filter and show everything. ie. 'if both are empty..
13:48 couldn't you have used an Else there?
Yes, you could!
Nice to know how to do this in MS Access. Considering it's just 1 table with a couple of parameters I would prefer do this in MS Excel using a pivot table. Much faster cause no programming, many users are able to do the job themselves without the need to know any VBA, and far more flexible in case someone wants to change the report or alter the filters.
I would rather use an Access report if the report is based on more than 1 table, data on which the report is based frequently changes while the report format remains the same, etc. Or perhaps that's just me;) As just an example how to do this in Access using VBA it's ok of cause, don't get me wrong.
You've hit the nail on the head! If my query has one table or many tables, it will not matter, since this technique will work for all cases. Developers can use this technique to give the filtering ability to many concurrent end users on the same data who don't have advanced abilities in Excel. Thanks for checking it out!
Great Video Sean
I would like to use this method, using an "Unbound TxtBox" on the search form, tried using this Set frm = Forms!Search_Form
strFilter = ""
If Len("" & frm!txtKeywords) > 0 Then
strFilter = "(txtKeywords) = '"
If Len(strFilter) > 0 Then
strFilter = strFilter & ""
End If
Me.Filter = strFilter
Me.FilterOn = True
Can you advise a solution, Geoff
Hey Geoff, for this you might want to change "(txtKeywords) =" to
"[txtKeywords] In (" & strFilter & ")"
That will use In so that you can give a list of values delimited with commas. If they are numbers, this is fine; if they are alpanumeric, then make sure to put ' around each one.
The second block is redundant "If Len(strFilter) > 0 Then. You can remove that line and the line just after. Make sure to check that you have "End If" for each If statement. Cheers
Hi Sean, thanks for the great video. I'm not a developer, but I was able to get two dropdowns working. How can I build on this to get five dropdowns working? Adding another section as below returns all entries.
'Country
If Len("" & frm!cboCountry) > 0 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " And "
End If
strFilter = strFilter & "[Country name] = Forms!Filter_Form!cboCountry"
End If
Thank you!
You need to make sure that this section goes before the Me.Filter statements. Can you post the whole Sub? Thx!
@@seanmackenziedataengineering Thanks! This includes the 3rd dropdown (Priority)...the one that is not working:
Private Sub Report_Open(Cancel As Integer)
'Add a filter
Dim frm As Form
Dim strFilter As String
Set frm = Forms!Filter_Form 'must be open
strFilter = ""
'If both are empty, show everything
If Len("" & frm!cbo_TechOwner) = 0 And Len("" & frm!cbo_Phase) = 0 Then
Me.Filter = ""
Me.FilterOn = False
Exit Sub
End If
'TechOwner
If Len("" & frm!cbo_TechOwner) > 0 Then
strFilter = "[Technical Owner] = Forms!Filter_Form!cbo_TechOwner"
End If
'Phase
If Len("" & frm!cbo_Phase) > 0 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " And "
End If
strFilter = strFilter & "[Project Phase] = Forms!Filter_Form!cbo_Phase"
End If
'Priority
If Len("" & frm!cbo_Priority) > 0 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " And "
End If
strFilter = strFilter & "[Priority] = Forms!Filter_Form!cbo_Priority"
End If
'Add filter
Me.Filter = strFilter
Me.FilterOn = True
End Sub
Hi Geoff, very good video but i have a question similar to what someone wrote a year ago: "Great Video Sean
I would like to use this method, using an "Unbound TxtBox" on the search form, tried using this Set frm = Forms!Search_Form
strFilter = ""
If Len("" & frm!txtKeywords) > 0 Then
strFilter = "(txtKeywords) = '"
If Len(strFilter) > 0 Then
strFilter = strFilter & ""
End If
Me.Filter = strFilter
Me.FilterOn = True" i am having a difficult time getting this to work on the most simplest of levels using a test form with one unbound text field and one simple report searching one specific field. The report field is called 'CASEREF'; in the form, the field is called 'RE". I want the user to be able to enter more than one criteria separated by commands so that the report will filter based on those entries. Do you have a simple spreadsheed that shows how to connect those two (form and report) to filter correctly? Your help would be most appreciated, thank you
This is a great topic, so it was a good one for today's video! Take a look: ua-cam.com/video/yFCBWH8YHjk/v-deo.html Thanks!
How to add search range in it?
Good question. For example, say you have a date range. On your form, create txtDateFrom and txtDateTo text boxes. Under the format tab in properties, change the format to a date format. Then, in your filter you will say something like:
MyDateField Between Forms!MyForm!txtDateFrom AND Forms!MyForm!txtDateTo
Good luck!
Great video. I'm trying to add a date filter to it and am really struggling, I want my customer to be able to filter on Course Name as well as a date range. I am new to VBA and still have difficulty with syntax. Can you help? Thank You!
Option Compare Database
Private Sub Report_Open(Cancel As Integer)
'Add a filter
Dim frm As Form
Dim strFilter As String
Set frm = Forms!ClassDateFilterForm2 'must be open
strFilter = ""
'If all fields are empty, show everything
If Len("" & frm!cboCourseName) = 0 And Len("" & frm!cboStartDate) = 0 And Len("" & frm!cboEndDate) = 0 Then
Me.Filter = ""
Me.FilterOn = False
Exit Sub
End If
'Course Name with No Dates
If Len("" & frm!cboCourseName) > 0 And Len("" & frm!cboStartDate) = 0 And Len("" & frm!cboEndDate) = 0 Then
strFilter = "[SCO_Course_Name] = '" & frm!cboCourseName & "'"
End If
If Len("" & frm!cboCourseName) > 0 And Len("" & frm!cboStartDate) > 0 And Len("" & frm!cboEndDate) > 0 Then
strFilter = "[SCO_Course_Name] = '" & frm!cboCourseName & "'" & "SCL_Date Between #" & frm!cboStartDate & "# & #" frm!cboEndDate & "#
End If
'Add filter
Me.Filter = strFilter
Me.FilterOn = True
End Sub
Hey Susan,
Looks pretty cool! Neat project. Try this in your last filter statement:
strFilter = "[SCO_Course_Name] = '" & frm!cboCourseName & "' And SCL_Date Between #" & frm!cboStartDate & "# And #" & frm!cboEndDate & "#"
Thanks
Glad it helped!
thanks
Glad you enjoyed it!
Hi how are you?
Good thanks! And you?