Linking button and text field?

[sgtblitz]

Hi guys, hows it going?

Basically, I made an initial post in the general category and you can see it from here to get a little background:

http://www.access-programmers.co.uk/forums/showthread.php?t=144955

I have this code from "Guus2005" (thanks ever so much boss) that I altered for my DB, and it goes like this:


Private Sub Command0_Click()
Dim strSql As String
Dim dbs As Database
Dim rst As DAO.Recordset

strSql = "Select * from tbl_Products where [Product Name] = '" & strProductname & "' or [ISBN] = '" & strISBN & "' or [Author/Composer] = '" & strAuthor & "'"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSql)

If rst.RecordCount = 0 Then
MsgBox "Selection unsuccesfull"
Else
MsgBox "Found!"
End If
End Sub

I have assigned it to a button in a form and from clicking it it seems like its working, my only problem is that, upon clicking it I want it to use the value put inside the text box which is also in the form as the criteria for the search.

Could anyone help me on it at all please?

Thanks

Daniel

 

[KenHigg]

Have you considered using dcount()?

 

[sgtblitz]

oh crud, I forgot to mention, I do not know VB at all, I can just figure out a few things just using logic, so KenHigg, i would not know how to use dcount() unless maybe it was put in some code for me to look at. I would have tried to find out myself, but i have 2 weeks to do a huge report for school, so i have to make sure i finish it quick.

Thanks in advance btw guys

 

[George Too]

sgtblitz,
If I read this correctly, in your form you must have text boxes for each search criteria: Product name, ISBN, and author. Dim your variables strProductname, strISBN, strAuthor as strings and then set your variables = to the corresponding text boxes in your form eg strProductname = me.txtProductName, etc.

The SQL will look something like

strSQL = "SELECT * FROM tbl_Products " & _
"WHERE (((tbl_Products.[Product Name]) Like '" & strProductname & "')) " & _
"AND (( tbl_Products.[ISBN] Like '" & strISBN & "')) " & _
"AND (( tbl_Products.[Author/Composer] Like '" & strAuthor & "');"

You can play with the SQL code to use wild cards to compensate for empty fields then show all etc.

 

[sgtblitz]

To be honest, I would really prefer it to be more like a keytword search in one single text box, so whatever is put in the text box is checked against all 4 fields, kind of like how itunes works ie if the user put in the composer the values with that composer would appear, and if they put the song name in the same text box, all the songs with the same name would appear... All from the same text box.

 

[sgtblitz]

OK guys, ive kind of fixed it, so its searching and its finding the data as it should do. My main thing now is tthat I want to be able to display the query in a listbox or a subform in the SAME FORM. Any ideas please?

 

[George Too]

Create a form with the fields you want displayed and make its default view datasheet (my personal preference). Create a query that is going to serve as the record source for this table. In your main form add the subform control and set as its source object the form you created earlier. Now write code in your search button that instead of displaying the filtered records on screen it will modify (apply your SQL to) the stored query which in turn will update the subform. Just don't forget to requery the subform.