> vb.net 2003
> access 2003
> excel 2003
>
> If 150 records exist in tblData, there will 150 results in Excel...
> It will loop starting at row(record) 1 in tblData and loop to record
> 150...
>
> So actually the sql for tblData could just read:
> dbCommand = New Data.OleDb.OleDbCommand("SELECT fldWwg FROM tblData",
> fldWwg (150 records exist in tblData)
> 2YY34A
> 1A324
> 1A667
> 1ASD3
> 2YY35A
> 2ARR5
> 2YY56
> etc...
>
> When a match is found in tblCoreSkuinformation...The following is
> returned to excel.
> "fldWwg" from tblData and
> "ITEM", "WWGDESC"  FROM tblCoreSkuInformation(Lets say there are 200
> records here)
>
> "ITEM" is the primary key here SO ONLY ONE RECORD WILL EXIST(ONE TO
> ONE)
>
> 2YY34 HITS ON 2YY34A Stops loop at record  143  ,,, results
> returned  : Loop Ends move to NEXT record in tblData
> 1a324
> '==================================================
> fldWwg    Item             WWGDESC
> 2YY34A    2YY34           WIDGET, ALL KINDS
> 1A324     1A324            WIDGET BASKETBALL
> 1A667     NOT FOUND    <---- THIS DID NOT RETURN DATA FROM
> tblCoreSkuinformation so post  "NOT FOUND"
> 1ASD3     1ASD3           GRAPES, SUNNY VALLEY
> 2YY35A    2YY35           SCOOTER, 2 WHEELS
> 2ARR5     NO FOUND              <---- THIS DID NOT RETURN DATA FROM
> tblCoreSkuinformation   "NOT FOUND"
> 2YY56     2YY56            TENNIS BALL, DIRT COURT
> etc...for 150 returns
>
> Question: when "2YY34A" is taken to match "2YY34".  Are we saving the
> former, or the latter?
> Answer :"Both"
> Question: And are we saving any of the other fields from whichever of
> the tables that version comes?
> Answer:  Yes  as stated above...
> Question: And, having found a 'match' for "2YY34A" once, do we then
> have to continue looking through all the rest of the records in the
> other table to see if there are any more matches?
>
> Stuck on the looping part?
> CAN'T PROPERLY LOOP THROUGH  TBLDATA  TABLE?
>
> Private Sub FirstSearch()
>         Dim dbConnection As Data.OleDb.OleDbConnection
>         Dim dbCommand As Data.OleDb.OleDbCommand
>         Dim dbDataAdapter As Data.OleDb.OleDbDataAdapter
>         Dim dbCommandBuilder As Data.OleDb.OleDbCommandBuilder
>         Dim dbDataSet As Data.DataSet
>         Dim dbCommand2 As Data.OleDb.OleDbCommand
>         Dim dbDataAdapter2 As Data.OleDb.OleDbDataAdapter
>         Dim dbCommandBuilder2 As Data.OleDb.OleDbCommandBuilder
>         Dim dbDataSet2 As Data.DataSet
>
>         dbConnection = New
> Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\DM2007\SkuCat.mdb;User Id=admin;Password=;")
>         dbConnection.Open()
>         dbCommand = New Data.OleDb.OleDbCommand("SELECT fldWwg,
> fldMfgname, fldMfrnum, fldDescription FROM tblData", dbConnection)
>         dbCommand2 = New Data.OleDb.OleDbCommand("SELECT ITEM,
> WWGDESC  FROM tblCoreSkuInformation", dbConnection)
>
>         dbDataAdapter = New Data.OleDb.OleDbDataAdapter(dbCommand)
>         dbCommandBuilder = New
> Data.OleDb.OleDbCommandBuilder(dbDataAdapter)
>         dbDataSet = New Data.DataSet
>         dbDataAdapter2 = New Data.OleDb.OleDbDataAdapter(dbCommand2)
>         dbCommandBuilder2 = New
> Data.OleDb.OleDbCommandBuilder(dbDataAdapter2)
>         dbDataSet2 = New Data.DataSet
>
>         dbDataAdapter.Fill(dbDataSet, "tblData")
>         dbDataAdapter2.Fill(dbDataSet2, "tblCoreSkuInformation")
>         With dbDataSet.Tables("tblData")
>             For Each dbRow As Data.DataRow In
> dbDataSet.Tables("tblData").Rows
>                 Dim input As String = dbRow.Item("fldWwg")
>                 Dim pattern As String = "(.*?)(\d[a-zA-Z]{1}\w\w\d[a-
> zA-Z]?)(.*)"
>                 Dim mc As MatchCollection = Regex.Matches(input,
> pattern)
>                 Dim output As String = ""
>                 Dim trimStr() As Char = {" "}
>                 ' at some point in the code below, I need to capture
> the array of Match/nonmatch items
>                 'then present these items in an excel sheet.
>                 ' The looping below is not correct..it appears to
> searching and finding ok.. but is
>                 ' not looping the tblData table correctly
>                     For Each m As Match In mc
>                         output = m.Groups(2).Value
>                     For Each dbRow2 As Data.DataRow In
> dbDataSet2.Tables("tblCoreSkuInformation").Rows
>                         Dim strItem As String
>                         strItem = dbRow2.Item(0)
>                         'dbRow2.Item("ITEM") = output.TrimEnd(trimStr)
>                         If dbRow2.Item("ITEM") =
> output.TrimEnd(trimStr) Then
>                             MsgBox("SUCCESS")
>                         Else        '
> MessageBox.Show(dbRow.Item("fldwwg"))
>                             ' MsgBox("NOMATCH")
>                         End If
>                     Next
>                 Next
>             Next
>             '     dbDataAdapter.Update(dbDataSet, "tblData")
>         End With
>
>         dbDataAdapter.Update(dbDataSet, "tblData")