View/Edit Record Based upon Combo Box

[BigJimSlade]

Big Jim here,

I would like to be able to select an item from a combo box (based upon distinct rows of information) and have my form move to the particular record where this item is found. Is there an easy way to do this? If not, a hard way is just fine.

Thanks in advance,
Big Jim

[This message has been edited by BigJimSlade (edited 10-31-2000).]

 

[chilluk]

If the combo box contains the key field, then it is easy.

In the AfterUpdate event of the combo, build a SQL string e.g.

strSQL = "SELECT * FROM TABLE WHERE KEYFIELD = " & Combo.Column(0)

Then change the recordsource of the form to be the SQL string (Me.Recordsource = strSQL). You can do this other ways, but this is the most efficient I find.

 

[gino]

lookup the requery in help file. me.form.requery will be helpful to refresh the record based on combobox. insert the requery in the after update event..........

have your query criteria based on the unbound combobox field for example:
[forms]![yourform].[yourcombobox]

i suggest having the combox unbound. this allows you to change the update the value in combobox while refreshing record, but if you plan to use an index field check previous post....



[This message has been edited by gino (edited 10-31-2000).]

 

[chrismcbride]

The accepted way of doing this is using the RecordsetClone property. You use the FindFirst method of the RecordsetClone to locate the record that you want to move to, then set the Forms Bookmark property to the RecordsetClone's Bookmark.

Me.RecordsetClone.FindFirst "emp_no = " & me.cboEmployee

me.Bookmark = Me.RecordsetClone.Bookmark
This will do it as long as the value you select in the Combo box is unique.
Good Luck
Chris