tag:blogger.com,1999:blog-777975398841809391.post3170612185933872474..comments2024-03-05T22:21:42.995-08:00Comments on Microsoft Access Tips: Using a Combo Box to Search for a RecordUnknownnoreply@blogger.comBlogger25125tag:blogger.com,1999:blog-777975398841809391.post-42921981498585587452012-06-21T11:30:24.265-07:002012-06-21T11:30:24.265-07:00Hi I was hoping for some help. I'm creating a...Hi I was hoping for some help. I'm creating a db (sports related) and I want to add a combo box to the form in order to search for players. I've tried the different pointers on here, but am still having trouble. Anyone willing to take a look? Any help would be greatly appreciated.HatingMyJobhttps://www.blogger.com/profile/04976842375628937654noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-41910959539340521182012-05-03T02:44:33.174-07:002012-05-03T02:44:33.174-07:00Hi Mili
I think the problem you are describing ma...Hi Mili<br /><br />I think the problem you are describing may relate to how you have set the combo box properties. Make sure the BOUND COLUMN property is set to <b>1</b> (the primary key in my exercise). Then make sure the COLUMN COUNT property is set to to <b>2</b>. This ensures that although two columns are displayed in the Combo box drop down list, the value being search is the first of those columns.<br /><br />There is more information on Combo Box properties in my post on <a href="http://ms-access-tips.blogspot.co.uk/2011/07/customizing-access-combo-box.html" rel="nofollow">Customizing a Combo Box</a>.Justin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-64193341605594970882012-05-02T13:36:22.083-07:002012-05-02T13:36:22.083-07:00when row in table is primary key search dont workwhen row in table is primary key search dont workMILIhttps://www.blogger.com/profile/03242565248653305685noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-46535527180152664222012-04-15T14:47:21.779-07:002012-04-15T14:47:21.779-07:00Nvm figured it out. Had to change the properties t...Nvm figured it out. Had to change the properties to date and add a quote:<br /><br /> rs.FindFirst "[NAME] = '" & Me![Combo9] & "'" & "And [DATE] = #" & Me![DATE] & "#"weyolohttps://www.blogger.com/profile/18285096368173204847noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-32758352799773351492012-04-15T13:55:59.289-07:002012-04-15T13:55:59.289-07:00Anyone know why I'm getting an error "Typ...Anyone know why I'm getting an error "Type Mismatch" using multiple criteria?<br />Private Sub Combo9_AfterUpdate()<br /> ' Find the record that matches the control.<br /> Dim rs As Object<br /><br /> Set rs = Me.Recordset.Clone<br /> rs.FindFirst '"[Name] = '" & Me![Combo9] & "'" _<br /> And"[Date]=#" & Me![Combo9] & "#"<br /> <br /> If Not rs.EOF Then Me.Bookmark = rs.Bookmark<br />End Subweyolohttps://www.blogger.com/profile/18285096368173204847noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-44428853337441607252012-03-27T11:47:42.034-07:002012-03-27T11:47:42.034-07:00Thanks for the additional info Archie. From what w...Thanks for the additional info Archie. From what what you are saying it sounds like you are using an MS Excel spreadsheet rather than an MS Access Database. Unfortunately, I can only help you with questions relating to the Access Database.Justin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-20883316650305588612012-03-27T10:45:42.293-07:002012-03-27T10:45:42.293-07:00Hi Justin,
Thanks for replying.Lets take a hypoth...Hi Justin,<br /><br />Thanks for replying.Lets take a hypothetical example. i have a list with alphabets from A to Z.Each alphabet is a name of a person.i have a workbook which has ten sheets. there is a macro which updates all the ten sheet. so as of now each alphabet is to be manually choosen from the drop down list and then the macro is run to update all ten sheets.a new sheet appears for each alphabet which is then saved at desired location.so i was looking for a code where the macro selects each alphabeet name ,runs the inbuilt macro, saves the new workbook and then changes the alphabet name to the next one.is there any such code to automatically selct entries from the drop down list and save it.Archiehttps://www.blogger.com/profile/05880293819976960391noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-72606907230928445682012-03-26T10:59:39.393-07:002012-03-26T10:59:39.393-07:00Hi Archie
Sorry I am not quite sure what you mean...Hi Archie<br /><br />Sorry I am not quite sure what you mean. What is it you want to populate from the drop down list?Justin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-65287736977686934342012-03-25T20:44:11.842-07:002012-03-25T20:44:11.842-07:00hi I need help on how to populate from a drop down...hi I need help on how to populate from a drop down list.i have a list of 200 entries and a report is generated once i click on a button updating ten sheets. i am trying to find a way where i dont have to selct the entries from the drop down manually and the report is generated for all 200 from the macro. can anyone help?Archiehttps://www.blogger.com/profile/05880293819976960391noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-42633091773951587312012-03-22T11:38:53.261-07:002012-03-22T11:38:53.261-07:00Hi
You need to modify the 4th line of my code fro...Hi<br /><br />You need to modify the 4th line of my code from the example from:<br /><br /><b>rst.FindFirst "ID = " & Me!ctlSearch</b><br /><br />To:<br /><br /><b>rst.FindFirst "ID = '" & Me!ctlSearch & "'"</b><br /><br />This will ensure the ID text value being searched for is enclosed within quotation marks. (Note I have used a single quotation mark here ie ' because the search value is already nested within a string within this line of code). In your case, the code will search for <b>ID = "10x-7"</b> rather than <b>ID = 10x7</b>. It's only a subtle difference, but it does make the difference between the code working and not working.Justin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-64415139013037655032012-03-18T04:34:37.943-07:002012-03-18T04:34:37.943-07:00All I can really suggest, in that case, is that yo...All I can really suggest, in that case, is that you try going through the example exercise from my main blog post above. Just follow the instructions I gave to create the form with the combo box. It takes you right through the process. Once you have got it working for yourself, you might find it easier to work out what is going wrong in your own particular database. Sorry I can't be of more help.Justin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-73027670973122845842012-03-17T20:08:13.783-07:002012-03-17T20:08:13.783-07:00I checked to make sure the Microsoft Office 12 Acc...I checked to make sure the Microsoft Office 12 Access database engine Object Library was checked. The options I have checked are:<br /><br />Visual Basics for Applications<br />Microsoft Access 12.0 Object Library<br />OLE Automation<br />Microsoft Office 12 Access database enigne Object Library<br /><br />I have changed the code to:<br /><br />Dim rst As DAO.Recordset<br /><br />, but still getting the message box. Could it be some the wrong with the Row Source SQL query? I have blown away the combo box a couple times trying different ways of setting up the box, but never have got it to work.Zack T24https://www.blogger.com/profile/11340452669522222644noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-34807497408030660522012-03-17T09:38:14.006-07:002012-03-17T09:38:14.006-07:00Hi Zack
I think I know what the problem could be....Hi Zack<br /><br />I think I know what the problem could be. Can you confirm that there is a reference to the <b>Microsoft Office 12 Access database engine Object Library</b>. You can check this by opening the VBA editor, clicking the TOOLS menu, and then REFERENCES. <br /><br />If there is not a reference then you just need to tick the relevant check box. There is more information about this procedure in my blog post on <a href="http://ms-access-tips.blogspot.co.uk/2012/03/accessing-data-with-vba-code.html" rel="nofollow">DAO Recordsets</a>.Justin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-67905892176023971222012-03-16T12:41:20.946-07:002012-03-16T12:41:20.946-07:00If you would like I could zip the file up and use ...If you would like I could zip the file up and use a program like dropbox to send it you a copy of it.Zack T24https://www.blogger.com/profile/11340452669522222644noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-42189140599322993332012-03-16T12:27:32.005-07:002012-03-16T12:27:32.005-07:00Hi Justin,
First, let me say thanks for the help....Hi Justin,<br /><br />First, let me say thanks for the help. I haven't had to mess with Access too much since college, and I am a little rusty. Especially in Access 2007. <br /><br />The combo box does populate the data (which is Player ID, Player Lastname, and Player Firstname). I checked the Bound field property, and it is set at one. My column count is set to three. The Player ID is a numeric field; set as a autonumber. <br /><br />Just a brief description of what I am trying to do is:<br /><br />I have a multiple page form for players for our local little league. I am wanting to find and reopen a certain players form to add or adjust information during the season. We have several to come in during registration and not have all the required documentation such as birth certificate, proof of residency, and etc. <br /><br />Any advice would be greatly appreciated.Zack T24https://www.blogger.com/profile/11340452669522222644noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-36879708291183990672012-03-16T02:57:12.518-07:002012-03-16T02:57:12.518-07:00Hi Zack
I have just tried out the code you have s...Hi Zack<br /><br />I have just tried out the code you have supplied and the good news is that the code itself <b>does</b> actually work. This suggests that the problem might be with how you have set up your combo box.<br /><br />Is your combo box list populated with data when you try to select an item? If so, is it showing data from the Id field (ie <b>Player Id</b>) and/or is it set up to display a description of what the ID field actually is? It is important that if the Id field you are searching on is the first field the table, that the BOUND FIELD property (DATA TAB) of the combo box is set to 1. You can then opt to display other field values from the record by adjusting the COLUMN COUNT and COLUMN WIDTH properties (FORMAT TAB)accordingly.<br /><br />Also, is your Player Id field numeric? (My sample code is set up to search on a numeric field).Justin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-44051763348392772982012-03-15T17:59:28.242-07:002012-03-15T17:59:28.242-07:00Just in case you would like to see the VBA script....Just in case you would like to see the VBA script. This is what I have:<br /><br />Private Sub RecSearch_Change()<br />On Error GoTo myError<br />Dim rst As Object<br /><br /> Set rst = Me.RecordsetClone<br /> rst.FindFirst "[Player ID] = " & Me.RecSearch<br /> Me.Bookmark = rst.Bookmark<br /> Me.RecSearch = Null<br /> <br />leave:<br />If Not rst Is Nothing Then Set rst = Nothing<br /> Exit Sub<br /><br />myError:<br /> MsgBox "Record Not Found"<br /> Resume leave<br /> <br />End Sub<br /><br />The combo box name is "RecSearch", and the Primary Key I am wanting to look up is "Player ID".Zack T24https://www.blogger.com/profile/11340452669522222644noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-54141123851481262672012-03-15T07:07:09.197-07:002012-03-15T07:07:09.197-07:00Hi,
I have checked the name properties of the com...Hi,<br /><br />I have checked the name properties of the combo box and the name of the id field, but I am still getting the record not found message box. Do you have any ideas?Zack T24https://www.blogger.com/profile/11340452669522222644noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-7044269632725878842012-03-09T05:47:55.736-08:002012-03-09T05:47:55.736-08:00Hi Phil
You need to make sure that the line of co...Hi Phil<br /><br />You need to make sure that the line of code responsible for constructing the search term contains the correct reference to the ID field in your table, and to the name property of your combo box on the form.<br /><br />In my example the NAME property of the combo box control is called ctlSearch; and the name of the ID field is called "ID". I would suggest you look at this line of code from my example snippet, and amend it as necessary for your own particular database:<br /><br />rst.FindFirst "ID = " & Me!ctlSearchJustin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-56754234293573342282012-03-09T03:27:20.406-08:002012-03-09T03:27:20.406-08:00Hi,
I am using this as a name dropdown menu, when...Hi,<br /><br />I am using this as a name dropdown menu, when I select the list I get the correct names within the menu, but when I click on the name to see the records, it comes up saying results not found, even though the combo list has populated..can you help?<br /><br />PhilPhilhttps://www.blogger.com/profile/08234307991543591325noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-35751157577364249382012-03-08T02:26:46.270-08:002012-03-08T02:26:46.270-08:00Hai Justin,
Thank you soo much.... Now its fine.Hai Justin,<br /><br />Thank you soo much.... Now its fine.vajeethhttps://www.blogger.com/profile/03421303526388849993noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-4083877170383768442012-03-08T02:16:20.802-08:002012-03-08T02:16:20.802-08:00Hi Vajeeth
If you want to type text into the comb...Hi Vajeeth<br /><br />If you want to type text into the combo box (in addition to selecting a list item), then you will need to move the VBA code from the ON CHANGE event to the AFTER UPDATE event.<br /><br />Otherwise, the code will run as soon as you enter the first character in the combo box. However, by using the AFTER UPDATE event, the code will not run until you hit the ENTER key or move out of the combo box control. This gives you chance to type in the full search term.Justin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-75096534422352702622012-03-07T23:17:22.736-08:002012-03-07T23:17:22.736-08:00Hai Justin,
Thanks for your help now i have got i...Hai Justin,<br /><br />Thanks for your help now i have got it. But one problem in that. I cant type the text in my Combobox. When i type it shows "results not found" can you help me with that please.vajeethhttps://www.blogger.com/profile/03421303526388849993noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-53740271138437465652012-03-07T06:08:44.650-08:002012-03-07T06:08:44.650-08:00Hi Vajeeth
I would suggest you first check the NA...Hi Vajeeth<br /><br />I would suggest you first check the NAME property of the combo box control. It should be called ctlSearch. Then check the name of the ID field that you are searching on. It should be called ID.<br /><br />This is so the following line of VBA code (from the example snippet) matches:<br /><br />rst.FindFirst "ID = " & Me!ctlSearchJustin Farrellhttps://www.blogger.com/profile/02955065460330794920noreply@blogger.comtag:blogger.com,1999:blog-777975398841809391.post-32551957410676230682012-03-07T05:34:59.500-08:002012-03-07T05:34:59.500-08:00Hai,
When I Try this it shows record "not fo...Hai,<br /><br />When I Try this it shows record "not found". But i have dats in my records. Can u guide me pls.vajeethhttps://www.blogger.com/profile/06536689446314741726noreply@blogger.com