VBA code for setting combox as record selector as opposed to inbuilt macro (1 Viewer)

[chrisjames25]

Hi, currently have a form with a combobox at top that acts as a record slector based on whatever i choose with the cbox. This was all programmed via control wizard. I would like to know how to do the same act but by using VBA.

PLease see macro image attached, also tried to type it out below in case people are skeptical about attachments. NOt sure on the etiquette.

Search For Record:
Object Type:
Object Name:
Record: First
Where Condition: =="[Category_ID]="&Str(Nz([Screen].[ActiveControl],0))


MAny thanks

 

[Gasman]

Have you tried opening the form and the Database Tools/Convert Form's Macroes to VBA ?

 

[chrisjames25]

Sadly it is greyed out so i cant click convert to macro.

 

[Gasman]

Something like

Me.Recordset.FindFirst "Category_ID=" & Nz([Screen].[ActiveControl],0)

if the combo value is numeric. Not sure why the had the Str function with it?
I would use the control name though

Me.Recordset.FindFirst "Category_ID=" & Me.cboCategory_ID

HTH

 

[MarkK]

If the combobox is on the form in question, it would more reliable to refer to it directly, or better yet, handle its AfterUpdate or Click event, and then call a routine that does the navigation. Consider...

Private Sub SearchCombo_Click()
    Me.GoToID Me.SearchCombo
End Sub

Public Sub GoToID(CategoryID as long)
   With Me.RecordsetClone
      .FindFirst "CategoryID = " & CategoryID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

...noting that doing the navigation in a clone is quicker, and doesn't update the UI until the record is actually found, if it is actually found.
hth
Mark

 

[chrisjames25]

massive thanks to both of you. THis has bothered me for days. Gasmans is working away now.

MarkK - apologies for being stupid but i have got a bit lost on your code regarding REcordsetclone. I shall go away and read about it and .bookmark.

 

[chrisjames25]

Ps it appears i need to spread more love around the community beofre i can add rep to you guys as you have helped me so much lately