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!Item 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.GotoRecord 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.FindRecord() 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.FindRecord 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

Expand|Select|Wrap|Line Numbers

 
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.

Quote:

Originally Posted by NeoPa

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 :

Expand|Select|Wrap|Line Numbers

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.

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 
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 :

Expand|Select|Wrap|Line Numbers

 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.

Wayneyh

Again, thankyou for all your help.

Kindest Regards
Wayne

NeoPa

Quote:

Originally Posted by lovelydan

i want this code to open a subform with only records that matches the critiria but not only pointing to a certain record with the search term. how can i amend it...eg like what happens when you filter a query..

It seems you've already worked out to post your question in its own thread Dan.

I will delete these two posts as they are thread hijacking. No warning this time. You seem to be fixing your problems without help. Further help, if required, can be found in your own thread.

Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

re: Search Datasheet form

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about