Austin, just wanted to say Thank you! Your videos are really helpful in helping me refresh my access. Love that you get to the point, and you don't add a lot of useless information. Thanks
Hello Tewan, I even had to create a channel in order to comment on your video. I truly want to thank you for all the time you have put on this tutorials. I am workiing on a Database Project for work and I am implementing many of the things you have shown. Truly thankful for doing this and sharing them with the world. Thanks a lot!
Great job on this Austin, I had a similar need where I wanted to use a record from one table to limit the list box and your demo did the trick! Thanks!!
Great video thanks a lot. Had a parameter error at first but then noticed some of your comments below and was able to resolve the issue. Thanks a lot for posting this, very helpful.
I have some slightly different experiences with MS Access 2016. The index based combo box filter works without issue. However, I have two scenarios which are giving blank drop downs but the query works in the design view. Scernario 1: Target for the filter is storing the string as the bound column rather than the Integer when the value is saved (primarly to avoid just index numbers showing in reports etc). So I use the crosstab and select to group the stored text to give a single distinct list of values for my combo box, and in the test when I ran the query shows the distinct list of values but when I save and move back to the form view I get the correct number of rows in the combo box but they are all blank (Null). The same happens with a grouping of a free text field - the query is working when you click Run! but nothing shows in the dropdown for the combo box..... ANY IDEAS?
Hi, where do I see the name of my subform in the splitform? I've created the search box using combo box. But i cant seem to make the datasheet in the subform filter to the selected value.
Hi, Great video and very helpful. I've ran into a problem though and I've looked at it from every angle and can't seem to figure it out. I have 4 levels of cascading combo boxes. When I select a value for the first box, the second box filters the available options perfectly and I'm able to select one just fine. When I get to the next, third combo box however, I immediately get the "This expression is typed incorrectly or it is too complex..." error and there are no values available to select from. Same story for the 4th one. The queries for each of the 4 combo boxes reference are virtually exactly equivalent to each other as well as the criteria code. I just can't figure out why the concept works fine for the second box but won't work for the third or fourth when they, again, are virtually exactly the same. I have the right VBA to requery the next box as each box is filled, each row source is pointing to the right place, the boxes, queries, and tables are all named and referenced correctly, everything seems to be right, but it won't still work. I'm completely stuck. Anyone?
Hi Austin, I'm a teacher and designed a dbase containing students grades for certain projects during each semester. I would like to design an unbound search box that will have drop down list field from another table, once I choose from the list, it will generate a report which I've already designed/sorted/calculated grade average for each student. example: I will click on the report and an unbound search box pops up. I will click on the drop down list and choose from the list coursenumber, semester, and project (231-spring2016-project1), then hit command box run report and it will generate a report from an existing report design format and let me preview before printing.
Hi Austin, Could you please explain not only what you do but why you do while doing something so we can have a better understanding. Also bigger font while writing code would be helpful...... Thanks for your knowledge sharing.....
hello austin, don't know if you are using the same Acess 2010 like me but i have an issue with the use of the subform/subreport and combo box design tools. when i click and drag them to the sheet, instead of showing me the subform or combo box wizard table i have directly the property sheet table which is very frustrating...... please help me i've been trying to debug it since .......
Hey Austin! This has been the most helpful tutorial on this subject that I have seen so far. My only problem is that it is only displaying the first matching record in my table (I have several that should match). Any suggestions?
What if the form you are using as a sub-form gathers information from multiple tables? While the form does take me to the correct record, not all of the fields are filled in. Specifically the ones that are obtained from another table.
can you use both combo boxes to seearch for a specific record? like customer type = individual and gender = male, the table should now display all individual male customers, is this possible? thanks!
Hi, I am having difficulties when running these two forms: - frmFirstPage> when I change from Design to Form View it displays error :"Create Search Form cannot find the referenced form 'Navigation Form'" and points to line: Me.user = Forms![Navigation Form]!TxtLogin - frmAddUser when click on Close displays error : "The Command or action 'BrowseTo' isn't available now", line is: DoCmd.BrowseTo acBrowseToForm, "frmFirstpage", "Navigation Form.NavigationsubForm", , , acFormEdit Also, when I run .accdb it show pop-up "Your comp. LoginID has not setup...". What could be a problem? Thanks!
Hi, i cant get it to work on my form. When I click a choice in the combo box, the form shows the correct record but the subform (datasheet view) does not filter. It shows all data in the datasheet and highlights the first record that matches the combo box.
I truly wished this worked, but I'm getting the same error that numerous other posters are reporting. Is there a clear answer to the "Compile Error: Method or data member not found" error?? I know others in the comments have had the same issue but no one has posted a clear answer. I've added the extra quotes as I'm dealing with text and not numbers and the error continues. I've read that this error is tied into the use of 'Me' but I can't confirm that is what is causing the error in this case. If possible I would really appreciate an answer or some direction as to why this error is occurring. What should I be looking for to fix this?
I noticed at about 9:49, you have dropdown selector (listboxes?) for Gender and Customer_Type_ID on the form. How did that happen? Mine doesn't look like that and the coding isn't working. I'm really new at Access. Thanks!
Hi Austin , how can i send report based on the value of that combo box by email i khow how can i send report by email with macro , but it send all data, idon't want that ,i need the results of combo box for example or any other way to filter data by form. thx
Hi, I have one question, I made one very simple database it has five fields. One of them is payment combo box. I filled it according money in money out. When I generate report it sums whole in and out sum together. How can i separate paid out and paid in subtract from total sum please? Thanks
Can hou also do this with a query? Instead of a subform? The VBA doesn’t work correctly. In the last 2 line of the VBA you use a tbl..... but can you also use a query?
Hello Austin, this is exactly what I have been searching days for. Will this still work if I have alphanumeric variables in my combo-box? I have "FY 16-17", "FY 17-18" that I am trying to let user choose from.
hello! when I typed "Me. ..." with all my info but I am not getting the auto-filled or auto-added. The last two lines are giving me errors. Help please.
What should i do if i want all three filter applied in final data? Like if i select customer type as business and at the same time i select gender as male then the final data should reflect data after applying both the filters. Please help
As you very well defined how to select subform value by using combobox. I want to use one combobox to update / replaced only selected values by combobox. Thanks in advance
Hi Austin, my query did not display result as intended for certain ids, but worked for other ids. Can you please help? Can I show you the error via virtual meeting?
Hi austin- i've followed the step on the video as far as i know. The difference is that i created an append query and link my table to the link table. Now i keep getting error as my subform is not referenced.
thanks for sharing the video sir, it was very helpful. Can you make a video combining this video with your other video with date range search and also with print report button? thanks
Hi, It doesn't work for multiply items subform. I have tried to put in subform data from two tables. After applying the combobox cells from second tables have value #Name? Could you please advise a code for multiply items subform?
that is part of Visual Basic coding. Its a created array to store the data. Its not actually a location in the database. he could have named it any thing like Dim my1234 AS String and it would have still work as long as he called it back using the same name.
If you can FilterOn then you can disable Filter by using this: Me.FilterOn = False If you use SQL then you use this: strSQL = "Select * from tableName" Me.RecordSource = strSQL Me.Requery
Dear Sir, appreciate your lecture. can I use Date in "00-xxxx-00" format as a criteria for a where conditions in access form Macro building? how? can you give me a detail coding which I should put in the where condition? thanks a lot
Hi good day .. Need your help.. In Microsoft access if we want the form to display total in numbers.. For example the total of new job, total of pending jobs, total jobs waiting for spare parts and so on.. Is it possible
Hi. I copied your code exactly. but when I choose a value from combo box, I keep getting error "3251" "operation is not supported for this type of object". could you help me please?
how... if. Im using that subform.. not a table form.. I mean.. the subform is come from query. I trying that ur steps, but it doesnt working. Could you help me. bcuz. I have to use that query..cause there's formula on it.
Hi Austin, I follow just like in the video. It work but mine only filter one in the time. If I choose 1st cbox then when I choose the 2nd cbox, it change only follow the 2nd cbox. It only filter the whole table, not multifilter. What it's happen? I want it filter the 1st selection cbox. Am I missing something?
If you want to filter 2 combo boxes together then you need to use AND operator for your criteria. for example, "Select * from Customertable Where ([Gender] = '" & me.CboGender & "' AND [State] = '" & me.CboState & "')"
Hello, do you have a video tutorial on how you created your report? In your second video, you used the same report for all three categories (Customer, gender, and state). How did you do that and how were you able to get a total ...
Hi, thanks for all your tutorials , they have been really helpful. I have a question, is there a way to export these results to excel? Or is this for display only? Thanks.
After following your instructions I am getting a syntax error. When I debug It shows the error on the record source line. Please help. Dim myReagent As String myReagent = "Select * from Receipt&Verify where ([Product_ID] = " & Me.CboReagent & ")" Me.ReceiptVerifySubform.Form.RecordSource = myReagent Me.ReceiptVerifySubform.Form.Requery
Hi Austin, how did you learn Access VBA? I mean what book have you read or some other resources? I'm trying to learn access vba, but I don't know how, I've read a few books, but they can't help me.
Hi! Great tutorial, quick question though, is there a way to do this with Dates? Such as having a combo box that uses Dates in a Date/Time format. I've tried a few different things to no avail. Thanks!
it also will work with date field like:task = "Select * From QryEmpPayDetail Where [PayDate] >= #" & Me.textFieldDate1 & "#" & "And [PayDate] >= #" & Me.textFieldDate2 & "#"
I have a problem. I created 2 tables - ItemGrouptbl where there are 2 fields - ItemID (autonumber) and ItemGroup (group names). The main data is in a table ItemMastertbl where the field for search is ItemGroup along with other data. The Subform created linked to ItemMaster is called ItemMasterSubFrm. The Code after event in the cbo box linked to ItemGrouptbl is as below. Private Sub cboItemGroup_AfterUpdate() Dim SGroup As String SGroup = "Select * from ItemMastertbl where ([ItemGroup] = " & Me.cboItemGroup & ")" Me.ItemMasterSubfrm.Form.RecordSource = SGroup Me.ItemMasterSubfrm.Form.Requery End Sub I get error runtime on Me.ItemMasterSubfrm.Form.RecordSource = SGroup which saya "3464 - data type mismatch". It appears to be simple but with no VBA knowledge I cant resolve this Please help!!
Hi Austin Thanx for making this video. I wanna know is there a way i can select no value and get the full table. When i select a value and get filtered results in sub forum but when i delete value from combo box i get an error msg.
I watched your first example and copied the code exactly and I kept getting an error. I was wracking my brain for a few hours and then decided to watch the second example to see if I had missed anything. It was then I saw you were getting the exact same error as me. This is because I was using a string and although you define the first example as a string it was really a number. How frustrating! In future I will watch the whole video before attempting to replicate it.
ty but don't think this is a cascading combo box. i believe the idea is to limit the item to reach a specific record. box1 generates box 2 choices, box2 generates box 3 choices. then you have a specific record to change. You didn't mention if this was a sub-form underneath.
Thank you for responding. Is that any possible coding or method to start using VBA in access? Here is one of my coding.. Private Sub cboState_AfterUpdate() Dim myState As String myState = "Select * from CUSTOMER where ([state] = '" & Me.cboState & "')" Me.CUSTOMER_subform.Form.RecordSource = myState Me.CUSTOMER_subform.Form.Requery End Sub I'm not sure what goes wrong here..
Can anyone help me . I have combo box and text box for data entry. Is there any way when i select item listed on combo box and enter anything on textbox would go to the sheet where i want to put. But combo box will only find the item on the sheet of what was being selected . Only the text box will be entered once selected item found
I have curated a small data base, I would like to select forms using a combobox along with a command button to filter. can you show me how to accomplish this ? Just started using access 1 years ago.
Could you please explain me.tb1_Customer_subform1 ? The subform you created didnt have _ before subform . Also it didnt have 1 after subform Is that something excel automatically provide upon typing me. and the list pops up and you select?
I have the subform names tbl_Customer subform1. it names subform1 because I already have another subform names tbl_Customer subform. Number 1 is not the case. it shows Customer_subform because the VBA auto added it when you type me.tbl_ and picked Customer subform. the VBA will auto filled for you as Me.tbl_Customer_subform as one word. Me.tbl_Customer subform will not work because it has two words. However you can use bracket [] like Me.[tbl_Customer subform] You will see the similar auto fill-in with under line (_) on click event like: Private Sub Command386_Click() 'your code End Sub
After typing the VBA code and trying to use the combobox search, i recieved an error message " compiled error : sub or function not defined" what can i do to solve this problem ?
Hi Austin, the table I am using is linked to an excel document. When I try to filter using the combo boxes I always get a Run-time error '3131': Syntax error in FROM clause. Do you know if the linked table could be a cause of the error? And is there any way around that?
I had also problem with this it says Run-time error '3131': Syntax error in FROM clause Here the code I use Dim myGatepass As String myGatepass = "Select * from Data Entry Form where ([Gatepass]) = " & Me.cboGPSeries & ")" Me.Data_Entry_Form_subform.Form.RecordSource = myGatepass Me.Data_Entry_Form_subform.Form.Requery End Sub Note: the 3rd line is that gave an error its been Highlighted yellow when I click Debug when Run-time error 3131 appears. Could you help me with this?
hi austin, thanks for your insightful explanation..i am writing a similar code but keep having this error and was hoping you could help....i am using a textbox to collect the search criteria...i tried using the "Like" statement to return my resultset..but i keep getting a mismatch error 13 display message....can you help me here
Hi, thank you for the videos, I have learned lot from them. Please help me:My invoice total field is suddenly shown Blank. it was working fine for months, not sure why now its field shows Blank, Same with my Reports, the Total fields are now shown Blank.
Is better just to create a query (in query design) as data source and put forms! (with build)... in the criteria on the same line ( in order to be an "and", cumulative) . Is much more easy, and without code.
@@austin72406 Hi again! in ur video did you correct the form to from? or it automatically turn to from? anyway I used FROM and I'm getting syntax error in FROM clause. And in your tbl_customer_Type did you use wizard to list out the customer type?
you compare the data from text box that you typed in to the field name in the table. If it matches then show the record for that name matching the text box. And then put the result or data source from search to the form to show all result. I have a download file for search customer here: www.iaccessworld.com/downloads/customer-order-payment/
+austin72406 thank you, I will try it later, how about getting the total of each category, I have field name which have 111, 112, 113, 114, 1141, 115, 1151, 1211, 1212, 1213, 1214 and so on.. I need to group them by first two characters, and get the total, that total of 11(111, 112, 113....) and 12(121, 122, 123...) only reflect in text box, how to do this?, thank you for your help....
you will need to do it query first as easy way. you will need create a field for group of two (11 or 12). example, groupOfTwo: Left([fieldname],2) then you find the total in report footer by function =sum([groupOfTwo])
Hi, I need your help in the last part - I've zero knowledge of VB so please excuse and accept my apologies in advance. Here is my code which I changed as per my requirement... Private Sub cbopol_AfterUpdate() Dim polselction As String polselction = "Select * from SearchRates where ([POL] = " & Me.cbopol & ")" Me.POL.Form.RecordSource = polselction ---- (problem part) - :'( Me.POL.Form.Requery ----- (problem part) - :'( End Sub I'm stuck at me. part..... You have used subform whereas I'm using split form so what should I do? Appreciate your kind assistance. :)
getting an error: Private Sub cboFirstName_AfterUpdate() Dim myname As String myname = "Select * from Employee where([First_Name] = " & Me.cboFirstName & ")" Me.EmployeeSubform.Form.RecordSource = myname Me.EmployeeSubform.Form.Requery End Sub this is my code used to search peoples first names in my database but i keep getting an error with the section Me.EmployeeSubform.Form.RecordSource = myname the error is the one you had with gender and i have tried what you did to fix the error with the gender search but nothing seems to fix the code, any help would be wonderful
Hi... ive use your codes on my database access. But when i selelct something on my combo box what it does is clear all the data on my subform. Im using access 2007. Can you please help me?
Hi Austin, the code seems to be working for me, but the table only shows the first entry and it only shows it if the search criteria is met. for example lets say I have 10 pets with 4 different types of pets. only the first pet is coming up after the update if it matches the search criteria, all the other combo box options are blank even if they should be matching a value. Here is the code. maybe I missed something minor: Private Sub Products_Use_AfterUpdate() Dim myRequirment As String myRequirment = "Select * from Software_REQ where ([Versions effected] = '" & Me.Products_Use & "')" Me.Software_REQ_subform.Form.RecordSource = myRequirment Me.Software_REQ_subform.Form.Requery End Sub
