I've been trying to find help with Access 2010 for days. And this is the first series of videos that really explain how to navigate access and brakes down how things work. I've learned more in 20 min. than i have in 4 days. Thank you.
Great stuff! Helped me a lot. I have a question regarding the "where condition" in the embedded macro for the Print Preview Button : I have added the (All) button in my combo box to show all entries in the form.How should I change the "where condition" (min 7:44) so that it works also for when selecting (All) . Thank you very much!
The reason that this first code example does not work (Enter Parameter error message) is that the apostrophes are not include in the last part of line 3. The apostrophes are included in the second and third code examples @ time point 14:45. Check it out!!!
the apostrophe (') is used only to the value is integer that we reference to, not string or text. for example that doesn't need apostrophe , where ([Customer_type_id] = " & Me.cboCustomerType & ")" the value of CustomerType combobox is number that links to a CustomerType table. for example that need apostrophe , where ([Gender] = '" & Me.cboGender & "')" The value of Gender is text or string that is linked to a Gender field in Customer table.
Austin your videos are great and I've followed all you instructions. My problem is that instead of multi filtering it clears each selection each time. eg If I select a department (Skips) it lists all skips in all locations but if I also select to just see in a particular location ie Coleraine it shows everything in that location not just skips. I have no idea what I have done wrong. Please can you help.
Hi Austin, you are the only resource I can find regarding Access, please help me! How do I make it so multiple combo boxes are filtering at the same time? I think below, you mentioned to use a And operator, can you show me how exactly to use that? Thank you so much!
Thank you for the great tutorial!. Is it possible for you to do another tutorial on how to export the data from subform that you have searched (filtered) to excel by VBA without making a report first.
Hi Tewan, first of all many thanks for your all tutorials which are very helpful for access users. Most important thing is your allo f them are very clear and easy to understand. your have made them in such a way.. thanks for that. i have a question on this, can we make a search/filter by using the Combo box. i need to do is that 1. First select customer type 2. Then out of that particularly selected Customer type i need to filter gender 3. Then out of that filtering i need to filter state. 4. then i need to print a report for those filter. Eg. I need to take a result of Individual, male customers in California. is it possible by this one ? or need some separate additional codeings. pls advise. brgds a
austin72406 Hi Tewan, thanks for your reply. I'm not expert in VBA. so appreciate if you show me an example, if possible. Secondly, i have a table when entering data i need to check two fields. If field 1 and field 2 filled with similar data of the previous records, error msg should appear that filed 1 & 2 are already exist. however, if field 1 is separate from previous data but field 2 is still same, that record should be accepted. eg. Record 1 ; Vehicle name = Toyota Vehicle number = 1234 if we entered same above data for next record it should not be accepted. Record 2 ; Vehicle name = Toyota Vehicle number = 4231 Here it should be accepted. Trust you can understand what i wanted to say. how can i do this ? do you have any tutorial which i can understand this ? pls help me. thanks Htadis
Hi , thank you was the great tutorial , I was able to understand how this works. I now seem to have a problem. When I choose the type , say business - in the report it comes as Individual and then when I close it and try again with Business- business pops up. It is the previous passed value and generating report. Any advise on correcting this err ?
Hey austin72406! Thanks a million for all your tutorials. I've already learnt a lot! :) ...but now I've got a problem with the button which greats a report out of my search form. I followed you macro instructions but when I click on the button I get a blank form (when I typed a key word in before) or a full form (show all). I'd like to have a Button on my Searchform which greats a proper Report... Thanks for your help in advance!
Pls, I need a way out. I used the following code for combo box search in ms access 2007 and am having some error. Dim MyClass As String MyClass = Select * from StdLedger where ([Class] = " & CboClass &") Me. StdLedger_Subform.form.RecordSource = MyClass Me.StdLedger_subform.form.requery And am having error 307 saying: missing some operator in your query [Class] " = John McLean " couldn't be found.
I tried it and it seems like the code I wrote to make the other values null is not working :( . I have 3 boxes. Box1, Box2 and Box3. It seems like my filter disappears when I pick the values on Box 2 or Box3 when it should keep the filter on the previous values already picked I don't know what I did wrong ;( Can you please help?
Tewan, i have completed a lot of my job with help of your tutorial. Thank you so much. But i would like to ask you solution from my little problems. What should i do in SQL statement if i type 2 character in one textbox and record showing any field that contains each two character that i type? for example in textbox i type Sam Smith. And search result showing any field that contains 'Sam' and 'Smith' words?
Thank you very much for providing such nice information. I tried the code you used, it is working fine with numerical values but when i used it for "Short Text" it shows a "Run Time Error 3464" Data type mismatch in criteria Expression. Please guide how to solve this problem. Regards,
hello austin Ii am using the same coding but when there is a blank field in a record it is not shown on the record. i am new to vba kindly tell me the code
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
Please make sure the Row Source of cboFirstName is only field First name. If you have the Customer_ID field at the front then you should delete it and leave it only the FirstName field because you want to search for FirstName and it is a text field or String data type. if you have only FirstName field then the SQL statement should be: myname = "Select * from Employee where([First_Name] = '" & Me.cboFirstName & "')" Note: need a apostrophe (') before " and after "
Great Video....I have set it up and all looks good but I am receiving an error Run-time Error '3075': Syntax error (missing operator) in query expression '([txtVendorName] = Carolina Creative Products)'. Private Sub ComboVendorName_AfterUpdate() Dim my As String myCustomer = "Select * from tblArticleGcc where ([txtVendorName] = " & Me.ComboVendorName & ")" Me.tblArticleGcc_subform.Form.RecordSource = myCustomer Me.tblArticleGcc_subform.Form.Requery End Sub
I've been trying to find help with Access 2010 for days. And this is the first series of videos that really explain how to navigate access and brakes down how things work. I've learned more in 20 min. than i have in 4 days. Thank you.
2021 May 28, I am searching back your tutorials and watching it again.
VERY EASY WAY TO EXPLAIN AND VERY PRECISE AND TO THE POINT.
VERY NICE AND HELPFUL
You are such a blessing!! You make me look like a genius on all of my Access projects--thank you s very much!!
Great stuff! Helped me a lot.
I have a question regarding the "where condition" in the embedded macro for the Print Preview Button : I have added the (All) button in my combo box to show all entries in the form.How should I change the "where condition" (min 7:44) so that it works also for when selecting (All) . Thank you very much!
you leave it blank on Where Condition. And you don't have to set Tempvar.
+austin72406 : Yea thanks, but I would like to combine the 2 conditions .. is this possible?
The reason that this first code example does not work (Enter Parameter error message) is that the apostrophes are not include in the last part of line 3. The apostrophes are included in the second and third code examples @ time point 14:45. Check it out!!!
the apostrophe (') is used only to the value is integer that we reference to, not string or text.
for example that doesn't need apostrophe , where ([Customer_type_id] = " & Me.cboCustomerType & ")"
the value of CustomerType combobox is number that links to a CustomerType table.
for example that need apostrophe , where ([Gender] = '" & Me.cboGender & "')"
The value of Gender is text or string that is linked to a Gender field in Customer table.
Austin your videos are great and I've followed all you instructions. My problem is that instead of multi filtering it clears each selection each time. eg If I select a department (Skips) it lists all skips in all locations but if I also select to just see in a particular location ie Coleraine it shows everything in that location not just skips. I have no idea what I have done wrong. Please can you help.
I am quite new to Access. But, can we do that only with macro builder? thanks
can a search by date be added to this example?
Hi Austin, you are the only resource I can find regarding Access, please help me! How do I make it so multiple combo boxes are filtering at the same time? I think below, you mentioned to use a And operator, can you show me how exactly to use that? Thank you so much!
yes need And Operator. there are many ways to do it.
may I know when I insert Me.SubRegister (which is my subform) it not recognized?
Hi Austin, I need some help on my Access 2010 database. Do you offer assistance?
Thank you for the great tutorial!. Is it possible for you to do another tutorial on how to export the data from subform that you have searched (filtered) to excel by VBA without making a report first.
hi, what would I need to do to make the filters dependent from the others... like customer_type= Government AND gender = Male.... AND...
Another awesome tutorial Tewan!!!
help. I am using the same coding but can show all the data same value .
Hi Austin
Great Info
I tried the coding and when i look for the o/p a dialog box appears saying "enter the parameter value"
Kindly Clarify
Hi Tewan,
first of all many thanks for your all tutorials which are very helpful for access users. Most important thing is your allo f them are very clear and easy to understand. your have made them in such a way.. thanks for that.
i have a question on this, can we make a search/filter by using the Combo box. i need to do is that
1. First select customer type
2. Then out of that particularly selected Customer type i need to filter gender
3. Then out of that filtering i need to filter state.
4. then i need to print a report for those filter.
Eg. I need to take a result of Individual, male customers in California. is it possible by this one ? or need some separate additional codeings.
pls advise.
brgds
a
you will need to use 'And' operator between each combo box in the criteria of Select Statement for the recordsource.
austin72406 Hi Tewan,
thanks for your reply. I'm not expert in VBA. so appreciate if you show me an example, if possible.
Secondly, i have a table when entering data i need to check two fields. If field 1 and field 2 filled with similar data of the previous records, error msg should appear that filed 1 & 2 are already exist. however, if field 1 is separate from previous data but field 2 is still same, that record should be accepted.
eg.
Record 1 ;
Vehicle name = Toyota
Vehicle number = 1234
if we entered same above data for next record it should not be accepted.
Record 2 ;
Vehicle name = Toyota
Vehicle number = 4231
Here it should be accepted.
Trust you can understand what i wanted to say.
how can i do this ? do you have any tutorial which i can understand this ? pls help me.
thanks
Htadis
Hi Did you ever figure out how to do this? I'm having the same kind of trouble. Thank you!
how i can make that report (Customer List) ?
I want to know too:/
it's in here th-cam.com/video/wXJxnbeQrrQ/w-d-xo.html
@@emmanuelantunes8811 it's in here th-cam.com/video/wXJxnbeQrrQ/w-d-xo.html
Thanks, this is a very big help for me. You explained it well and clear.
Hi , thank you was the great tutorial , I was able to understand how this works. I now seem to have a problem. When I choose the type , say business - in the report it comes as Individual and then when I close it and try again with Business- business pops up. It is the previous passed value and generating report. Any advise on correcting this err ?
the criteria should set to the customer type. you can put Me.refresh before open report.
Hey austin72406! Thanks a million for all your tutorials. I've already learnt a lot! :)
...but now I've got a problem with the button which greats a report out of my search form. I followed you macro instructions but when I click on the button I get a blank form (when I typed a key word in before) or a full form (show all). I'd like to have a Button on my Searchform which greats a proper Report...
Thanks for your help in advance!
make sure you assign a criteria to a tempvars then use that tempvars as a criteria to open report.
Is there a macro for this?
Pls, I need a way out. I used the following code for combo box search in ms access 2007 and am having some error.
Dim MyClass As String
MyClass = Select * from StdLedger where ([Class] = " & CboClass &")
Me. StdLedger_Subform.form.RecordSource = MyClass
Me.StdLedger_subform.form.requery
And am having error 307 saying:
missing some operator in your query [Class] " = John McLean " couldn't be found.
I tried it and it seems like the code I wrote to make the other values null is not working :( . I have 3 boxes. Box1, Box2 and Box3.
It seems like my filter disappears when I pick the values on Box 2 or Box3 when it should keep the filter on the previous values already picked
I don't know what I did wrong ;(
Can you please help?
Hi did you figure out how to solve this? I'm having the same problem. Thanks so much!
me too
The Command "Me.tbl_Customer_subform1.Form.RecordSource = Task" gives me the error "Compile Error, Method or data menber not found" how can I solve it
These are excellent videos myfriend
Thank you Jonesy for your comment
thank you, both videos were really helpful!!
Tewan, i have completed a lot of my job with help of your tutorial. Thank you so much.
But i would like to ask you solution from my little problems.
What should i do in SQL statement if i type 2 character in one textbox and record showing any field that contains each two character that i type? for example in textbox i type Sam Smith. And search result showing any field that contains 'Sam' and 'Smith' words?
Nate River can use a split function. or you can use two textbox as shown on my advance search video at th-cam.com/video/3SYXsdj_1Og/w-d-xo.html
austin72406 for split function, can u give me some example coding? I really not understand how to use it in SQL Statement T.T
Thank you very much for providing such nice information. I tried the code you used, it is working fine with numerical values but when i used it for "Short Text" it shows a "Run Time Error 3464" Data type mismatch in criteria Expression. Please guide how to solve this problem.
Regards,
hello austin
Ii am using the same coding but when there is a blank field in a record it is not shown on the record. i am new to vba kindly tell me the code
the record that matches the search criteria only will display after search.
+austin72406 but the record which have a blank field in any of the filter is not visible.hiw can i fix this
so great !useful! all are what i want .
Very nice. Thank you!
Really nice tutorial
Very Nice.... Thanks a Ton!
Good tutorial
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
Please make sure the Row Source of cboFirstName is only field First name. If you have the Customer_ID field at the front then you should delete it and leave it only the FirstName field because you want to search for FirstName and it is a text field or String data type.
if you have only FirstName field then the SQL statement should be:
myname = "Select * from Employee where([First_Name] = '" & Me.cboFirstName & "')"
Note: need a apostrophe (') before " and after "
Thanks for this helpful video
You are great ;-)
thank you so much
please share coding in module brooo
Great Video....I have set it up and all looks good but I am receiving an error
Run-time Error '3075': Syntax error (missing operator) in query expression '([txtVendorName] = Carolina Creative Products)'.
Private Sub ComboVendorName_AfterUpdate()
Dim my As String
myCustomer = "Select * from tblArticleGcc where ([txtVendorName] = " & Me.ComboVendorName & ")"
Me.tblArticleGcc_subform.Form.RecordSource = myCustomer
Me.tblArticleGcc_subform.Form.Requery
End Sub
looks like you did not declear myCustomer.
it suppose to be Dim myCustomer As String
austin72406 thank I got it figured out I had left off the single apostrophe