Referencing a specific table field in a VBA routine

[KevinWilcox]

If the code is all on line, you can omit the End If.

Yep, coming back to me now. I think my habit was to have it on new lines so (ironically) I could see it clearly closed when indented. Leastways, looking back at some evry old work that's what I seem to have done.

 

[theDBguy]

Good morning. I went to bed last night and woke up this morning and everything was fixed.

Glad to hear you got it sorted out. Good luck with your project.

 

[KevinWilcox]

Well, almost but not quite! It kind of works, but appears to only return the first match found in each table. Any suggestions?

 

[KevinWilcox]

.... but, having reflected on all that I've read around this over the last couple of days, I think the need for this is a product of flawed db design on my part and I'll look at making life easier by getting all of those long text fields into one table, with type categorisation to drive subforms.

 

[June7]

Did you do the code fixes described in post 12?

 

[KevinWilcox]

Did you do the code fixes described in post 12?

I didn't, actually, as while I understand the movefirst/movelast logic, I can't figure out where they go, and there's a movefirst and movenext in the function which I figured were meant to do just that. So if you could tell me which line it needs to go after, I'd appreciate that.

I'm passing the first columns value to an sql statement string (and adding each subsequent value to the same) which will become the recordset for a form. I'm not too worried about this becoming overly long right now, searching all the text fields is a luxury item and users can use a set of filters as well, so I'd just add an error routine or cap the return to something like 20 records.

If I'm able to get this working I'll live with the fact that I could have done it better by design!

 

[June7]

As stated, I put them right after the line that sets/opens recordset: Set rs = db.OpenRecordset(sql)

 

[KevinWilcox]

As stated, I put them right after the line that sets/opens recordset: Set rs = db.OpenRecordset(sql)

Becoming blind as well as stupid. Though this isn't working at this end, returns that there are no records.

 

[June7]

Worked for me.

So, ready to provide your db for analysis?

 

[KevinWilcox]

More than ready, if you don't mind doing so. Just don't give me a hard time on how scrappy or badly designed it is. Originally I set out to do a codeless, very simple db on the fly to track my team's projects rather than do monthly reports in Word, which I expected to do in a one evening. But the functionality needed means it's grown 'organically' and I haven't given much attention to tidying up, especially on the code side. Nor set the fundamental relationships. I know, not good.

I've been using frm_Search to test this code, putting 'aud' in the text box. That should return several matches in, I think, each of 4 tables. You'll see I've modified the search sub to get the sql and, more slightly, the function this evening.

 

[June7]

Makes no sense to apply text criteria to number field. Try that in a query object and will get "data type mismatch" error. So these SQL fail in VBA opening recordset. Need to exclude all non-text fields from the SQL construct. Consider:

    For Each fld In tdf.Fields
        If fld.Type = 10 Then
            sql = sql & "[" & fld.Name & "] like '*" & _
            criteria & "*' or "
        End If
    Next

One table does not have text fields and this causes run-time error when activating the MoveLast and MoveFirst lines. There were several other issues. Here is functional code:

For Each tdf In db.TableDefs
    doInclude = (Not CBool(tdf.Attributes And dbSystemObject)) And _
                (Not CBool(tdf.Attributes And dbHiddenObject))
    If (doInclude) Then
        sql = ""
        For Each fld In tdf.Fields
            If fld.Type = 10 Then
                sql = sql & "[" & fld.Name & "] like '*" & criteria & "*' or "
            End If
        Next
        If sql <> "" Then
            sql = "select * from [" & tdf.Name & "] where " & Left$(sql, Len(sql) - 3)
            Debug.Print sql
            Set rs = db.OpenRecordset(sql)
            If Not rs.EOF Then
                Set item = New SearchResults
                item.Tablename = tdf.Name
                rs.MoveFirst
                ReDim items(0 To rs.Fields.Count - 1)
                Do While Not rs.EOF
                    For j = 0 To rs.Fields.Count - 1
                        items(j) = rs.Fields(j).Value & vbNullString
                    Next
                    item.ResultRows.Add items
                    rs.MoveNext
                Loop
                For j = 0 To rs.Fields.Count - 1
                    item.ColumnNames.Add rs.Fields(j).Name
                Next
                results.Add item:=item, Key:=tdf.Name
            End If
            rs.Close
        End If
    End If
Next
Set SearchAllTables = results

exit_Search:
    Set tdf = Nothing
    Set fld = Nothing
    Set rs = Nothing
    Set db = Nothing
    Exit Function
errhandler:
    With Err
    MsgBox "Error: " & .Number & vbCrLf & _
            .Description, vbOKOnly Or vbCritical, "SearchAllTables"
    End With
    Resume exit_Search

modSamplecode has invalid uses of Me keyword and unknown function EnableControls() causing compile errors.

 

[KevinWilcox]

I cannot thank you enough. I need to tweak the concatenation of the sql, which is within my abilities, but it now works just as intended. I've removed modsamplecode, I've been recycling a lot from a db I wrote about 15 years ago and digging as deep as I can to try to remember stuff I learned back then, as a lot of it is gobbledygook to me now. I had clocked, on this round, a thread around specifying text only fields though it hadn't (and probably wouldn't have) occurred to me that the non-text table would cause a failure.

What is wrong with my use of me.? What's the better, proper way? I use that keyword a lot. As mentioned, I mostly borrow code from wherever I can find it and I'll almost certainly have picked up that methodology that way, found it worked so stuck with it.

 

[June7]

Me is only valid behind forms and reports. It is shorthand for referencing the object code is behind. Code like Me.textboxname in a general module makes no sense as that module does not have textbox. If referencing form/report from a general module, use collection reference, like: Forms!myformname.textboxname.

If you want the reference to be dynamic, there are several ways to do that.