Search Keyword using VBA Part 1
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 2024
- Search data by Keyword with VBA and SQL. Download sample file at :www.iaccessworld.com/downloads...
Next video is open a another form linked to data
in current form: • Open Link Form using M... - แนวปฏิบัติและการใช้ชีวิต
I've been searching for something like this for weeks. I truly appreciate what you did. It was an outstanding tutorial and I feel so powerful.
Hello... I would to thank you for making these wonderful tutorials for the community. They are all smooth and great and well done. I appreciate a lot your contribution... and teaching...!
OMG I've been searching for this for months and this is the only way works. Thank you!!!
Best tutorial for MS Access so far. Thank you.
Thank you for taking the time to put this together, it works perfectly for me!!!
Hi Tewan, congrats on the videos, everything works great
Hey Tewan, I would like to ask you how could I can create a form from a table with a lot of columns, I did what you did in video but every time access told me that my table has so many columns that it can not be transferred to a form. How can I solve this problem? Please......
Hello Tewan,
I am very new to Access and need help on:
I want to get value of combobox and show the relevant country name (from countries table) in a separate text box.
(Cities list populated from cities table, i have country id in cities table as FKey)
Could you help ?
Hello Sir,
I have got some concern with navigation form, when I created search combo box in a form (frmUserDetails) it perfectly works. Now I have a navigation form and a sub navigation form in which I added the form frmUserDetais but every time it gives me a message box of "Enter parameter value". How do I resolve this?
ok i followed all the steps and created the form, now when i link it to a switchboard it doesnt works. How do i know it doesnt works because my information on the form doesnt load and plus when i try and search for a name nothing happens. Even when i click show all nothing happens. How do i solve this issue please?
Hi austin can you please help? I've done everything you said but my show all and search dont work, when I go back to design view to change to continuous I dont see this option there
Hello, can I use this if the fields in my query contains number values and not string?
how about search then edit the record ? do you have that kind of video ?
Hi Tewan, how to send a reminder from access using whatsApp according to criteria
I'm using an ID as reference so i put "integer" instead of "string" in varuables and i always get "Type mismatch" error wtf
hello i am getting following error please help me,
Runtime error 3008
The table tblBaskets is already opened exclusively by another user, or it is
already open through the user interface
strtext1 = Me.txtsearch1.Value
getting error in this area of search can help me pls
Nice Tutorial! Good Job :D Keep it up! Thank you very much! :)
Like is a sequential search. Not by full text index. Am I right?
Thank you so much with this video it really has helped me well in making my database for my final project...
you're welcome. Thanks Wahyuddin
i try to woring it but it's can't because the Macro things not set... how to solve that please?
Great Tutorial indeed but I am experiencing problem when "show all" is clicked on. The response is Syntax (compile error). Please help me to proceed, I'm stuck.
Great tutorial bruh, just am trying to make it more interactive when no record is found. Would you mind sharing your wisdom on how to go about it?
Hi all. Can you help? I know something isn't right with this especially the date parts →
Dim strsearch As String
Dim strtxt As String
Dim startdate As Date
Dim enddate As Date
strtxt = Me.txtNAMESEARCH.Value
startdate = Me.txtSTARTDATE
enddate = Me.txtENDDATE
strsearch = "SELECT * from qryALLSTAFFSHIFTSandDUTIES where (NameDetail like ""*" & strtxt & "*"" AND [Dates] between '# & startdate & #' and '# & enddate & # '"
Can you help pls.
Thanks.
thanks bro! works like a charm!
Nice! Very helpful. Thank you.
:)
Hello Austin72406 ! Thanks for your sharing ! I really like your video tutorial. But i'm in trouble with the code. In the video, you used main form, but in my training, i have been use sub form. So, your code didn't work.. PLEASE, HELP ME... I've got error in: Me.RecordSource = Strsearch the line was have yellow colour..
Hi Austin,
How do I incorporate another table into the string search?
So far I have this for my first table:
strsearch = "SELECT * FROM [tblCompany] WHERE (([Company_name] LIKE ""*" & strText & "*"") OR.....
How do I UNION or JOIN the second table to this search?
the easy way is creating a query that combines two tables then Select * from queryname.
It work but no data visible in the body of result
What can i do?
how search Unicode in Form above , please help me, thanks
You are my lifesaver!
Pls solve out mine as your vba code I gave but no works
only 1 column is not working for me. the rest is ok. is there any problem if my Data Type is a number?
Would you please explain shortly how it should work with a Drop down list in the form. In my case I can search with the ID but not with the actual value.
you will need to refer the ID on combo box to the actual value of combo box then compare to the search text box.
for example, you have data on combo box like
1 = cat
2 = dog
3 = bird
now you type "cat" on a search box. A code will be different from this video. you cannot search directly like cat = 1 on combo box.
we need to link 1 to CAT on its table like
dim strAnimal as string
strAnimal = dlookup("Animal", "Animaltable", "ID =" & comboboxName )
strSearch = "select * from tablename where (AnimalName = " & strAnimal & ")"
Hi Austin, is it possible to create a message when the search comes up empty? Do we use ElseIf ?
you can use count record method. If count record from search = 0 then generate the message.
I have CountRecord function at my website here: www.iaccessworld.com/function-count-records/
Hi. Thank you very much for all your examples that are very usefull in my daily job. Can you help me on this: If I want to hilight and give a colour to my search data like data1*data2*data3... how can I do It using VBA code?
This is very usefull to mark the criteria we chosed. Thank you very much.
Best regards from Portugal
Paulo
thnaks for your video. Can u tell me when i create a report like this form what is the code i use......?
absoluetly amazing . thanks
Another top video!
Thank you so much this helped me so much
wow i follow your your vedio youutube
its amazing
works very great. what code do you put under the recordcount text box to display the results on pressing the ENTER key without pressing the the Search button?
for recordcount, I have the function at my website here: www.iaccessworld.com/function-count-records/
for getting result without pressing the Enter Key, Normally you will need to click the Search button to get a result.
but you can call the on Click for Search button on the After Update Event of Search box.
example, Private Sub CmdSearch_Click()
'''=== search button
Call Search
End Sub
Private Sub txtSearch_AfterUpdate()
call CmdSearch_Click()
End Sub
Thanks sir for the response . indeed the the module works perfectly and much appreciated. But i realize the Recordcount Textbox will not indicate the number of the recordset on pressing the ENTER key Unless the Search Button is pressed. What code do i put under the RecordCount textbox on pressing the ENTER key.
Kind regards
can anybody help me with this error [run-time error "2580"]
Private Sub Command173_Click()
Dim strsearch As String
strsearch = "SELECT * from tlb_Tempahan Peralatan"
Me.RecordSource = strsearch -----[ the debug is here ]
End Sub
is it possible to make this but with 2 textboxes? example: list propertises between 2 prices
I have the sample file for download, but not free here:
www.iaccessworld.com/downloads/advance-search-keyword-ms-access/
It's just a little bit for contributing to my work.
Hi Austin.I have applied the code you have show in the video but every time I am getting error message please help
Syntax error (missing operator) in query expression ' (( Employee name like "*Alok Dhar*"))'. The Show all button is working fine but serach button is not working
My code is given below;
Dim Strsearch As String
Dim StrDate As String
StrText = Me.Txtsearch.Value
Strsearch = "SELECT * from tblkaizenform where ((employee name like ""*" & StrText & "*"")"
Me.RecordSource = Strsearch
you need a [....] bracket for field name like [Employee Name]
Thanks austin its working fine...grt job
Why we need bracket to write the field name ?? Please, tell me. Thanks a lot..
@Nana sara very helpful thank you
Hello austin72406, thank you for your quick response, it shows that I have write permission and am the administrator, still having no luck. Is there something else I need to check or may be doing wrong?
can you check if your still thre after reopen program? If Access saves your code then shouldn't be problem.
Would it be possible to search with multiple keywords using this method? For example, could this be modified this so I could search for the words "John" & "Smith" and get the on entry for John Smith?
it will work with 2 keyword as search box treats it as one word. you can type in "John Smith" then will get the result as John Smith. If you want you can follow below...Search for two keywords
The above example only search for a single word. If you want to search
customer name by entering two words like "John Smith" you will need to revise
the search code a little bit. You need to change the code where it says
(CustomerName Like ""*" & strsearch & "*"").
Start with
Dim strSpaceFix as String
Dim strLoad as String
'Load Text Box contents to strLoad
variable
strLoad = Me.txtSearch.Value
'Replace spaces with addition search
code
strSpaceFix = Replace(strLoad, " ", "" & "*"" AND [CustomerName] Like
""*" & "", 1, -1)
Then add strSpaceFix to the SQL statement
([CustomerName] Like ""*" & strSpaceFix & "*"")
Excellent. Thank you
Great video, keep it up! I just have one question:
Is it possible to search two categories in one box? For instance could I return customers with the last name smith and all customers based in San Diego.
Watch this video advanced search. It may help th-cam.com/video/3SYXsdj_1Og/w-d-xo.html
Hi Austin, I would like to search via number too, example customerID. However, it keeps giving me error. Could you help me check on the following code?strsearch = "SELECT * FROM tbl_customer where ((Cust_last like ""*" & strText & "*"") or (Cust_First like ""*" & strText & "*"") or (CustID like ""*" & strText & "*"" ))"Thanks a lot!
the error comes from (CustID like ""*" & strText & "*"" )
since your strText is a string as you decleared at the beginning.
you can refer to the original entry text box (txtSearch) like: (CustID like ""*" & Me.textSearch & "*"" )
Or you may try to convert strText to number by using the CInt(strText) as
(CustID like ""*" & CInt(strText) & "*"" )
Hi Austin, thank you for your reply. It works if I am only checking for customer ID itselfstrsearch = "SELECT * FROM tbl_customer where (CustID like ""*" & CInt(strText) & "*"" )"but when it is a general search I am getting Run-time error '13': Type Mismatch. Is there any reason for that error?Code: strsearch = "SELECT * FROM tbl_customer where ((Cust_last like ""*" & strText & "*"") or (Cust_First like ""*" & strText & "*"") or (CustID like ""*" & CInt(strText) & "*"" ))"Please Help
hello austin thankyou for a great video i used your code and it works but when there is nothing in the textbox and I click on search I get a runtime error 94 imvalid use of null can you please help me in fixing this issue or what I need to do to fix it
you need to test it if there is a test entered before processing the search.
You can use If Statement to test it.
If isnull(me.Textbox) then
msgbox "Please enter a keyword"
else
' process your search code here
end if
hey austin ive got a problem, can i used this code run on single form because im having a syntax error
Thread: run-time error 3131 "syntax error in FROM clause"
heres my code
Private Sub ejstrider_Click()
Dim strsearch As String
Dim strText As String
strText = Me.txtSearch.Value
strsearch = "SELECT * from where ((DriversName like ""*" & strText & "*"")Or(Citizenship like ""*" & strText & "*"")Or(TypeOfLicensed like ""*" & strText & "*"")Or(TalentCategory like ""*" & strText & "*"")Or(ProjectSite like ""*" & strText & "*"")Or(Company like ""*" & strText & "*"")"
Me.RecordSource = strsearch
End Sub
thank you in advance.
look like your code is missing table name :
"SELECT * from where...
These videos are excellent,.Can anyone point me in the direction of how to create a pop up or count box to count the number of records returned by my search. I can count the total number of records in my database, but not the number of filtered records based on the search
Hey Tewan! Thanks a lot for your videos! I've already learnt a lot!
My searchform is working well, but when I haven't typed anything in the searchfield and press "search" I get "Run Time Error Report '94' - Invalid use of Null"! What kind of code do I have to add that this error report disappears?
{I tried If isnull (mytxtbox) then me.mytxtbox.setfocus} -> but doesn't work
thank you!
yes you need IF Statement tests if is null then give message to enter a keyword elseperform the search functionend if
Thanks a lot Ive sorted it out!
Hi... I was looking for the Count code which is on the upper left of the form counting the Total filtered records.
on my website here: www.iaccessworld.com/function-count-records/
I'm new on access, cant make it work for the result on the filtered rows as shown in your video here. That's what I'm lacking. :(
Sir, you have uploaded a great video. But sir if I want to create every day's records altogether of a customer(same ID.) then is it possible in ms access ?. If possible then please create a video about it. If not, then possible in visual basic ? Please create a video about it.
Hi, I found a bug.
after you add the call function to the keyword textbox,
if you type something in the keyword box, and the press the "show all" bottom
it will run the keyword search function. not the show all function.
how to fix that?
thank you
clear keyword and called Show All function/command
thank you. Can you show us how to disable the shift bypasskey in access 2013?austin72406
Hi, for the Search button, I get an error, when I debug it does not like Me.RecordSource = strsearch. I don't know what I did wrong.
Kat Ramirez comes from your SQL invalid. please check your SQL statement again or you have the data for your SQL.
Question... What if I need to include a field with multiple values to the search bar? I get a "#Name?" when I include that particular field on the form. Or I get an error for the WHERE or HAVING clause
Hi Austin, I tried the VBA but still no work with error message "Run-time error '3131': Syntax error in FROM clause.
Private Sub Search_Click()
Dim strsearch As String
Dim strtext As String
strtext = Me.Txtsearch.Value
strsearch = "SELECT * from Database where ((IR__No like ""*" & strtext & "*"") or (PR_No like ""*" & strtext & "*"") or (Precise_Incident like ""*" & strtext & "*"")"
Me.RecordSource = strsearchEnd Sub
Me.RecordSource = strsearch (YELLOW COLOR THIS)
It's work now... thank you very much !!!
you r great man
im having an error, runtime error '94' Invalid use of Null when i view my report?? how do i fix this.
that mean you search and one of field data is blank and search for blank like (Customerid =). this is no result from searching and will be error on preview report.
+austin72406 i have the same issue, how do you resolve this? what i want to happen is instead of having an error i would like to display a message box saying "Enter Keyword". Please help.
nice tutorial. how i wish i learn the vba code. one question: i have a form with a subform. i want to search by keyword within the subform. do you have any idea how to do this?
I would prefer to use a split form rather than subform. a split form has a datasheet view like subform and header section to add your search box, so it uses one datasource for searching.
Thanks for the vid, but when I click the button, I get "Compile error: Method or data member not found" and TxtSearch is highlighted in the line strtext = Me.TxtSearch.Value
OK, I found what I did wrong, but now I get "Run-time error '3075': Syntax error in date in query expression '(item# like "*fuse*")'." and Me.RecordSource = strsearch is highlighted when I go to debug.
If you have a Date type field like [StartDate], Me.txtSearch is a text box on the form. Data type is not matching. You need to format txtSearch as Date if you search for date like 1/2/2014 on StartDate field. use
Dim strDate as Date
strDate = format(me.txtSearch.Value, Shortdate)
then use as or ([StartDate] like ""*" & strDate & "*"")
I hope this help.
Thanks alot this helped a ton!!
If i have a table that is already filtered, and want to search, how what code do I Need to come back to this filter after searching with my keywords??
@austin72406 can you help me here?
I reallz appriciate your movies!
How about adding another search box that will search on the result of first search box
thanks a lot for the video, but something missing it is :
What about the show nothing at the first form view ? how to do that sir ?
it's similar to show all, but add customerID = "" on the SQL line on the Form On Load Event
thx a lot
Do you have a tutorial, using date and keyword search at the same time? And also exporting the search result to an excel? Cheers
I have similar Video and sample file here: Search by Date Range :th-cam.com/video/EFQ6ClG3KUs/w-d-xo.html
and the Sample download file: www.iaccessworld.com/downloads/search-multiple-fields/
austin72406 Thank you! i did access more than a decade ago and your videos are the most helpful in TH-cam! Thanks for sharing, keep it up! I just subscribed - your channel needs more views. =)
Something funny happened the first time I tried this - couldn't get the fields to show up below Detail. I ended up throwing that particular form out, restarting from scratch and everything worked great. I think it could have been an order of operation issue at my end.
I'm grad you're able to solve it.
Dear austin72406, I have created my file as said in your video but when i type name or id and click search box it gives an error "#Name?" in each of the results feilds can u help me out ? being a newbie pls help me....Thanking u in Advance...!
for image click this link drive.google.com/file/d/1EMr032EuXWK3SoiQ9jy0XqKNFWW3iWuT/view?usp=sharing
Hello sir, I have one question, i made that search for number and for examlpe when i search for 33, it shows all records containing 33 like 15332, so i don't want that i want just show 33, how can i do that ? thanks a lot.
easy, you will need to change to strsearch = "SELECT * from tbl_customer where ([yourFieldName] = " & strText & " )"
austin72406 yes, i did that after asking you, just by test , now i see your answer is just like my solution :) thanks a lot.
you're welcome
Why we need bracket to write the field name ?? Please, tell me. Thanks a lot..
Hello, this works great but once I exit out and log back in, it no longer works. Am I missing a step. Please Help!
Hello Super Sook, seem like you have same issue on different functions. It looks like the Access did not save your code. If you update Access on the Network and already opened by someone else then code is not saved after you close it. If you don't have a Write permission on the network or folder on network then Access does not save your code.
HI sir, I have followed all the steps but the search button doesn't work, would this function still works if I am using Access 2007? And I don't want the users able to add, edit or delete data in this form, how should I do?
it should work for all version since it uses the VBA. follow the How To for create form as read-only here: www.iaccessworld.com/set-form-as-read-only/
Problem solved, Thank you :)
I made the same thing with you but when to click on the Show All button, It showing the error - Run time error '3131': Syntax error in From Clause my code: Private Sub Command247_Click()
Dim strsearch As String
strsearch = "SELECT * from List of Clinical Trials"
Me.RecordSource = strsearch
End Sub
DO you know where I am wrong
if your table is named with many words then your need bracket [...]
strsearch = "SELECT * from [List of Clinical Trials]"
Thank a teacher :>
Man you are cool!
Hi Austin, is it possible to make same search as type but not on the name or last name, but on ID or unique number please, can you do tutorial on that please?
it is the same logic. if the data type of your ID or unique number is number then you don't need an apostrophe (') referencing to the search text box.
Thank you Austin, I'l try that, I am pretty new to VBA, so hopefuly it will work:) Thanx again , you are awesome:) I hope you won't mind me contacting you again if i stuck? I'm trying to do a cook book for me privately, if i mae it I will gladely share it with you if you want:) Thank you one more time for your time and help:)
Gratefuly,
John
hi ...many thanks for every thank
but i have some problem with the command run
i have debug in // me.recordsource= task
and
me.recordsource= strsearch
please help my why i have error!!!
only one source can be assigned to RecordSource. you can use And for both sources. if you have more than one source of data, you can combine them first by using the Select Statement SQL then assign to me.recordsource.
Can you explain why you used ""*" so often and what it means exactly? I'm not seeing the logical flow of why all those double-quotes are necessary (though they clearly are because I broke it without them!)
For those interested, 2 double quotes is the escape char for another double quote. That is, """ within a string adds a single double quote to the string (the first two indicate to NOT end the string).
David Jones the '*' means 'all'. So it's SELECT * (all) FROM tbl...
IT DOES NOT WORK FOR ME
Thank you very much
hello when i try to click the search it give me a blank result thanks in advance,
please make sure you have all code correct.
Thanks a lot brother.
Always welcome
Hallo mr austin, i want to ask..
How to make a singgle report from sub form... For example: Can show report just one person... Not show many people... Thank you.. I love you video. 👏 please answer me...
it is similar to open form that link to specific id on current form.
see my post here: www.iaccessworld.com/how-to-open-linked-form-using-macro/
can you help me, how to make total like in your form
you can set Control Source of textbox to = count(*). it is good for small number.
If you have a large number output then the best way is using a function that created by using
recordset or count from recordset.
then assign function to textbox.
I have the example at my website : www.iaccessworld.com/function-count-records/
thank you, very helpfull
Now is the error with the search Button, It showing the error - Run time error '3075' : Syntax error (missing operator) in query expression '(Study Coordinator like *Viola*)'. my code Dim strsearch As String
Dim strText As String
strText = Me.txtsearch.Value
strsearch = "SELECT * from [List of Clinical Trials] where (Study Coordinator like ""*" & strText & "*"")"
Me.RecordSource = strsearch
DO you know where I am wrong
(Study Coordinator like
should be [Study Coordinator] like
hi austin awesome video! thanks been searching for some answers for my project and i finaly saw ur vid.ure a great help but one thing i need to fix wich is the form even if i choose continous form instead of single form it still appears as a single record would appreciate your help i already subscribed thanks in advance
need to put all field under the Detail section. You also can use a Split form that show both form header and datasheet.
my video of how to create Split form here: th-cam.com/video/V38sS2YGJQY/w-d-xo.html
+austin72406 i already put all the field in the detail section just like how you did in the tutorial then you format it to continous form but it didnt work with my form just like what you did
+Jerhumme Marcelo austin i badly need your help as the form only display one record and even formatting it as continous form it still display one record unlike what you did which all records displayed tabularly
can you post your code here? I can take a look
Hi i have a tried & used all your tutorial.. its really a big help.. i have search same code but im using macro but unfortunately it doesn't work in navigation form im using the split form there's a big con flick with the macro search code that i have when using navigation form..
i have a problem using the search when its Null.. like if someone accidentally leave the txtSearch and then press the search button it will pop-up an
Run-time Error 94
Invalid use of Null..
you should verify txtSearch with if function first like
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
msgbox " Please enter keyword first"
else
proceed the action
end if
austin72406 thanks for the reply i really appreciate it.. i have found a way around it i put and Error trapper "On Error Goto" then setfocus on the textbox "txtSearch".. your insight are really helpful.. keep it up.. Thank You and God Bless..
+austin72406 Is there a way to run an If IsNull(Me.txtSearch) = True Then that would return all records?
Man this is Just Great, You made it very Simple, I have 2 things to ask hear 1) After you hit the Showall butten, the data in the keywords textbox (which we entered to search previously) is still there unless you go back to the box select the data and deleat, can you please advise how to clear it as you hit the showall butten.2)With this type of search option, how big database can be searched? i mean i am going to have like 30,000 rows of data in my table over a year
1. set a code to make a txtSearchbox = ""
2. it should be fine to handle 30,000 records
+austin72406 Thank You Austin, you did not specify where do i set the code txtSearchbox =""
set at ShowAll button if you're using VBA.
I have exactly copied the same concept like you did in this Video, Please tel me where exactly this code needs to be added.
I entered the coded exactly. The search all works fine but search button always gives me a blank record... this is what I have
Private Sub CmdSearch_Click()
Dim StrSearch As String
Dim StrText As String
StrText = Me.TxtSearch.Value
StrSearch = "SELECT * FROM TblMasterLog WHERE (Project_Manager like ""*" & StrText & "*"")"
Me.RecordSource = StrSearch
End Sub
your code is correct, but make sure your display form or subform is not linked to the main form. if the subform is linked (master field and child field) to the main form then it will be blank because no record found from search that matches with the main form.
This works! thanks! could you add condition code if else when result is not found. thank you!
If IsNull(Me.TxtSearch) Then
MsgBox "Please enter a value to search!", vbInformation, "Inglewood CRM"
Else
strText = Me.TxtSearch.Value
strsearch = "SELECT * from ContactManagement where ((forname like ""*" & strText & "*"") or(surname like ""*" & strText & "*"") or(company like ""*" & strText & "*"") or(leadSource like ""*" & strText & "*""))"
Me.RecordSource = strsearch
End If
Seb Goring Thank You!
Thank you
PLEASE HELP! Nothing happens when I click "Search" command button
Private Sub Command46_Click()
Dim strSearch As String
Dim strText As String
strText = Me.txtSearch.Value
strSearch = "SELECT * FROM TblSlot where ([ClassID] like ""*" & strText & "*"")"
Me.RecordSource = strSearch
End Sub
I tried another tutorial by the same guy and also that one nothing happened as well. What gives? Thank you for whomever spots the error!
OMG! For all those having similar problems... Check to see if there is a box asking to "Enable Content."
It really helps to enable.
Hi austin, Thank you very much for your video,
can you help the same way to search date (specific date)
Thank you
i just update How to on my new website www.iaccessworld.com/create-search-form-by-keyword-using-vba-ms-access/
austin72406 Thank you very much i will go through it
I love the video, i will be checking out others that you have made but i am working on my final project for my intro to RDBMS class and i want a search box that a msg box will pop up if no results are found. I have another method for the search box that works, but i am trying your method and i can't get the show all button to work. When i click on it this error msg pops up, The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control. Here is my code:
Private Sub ShowAll_Click()
Dim strsearch As String
strsearch = "SELECT * from DVD_BlueRay_Collection"
Me.RecordSource = strsearch
End Sub
Any help you can provide would be greatly appreciated.
if you have total text box name txtTotal then you can add messge box like below after Me.RecordSearch = strsearch me.RecordSource = strsearch If Me.TxtTotal.Value = 0 Then MsgBox "No result found from searching" End Ifif showall button not working, check name of your table if correct. also make sure the data of your form is same as your recordsource on search.
Me.RecordSource = Strsearch
i am having trouble in both the search button and search all. It shows debug and red colour is coming in Me.RecordSource = Strsearch
that means your SQL statement is incorrect.
austin72406
please give me the correct SQL
austin72406 austin can u help me what if i want to put the data that i search in Subforms/Subreports? #TIA :)
show all button is working fine but search button is not working
something missing on your code. please check carefully.
+austin72406 same thing to me. below my code if there is any mistake:Dim strsearch As String
Dim strtext As String
strtext = Me.Keyword.Value
strsearch = " select * from PInfo where ((FirstName like ""*" & strtext & "*"")or(FamilyName like ""*" & strtext & "*""))"
Me.RecordSource = strsearch
austin72406 How can i lock the result form? I want disable any kind of editing, data entry on this search form. Thank you for very nice presentation.
under the design view and under the property of each field for datasheet, you can lock field by change Yes under Lock, No to enable, no to edition/deletion etc.
@austin72406
Thanks. That works. Another issue i have is i have one of the field (eg: name field) as lookup field in the table. And when i apply this field under search field on your instructed event code, it does not work!! what seems to be the reason for that?
Name is a word that reserved for access VBA. You should not use it as a name of field name
The field name is not just the name. Its EmployeeName. Since this field is lookup field from another table, search does not work if i use this field for search
+austin72406 What i mean is how to make this code search values in an unbound combobox which is also on the form??
i have one more problem my friend how to make preview report i'm having a hard time on my code i try the one on you're other tutorial but i can't make it work here is the code.
Private Sub Command57_Click()
Preview Report
Dim task, strCriteria As String
On Error GoTo ErrorHandler
strDoc = "DriversReport"
If Me.cmdShowAll.Enabled = True Then
DoCmd.OpenReport strDoc, acViewPreview, TempVars!vartempfilter
Else
task = " select * from DriversData_Table"
DoCmd.OpenReport strDoc, acViewPreview, task
ExitPoint:
Exit Sub
did you able to open report?
did you receive any error yellow line?
please specify your error on code.
no my friend it won't open the report here's the image of error i1314.photobucket.com/albums/t574/Ejstrider_Symbianize/error%20report_zpszesk5okb.png
looks like your code is correct except:
Preview Report
you can delete it or put make it as comment by putting ' in front like
'Preview Report
i guess it work but come to an error like this
i1314.photobucket.com/albums/t574/Ejstrider_Symbianize/error%202_zpshld1yxcq.png
i1314.photobucket.com/albums/t574/Ejstrider_Symbianize/error%203_zpsx7ie2rvp.png
here is my database my friend www.dropbox.com/s/qbbtr9p95s2bkwb/mostafa_.accdb?dl=0 can you check it out im new regarding access and vba so im zero knowledge regarding this so i need help so badly, thank you very much
Jeu vous dommande Search sur ecrir merci boco
I could send you my table if you so desire
Thats what I entered
Private Sub Command23_Click()
Dim strsearch As String
strsearch = "SELECT * from experiencethepower"
Me.RecordSource = strsearch
End Sub
if you use Subform to display your result from searching then you will need to set the strSearch to that subform like:
Me.SubformName.ResordSource = strSearch
Me.SubformName.requery
You need Me.Requery on your current form after Me.RecordSource = strSearch