By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,218 Members | 1,326 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,218 IT Pros & Developers. It's quick & easy.

Search Datasheet form

P: 67
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
Apr 23 '09 #1
Share this Question
Share on Google+
16 Replies


NeoPa
Expert Mod 15k+
P: 31,707
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).
Apr 23 '09 #2

P: 67
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.
Apr 23 '09 #3

NeoPa
Expert Mod 15k+
P: 31,707
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.
Apr 23 '09 #4

P: 67
Yes i did try DoCmd.FindRecord but i think my code was totally wrong.

regards
Apr 23 '09 #5

NeoPa
Expert Mod 15k+
P: 31,707
I think we probably need to see your code then Wayne.
Apr 23 '09 #6

P: 67
Here is the last code i tried

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2.  
  3. If Me.txtSearch = "" Or IsNull(Me.txtSearch) Then
  4.     MsgBox "You Must Enter Something To Search For !"
  5. ElseIf Forms!frmItems!frmItems_Sub!Item = Me.txtSearch Then
  6.     DoCmd.FindRecord ("Forms!frmItems!frmItems_Sub!Item = Me.txtSearch")
  7. End If
  8. End Sub
Apr 23 '09 #7

NeoPa
Expert Mod 15k+
P: 31,707
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 :
Expand|Select|Wrap|Line Numbers
  1. 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?
Apr 23 '09 #8

NeoPa
Expert Mod 15k+
P: 31,707
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
  1. Call DoCmd.FindRecord(FindWhat:='Something', _
  2.                       Search:=acSearchAll, _
  3.                       OnlyCurrentField:=acCurrent, _
  4.                       FindFirst:=True)
Apr 23 '09 #9

P: 67
Hi NeoPa

The btnSearch Is on the main form frmItems.
Apr 24 '09 #10

NeoPa
Expert Mod 15k+
P: 31,707
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.
Apr 24 '09 #11

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

Regards
Wayne
Apr 24 '09 #12

NeoPa
Expert Mod 15k+
P: 31,707
8-)

Let us know how you get on.
Apr 24 '09 #13

P: 67
Hi NeoPa

I works with the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. If IsNull(Forms![frmItems]![txtSearch]) Or Forms![frmItems]![txtSearch] = "" Then
  3.     MsgBox "You Have Not Typed Anything To Search For"
  4. Else
  5.     Forms!frmItems!frmItems_Sub.SetFocus
  6.     DoCmd.GoToControl ("Item")
  7.     DoCmd.FindRecord Me!txtSearch
  8. End If
  9. End Sub
  10.  
Apr 24 '09 #14

NeoPa
Expert Mod 15k+
P: 31,707
Thanks for posting Wayne, and well done :)

May I suggest some minor changes that may help :
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. With Me
  3.     If IsNull(.txtSearch) Then
  4.         MsgBox "You Have Not Typed Anything To Search For"
  5.     Else
  6.         With .frmItems_Sub
  7.             Call .SetFocus
  8.             Call DoCmd.GoToControl("Item")
  9.             'May be possible to replace with :
  10.             'Call .Item.SetFocus
  11.         End With
  12.         Call DoCmd.FindRecord(.txtSearch)
  13.     End If
  14. End With
  15. 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.
Apr 24 '09 #15

P: 67
Again, thankyou for all your help.

Kindest Regards
Wayne
Apr 24 '09 #16

NeoPa
Expert Mod 15k+
P: 31,707
@lovelydan
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.
Apr 28 '09 #17

Post your reply

Sign in to post your reply or Sign up for a free account.