Searching-Two different recordsources

Two forms:
1. frmMain
2. frmResult

frmMain has:
1 ADOC control-name "ADOC1" bound to msAccess called "MyData.mdb" Table called "MyTable"
1 TEXT box bound to "ADOC1" Field from MyTable called "fldNumber"

I have more textboxes but the one stated is involved in this question

2nd Form
Called "frmResult"

Contains three MSFlexgrids


Also Another msAccess mdb called "Storage.mdb"
To a table called "MyStorage" is involved.

MyStorage structure is
All  text fields
1. fldNum
2. fldNam
3. fldDescription
4. fldVendNum
5. fldVendNam


A person types a value into the textbox "fldNumber"(frmMain)
This value is passed to a table in the second mdb called
"Storage.mdb to a table called MyStorage and to the field
If there is a match The MSFlexGrids return the following:

MSFlexgrid1 returns the fields

MSFlexgrid2 returns the field

MSFlexgrid3 returns the fields
All from the "MyStorage" table

Things I'am pondering:
1.Bind the MSFlexgrids to an ADOC control
2. I cannot add a seperate field to type the search string because this would be redundant.
3. I have used  the standard combobox search to  MSFlexgrid type of routine, but I know * searching will be likely involved.
4. Add an ADOC control but do not bind to MSFlexgrids.

Any help with code would be appreciated.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
Hi fordraiders, it is 2:13 am and I am still in the office, so thought I would have a stab at this one for you.

If your flexgrids in the frmResult form are unbound then it really doesn't matter how you get to it. I would probably suggest the following.

In the frmMain.fldNumber textbox change event script, call a procedure in the second form (frmResult)


Private Sub fldNumber_Change()
  Load frmResult
  frmResult.PopulateGrids fldNumber.text
end sub

In the second form put something like:

Public Sub PopulateGrids(strFldNum as String)
    Dim cnnAccess As New ADODB.Connection
    Dim rstStorage As New ADODB.Recordset
    cnnAccess.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Storage.mdb;Persist Security Info=False"
    rstStorage.Open "SELECT * FROM Storage WHERE fldNum Like '*" & strFldNum & "*'", cnnAccess, adOpenDynamic, adLockOptimistic

    'now populate the grids.

    set rstStorage = Nothing
    set cnnAccess = Nothing
End Sub

If you feel like sending this to me to have a look at, I would be happy to!
FordraidersAuthor Commented:
Thanks for the help.
Here is the final answer with the few changes

Public Sub PopulateGrids(strFldNum As String)
    Dim cnnAccess As New ADODB.Connection
    Dim rstStorage As New ADODB.Recordset
    Dim intRecordCounter As Integer
On Error GoTo NoMatchError:
    cnnAccess.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Data\Stor.mdb;Persist Security Info=False"
    rstStorage.Open "SELECT MyStorage.fldNum, MyStorage.fldNam, MyStorage.fldDescription, MyStorage.fldVendNum, MyStorage.fldVendNam FROM MyStorage WHERE ' ' + MyStorage.fldNum + ' ' Like '%" _
    & strFldNum & "%'", cnnAccess, adOpenStatic, adLockReadOnly
         If rstStorage.RecordCount = 0 Then
   MsgBox ("No Records Found")
   Exit Sub
   End If
   'now populate the grids.
    MSFlexGrid1.Redraw = False
    MSFlexGrid2.Redraw = False
    MSFlexGrid3.Redraw = False
    intRecordCounter = 1
    With rstStorage
            MSFlexGrid1.Rows = intRecordCounter + 1
            MSFlexGrid2.Rows = intRecordCounter + 1
            MSFlexGrid3.Rows = intRecordCounter + 1
            MSFlexGrid1.TextMatrix(intRecordCounter, 0) = rstStorage!Fldnum
            MSFlexGrid1.TextMatrix(intRecordCounter, 1) = rstStorage!fldnam
            MSFlexGrid2.TextMatrix(intRecordCounter, 0) = rstStorage!fldDescription
            MSFlexGrid3.TextMatrix(intRecordCounter, 0) = rstStorage!FldVendnum
            MSFlexGrid3.TextMatrix(intRecordCounter, 1) = rstStorage!FldVendnam
            ' added by dave
            intRecordCounter = intRecordCounter + 1 ' new line added for complete looping
        Loop Until .EOF
    End With

    MSFlexGrid1.Redraw = True
    MSFlexGrid2.Redraw = True
    MSFlexGrid3.Redraw = True
    Set rstStorage = Nothing
    Set cnnAccess = Nothing
Exit Sub

MsgBox "ERROR #" & Str$(Err.Number) & _
         " AT LINE " & Str$(Erl) & _
         " - " & Err.Description & " - REPORTED BY " & Err.Source
    MSFlexGrid1.Redraw = True
    MSFlexGrid2.Redraw = True
    MSFlexGrid3.Redraw = True
Resume Next
End Sub

Private Sub cmdSearch_Click()
 Load frmResult
    frmResult.PopulateGrids fldNumber.Text
End Sub

TimCotteeHead of Software ServicesCommented:
Glad to be of help! Thanks for posting the code, I have been reminded once today for not always pasting the final version of code especially when some of the discussion takes place off-line.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Get ready for another biggie question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.