Option Compare Database
Option Explicit

    Public varMatID As Variant
    
    Public varCat As Variant
    Public lCat As Integer
    
Sub ConvertMaterial()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rstout As New ADODB.Recordset
        
    Dim rstPrice As New ADODB.Recordset
        
    Dim strSQL As String
    
    varMatID = ""
    
    varCat = ""
    lCat = 0
        
    strSQL = ""
    
    
   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=d:\Documents and Settings\My Documents\AAA - Clients\Conversion DBs\Material Conversion db.mdb;"

   ' Open the feed table - Material_Master_List recordset
   rst.Open "SELECT * FROM Master_Material_List", _
      cnn, adOpenForwardOnly, adLockReadOnly
      
   ' Open the output table - tblMaterial recordset
   rstout.Open "SELECT * FROM tblMaterial", _
      cnn, adOpenKeyset, adLockOptimistic
    
   ' Open the output table - tblMatPrice recordset
   rstPrice.Open "SELECT * FROM tblMatPrice", _
      cnn, adOpenKeyset, adLockOptimistic
    
    ' Loop through the feed table and write output records
    Do Until rst.EOF
        If Not IsNull(rst!MaterialCategory) Then
            ' WRITE A RECORD
            
'=========================================================================
' CHECK AND ADD CATEGORY to tlkpCategory
Category:
            varCat = 0
            varCat = DLookup("[PKCat]", "tlkpCategory", _
                    "[CatCategory] = '" & rst!MaterialCategory & "'")
            
            If Not IsNull(varCat) Then
                lCat = varCat
                ' Category is OK, check rest of record
            Else
                DoCmd.SetWarnings False
                strSQL = "Insert INTO tlkpCategory (CatCategory) " _
                    & "VALUES ('" & rst!MaterialCategory & "');"
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
                GoTo Category
            End If
'=========================================================================
        
' CHECK AND ADD PRODUCT to tblMaterial
            
            varProduct = ""
            varProdBid = ""
            
            Select Case (lCat)
                Case 2, 3, 4, 5, 6
                    varProduct = rst!Product
                Case Else
                    varProduct = ""
            End Select
            
            varProdBid = rst!Product
            
'=========================================================================
        
        End If
      
        rstout.AddNew
        rstout!MatCategory = varCat
        rstout.Update
  
        varMatID = rstout!PKMat

'=========================================================================
' CHECK AND ADD PRICE and PRODCODE to tblMatPrice

            varPrice = 0
            varCode = ""
            
            If (((IsNull(rst!Price)) Or (rst!Price = "") Or (rst!Price = 0)) And _
                ((IsNull(rst!partnumber)) Or (rst!partnumber = ""))) Then
                    ' nothing to add - no Price or ProdCode
                Else
                    varPrice = rst!Price
                    
                    If (IsNull(rst!partnumber)) Then
                        varCode = ""
                    Else
                        varCode = rst!partnumber
                    End If
                    
                    rstPrice.AddNew
                    rstPrice!MatID = varMatID
                    rstPrice!SupplierID = 1
                    rstPrice!MatPriceEffDate = #10/1/2005#
                    rstPrice!MatPrice = varPrice
                    rstPrice!MatProdCode = varCode
                    rstPrice.Update
            End If
'=========================================================================
    
        rst.MoveNext
    
    Loop

    ' Close the recordset
    rst.Close
    rstout.Close
    rstPrice.Close
    
    Debug.Print "Finished"
    
End Sub 'ConvertMaterial
'