Tieing Combo Box with navigation buttons

[Naterial]

I have a form with a drop-down (combo box) with a list of 435 names. It also has standard navigation buttons on the form footer. My question is, how can I 'tie' the combo box with the navigation buttons so that the box can control the record number in the navigation? That is, if I select a certain name, the number in the navigation box window will match that name's record number.

 

[jfgambit]

If you want to move to the specific record selected, why not use the following:

Private Sub ComboboxName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NameField] = '" & Me! [ComboboxName] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Are the names in the combobox unique? And are you pulling the Unique Record ID in the combobox as well as the name?

 

[dcx693]

I'm not sure if I understand correctly. Do you mean that you want the database to find the record number corresponding to a chosen name? You can use a Dlookup if you just want to find the number. If you want to move to the record, you can use the DoCmd.FindRecord command. If you want the form to find the record number then move to it, use both techniques above. (Though if you're doing that, you may be able to put the user's record number in a hidden column in your combo box. That way, you can cut out one step in code.)

If you want to filter the records display to just show that particular user info, you can use the Filter property of the form.

 

[Naterial]

Well, you see, here's the thing; this small form just has three controls on it. (I forgot to mention the Command button) First, the ComboBox with the drop-down list of 435 names. A command button that read "Go to Record" and the standard navigation buttons at the form footer. When the Command button is pressed, it opens a large, detailed form with all the record's fields (drawn from three different tables) for editing, etc. to be performed there. Basically, the small form with the Combobox, Command Button and Navigation button is only intended to point to a certain Name (record). Technically, it should work by itself, but it doesn't well. If you select a record via the navigation buttons, (say record number '231') and click the Command button (Go to Record) it opens the large form. That's fine, but the large form already has that capability. I want the ComboBox's selected value to reflect the number in the navigation buttons so when I click 'Go to Record' the large form (filtered) will open that record. It's already filtered, it already works (when selecting from the navigation buttons on the small form) the problem seems to be 'tieing' the list of Names in the ComboBox with the navigation buttons. The names themselves are not unique, but each name is linked to a separate field called 'MasterID' which -is- unique. Do you think by including 'MasterID' on the form (hidden perhaps) that will clear up the problem?

 

[jfgambit]

Nat:
Take a look at the attached example.

It should help you with what you are trying to accomplish

 

[Naterial]

Hmn..well, not exactly, (I looked at the Zip, thanks). Ok, I have the ComboBox, by using the mouse wheel, I can click on the box and scroll through the drop-down list of names just fine. By doing so, it also increments the navagation buttons to the corresponding record number, (Access automatically does that) the problem lies in clicking the drop-down arrow and selecting a name. By doing -that-, and selecting a name, the navigation buttons do not increment as they do with scrolling down in the ComboBox with the mouse wheel. How do I select a name in the ComboBox and have the navigation buttons move to the record's number without scrolling with the mouse wheel? I always thought Access did it automatically, but it does not.

Desired:

Open form -> click on combo box -> select a name from drop-down list -> number in navigation buttons window should increment/decrement to that name's corresponding record number

Present:

Open form -> click on combo box -> scroll down through list of names with mouse wheel -> number in navigation button window increments/decrements correspondingly to name's record number

 

[Oldsoftboss]

If I'm on the right track, hope it helps, otherwise ......

In the Form-current event you need to put Me.txtbox = Me.CurrentRecord
Just substitute me.txtbox with the name of the textbox that displays your record No

HTH
Dave

 

[Naterial]

I'm not sure that will work, as the matching I'm talking about is getting the navigation buttons to align with the selections in the combobox's drop-down list. The 'textbox' that contains the number is the built-in box between the navigation buttons I was talking about.

 

[Mile-O]

Use a recordset clone

 

[Oldsoftboss]

On thinking about your problem may I suggest the following:

You have a form with a combobox and 2 buttons
The form has the recordsource set to the table with the 400 odd records.

If this is the case then what is happening is that when you scroll with your mouse you are actually scrolling through the records. As there are no fields on your form that get there info directly from the table, then you do not notice any change. If you press the PgUp and PgDown buttons on the keyboard, it will probably do the same thing.

The items in the combobox do not get the info from the form but if you look at the combobox properties - row source, you will see that it will read something like

SELECT tblPatient.PatientID, tblPatient.LastName, tblPatient.FirstName FROM tblPatient ORDER BY tblPatient.LastName;

This row source is taken from a table called tblPatient and selects the rows ID, LastName and FirstName

To get the navigation button to follow the combobox you need to implement the code suggested by jfgambit

My suggestion is to get rid of the navigation buttons at the bottom of the form by setting the form properies - navigation buttons to No

Also you probably dont need a record source for your form.

HTH
Dave

 

[Naterial]

Thanks..

Thanks, I'll give that a try.