Search a sub-form(datasheet view) by keying start of field search arg?

[Paul7905]

I POSTED THIS ON THE FORMS GROUP, NO TAKERS, SO I AM POSTING HERE IN HOPES SOMEONE HAS A SOLUTION


I have a main form that contains a subform. the subform displays inventory items in datasheet view (subform must display the data in datasheet view per user mandate)

Currently, user has to use the scroll bar on the subform to scroll to the inventory description they want so they can do some data entry.

I am looking for a way that will allow the user to key into a field on the main form (or subform) that will do a search on a specific data column in the subform. the field we need to search on is text format (item description).

the user wants to be able to key in an alpha character and have the system do a start of field search and scroll automatically to the first item beginning with the character keyed. Likewise, when another character is keyed, for example, the user keys "AB" the system would cause the datasheet to scroll to the first item having an item desc starting with "AB"

I have VB code that will do this in the keydown event when the searched data is in a "listbox" however I cannot figure out how to do it when the data is being displayed in a subform in datasheed view.

Can anyone tell me if it can be done and provide a sample solution??


thanks !
Paul

 

[grtammi]

Hi Paul!

Try something like this - it works for me ...

1) Create a text box (I'll use txtBox for the name) and a command button (cmdBtn) on the main form.

2) The user enters the search string in the text box and clicks the command button.

3) Code for the OnClick event of the command button :

Private Sub cmdBtn_Click()
  'Error Handling here
  If txtBox = "" Or IsNull(txtBox) Then
    Msgbox "Enter a value to search for first!"
    Exit Sub
  End If

  'passed error check, search for value entered
  DoCmd.FindRecord txtBox, acStart, False, acSearchAll, False, acCurrent, True

End Sub

The above code works in Access97 - should work in any other version of Access (I hope - who knows with the 'Softies?)

If you want to add Find Next capabilities to your form, you can do the following:

1) Add another button to your form and change the Enabled property to No (I'll call the button btnNext for reference).

2) In the above code, underneath the passed error ... comment, add the following line:

    btnNext.Enabled = True

3) The OnClick event for btnNext is as follows:

Private Sub btnNext_Click()

  Docmd.FindNext

End Sub

If you need anything else, I have this thread marked for e-mail notification ...

HTH

Greg

Remember: when you don't understand, just nod and smile ...