Forms and Combo Boxes

[marko]

Hi. This is a easy one, i think.
I have a form which shows all my records, the main column is ID. In the
form header i have a combo box in which i have all off the ID's. Now
when i go to the combo box and select a particular ID i would like to
see the record with that ID but not filtered, i would just like to jump
to that record. And if i scroll up or down i would be scrolling from
that record that i just jumped to.
Thanks!

Marko

 

[Graham Mandeno]

Hi Marko

First, your combo box must be unbound (i.e. ControlSource is blank). Also,
if it has multiple columns, the BoundColumn must be the one that holds the
record's primary key (ID).

Now, say your combo is named "cboSelectRecord". Add the following code for
its AfterUpdate event:

Private Sub cboSelectRecord_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ID Field]=" & cboSelectRecord
If .NoMatch Then
MsgBox "Something wrong - record not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

Insert your own IDD field name in the obvious place.

This assumes your ID field is numeric. If it is text then change the
FindFirst line to:
.FindFirst "[ID Field]=""" & cboSelectRecord & """"
(count the quotes carefully! = 1 then 3 then 4)

 

[marko]

Thanks a million!! It works perfect!
I have one more question if you are willing to answer:
Now when i select an ID from my combo box it goes to a certain record
but when i scroll through records the combo box remains the same, it
doesnt change. How can i make it change, how can it be the same as the
record ID which changes when i scroll through records?
Thanks again!!!

Marko

 

[Graham Mandeno]

The event which occurs when your form displays a new record in the Current
event. So, when your Current event fires, you need to set the value of your
combo box to the same as the ID value of the current record:

Private Sub Form_Current()
cboSelectRecord = [ID field]
End Sub

 

[marko]

You know everything! Thanks onece again!!! This works great too!!
I have one more question if you wont mind:
My combo box shows 4 columns and those coluns are very broad. can i
make the combo box narrow but the dropdown selection as wide as it
needs to be to show everything. So the combo box is let's say 2cm of
width and when i press that down arrow on the combo box the dropdown
selection would be 8 cm wide?
Is this possible?
Thanks again for all your answers!!

Marko, Croatia

 

[marko]

Found It!!!!!

Marko

 

[Graham Mandeno]

Yes - change ListWidth from "Auto" to whatever width you want the
dropped-down section to be.

Also, you can set individual column widths with the ColumnWidths property.
The first column with a non-zero width will be the one that shows when the
list is not dropped down.

 

[marko]

Thanks!!!!

Marko