search?

[AC-Dev]

i am copying that into my code and attempting to make the adjustments. thank you both for your help so far.

 

[AC-Dev]

So, for all of those kind enough to continue on my journey of success, i have another problem for you.

I took the code and configured it to fit my needs and came up with the following:

Private Sub btnSearch2_Click()

clearResults

DoCmd.Hourglass True

Dim myDb As DAO.Database
Dim i As Integer, j As Integer
Dim myTable As DAO.TableDef
Dim tableName As String
Dim booRstSch As Boolean
Dim numFields As Integer
Dim rstSearchTable As Recordset
Dim wherestr As String
Dim myfield As Field
Dim fldstr As String
Dim fldType
Dim blankpos

Set myDb = CurrentDb ' wrkJet.OpenDatabase(selectedFile, , True)

' Loop through all tables extracting the names

For i = 0 To myDb.TableDefs.Count - 1
  Set myTable = myDb.TableDefs(i)

  tableName = myTable.Name

  If Left(tableName, 4) <> "MSys" And Left(tableName, 4) <> "usys" _
   And Left(tableName, 1) <> "~" Then
   
   '     Now find the text fields
 
       booRstSch = True
       numFields = myTable.Fields.Count
       
       Set rstSearchTable = myDb.OpenRecordset( _
        tableName, dbOpenSnapshot)
       wherestr = ""
       For j = 0 To numFields - 1
         Set myfield = myTable.Fields(j)
         fldstr = myfield.Name
         
         fldType = myfield.Type
         If fldType = dbText Then
         
         '         Jet fieldnames can include unusual letters
           blankpos = InStr(1, fldstr, " ") + _
            InStr(1, fldstr, "#") + _
            InStr(1, fldstr, "-") + _
            InStr(1, fldstr, "/")
           If blankpos > 1 Then
 
'            Make sure blank spaces and other odd
'            fieldname characters are handled correctly
             fldstr = "[" & fldstr & "]"
 
           End If
        
           wherestr = wherestr & "(" & fldstr _
            & " like '" & searchString & "'" & ")"

        End If
     Next j

        '      Now search for a string that matches
 
       If Len(wherestr) > 1 Then
         With rstSearchTable
                  
            [COLOR=Red] .FindFirst wherestr[/COLOR]
          
           If .NoMatch Then
            
              sqlfilternot = sqlfilternot & UCase(tableName) _
               & " : Not Found" & vbCrLf & _
               "Select * from " & tableName _
               & " where " & wherestr & ";" _
               & vbCrLf & vbCrLf
            '  GoTo nextTable

          Else
           sqlFilter = sqlFilter & UCase(tableName) _
            & " : FOUND" & vbCrLf & "Select * from " _
            & tableName & " where " & wherestr & ";" _
            & vbCrLf & vbCrLf
            End If
 
        End With
       End If
    End If
Next i
    DoCmd.Hourglass False
End Sub

the red line is my eerror which reads:

Run-time error '3077':

Invalid use of '.','!', or '()'. in expression.

within that 'with' loop there is two sets of code, one that writes code in one box if a match is found and one that write the code in another if there is no match. the code runs through my first unsuccessful table and then i get the error.

any ideas?

any ideas?

 

[pbaldy]

Add

Debug.Print wherestr

right before that line and run the code. That will print the finished string to the VBA Immediate window. If you don't spot the problem, post the result here.

 

[AC-Dev]

Add

Debug.Print wherestr

right before that line and run the code. That will print the finished string to the VBA Immediate window. If you don't spot the problem, post the result here.

ok so when running that code the result is:

(Aircraft like 'test')
(Item like 'test')('Section like 'test')

So i checked the second table(where it seems to stop) and i cant find any field with those characters. So i am not sure of the problem.

 

[AC-Dev]

so i just did a little test and added a field into my first table. simple text field and every record is just one random word - no characters other than letters. previously, this table had one field and it searched it no problem.

When i run the code, i get the same error. my thoughts are - this error occurs when the table has more than one field. i am going to attempt to fix that. wish me luck!!

 

[pbaldy]

The first looks okay, but the second doesn't (though personally I'd get rid of the parentheses to avoid confusion). It would need to look like:

Item like 'test' OR Section like 'test'

so you'd need to adjust the code to achieve that result. It occurs to me another problem you may run into are type mismatch errors. If you run the criteria above against a field with a numeric data type, you'll get a mismatch error. Same with a date field. At a glance the code in that link appears to have accounted for that, so perhaps it's a non-issue.

 

[AC-Dev]

