Search Datasheet form

[Wayneyh]

Hi everyone

I have been asked to create a search form for our shop that searches a form in datasheet view.

I have a Form called frmItems which has a subform called frmItems_Sub which has a textbox called Item in it which displays in datasheet view. frmItems has a button called btnSearch which opens a Form in popup view. When the user has entered the search criteria and clicks on the search button it should go to the record in the Forms!frmItems! frmItems_Sub!It em and highlight it.

Hope this makes sense

Regards
Wayne

[NeoPa]

Can you tell us what the fields are in the recordset?

I'm not sure exactly how this would be done, but with proper info I suspect we could find a way (or other experts may know already and jump in of course).

[Wayneyh]

Hi NeoPa

The Recordset contains
ID - AutoNumber
CompanyName - Textbox
ItemCode - Textbox
Item - Textbox
QuantityInStock - Number
txtDelivered - Textbox
txtSale - Textbox

Hope this helps as i am stuck. I tried a few things with the DoCmd.GotoRecor d but couldn't get it to work. Any ideas would be appreciated.

[NeoPa]

Let me start by asking if you've tried using the DoCmd.FindRecor d() method?

If not, then it's probably a simple case of playing around with that. If you have, then maybe I'll have to help you play around with it to find the settings you need. This will be a little more complicated of course.

Let me know where you're at.

[Wayneyh]

Yes i did try DoCmd.FindRecor d but i think my code was totally wrong.

regards

[NeoPa]

I think we probably need to see your code then Wayne.

[Wayneyh]

Here is the last code i tried

Private Sub btnSearch_Click()

If Me.txtSearch = "" Or IsNull(Me.txtSearch) Then
    MsgBox "You Must Enter Something To Search For !"
ElseIf Forms!frmItems!frmItems_Sub!Item = Me.txtSearch Then
    DoCmd.FindRecord ("Forms!frmItems!frmItems_Sub!Item = Me.txtSearch")
End If
End Sub

[NeoPa]

While I'm looking at this I would just draw your attention to another post I made elsewhere. I think it may help you.

It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question.

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :

Option Explicit

To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.

Your line #6 is where it should be happening, but it rather looks like there is not much understanding there (Not a problem. We can help with that).

Can you tell me is [btnSearch] a control on your main form or the subform?

[NeoPa]

Basically, you need to make the form you need to search on, the current form, and the control the current control, then code similar to this would be needed. This is as far as I can take you with the currently available information.

Call DoCmd.FindRecord(FindWhat:='Something', _
                      Search:=acSearchAll, _
                      OnlyCurrentField:=acCurrent, _
                      FindFirst:=True)

[Wayneyh]

Hi NeoPa

The btnSearch Is on the main form frmItems.

[NeoPa]

In that case you would need to switch focus to the subform before setting the control focus to the control you're searching. See post #9 for what to do then.

PS. I don't think there's an easy way of highlighting an individual record on a datasheet (or continuous) form. Anything applied to controls or the detail part of the form, is applied to all instances across the board. Only by changing a bound value could you do something like this (It would mean a field in the record which existed purely to show the highlight on the form). If set, then conditional formatting could be used to draw attention. This is not a great idea practically, as it involves ensuring all the values are set as expected before opening the form, and ensures you cannot use it in any type of multi-user environment.

[Wayneyh]

Thanks for all your help NeoPa. I will mess about and see if i can get it to work.

Regards
Wayne

[NeoPa]

8-)

Let us know how you get on.

[Wayneyh]

Hi NeoPa

I works with the following code:

Private Sub btnSearch_Click()
If IsNull(Forms![frmItems]![txtSearch]) Or Forms![frmItems]![txtSearch] = "" Then
    MsgBox "You Have Not Typed Anything To Search For"
Else
    Forms!frmItems!frmItems_Sub.SetFocus
    DoCmd.GoToControl ("Item")
    DoCmd.FindRecord Me!txtSearch
End If
End Sub

[NeoPa]

Thanks for posting Wayne, and well done :)

May I suggest some minor changes that may help :

Private Sub btnSearch_Click()
With Me
    If IsNull(.txtSearch) Then
        MsgBox "You Have Not Typed Anything To Search For"
    Else
        With .frmItems_Sub
            Call .SetFocus
            Call DoCmd.GoToControl("Item")
            'May be possible to replace with :
            'Call .Item.SetFocus
        End With
        Call DoCmd.FindRecord(.txtSearch)
    End If
End With
End Sub

PS. Using Call is simply a way of saying explicitly that any return value from the procedure (function or subroutine) is ignored. It means that the syntax using parentheses can be used consistently.