How to move records based on selection made from a combo box

[mparekhji]

Hi All:

I have a problem which will probably be simple for you but i'm finding it difficult to solve:

I have one table in my database. It has the following fields:

tableName : tblReceipe

ReceipeID
ReceipeName
Category
ReceipeImage

What i want to do is, i dont want users to navigate using the navigation buttons.
I want the users to select the ReceipeName from a drop down combo box, and when they select the a receipe from the combo box, then that record should become the current record.

I have created the form frmReceipe, based on the Receipe table and i have text boxes for all other fields and combo box for ReceipeName field.

Now i want to select a Receipe from this box and all the other fields sould go to the record of that receipe.


Thanks in advance!!
-Manish

 

[jebry]

Hi Manish!

Try the following code:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblReceipe", dbOpenDynaset)

rst.FindFirst "ReceipeID = " & YourCombobox.Value
If rst.NoMatch = True Then
Call MsgBox("There is no receipe by that name&quot
Else
Me.Bookmark = rst.Bookmark
End If

Set rst = Nothing

A couple of things to note:

Your combo box should have a row source that is a query which selects the receipeID and receipename from tblReceipe. The bound column should be 1 and the column widths should be 0",2". The second number should be the same width as the combo box. The control source of the the combo box should be blank. You should create bound text boxes to display the ID and name.

It is probably best to put the code above in the click event of a command button that can be used to activate the search.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Taff07]

You can always use the Wizard option on Set-up of a Combo box to do what you want. Afterwards if need be you can view the code created yourself for future use.

Taff

 

[mparekhji]

Thanks a lot Jeff and Taff. It works now. I did the following:

Private Sub cboReceipe_AfterUpdate()
   Dim strCriteria as String

   Dim rst as DAO.Recordset
   Set rst = Me.RecordsetClone

   strCriteria = "ReceipeID = " & cboReceipeName

   rst.FindFirst strCriteria
   Me.Bookmark = rst.Bookmark
End Sub

-Manish