Searching-Two different recordsources

Setup:
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

MSFlexgid1-Unbound
MSFlexgrid2-Unbound
MSFlexgrid3-Unbound


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


WHAT I NEED TO HAPPEN:

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
"fldNum"
If there is a match The MSFlexGrids return the following:

MSFlexgrid1 returns the fields
fldNum
fldNam

MSFlexgrid2 returns the field
fldDescription

MSFlexgrid3 returns the fields
fldVendNum
fldVendNam
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.
fordraiders







 
LVL 3
FordraidersAsked:
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)

e.g.,

Private Sub fldNumber_Change()
  Load frmResult
  frmResult.PopulateGrids fldNumber.text
  frmResult.Show
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"
    cnnAccess.Open
    rstStorage.Open "SELECT * FROM Storage WHERE fldNum Like '*" & strFldNum & "*'", cnnAccess, adOpenDynamic, adLockOptimistic

    'now populate the grids.

    rstStorage.Close
    cnnAccess.Close
    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!
0
FordraidersAuthor Commented:
Tim,
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"
    cnnAccess.Open
    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
        .MoveFirst
        Do
            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
            .MoveNext
        Loop Until .EOF
    End With

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

NoMatchError:
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
    frmResult.Show
End Sub


fordraiders
0
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.
0

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:
Thanks,
again.
Get ready for another biggie question.
fordraiders
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.