Is there any way to populate the combo box for any text search as user type any text in the combo box?
Normally combo box searchs and populate its list in alphabetical order starting from left most character till the end. It does not populate its list for any text if it comes in the middle or at the end of a string.
Thanks Adam I have a search button on form along with a text box to search any typed string, but I want the easy way for users to type any string in the combo box either way.
For example: There is a table with a location field which contains 10,000 records. A user entry form with a combo box, user can select any location from that combo to use it in entry form. user can use the search box also for the desire location to use, but it gives him extra search burden to go through for desire location.
Is it possible to populate a combo box for any string typed in the combo box at any position, i.e. start, middle or at the end of the location field?
anything is possible khalid, but for simplicity, you might just want to say to yourself, "nah, it doesn't need to be that simple".
i think what you'll find is that if you try to implement this sort of thing, the workload is going to outway the very small benefit a user will get from it. I've done it, so trust me, it's better off not done anyway.
if you have upwards of 10,000 locations, i would display these in a listbox instead, then have a textbox that you can 'filter' these with. i do this all the time:
the code required is:
on the "on change" event of the search textbox:
Code:
Private Sub txtSearchText_Change()
Dim vStrSearch As String
vStrSearch = txtSearchText.Text
txtSearchVal.Value = vStrSearch
Me.lstItems.Requery
End Sub
where txtSearchText is your search textbox; and
where txtSearchVal is a hidden textbox on the same form.
the listbox has a criteria in it to filter for that string in the search textbox, and is refreshed on each character change in the search textbox.
in your listbox's source query (of the field you want to search by):
Code:
Like "*" & [forms]![frmItems].[txtSearchVal] & "*"
where frmItems would be the form on which the search textbox is found.
your "after update" event of the listbox would then of course have your standard "find that record" code:
Code:
Private Sub lstItems_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ItemID] = " & Str(Nz(Me![lstItems], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
hope that helps.
edit: oops, forgot that i also have a "reset search" button, which makes clearing the search textbox a breeze (no need for the user to hit the backspace button umpteen times)
"on click" event of "reset search" button
Code:
Private Sub cmdResetSearch_Click()
Me.txtSearchText.SetFocus
Me.txtSearchText.Text = ""
Me.lstItems.Requery
End Sub
Attachments
Search box to filter large number of records and display in listbox.jpg
but it's just a fun to create a combo box in such a way which could gives the user a recordset in a listbox or in form while he/she types in combo box and rowsource of a form or listbox get updated with each stroke he/she types in the combo box.
Thanks wiklendt for your such a comperhansive reply.
I have a search form which has the following code on open:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_handler
Dim txtSearch As String
Dim frmName As String
'check for the open form
If IsLoaded("WorkOrder_F") Then
txtSearch = Forms!WorkOrder_F.txtLocationSearch.Value
Else:
txtSearch = Forms!WorkOrderPend_F.txtLocationSearch.Value
End If
'define the record source
Forms!SearchLocation_F.RecordSource = "tblLocation"
Dim strNewRecord As String
strNewRecord = "SELECT * FROM tblLocation WHERE (tblLocation.LocationStatus) Is Null AND (tblLocation.Location) Like '*" & txtSearch & "*'"
'set the record source
Me.RecordSource = strNewRecord
this code opens the form with the given SQL statement criteria and users can handle others tasks on the form. (I would show you my form's screen shots but i don't know how to paste it here in this forum)
this code opens the form with the given SQL statement criteria and users can handle others tasks on the form. (I would show you my form's screen shots but i don't know how to paste it here in this forum)
first, you should always wrap any code you provide in code tags. this makes the post keep the code formatting (such as indenting etc) and thus makes the code easier to read.
second, it looks like (to me, but i could be wrong) the code you pasted filters the source of the form itself and that this can ONLY be changed by typing in the search textbox and ONLY ONCE - when the form is opened. with my method, you can EITHER use the search box OR the listbox AND you can jump from record to record.
i recently uploaded a DB (for another reason), but has this functionality, here. open any of the forms form the right-most buttons on the swtichboard (in the "supporting data" section).
lastly, here is a link to explain the finer points of how to enhance your posts with formatted text, images and attachments. in order to display the screenshots in my posts, i add them as attachments ("go advanced" button under quick post, then scroll down and "manage attachments"), then click on the attchement, copy the url of the attachment, paste the url using the "image" picture icon in your post toolbar.
user can select Active Location by pressing save button one the search location form and can use in the application main form, here user can also create new locations and can send location activation request to admin... any how:
I will modify my search form and will adopt your techniques.(I didn't try yet,but I am wonder that it works while user types any text in the search box and the list will get modify with each stroke typed) it will be fantastic...
i recently uploaded a DB (for another reason), but has this functionality, here. open any of the forms form the right-most buttons on the swtichboard (in the "supporting data" section).
This is very good utility, but its not compatable with Access-2007, and give error messge while compacting any database, can you modidy your code for open file dialouge box filters to open accdb and accde files:
Code:
Public Function OpenCommonDialog(ByVal strTitle As String) As String
Dim strFilter As String
Dim udtOpenFile As udtOpenFileName
If (conHandleErrors) Then On Error GoTo ErrorHandler
strFilter = "*.mdb" & vbNullChar & "*.mdb" & vbNullChar & _
"*.mde" & vbNullChar & "*.mde" & vbNullChar & _
[COLOR=blue]"*.accdb" & vbNullChar & "*.accdb" & vbNullChar & _[/COLOR]
[COLOR=blue] "*.accde" & vbNullChar & "*.accde" & vbNullChar[/COLOR]
' Load udt with the passed arguments.
With udtOpenFile
.lngStructSize = Len(udtOpenFile)
.lngWindowOwner = Screen.ActiveForm.hwnd
.strFilter = strFilter
.strFile = Space$(254)
.lngMaxFile = 255
.strFileTitle = Space$(254)
.lngMaxFileTitle = 255
.strInitialDir = CurDir() & vbNullChar
.strTitle = strTitle
.lngFlags = 0
End With
' Call the API function.
If apiGetOpenFileName(udtOpenFile) Then
' If no errors then strip any trailing Null and leading/trailing spaces.
If InStr(udtOpenFile.strFile, vbNullChar) Then
udtOpenFile.strFile = Left$(udtOpenFile.strFile, InStr(udtOpenFile.strFile, vbNullChar) - 1)
End If
OpenCommonDialog = Trim$(udtOpenFile.strFile)
Else
' Else return a ZLS.
OpenCommonDialog = ""
End If
ExitProcedure:
Exit Function
ErrorHandler:
OpenCommonDialog = ""
DisplayError "OpenCommonDialog", "mdlFielOpen"
Resume ExitProcedure
End Function
I also can not find :
open any of the forms form the right-most buttons on the swtichboard (in the "supporting data" section).
oops. sorry. i've been uploading a few things lately, and pointed you to the wrong one. the file i wanted to direct you i've attached to the bottom of this post.
to clarify, the sample database i've just posted to show you the search textbox and the listbox function.
first of all, you've changed/complicated your original question. your original question was to filter a combo when you type text into it. now you are asking about an API opening .accde files. that's something quite unrelated.
the CODE i supplied for linking BE, is compatible with access 2007, as i use it myself. the FILE in that 'wrong' link is not mine, though it did work on my machine when i had a look at it - my machine has access 2007 AND then file itself is an .accdb file, so, yes it is 'compatible' - i think you are confusing "compatibility of code" with "won't do what i want" or "doesn't work when i change it".
i just tried to modify the mdlFileOpen in the AutoComapct sample, as you showed (in fact, i copied an pasted your additions). the program copied the full folder and file path into the appropriate table. it looks like that's all you really need it to do.
you DO have to make sure to change the selected type in the file dialog...
and also change the destination table and field.
the compacting on .accdb/e doesn't work, sure, but that's because ".accdb" is a longer extension, and the compacting code as written to handle an extension of three letters, not five. perhaps the code can be changed to look for the first period (".") instead. and i don't know if the compacting process was changed between the two file types, so that might need reviewing too, should any troubles be encountered.
you've changed/complicated your original question. your original question was to filter a combo when you type text into it. now you are asking about an API opening .accde files. that's something quite unrelated.
I am sorry for jumping into complications and discussing of window API calls function here which is not related to the subject question;
actually I was reading the code which was provided with the ‘wrong’ linked compacting utility.
This is great utility for compacting purpose, it is in mdb format not accdb format; I have ms-access-2007 on my machine and I converted it to to access-2007 format; but still some errors (may be due to file extension of 3 characters instead of 5) any how:
Now coming back to the search text box functionality which you posted earlier; it’s GREAT! Wiklendt believe me its really works as I was needed. I change the code a little bit to meet my requirement. My list box is refreshing/changing as I type any character in the search text box.
Many thanks
I have to modify my search form as you can see the screenshots there is no room on the form to display the search result listbox all the time on the main form (due to congested main form)
Should I adopt the technique, whenever user click on searchtext ( or combo box)? the ‘lstItems’ list box should appear and show the searched locations in list box of user need, what do you think? Any idea…
This is great utility for compacting purpose, it is in mdb format not accdb format; I have ms-access-2007 on my machine and I converted it to to access-2007 format; but still some errors (may be due to file extension of 3 characters instead of 5) any how
my apologies - i just checked my archives, the zip file does indeed include an .mdb, not .accdb... you are right.
i also have 2007 and it must have 'upgraded' the file - i did this ages ago, so did not remember.
you can still use the mdlFileOpen code, even if you don't need the Compact code. you were asking (i thought this is what you meant by "location" in your screenshots? ) about storing a location by using the file browser, well, that's what that bit of code (mdlFileOpen) does. the alterations you added will give you the functionality to "select" an accdb file and store the path in your table.
anyway, you now have a funky bit of code to apply as desired
thanks, i'm glad it has worked for you. i was extatic to find it myself - i should mention here it's not "my" code, i found it searching in these forums this is way back before i decided i really should be noting where i get my bits n pieces from.
I have to modify my search form as you can see the screenshots there is no room on the form to display the search result listbox all the time on the main form (due to congested main form)
yes, i almost fell off my seat when i saw your forms! you've put a lot of work into it, so i hope you do manage to get all the functionality out of it that you want.
combo boxes have an "autoexpand" property. as far as i understand, when the combo has focus, the "list" portion of it expands automatically for the user, so perhaps this will work while-you-type? you'll have to try it - i've never done it so don't know how to manipulate it this way. you can also change how many rows/records the user can see when the combobox expands... i think the default is 6, but you can increase this (not sure if there's an upper limit?) but making this larger will make the combo feel less claustrophobic, expecially with 10,000+ records!
(ah, just found it: called "ListRows" and the default is "16"....)
if you really want the compact database feature, bob larson has some code on his website that you could probably incorporate into your project. i'm not sure how you are distributing your app, but i tend to set my databases to "compact on close", which happens automatically when the app is closed. of course, if your app is on 24/7, you'd need another method, hence this code.
if you really want the compact database feature, bob larson has some code on his website that you could probably incorporate into your project. i'm not sure how you are distributing your app.
Yes wiklendt;
I used to compact my be thrice a week, because its a huge database, sometime i get some funny chines characters in some fields due to network problem (slow or down).
My Application is 24/7 and I have a function in my fe application on client machines which trigs every 20 minutes and checks for idol users (if there is no activity fond on forms for 5 minutes) a form popups with a count down timer message telling the users that application will be closed in 5 minutes due to none use; if user click the OK button the timer is reset to 0. (this is not my code but this is great effort by someone to check the idol detect time.)
Another functionality I developed is:
double clicking on logged-in user id in Admin panel will force user logout from the system, but he could come again to the database which is problem;
Although I can kick out all or specific user from the application as well and there is a chat panel I have created for users to talk to admin and send messages bla;bla.
I usually send my application to users by mail (ms-outlook)in zip file; but this is lengthy work to do.
I then realized that i should create a function which will copy the application from network folder automatically to user desktops whenever there a change/modification in the application version.
This function is doing well and copy the new version on users desktops automatically; i just have to change the version name in the version table which compares the old version name on user login forms.
boblarson; said:
If the app is on 24/7 then you would need something like this to kick the users out of the database and then run compact and repair.
Thanks Mr. boblarson
As i mentioned my application is 24/7.
I check your website and fond your utility which is very simple and powerful. Your technique is fantastic; I will try your techniques.
Only one thing i will mention here:
If user opens the database and if it is in maintenance mode; he will get a message to try to login later because the database is closed for maintenance purpose:
but:
if user don't click on the Msgbox 'OK' button the database is still in hang mode and will not allow admin to open it exclusively for compact and repair.
Code:
Private Sub Form_Load()
Dim db As DAO.Database
Dim snp As DAO.Recordset
Dim msg As String
Set db = CurrentDb
Set snp = db.OpenRecordset("Settings", dbOpenSnapshot)
If snp![logoff] Then
[COLOR=BLUE]msg = "The database is closed for updating" & vbCrLf & vbCrLf & "Please try later."
MsgBox msg [/COLOR]
Application.Quit
End If
DoCmd.Hourglass False
End Sub
would in't be better to show a form instead a message box with a message and close the form automatically after 1 minute if user not hit OK button?
That isn't my code, I just pointed you to Roger Carlson's Access Library. Yes, it seems that you have a good point and you might want to modify it so that it does that.
believe me its really works as I was needed. I change the code a little bit to meet my requirement. My list box is refreshing/changing as I type any character in the search text box. 
