asked on

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

TimCottee

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!

Fordraiders

ASKER

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

ASKER CERTIFIED SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

Thanks,
again.
Get ready for another biggie question.
fordraiders