Button that searches for data in a text field

[supportt]

I know this can't be that hard, but I am slammed for time and need to find a quick way to do it. I have a simple form, that has an image(button)and a text box, that's it. I would like to be able to enter text in the box and onClick of the image (button) it searches my database for that value. The values are unique, only one per record, so if they type a value that is not in the database, there would be no record.

Thanks
David

 

[David R]

DoCmd.OpenReport "ReportName",acViewPreview,,"[table/queryName].[FieldToMatch] = '" & Me.[txtBox] & "'"

Note the single quotes around the text value.

 

[supportt]

Thanks for the info, but I have a question? The code you sent, is that behind the button or Field? I tried it behind the button and it pops up a box to enter the search string. Really want it to do a straight search no pop ups.

Thanks Again

David

 

[David R]

It should be behind the button, however make sure the field names match.

"ReportName" is your report's saved name
"[table/queryName]" is the query or table behind your report
"[FieldToMatch]" is the field name in your table/query
"Me.txtBox" is the text box on this simple form, filled with the value you want to search for.

Is that clearer?

David R

 

[supportt]

Well, I have checked and double checked and can seem to get this working right. If I open the form and type a number in my Text field and hit enter, then run the query directly from the query, it works. But, If just use the form it brings up a blank report with no records? Here is my code I am using, seems to be right. If you could take a quick look.
This is what I have behind my Image(button):
Private Sub Image44_Click()
DoCmd.OpenReport "SIEBEL_S_ORG_EXT_GetValue_SiteID", acViewPreview, , "[SIEBEL_S_ORG_EXT/SIEBEL_S_ORG_EXT_Query].[LOC] = '" & Me.[Text40] & "'"
End Sub

Thanks for the help

David

 

[David R]

Whoops, I think you took me a bit too literally. [SIEBEL_S_ORG_EXT/SIEBEL_S_ORG_EXT_Query].[LOC] should probably just be [SIEBEL_S_ORG_EXT_Query].[LOC]. I put 'table/query Name' because I wasn't sure whether you were basing off of a table or a query. I didn't mean for you to include both, the query includes the table, right?

[This message has been edited by David R (edited 04-26-2002).]

 

[supportt]

Yes, if I look at the query in design mode, the table is in it.....

Thanks

David

 

[supportt]

Ok, I think I might be close....I took out the Query name and only used the table name. The results are close, if I type a number in the text box and hit the enter key and then click my image(button) it works. But, if I don't hit enter after keying in the number (in the text box) it does not work? Maybe you could direct my next questions as well, I also have a image(button) to clear the text box for a new search. Any idea how I could make that happen?

Thanks Again for the help, I really do appreciate it.

David

 

[David R]

If the query contains the table then you should be referencing it, not the table directly. This cuts down on the number of fields Access is trying to read, if possible.

I'm not sure why you would have to hit Enter before you could use your search button. Try putting this line before your DoCmd.OpenReport...
MsgBox Me.Text40 'this is just for debugging

See what value it gives you when you click your button, that may help you track down what's happening.

To clear your text box after you run the report, put this after the DoCmd.OpenReport (otherwise you'll blank your value before you can search with it!)
Me.Text40 = ""

HTH
David R

 

[supportt]

The clear function works great, thanks. When I added the pop up message box to my code, really all it brought up was the number I typed in to search for. But, still have to hit my enter button to make it work, go figure.

David

 

[David R]

If the MsgBox brings up the value you want to search for them it means the value is there to be searched for...if that makes sense. Putting that line it let me check whether the data was there for the button to use. I don't know why you'd have to hit Enter first. What code are you using now, if it's not too much trouble? Copy everything from the form, if it's relatively short.

[This message has been edited by David R (edited 04-26-2002).]

 

[supportt]

This is what I am using now, code wise:
Private Sub Image42_Click()
Me.Text40 = ""
End Sub

Private Sub Image44_Click()
DoCmd.OpenReport "SIEBEL_S_ORG_EXT_GetValue_SiteID", acViewPreview, , "[SIEBEL_S_ORG_EXT].[LOC] = '" & Me.[Text40] & "'"
End Sub

And it is not a problem, I am just glad to get someone to help me with Access. I am very new to it.....but, find myself using it more and more for different things

 

[David R]

First idea: Does the code module start
Option Compare Database
Option Explicit

If not, see if that solves it.

Next idea: Try putting a breakpoint into your code right on the DoCmd.OpenReport line. Then see when Access actually gets to that line of the code. I don't see anything wrong with what you posted off of the top of my head.

So what's happening now is you open the form, enter a date, then click the image (button) and...nothing happens? Then if you go back into that text field, hit Enter, and then click the button, it works??

[This message has been edited by David R (edited 04-29-2002).]