Great video - just one thing that i noticed. The clear button clears the value in the combo boxes but does not actually clear the filter and bring back all the records in the subform below. This is very important I think. None seem to have asked this question. Please advise. thank you
An outstanding playlist. My compliments. Can you please tell me how to also include add search by from and to dates alongside the other combo boxes. Will be greatful. Thanks again.
Thank you so much for posting this video. Is there a way to have the combo boxes only show the remaining/available search criteria after you have already filtered the list using a combo box? For example, if in this database you had "Individual" selected and "Male" selected, the remaining list of states available to chose from in the "States" combo box should be only CA and NV - not CA, CO, NV, and TX.
No set a Master or Child on 1st Subform. But Need to set a link on 2nd subform by set Master to a text box txtCustomer_ID on a main form and set a Child to customor_id.
I have copied the code exactly how you have it for the combo boxes. I do have one issue I'm trying to figure out though. I have added a check box into the code and it works fine when I only have 1 check box in the code but when I try to add the second check box it gives me a syntax error. But if I have only 1 check box it works fine. Just can't figure it out any help would be great. Thanks.
Say hypothetically that one customer had two addresses and was thus listed twice in the subform. Barney Rubble on Flinstone Way and Barney Rubble on Oak Street. Many costumers have multiple rows because they have multiple addresses. I only want to show a costumer once in the subform, and I do not want to show that Barney only lives on two streets. Is there code that would allow me to limit the time each costumer shows up on the subform to one row?
Hi Austin I am getting two problems when I run this code 1) first cmbo is not working unless is firstly select cmbo2. problem 2 is when I add the third cmbo the whole code is stopping. This the Code function searchCriteria() Dim strNationality, strGender, Mycustomer As String Dim task, strCriteria As String If IsNull(Me.CmboCandidate) Then Mycustomer = "[Candidates] like '*'" Else Mycustomer = "[Candidates] = " & Me.CmboCandidate End If If IsNull(Me.Combo8) Then strGender = "[Gender] like '*'" Else strGender = "[Gender] = '" & Me.Combo8 & "'" End If If IsNull(Me.Combo10) Then strNationality = "[Nationality] like '*'" Else strNationality = "[Nationality] = '" & Me.Combo10 & "'" End If strCriteria = Mycustomer & "And" & strGender & "And" & strNationality task = "Select * from memberinfo where " & strCriteria Me.voters_subform.Form.RecordSource = task Me.voters_subform.Form.Requery End Function ---------------------------------------------------------------------------------------------------------------------------------------- Private Sub CmboCandidate_AfterUpdate() Call searchCriteria End Sub ---------------------------------------------------------------------------------------------------------------------------------------- Private Sub Combo10_AfterUpdate() Call searchCriteria End Sub ---------------------------------------------------------------------------------------------------------------------------------------- Private Sub Combo8_AfterUpdate() Call searchCriteria End Sub
Thank you so much! I liked and subscribed , this was very helpful in getting me set up. One question I have on this. Everything seems to work in the code (no errors or anything), but when I use the form, it does not return all records that meet the search criteria. Is it possible that there is a cap on the amount of data it returns, or possibly a timeout? I only have ~1500 records for it to search through, so idk if thats too much or not. Would appreciate any help you could give me.
For clarification: When I first open the form, it shows 1509 records. Then when I search for my first criteria, it returns 84 records (there are actually 346 that meet that criteria). Then when I clear all, it only displays 496 records (again, there should be 1509). Kind of stumped
I found the problem...the search criteria function does not return records that have a null value in any of the searched fields. Do you know how to correct this?
Hi Austin,First le me thank you for your sharing your knowledge. I have seen quite a few videos you have posted and BOY! they are great. I was wondering if you can help with a quick question. I followed your code you posted on your video "search form part 3" but I am getting the following error. Runtime-Error 3145.syntax error in WHERE Clause. I have checked for misspells but everything seems ok. Your help is greatly appreciated.
Hi Austin, This is issue has been resolved. However, I encountered another problem, I am getting a Runtime error-3075. The code works fine, except for the usual problem with handling apostrophes in customer names ("BJ's wholesale," "Dillar's Stpre," etc.) . Below is my code, your help is the greatly appreciated.Function searchcriteria() Dim customername As String Dim strcustomerno As String Dim strsalesrep As String Dim stryear As String Dim strbrand As String Dim strrange As String Dim strausitemno As String Dim task, strcriteria As StringIf IsNull(Me.Textcustname) Then customername = "[customer name] like '*'" Else customername = "[customer name] = '" & Me.Textcustname & "'" End If If IsNull(Me.textcustomerno) Then strcustomerno = "[customer code] like '*'" Else strcustomerno = "[customer code] = '" & Me.textcustomerno & "'" End If If IsNull(Me.TextSalesrep) Then strsalesrep = "[sales rep] like '*'" Else strsalesrep = "[sales rep] = '" & Me.TextSalesrep & "'" End If If IsNull(Me.textyear) Then stryear = "[year] like '*'" Else stryear = "[year] = '" & Me.textyear & "'" End If If IsNull(Me.TextBrand) Then strbrand = "[brand] like '*'" Else strbrand = "[brand] = '" & Me.TextBrand & "'" End If If IsNull(Me.TextRANGE) Then strrange = "[range] like '*'" Else strrange = "[range] = '" & Me.TextRANGE & "'" End If If IsNull(Me.TextAUSItemNo) Then strausitemno = "[ausitemno] like '*'" Else strausitemno = "[ausitemno] = '" & Me.TextAUSItemNo & "'" End If strcriteria = customername & "And" & strcustomerno & "and " & strsalesrep & "and " & stryear & "and " & strbrand & "and " & strrange & "and " & strausitemno task = "select * from invoicedmonth WHERE " & strcriteria Me.invoicedmonth_subform.Form.RecordSource = task Me.invoicedmonth_subform.Form.Requery End Function
All of your videos are great! They help me out a great deal. For some reason, my query is only returning one row. I think it might be a syntax issue with the query (strCriteria). I did a debug.print strCriteria just to see what the query looks like. Select * from table where [oneThing] like '*' And [thingTwo] like '*' And [thirdThing] = 'HELP' And [fourthThing] like '*' Do you see something that can be causing my query to only return 1 row? Thanks again for all your help with these.
your where clause is not right. it should be like this: = "Select * from table where (([oneThing] like ""*"")And([thingTwo] like ""*"")And([thirdThing] = 'HELP'""And([fourthThing] like ""*""))"
Great videos and it works fine except when I add new details to the linked table in the subform the multisearch does not find them. Why would this happen?
Austin, I have tried to refresh and Requery as suggested but it hasn't worked. There are 6 records I have added to the table and they are available to select in the search combo box but when selected there are no details brought up in the sub Form. Please help Thanks
set the recordsource to the ID that doesn't have a record in database like strSearch = "SELECT * FROM TblSlot where ([ID] = 000000)" Me.RecordSource = strSearch
Hi Austin, your videos are helpful but I really need your help on this step. I have used your code in my database per below: If IsNull(Me.cboBM) Then strBoardMatter = " [Board Approved Matter].[Type of Matter] like '*' " Else strBoardMatter = " [Board Approved Matter].[Type of Matter] = ' " & Me.cboBM & " ' " End If It comes out zero result. The "Type of Matter" selected from combo box are the value with English and Chinese charterers with some space in it. Does it matter? Looking forward to your reply.
the value in Type of Matter field must me exactly same as in combo box if the value in combo box is string (English, Chinese), not linked to the table of language.
The value of Type of Matter field is exactly the same as that in the combo box. My access is in Chinese version. So do you mean that access does not support Chinese? I don't so?
Great search form. What if some of the fields are blank in the table? My Database allows the user to skip unknown details leaving the field blank. These records are not shown with this search. I have modified the Clear Button to show all records. This includes those with blank fields. Code below....... Modify and add this code after you have set your combo boxes to Null: 'add your data source (table or query) instead of tblName strSearch = "SELECT * FROM tblName" 'add your subform name instead of frmSub Me.frmSub.Form.RecordSource = strSearch Me.frmSub.Form.Requery
it would have to be the most efficient use of a SINGLE report i've seen (i'm a newbie to access, and admittedly I haven't seen much yet...). This method has since allowed 30+ reports to be generated, where under any other normal circumstances and as a newbie, I would have created all these reports individually....... Thanks Tewan!!
Hi Austin, thanks for the videos, a great help, I've entered the code however I'm getting the following error: Complie error: Method or data member not found ? for the following: Me.Key_Management_System_Form.RecordSource = task I've been stuck for a few days now :-( if you could please help
Please Austin. Kindly show me how to refresh the list in the Subform after clicking on 'Clear' button. Each time I open the form, the result is always the same where I last left it. I want CLEAR button to refresh or Show All records in the list. Thank you sir.
set the recordsource to the ID that doesn't have a record in database like strSearch = "SELECT * FROM TblSlot where ([ID] = 000000)" Me.RecordSource = strSearch
hi i have an error 3145 this is my code: SearchCriteria = StatusType & "And" & Machine & " And " & Employee Task = "Select * from Assets where " & strCriteria Me.AssetsSubForm.Form.RecordSource = Task Me.AssetsSubForm.Form.Requery do you know where is the syntax error?
If IsNull(Me.cboStatusType) Then StatusType = "[StatusID] like '*'" Else StatusType = "[StatusID] = " & Me.cboStatusType & "" End If If IsNull(Me.cboMachine) Then Machine = "[Model] like '*'" Else Machine = "[Model] = " & Me.cboMachine & "" End If If IsNull(Me.cboEmployee) Then Employee = "[EmployeeNumber] like '*'" Else Employee = "[EmployeeNumber] = " & Me.cboEmployee & "" End If SearchCriteria = StatusType & "And" & Machine & " And " & Employee Task = "Select * from Assets where " & strCriteria Me.AssetsSubForm.Form.RecordSource = Task Me.AssetsSubForm.Form.Requery
Hi, your videos are great they helped me a lot. Old but good! For this function I get a mistake Syntax error, for the part from strCriteria = .... Could you please help? I don't have numbers as data. Im using Access 2016. Function SearchCriteria() Dim CustomerName, strUnit, strClass As String Dim task, strCriteria As String If IsNull(Me.Cmb_Customer_Name) Then CustomerName = "[Customer Name] = like '*' " Else CustomerName = "[Customer Name] = ' " & Me.Cmb_Customer_Name & " ' " End If If IsNull(Me.Cmb_Class_Type) Then strClass = "[Item Type] = like '*' " Else strClass = "[Item Type] = ' " & Me.Cmb_Class_Type & " ' " End If If IsNull(Me.Cmb_Unit) Then strUnit = "[Unit] = like '*' " Else strUnit = "[Unit] = ' " & Me.Cmb_Unit & " ' " End If strCriteria = CustomerName & "And" & strClass & "And" & strUnit task = "Select * from Open_Items_List where " & strCriteria Me.Open_Items_List_subform.Form.RecordSource = task Me.Open_Items_List_subform.Form.Requery End Function
I have few videos. you can search for Create Report on my channel. And I have one how to that related to create report at: www.iaccessworld.com/how-to-create-report-with-a-link-to-another-report/
if there is a value in report refers to value in form and the form is part of NavigationSubform. that value not appear in report unless the form is open. do you have any solusion?
Tnx but this part does not work in this code....... Show yellow color. Please help me Me.tbl_customer_subform.form.recordsource=task. Please help me....
i have: 1 table = Inventory - Equipment List the subform of this table is called: SubformEquipment 5 fields in these are called: ID, Identification Number, Generic Description, Location, Status. ID and Identification are not the same. ID is the id access has given it, and the other one is the official product id(includes numbers and letters, example: AB/S/1015). Location field also have numbers and letters, example: A10B. the rest of the fields contain text only. Identification Number = Combo32 Generic Description = Combo28 Location = Combo29 Status = Combo30 every combo box displayes the original texts from the field. no id numbers. here is the error i get when i make a selection in any one of the boxes, for example Generic Description: """"""" Run-time error '3011': The Microsoft access database engine could not find the object 'Select * form [Inventory - Equipment List] where [Identification Number] like '*' And [Generic Description] = 'Verdeelbox' And [Location] like '*' And [Status] like '*'. Make sure the object exists and that you spell its name and the path name correctly. If 'Select * form [Inventory - Equipment List] where [Identification Number] like '*' And [Generic Description] = 'Verdeelbox' And [Location] like '*' And [Status] like '*' ... """"""" the rest of the error message doesn't fit in the message box. in a comment i'll leave the code. i really hope you can help me out on this one.
Function SearchCriteria() Dim myReg As String Dim myGen As String Dim myLoc As String Dim myAvail As String Dim task As String Dim strCriteria As String If IsNull(Me.Combo32) Then myReg = "[Identification Number] like '*'" Else myReg = "[Identification Number] = '" & Me.Combo32 & "'" End If If IsNull(Me.Combo28) Then myGen = "[Generic Description] like '*'" Else myGen = "[Generic Description] = '" & Me.Combo28 & "'" End If If IsNull(Me.Combo24) Then myLoc = "[Location] like '*'" Else myLoc = "[Location] = '" & Me.Combo24 & "'" End If If IsNull(Me.Combo30) Then myAvail = "[Status] like '*'" Else myAvail = "[Status] = '" & Me.Combo30 & "'" End If strCriteria = myReg & " And " & myGen & " And " & myLoc & " And " & myAvail task = "Select * form [Inventory - Equipment List] where " & strCriteria & "" Me.SubformEquipment.Form.RecordSource = task Me.SubformEquipment.Form.Requery End Function
austin72406 well it didn't do the trick. got the same error but this time it said: ...where ([Identification Number] like "*")... i also tried closing in all the similar lines of code with (). that resulted in the same error. i also tried closing in all the * with "" "". that resulted in the same error. I double checked my spelling in the tables, subformes, vba and the form. it is all correct. i am clueless.
Hi Austin, your videos help me to learn ACCESS very fast. Do you mind if you can help me on this:I need to call a function from another function to show the total quantity. I used Function FindRecordCount and SearchCriteria then pressing a command buttonthe query will display and the total quantity on a textbox as well.Private Sub Search_Command_Click()Call SearchCriteriaMe.Text70 = FindRecordCount (task)End Sub
Hello austin, I copied your code and applied into my Access database. However, it does not work. Can you take a look ? Thank you so much ! I would like to search drug name, event and country, three string variables. When I search it, nothing change. Codes are here: Option Compare Database Private Sub searchdrug_AfterUpdate() Call SearchCriteria End Sub Private Sub searchevent_AfterUpdate() Call SearchCriteria End Sub Private Sub searchcountry_AfterUpdate() Call SearchCriteria End Sub Function SearchCriteria() Dim mydrug, myevent, mycountry As String Dim task, strCriteria As String If IsNull(Me.searchdrug) Then mydrug = "[DrugClassification] like '*' " Else mydrug = "[DrugClassification] = ' " & Me.searchdrug & " ' " End If If IsNull(Me.searchevent) Then myevent = "[AdverseEvent] like '*' " Else myevent = "[AdverseEvent] = ' " & Me.searchevent & " ' " End If If IsNull(Me.Searchcountry) Then mycountry = "[Country] like '*' " Else mycountry = "[Country] = ' " & Me.Searchcountry & " ' " End If strCriteria = mydrug & "And" & myevent & "And" & mycountry task = "Select * from Review where " & strCriteria Me.subform2.Form.RecordSource = task Me.subform2.Form.Requery End Function
austin72406 Thanks, Austin. However, I did not quite understand what you mean. In the video, you did not remove the data source, you just copy and paste the previous form and rename it. If you don't mind, Can I send my access database to you so that you can take a quick look? I think it is a very tiny thing which makes the code doesn't work, but I just don't know where it is. Thanks a lot !
Thanks so much for posting these videos. You saved me hours of time trying to figure how to do this on my own.
Hi Austin,
This is very helpful! Thanks for sharing this to us who are newbie in Access programming :)
Great video - just one thing that i noticed. The clear button clears the value in the combo boxes but does not actually clear the filter and bring back all the records in the subform below. This is very important I think. None seem to have asked this question. Please advise. thank you
i just program it to bring all records back as like no filter.
I love this form! I have tried everything I can think of to refilter the form. can you please enter the code on how you did it?
oh and is there a way to put a where condition on a e-mail button so the filtered form can be sent out?
An outstanding playlist. My compliments. Can you please tell me how to also include add search by from and to dates alongside the other combo boxes. Will be greatful. Thanks again.
Appreciate the video... I have a question though. What if I have a combobox that is using dates. How would you write this for the event procedures?
Thank you so much for posting this video. Is there a way to have the combo boxes only show the remaining/available search criteria after you have already filtered the list using a combo box? For example, if in this database you had "Individual" selected and "Male" selected, the remaining list of states available to chose from in the "States" combo box should be only CA and NV - not CA, CO, NV, and TX.
yes, check out at my website here: www.iaccessworld.com/combo-box-value-depends-on-another-combo-box/
Excellent video. Question do you need to change the master child links on the first subform? or does the recordsource take care of that.
No set a Master or Child on 1st Subform. But Need to set a link on 2nd subform by set Master to a text box txtCustomer_ID on a main form and set a Child to customor_id.
Thank you Sir! this is exactly what I was looking for!
Thank you for knowledge sharing for free
Thank you for posting this video. You are a life saver.
I have copied the code exactly how you have it for the combo boxes. I do have one issue I'm trying to figure out though. I have added a check box into the code and it works fine when I only have 1 check box in the code but when I try to add the second check box it gives me a syntax error. But if I have only 1 check box it works fine. Just can't figure it out any help would be great. Thanks.
Tnx for....... U.... I like your all video. Nice & awesome.
Say hypothetically that one customer had two addresses and was thus listed twice in the subform. Barney Rubble on Flinstone Way and Barney Rubble on Oak Street. Many costumers have multiple rows because they have multiple addresses. I only want to show a costumer once in the subform, and I do not want to show that Barney only lives on two streets. Is there code that would allow me to limit the time each costumer shows up on the subform to one row?
Hi Austin I am getting two problems when I run this code 1) first cmbo is not working unless is firstly select cmbo2. problem 2 is when I add the third cmbo the whole code is stopping.
This the Code
function searchCriteria()
Dim strNationality, strGender, Mycustomer As String
Dim task, strCriteria As String
If IsNull(Me.CmboCandidate) Then
Mycustomer = "[Candidates] like '*'"
Else
Mycustomer = "[Candidates] = " & Me.CmboCandidate
End If
If IsNull(Me.Combo8) Then
strGender = "[Gender] like '*'"
Else
strGender = "[Gender] = '" & Me.Combo8 & "'"
End If
If IsNull(Me.Combo10) Then
strNationality = "[Nationality] like '*'"
Else
strNationality = "[Nationality] = '" & Me.Combo10 & "'"
End If
strCriteria = Mycustomer
& "And" & strGender & "And" & strNationality
task = "Select * from memberinfo where " & strCriteria
Me.voters_subform.Form.RecordSource = task
Me.voters_subform.Form.Requery
End Function
----------------------------------------------------------------------------------------------------------------------------------------
Private Sub CmboCandidate_AfterUpdate()
Call searchCriteria
End Sub
----------------------------------------------------------------------------------------------------------------------------------------
Private Sub Combo10_AfterUpdate()
Call searchCriteria
End Sub
----------------------------------------------------------------------------------------------------------------------------------------
Private Sub Combo8_AfterUpdate()
Call searchCriteria
End Sub
Thanks austin,
I have a problem when one box is empty it gives error message. I expect to show all records without doing filter by the empty box
Thank you so much! I liked and subscribed , this was very helpful in getting me set up.
One question I have on this. Everything seems to work in the code (no errors or anything), but when I use the form, it does not return all records that meet the search criteria. Is it possible that there is a cap on the amount of data it returns, or possibly a timeout? I only have ~1500 records for it to search through, so idk if thats too much or not. Would appreciate any help you could give me.
For clarification: When I first open the form, it shows 1509 records. Then when I search for my first criteria, it returns 84 records (there are actually 346 that meet that criteria). Then when I clear all, it only displays 496 records (again, there should be 1509). Kind of stumped
I found the problem...the search criteria function does not return records that have a null value in any of the searched fields. Do you know how to correct this?
Hi Austin,First le me thank you for your sharing your knowledge. I have seen quite a few videos you have posted and BOY! they are great. I was wondering if you can help with a quick question. I followed your code you posted on your video "search form part 3" but I am getting the following error. Runtime-Error 3145.syntax error in WHERE Clause. I have checked for misspells but everything seems ok. Your help is greatly appreciated.
can you post your code here? i can take a look
Hi Austin, This is issue has been resolved. However, I encountered another problem, I am getting a Runtime error-3075. The code works fine, except for the usual problem with handling apostrophes in customer names ("BJ's wholesale," "Dillar's Stpre," etc.) . Below is my code, your help is the greatly appreciated.Function searchcriteria()
Dim customername As String
Dim strcustomerno As String
Dim strsalesrep As String
Dim stryear As String
Dim strbrand As String
Dim strrange As String
Dim strausitemno As String
Dim task, strcriteria As StringIf IsNull(Me.Textcustname) Then
customername = "[customer name] like '*'"
Else
customername = "[customer name] = '" & Me.Textcustname & "'"
End If
If IsNull(Me.textcustomerno) Then
strcustomerno = "[customer code] like '*'"
Else
strcustomerno = "[customer code] = '" & Me.textcustomerno & "'"
End If
If IsNull(Me.TextSalesrep) Then
strsalesrep = "[sales rep] like '*'"
Else
strsalesrep = "[sales rep] = '" & Me.TextSalesrep & "'"
End If
If IsNull(Me.textyear) Then
stryear = "[year] like '*'"
Else
stryear = "[year] = '" & Me.textyear & "'"
End If
If IsNull(Me.TextBrand) Then
strbrand = "[brand] like '*'"
Else
strbrand = "[brand] = '" & Me.TextBrand & "'"
End If
If IsNull(Me.TextRANGE) Then
strrange = "[range] like '*'"
Else
strrange = "[range] = '" & Me.TextRANGE & "'"
End If
If IsNull(Me.TextAUSItemNo) Then
strausitemno = "[ausitemno] like '*'"
Else
strausitemno = "[ausitemno] = '" & Me.TextAUSItemNo & "'"
End If
strcriteria = customername & "And" & strcustomerno & "and " & strsalesrep & "and " & stryear & "and " & strbrand & "and " & strrange & "and " & strausitemno
task = "select * from invoicedmonth WHERE " & strcriteria
Me.invoicedmonth_subform.Form.RecordSource = task
Me.invoicedmonth_subform.Form.Requery
End Function
All of your videos are great! They help me out a great deal.
For some reason, my query is only returning one row. I think it might be
a syntax issue with the query (strCriteria). I did a debug.print
strCriteria just to see what the query looks like.
Select * from table
where [oneThing] like '*' And [thingTwo] like '*' And [thirdThing] =
'HELP' And [fourthThing] like '*'
Do you see something that can be causing my query to only return 1 row?
Thanks again for all your help with these.
your where clause is not right.
it should be like this:
= "Select * from table where (([oneThing] like ""*"")And([thingTwo] like ""*"")And([thirdThing] = 'HELP'""And([fourthThing] like ""*""))"
Thank you so much for your great help.
impressed, thanks for sharing your knowledge...regards Alok
Great videos and it works fine except when I add new details to the linked table in the subform the multisearch does not find them. Why would this happen?
you may need to refresh data first before perform the search function
Austin, I have tried to refresh and Requery as suggested but it hasn't worked. There are 6 records I have added to the table and they are available to select in the search combo box but when selected there are no details brought up in the sub Form. Please help Thanks
Are you available for questions? I am trying this code. However, I get an error saying I have an invalid use of the Me keyword.
Thank you. For this video. Thank a lot.
Hello Austin,
how to clear/blank Subform by clicking Clear Button.
set the recordsource to the ID that doesn't have a record in database like
strSearch = "SELECT * FROM TblSlot where ([ID] = 000000)"
Me.RecordSource = strSearch
Hi Austin, your videos are helpful but I really need your help on this step. I have used your code in my database per below:
If IsNull(Me.cboBM) Then
strBoardMatter = " [Board Approved Matter].[Type of Matter] like '*' "
Else
strBoardMatter = " [Board Approved Matter].[Type of Matter] = ' " & Me.cboBM & " ' "
End If
It comes out zero result. The "Type of Matter" selected from combo box are the value with English and Chinese charterers with some space in it. Does it matter? Looking forward to your reply.
the value in Type of Matter field must me exactly same as in combo box if the value in combo box is string (English, Chinese), not linked to the table of language.
The value of Type of Matter field is exactly the same as that in the combo box. My access is in Chinese version. So do you mean that access does not support Chinese? I don't so?
Great search form. What if some of the fields are blank in the table? My Database allows the user to skip unknown details leaving the field blank. These records are not shown with this search.
I have modified the Clear Button to show all records. This includes those with blank fields. Code below.......
Modify and add this code after you have set your combo boxes to Null:
'add your data source (table or query) instead of tblName
strSearch = "SELECT * FROM tblName"
'add your subform name instead of frmSub
Me.frmSub.Form.RecordSource = strSearch
Me.frmSub.Form.Requery
Works perfectly..!!!
it would have to be the most efficient use of a SINGLE report i've seen (i'm a newbie to access, and admittedly I haven't seen much yet...). This method has since allowed 30+ reports to be generated, where under any other normal circumstances and as a newbie, I would have created all these reports individually....... Thanks Tewan!!
nice video...
Hi Austin,
thanks for the videos, a great help, I've entered the code however I'm getting the following error: Complie error: Method or data member not found ?
for the following: Me.Key_Management_System_Form.RecordSource = task
I've been stuck for a few days now :-( if you could please help
can you provide all your code here
Sabraz Ali You're missing [.Form] before [.RecordSource]. So it would be:
Me.Key_Management_System_Form.Form.RecordSource = task
Please Austin. Kindly show me how to refresh the list in the Subform after clicking on 'Clear' button. Each time I open the form, the result is always the same where I last left it. I want CLEAR button to refresh or Show All records in the list. Thank you sir.
set the recordsource to the ID that doesn't have a record in database like
strSearch = "SELECT * FROM TblSlot where ([ID] = 000000)"
Me.RecordSource = strSearch
Show all records by
strSearch = "SELECT * FROM Tblname"
Me.RecordSource = strSearch
Thanks a ton!!!!!
hi
i have an error 3145
this is my code:
SearchCriteria = StatusType & "And" & Machine & " And " & Employee
Task = "Select * from Assets where " & strCriteria
Me.AssetsSubForm.Form.RecordSource = Task
Me.AssetsSubForm.Form.Requery
do you know where is the syntax error?
error with SearchCriteria. where is Statustype come from. I would like to see code for StatusType, Machine and Employee.
If IsNull(Me.cboStatusType) Then
StatusType = "[StatusID] like '*'"
Else
StatusType = "[StatusID] = " & Me.cboStatusType & ""
End If
If IsNull(Me.cboMachine) Then
Machine = "[Model] like '*'"
Else
Machine = "[Model] = " & Me.cboMachine & ""
End If
If IsNull(Me.cboEmployee) Then
Employee = "[EmployeeNumber] like '*'"
Else
Employee = "[EmployeeNumber] = " & Me.cboEmployee & ""
End If
SearchCriteria = StatusType & "And" & Machine & " And " & Employee
Task = "Select * from Assets where " & strCriteria
Me.AssetsSubForm.Form.RecordSource = Task
Me.AssetsSubForm.Form.Requery
Hi, your videos are great they helped me a lot. Old but good!
For this function I get a mistake Syntax error, for the part from strCriteria = ....
Could you please help?
I don't have numbers as data. Im using Access 2016.
Function SearchCriteria()
Dim CustomerName, strUnit, strClass As String
Dim task, strCriteria As String
If IsNull(Me.Cmb_Customer_Name) Then
CustomerName = "[Customer Name] = like '*' "
Else
CustomerName = "[Customer Name] = ' " & Me.Cmb_Customer_Name & " ' "
End If
If IsNull(Me.Cmb_Class_Type) Then
strClass = "[Item Type] = like '*' "
Else
strClass = "[Item Type] = ' " & Me.Cmb_Class_Type & " ' "
End If
If IsNull(Me.Cmb_Unit) Then
strUnit = "[Unit] = like '*' "
Else
strUnit = "[Unit] = ' " & Me.Cmb_Unit & " ' "
End If
strCriteria = CustomerName & "And" & strClass & "And" & strUnit
task = "Select * from Open_Items_List where " & strCriteria
Me.Open_Items_List_subform.Form.RecordSource = task
Me.Open_Items_List_subform.Form.Requery
End Function
hi..how you create the report?...are there any code inside it?
I have few videos. you can search for Create Report on my channel. And I have one how to that related to create report at: www.iaccessworld.com/how-to-create-report-with-a-link-to-another-report/
ok
if there is a value in report refers to value in form and the form is part of NavigationSubform. that value not appear in report unless the form is open.
do you have any solusion?
you may use the TempVar to set that value to Tempvar then call that TempVar when open report
+austin72406
thanks .. i will search about it
Tnx but this part does not work in this code....... Show yellow color. Please help me
Me.tbl_customer_subform.form.recordsource=task.
Please help me....
i have:
1 table = Inventory - Equipment List
the subform of this table is called: SubformEquipment
5 fields in these are called: ID, Identification Number, Generic Description, Location, Status.
ID and Identification are not the same. ID is the id access has given it, and the other one is the official product id(includes numbers and letters, example: AB/S/1015). Location field also have numbers and letters, example: A10B. the rest of the fields contain text only.
Identification Number = Combo32
Generic Description = Combo28
Location = Combo29
Status = Combo30
every combo box displayes the original texts from the field. no id numbers.
here is the error i get when i make a selection in any one of the boxes, for example Generic Description:
"""""""
Run-time error '3011':
The Microsoft access database engine could not find the object 'Select * form [Inventory - Equipment List] where [Identification Number] like '*' And [Generic Description] = 'Verdeelbox' And [Location] like '*' And [Status] like '*'. Make sure the object exists and that you spell its name and the path name correctly. If 'Select * form [Inventory - Equipment List] where [Identification Number] like '*' And [Generic Description] = 'Verdeelbox' And [Location] like '*' And [Status] like '*' ...
"""""""
the rest of the error message doesn't fit in the message box.
in a comment i'll leave the code.
i really hope you can help me out on this one.
Function SearchCriteria()
Dim myReg As String
Dim myGen As String
Dim myLoc As String
Dim myAvail As String
Dim task As String
Dim strCriteria As String
If IsNull(Me.Combo32) Then
myReg = "[Identification Number] like '*'"
Else
myReg = "[Identification Number] = '" & Me.Combo32 & "'"
End If
If IsNull(Me.Combo28) Then
myGen = "[Generic Description] like '*'"
Else
myGen = "[Generic Description] = '" & Me.Combo28 & "'"
End If
If IsNull(Me.Combo24) Then
myLoc = "[Location] like '*'"
Else
myLoc = "[Location] = '" & Me.Combo24 & "'"
End If
If IsNull(Me.Combo30) Then
myAvail = "[Status] like '*'"
Else
myAvail = "[Status] = '" & Me.Combo30 & "'"
End If
strCriteria = myReg & " And " & myGen & " And " & myLoc & " And " & myAvail
task = "Select * form [Inventory - Equipment List] where " & strCriteria & ""
Me.SubformEquipment.Form.RecordSource = task
Me.SubformEquipment.Form.Requery
End Function
should be like: myReg = "([Identification Number] like ""*"")"
+austin72406 thanks
austin72406 well it didn't do the trick. got the same error but this time it said: ...where ([Identification Number] like "*")...
i also tried closing in all the similar lines of code with (). that resulted in the same error.
i also tried closing in all the * with "" "". that resulted in the same error. I double checked my spelling in the tables, subformes, vba and the form. it is all correct. i am clueless.
better way is to test one criteria to make sure it works first before you add another criteria.
Hi Austin, your videos help me to learn ACCESS very fast. Do you mind if you can help me on this:I need to call a function from another function to show the total quantity. I used Function FindRecordCount and SearchCriteria then pressing a command buttonthe query will display and the total quantity on a textbox as well.Private Sub Search_Command_Click()Call SearchCriteriaMe.Text70 = FindRecordCount (task)End Sub
Today i buy your work but i i test step by step follow you. But not work,I decide work you to test today lol
all i hear is "customer tight"
Hello austin, I copied your code and applied into my Access database. However, it does not work. Can you take a look ? Thank you so much !
I would like to search drug name, event and country, three string variables. When I search it, nothing change.
Codes are here:
Option Compare Database
Private Sub searchdrug_AfterUpdate()
Call SearchCriteria
End Sub
Private Sub searchevent_AfterUpdate()
Call SearchCriteria
End Sub
Private Sub searchcountry_AfterUpdate()
Call SearchCriteria
End Sub
Function SearchCriteria()
Dim mydrug, myevent, mycountry As String
Dim task, strCriteria As String
If IsNull(Me.searchdrug) Then
mydrug = "[DrugClassification] like '*' "
Else
mydrug = "[DrugClassification] = ' " & Me.searchdrug & " ' "
End If
If IsNull(Me.searchevent) Then
myevent = "[AdverseEvent] like '*' "
Else
myevent = "[AdverseEvent] = ' " & Me.searchevent & " ' "
End If
If IsNull(Me.Searchcountry) Then
mycountry = "[Country] like '*' "
Else
mycountry = "[Country] = ' " & Me.Searchcountry & " ' "
End If
strCriteria = mydrug & "And" & myevent & "And" & mycountry
task = "Select * from Review where " & strCriteria
Me.subform2.Form.RecordSource = task
Me.subform2.Form.Requery
End Function
the code looking fine. make sure you remove the data source from main form and subform2. leave it blank. subform2 will get data source from task.
austin72406 Thanks, Austin. However, I did not quite understand what you mean. In the video, you did not remove the data source, you just copy and paste the previous form and rename it. If you don't mind, Can I send my access database to you so that you can take a quick look? I think it is a very tiny thing which makes the code doesn't work, but I just don't know where it is.
Thanks a lot !
+Man Good have you resolve this? im still having the same problem