Populate form based on a text box

[teachmecr]

Hi All-

I have a form with in access which is pulling data from Ms Sql thru ODBC connection (a View). What i want is to have a text box as a search field where the user types in a number or part of a number and it will only display the relevant results in the form. I have done this before with a combo box but not with text box. What i have is right now when i open the form it pops up a dialog saying enter parameter value (which is my text box used for searching) then i put in some number and it displays the info on the form. What i want to do is to be able to go directly to the form where i have the text box. i put in the number there and gives me the results and then i should be able to look up for another number. On my form record source i have the following query

SELECT * FROM myview WHERE Num=txtSearch.value or right(Num,5)=txtSearch.value; and the other text boxes where i see the results they all have their control source set to the respective fields(columns) of the view. Any kind of help would be appreciated. Thanks

 

[GingerR]

HI--can multiple rows be returned? If so, I would have a main form (where you'd have a search text box and button to launch the search) and a subform that is in datasheet format which shows the results of the search. The Recordsource for the subform will be all records. User enters in something and hits the button, which sets the filter on the subform. the code says

Me.SubformNameHere.Form.Filter = "Num = " & me.txtSearch & " or Right(Num,5) = " & me.txtSearch
Me.SubformNameHere.Form.FilterOn = True

You can have another button to remove the filter with:

Me.SubformNameHere.Form.Filter = ""

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[teachmecr]

I tried your answer but it doesn't work...first it was giving syntax error when i fixed that..then it just doesn't give anything in the subform..

 

[GingerR]

what code did you use? Please paste it here.
Did you remove the 'enter parameter' thing?
Did you rebuild this to be a main/subform set up?
What are the names of your main and subforms?
What are the recordsources of each?

Need more info in order to help you.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[teachmecr]

main form is frmSearchOptionsTest and subform is TestSubform(its record source in its properties is "SELECT EquipCompanyNum, EquipLease, EquipSerialNum, EquipSeqNum, EquipDescription, CustName, CustDBA, OrigComment, TitleStatus, TitleType, TitleRecdDt, TitleReqDt, NewComment FROM dbo_EquipmentforAccessVw". Main from has the text box txtVinSearch (for searching). then i have a command button with on click event. the command button has the following code.
Private Sub cmdSearch_click()

Me.TestSubform.Form.Filter = "EquipSerialNum =Me.txtVinSearch"
Me.TestSubform.Form.FilterOn = True

i tried this as well

'Me.TestSubform.Form.Filter = "EquipSerialNum = " & Me.txtVinSearch & "

nothing is working

 

[GingerR]

Is EquipSerialNum text? Above is written for a number, plus it's written for exact match, so if TEXT and wildcard:

Me.TestSubform.Form.Filter = "EquipSerialNum Like '*" & Me.txtVinSearch & "*'"
Me.TestSubform.Form.FilterOn = True

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[ProgramError]

http://datapigtechnologies.com/flashfiles/searchform.html

the above may interest you too, but it will take you in a different direction.

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[teachmecr]

k thanks...works now..just one thing though..is it possible for the subform to initially load blank or null..and shows the result only when a search is made. thanks

 

[GingerR]

Instead, make the VISIBLE property of the subform be NO and save it. Then make the first line of code in your button be:

Me.TestSubform.Visible = True

So when you first open it, the subform isn't shown at all. Click the button, and it is.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[skyline666]

Hi teachmecr,

Just as an off note from your original question, you say that the record source for TestsubForm is a SELECT statement. What you could do is create a new stored procedure in SQL (I say in SQL becuase I assume that the db in Access and SQL are linked and are different versions, ie: Access 2003 and SQL 2005, if this is the case you most likely wont be able to make the query in Access itself and save it) that has the SELECT statement in, and then in the record source input the name for whatever you called the stored procedure (you will also need to change the record source type to Table/View/Stored Proc).

I suggest this as if you come to change some of the field names, then it will easier to change in the stored proc, and also you can reuse the stored procedure elsewhere. You can also then run the stored procedure in Access from the query section in the database window to see the results before you use it.

Andrew