combo box as a locator (Access XP)

Topic

New Reply

I have a combo box being used to locate specific members. When I have two members with the same last name I can’t get it to go to the second person. The Row Source reads SELECT qryMembership.LastName, qryMembership.FirstName, qryMembership.MembershipID FROM qryMembership; there is one bound column. Column count is 3. The event procedure code reads as follows

Private Sub Combo46_AfterUpdate()
‘ Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst “[LastName] = ‘” & Me![Combo46] & “‘”
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thank you for helping me work on my bugs.

Fay

Reply | Quote

Replies

Okay, what you need to do is change your combobox’s ‘bound’ column to your ID field. Then filter your form based on the ID, not the name.

Reply | Quote

Thanks for the response. If I change the bound column to 3 which was the location of the ID on the SQL grid it didn’t work. But if I replace the combo box and make sure the ID is first on the grid it works. Go figure. Did I misunderstand the Bound column option below the Row Source?

Again thanks for the help.

Fay

Reply | Quote

FYI, the Bound column selects the value that is linked to whatever the DataSource of the combo is, while the RowSource determines the source of the drop-down list the combo box displays. One of the very powerful tools in Access. I also wanted to note that we’ve seen some problems in using the Recordset.Clone method with AccessXP and ADO. For some reason, it doesn’t always seem to work reliably, though that’s the code the combo box wizard generates when you tell it to create a combo for record navigation purposes. We’ve migrated to applying a filter from the combo box, which seems very reliable, and often is quicker. Hope this helps.

Reply | Quote

I remembered you saying there have been some problems using the stock Recordset.Clone code when running the Select a Record on a Form wizard when placing a drop-down box. I happen to use this method frequently and was wondering if you could give me more detail on the issue of applying a filter from the combo box.

I am currently working on a project that uses the standard method for finding a record based on the combo box and don’t have any problems with that directly, but I also have a button set to Duplicate Record and, if you don’t select the record using the combo box you get a very basic run-time error that I can’t seem to trap out and use a less obscure warning. The run time error is ‘Invalid use of Null’. I want to replace that with a msgbox saying ‘Please select a record from the drop down box before duplicating a record’ but the Access VB error msgbox always take precedence.

So — actually, replacing the combo box may not help but I am curious as to why you avoid it and would like more details on your substitution.

And, as to the error on the duplicate record routine, I am using the canned Access coding which likes to call menu selections — is that a bad idea? Is there a better way to do it? I get confused by the acCmd and DoCmd and RunCommand options at any rate. But, I would like to not use the kind of code the Wizard likes to use, such as DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 — it would seem to me that using custom menus or hiding/removing standard ones would cause such code not to work. It just *looks* wrong.

Hope this isn’t too confusing.

Reply | Quote

First of all, the Recordset.Clone only seems to be a problem with databases developed on XP that run on Access 2000 systems. I believe it has something to do with XP defaulting to ADO when it builds the code, and A2K gets confused, but that’s only a theory. In any event we found that it didn’t always display the correct record, though this may have been fixed in the latest SP as we haven’t tested it.

To fix the duplicate record problem, why not disable the button on Form Open, and then enable it in code once the combo box is changed. The method you are using to duplciate the record works and doesn’t need any complex code, which doing it in DAO or ADO would require – and those menu items should still work, even if the menu is not visible.

As to the filter process in lieu of Recordset.Clone, one of the problems with the .Clone approach is that the full recordset is still there, and people can use PageUp and PageDn to move to a record which is different from that shown in the combo box. What we do is create a filter in code based on the contents of the combo box, and apply that to the form. It does typically require that you have a hidden text box on the form which contains the key for the record. Then anytime the combo box value changes, we remove the filter, we modify the filter, and then reapply it. Thus there is always only one record in the datasource, and things can’t get out of sync. If you would like a sample of the code involved, it may take a day or so, but I will try to create one.

Reply | Quote

thanks for the response. I went ahead and replaced the DoCmd with RunCommand code and that works also. I kind of like RunCommand because you can tell from looking at it what you are trying to do, rather than calling a menu item by number. Guess it’s just philosophical. I did notice a line in the VB Help that states that RunCommand replaced DoCmd after Access 97, which implies that DoCmd is, so to speak, deprecated. That doesn’t eliminate the error you get when trying to duplicate the form data before selecting a record, which was the original problem. You suggestion to disable the button is interesting; I might want to have the form select a record on open as well — say the first one, to reinforce that one navigates to other records thru the drop-down box. That would be even more intuitive, perhaps.

On the subject of paging up and down on a selected record, I just noticed before getting your reply that this occurs when the Mouse Wheel or Page Down is used and definately don’t want this to occur. I have a sub-field that needs to be tied to a specific record and don’t want users to accidentally scroll into a new record on the subfield. I have it set to cycle in current record in Properties but can’t find anything to answer to the Wheel situation. There is a way to detect PageUp or PageDown and do an event — maybe automatically return to the ‘actual’ record and delete the one just created. It would be better to disable the key totally but help says key events can’t be cancelled. Any suggestions?

I realize this thread is getting off-topic; if you want to move it to something like Subform/Form Navigation Control issues… that’s kind of what I am blathering about. I’m using A2K SR-1 on Win2KPro

Reply | Quote

Actually RunCommand replaced DoMenuItem. RunCommand is a method of the DoCmd object and the Application object.

Reply | Quote

If you want to disable keys, you can do the following:

Set the KeyPreview property of the form to True – this will cause all key events to be handled by the form before being passed on to the event handler of the contol involved.

In the KeyDown event handler of the form, set KeyCode to 0 for all key codes you want to be ignored. For example:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case vbKeyPageUp, vbKeyPageDown
KeyCode = 0
End Select
End Sub

Reply | Quote

> If I change the bound column to 3 which was the location of the ID on the SQL grid it didn’t work. But if I replace the combo box and make sure the ID is first on the grid it works.

Remember that referring to columns in a combo box is zero-based. IOW, if you have columns LastName, FirstName, and PersonID, bound column of 3, then to use your FindFirst you would look for PersonID matching, “Me![Combo46].column(2),” which is your bound third (not 2nd) column.

thx
Pat

Reply | Quote