using combo box to select a table to search

[london01]

Hello everybody,

what i'm trying to do:
combo box to list the tables.
test box to enter key words to search a field in a record.
search button to search the field using the key words form text box.
combobox "source row":"inv_table";"inv";"cd_table";"cd";"music_table";"music"

form 2 has a list box

dont know anything about access. how do i go about doing the following:

select a table from the combobox.
enter key words in the text field to search a field in a record. after clicking search.. the results are displayed in a list box located in from2.
is this possible?

 

[willir]

London

This is do-able, but once you select the "table", what do you want to use with the information?

The reason I ask is that this will affect the outcome on how to specifically approach the problem. For example, one approach would be to be to use the table defs for the database. Or another approach would be to use a value list.

But then when you have the combo box loaded with what is your next step?

 

[london01]

Hello willir

basically its a knowledge base for myself.
i have music_table and cd_table manually entered in the combobox.copied and pasted the combobox property in earlyer msg. both tables are the same interms of fields that i created so to not to make it too complicated.

text field to search key word in a table on a specific field. i have a field called intro_line so i like to do the search on this field.
hittig the search button would search the intro_line and if it finds any recoreds matching the key word it would display the recored in a listbox located in form2.. i have the interface done if u like to see....send me an email and i'll email it to you..if that helps

 

[willir]

Hmmm

Are you trying to select "music" or "cd", and then display details in the "hits" in a "contineous form" (what you call a list box)?

The first part is not too hard. Since you are using only a few "tables", I suspect a value list for the control source would work best. From your description, you seemed to have worked out this aspect.

The contents displayed in the contineous form is can be controlled by changing the record source.

The real challenge is to search for "hits". I do not know your database design, but I suspect you want to check album titles, song titles, lyrics (if you have included this in your database), bands, band members, writers.

Your select statement would be complicated where you could use the key phrase LIKE.

A simplified example...

The end user enters "Green Fields" in the text search field. We then restrict the search to the "Title"...

SELECT * From Music_Table
WHERE Song_Title LIKE "*Green Fields*"

Returns...
The Green Fields of Earth
Mr. Green Fields

Now add a bit of complexity, and search album and song titles....
SELECT * From Music_Table
WHERE Song_Title LIKE "*Green Fields*" OR Album_Title LIKE "*Green Fields*"

...See where I am going with this. As you add fields to search, the complexity of the SQL statement grows.

...Moving on...

Assumptions:
- Your "table" combo box is called "SelectTable",
- The text box to accept the search sstring is called "SearchString",
- Your subform which is a contineous form is called "DisplayMusicSbFrm"
- I will guess at your field names
- You have a command button called "SearchIT".

Code for the OnClick event for "SearchIT" may look something like...

Dim strSQL as String, strQ as String, strWhere as String
Dim strTXT as String

strQ = CHR$(34)    'double quote character

If Len(Nz(Me.SearchString),"") > 0 Then
   strTXT = Me.SearchString
   If Len(Nz(Me.SelectTable),"") > 0 Then
      strSQL = "Select * from " & Me.SelectTable
      strWhere = "Album_Title Like " & strQ & "*" & strTXT & "*" & strQ
      strWhere = " AND Song_Title Like " & strQ & "*" & strTXT & "*" & strQ
      strSQL = strSQL & " Where " & strSQL

      Me.DisplayMusicSbFrm.RecordSource = strSQL
      Me.DisplayMusicSbFrm.Requery
   End If
End If

Hope this gives some ideas. If you were to include bands, band members, you would have to tack on a LIKE clause for each.

I am curious. You have a Music table and CD table -- what is the difference between these? The fact that the fields are similar suggest they may be a better design to use one table for both instead of splitting the two. Although you would have to add something a field like "MediaType", this approach would simplify your code.

Richard

 

[london01]

Hi Willir,

To make my examples and my description of the problem easy i made up the cd and the music table. basically to make things simple and was gong to transfer the example replies back to my actual code.. i guess it didn't work out the way i figured...

I'm going back to school and taking mysql, php, html, winodws 2000 and maybe touch alittle bit on linux.. since i dont know too much about either of the subject i figured a knowledge base would come in handy to keep the little things that you tend to forget. the very things that u remember doing in school but could never remember...

so i created a table for each subject and my thinking for having a seprate table is that it could get big so i created a seprate table for each. is this a good idea, i'm not sure.




my tables are made up of:
introfield(text), description(memo), sample_example(memo), date(date) fields

introline field(using textbox): to put one line description . this field is used to hold key words to search.
descriptoin field(using textbox): for a description of the subject.
sample_example_code field(text field): for examples/sample code.
date field.. when the record was created.

the code u sent me is abut to complicated. how does the code u sent will send the resutls to the sec form?

 

[willir]

London

Just a thought....

From my perspective, there is nothing wrong with tracking "text" in one table. Consider the following...

SubjectTbl
SubJectCode - primary key (course number or ID or simple description)
SchoolYear
Professor

TopicTbl
TopicCode - primary key

SubjectDetailTbl
SubjectDetailID - primary key (autonumber since it is just you)
SubJectCode - foreign key to Subject table
TopicCode - foreign key to Topic table
KeyWord - text field for key work searches
Details - memo field for notes, only first 255 characters are searchable
LinkDoc - Hyperlink to external document, web page, report

I think this approach would be very powerful for you.

Want to review your notes for a test...
SELECT * FROM SubjectDetailTbl
WHERE SubJectCode = "MUSIC"
AND TopicCode = "HISTORY"
Retreive all information on music history

Want a detail information on Litz - Litz would have been entered into the KeyWord section....
SELECT * FROM SubjectDetailTbl
WHERE KeyWord Like "*Litz*"

Where the heck was that web page on Mozart??
SELECT * FROM SubjectDetailTbl
WHERE KeyWord Like "*Mozart*"

...and then follow the hyperlink to the web page.

Want to find a report you wrote and got an "A" on but can't find it, and can not remember the title...
You can do this too.

Does this sound like a better approach?

Richard