It occurs to me another problem you may run into are type mismatch errors. If you run the criteria above against a field with a numeric data type, you'll get a mismatch error. Same with a date field. At a glance the code in that link appears to have accounted for that, so perhaps it's a non-issue.

yes, the explination from the website states that it only searches text fields and excludes all others. attempting to add 'OR' now. standby

 

[AC-Dev]

SUCCESS!!

I have 2 last questions for you if you wouldnt mind. i searched the code to try and find it but cant seem to determine where/what to put...

First off - right now, the code only returns a 'found' statement if the WHOLE of that the person put in is found. ex. user inputs: "test" - the will not return if a value is "testing" or "test this"

basically, i would like to search for the possibility that the value of the record contains more than what the user put in. i have some fields with alot of input and i want the user to be able to put in one word and find a result.

The second is - do you/anyone see a way for the code to return exactly what field is returned? or even to store the recordset in a variable because i would like to show the user how many records are found in each table and then be able to take them to it.

Edit: ORRRRR immediatly append the record to it's coorosponding 'search' table which mirrors the exact name of the table with the inclusion of "_search" (ex "table1" and "table1_search"). that would be perfect

 

[pbaldy]

The first is easy, you just add wildcards so the final result looks like:

Item like '*test*'

I'm not sure offhand how you'd identify the field right off, since you're searching more than one at once. Appending the found records wouldn't be difficult, except for the possibility of different tables having different fields. You couldn't append to a single table without having a way of directing the source fields into the target table. If you're saying you'd have a search table with identical fields for every table, it would be doable. Just create an append query and don't specify the fields, which I think will work if they have identical structures.

INSERT INTO TableName_Search
SELECT * FROM TableName
WHERE...

 

[AC-Dev]

The first is easy, you just add wildcards so the final
INSERT INTO TableName_Search
SELECT * FROM TableName
WHERE...

i have a problem with this statement
everytime i try to implement it anywhere, as soon as i get to the next line to add "select", it gives me the error:

Compile error

Expected: end of statement

am i missing something here? or am i supposed to format it like this:

dim s as string

s = "INSERT INTO tablename_search SELECt * from tablename WHERE wherestr"

DoCmd.RunSQL s

 

[pbaldy]

Sorry, I was just giving the general syntax of an append query. To be used/executed, the string would have to be built much like your filter strings (sqlfilternot & sqlFilter). In other words, quotes, concatenation, line continuation characters, etc.

 

[AC-Dev]

dim sql AS String dim appendTableName AS String

appendTableName = tableName & "_search"

[COLOR=Black]sql = "INSERT INTO appendTableName SELECT * from tablename WHERE wherestr"[/COLOR]  [COLOR=Red]

DoCmd.RunSQL sql[/COLOR]

that is what i have and it is giving me an error which basically says it cannot find the input table or query 'tableName' on the red line. i double and tripple checked and it is spelled correctly from what i can see.

 

[pbaldy]

You need to concatenate in the 3 variables, just as you did in other parts of the code.

 

[AC-Dev]

You need to concatenate in the 3 variables, just as you did in other parts of the code.

Completed - still get the exact same error.

 

[pbaldy]

Since I've already told you how to debug strings like this, what is the result of that? What is the code now?

 

[AC-Dev]

the sql is as follows:

sql = "INSERT INTO appendTableName"
sql = sql & " SELECT * from tableName"
sql = sql & " WHERE wherestr"

the debug.print line results in this:

INSERT INTO appendTableName SELECT * from tableName WHERE wherestr

 

[pbaldy]

Does that look correct to you? Or does it look like you haven't concatenated in the variables?

sql = "INSERT INTO " & appendTableName

 

[AC-Dev]

Does that look correct to you? Or does it look like you haven't concatenated in the variables?

sql = "INSERT INTO " & appendTableName

...can someone pass me the dunce cap please?
thanks Paul (assuming that is your name. if not, can i call you Paul anyway? lol)

 

[pbaldy]

To paraphrase an old joke, you can call me anything but late for dinner (but Paul is really my name).

 

[AC-Dev]

To paraphrase an old joke, you can call me anything but late for dinner (but Paul is really my name).

ahaha good one .

for those still following this epic journey (or who even care), i shall relay the rest of the mission with you.
(which i should be able to do myself)

next, i am going to attempt to disclude (not a word) any of the 'search' tables from this search becuase once the search copies the record to the search table (located directly below) it then searches that table and gives me the error "cannot find table 'example_search_search'. (or create a statement to continue the search if there is no 'search' table when a record is found (dont want people being able to search for user passwords)

then simply count the records in the search tables and output the numbers to the user, who can then select a report to view the records..salutations all

Godspeed