How to add control in VB

[kirubagari]

Dear experts,

I need help on add control in VB..Im develping program and it will be read in MDB file..I have to avoid the double qoute in that program if the item description have the double code..For Eg.Laptop computer and if double qoute present on that i have to avoid that and output without the double code.


The second thing is i have control the item length and if the item length more than 40..i have to give some warning message..How i can do on that?
Kindly help me on this..Thanks

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim ProjID As DAO.Recordset
Dim strSQL As String
Dim intErrNo As Integer
Dim updCounter As Integer
Dim updStatus As Boolean
 
 
Function InsertIntoMaster()
    Set db = CurrentDb
 
    intErrNo = 0
 
    strSQL = "SELECT * FROM Project_Counter WHERE Desc_Project='Chinastk'"
    Set ProjID = db.OpenRecordset(strSQL)
 
    strSQL = "SELECT * FROM NewItem WHERE Updated=0"
    Set rs1 = db.OpenRecordset(strSQL)
 
    strSQL = "DELETE * FROM [Outlet Item List]"
    db.Execute strSQL
 
    Do While Not rs1.EOF
        updStatus = True
 
        strSQL = "SELECT * FROM dbo_ChinaStk_ProdCat "
        strSQL = strSQL & "WHERE PCCode='" & rs1("PCCode") & "' "
        Set rs2 = db.OpenRecordset(strSQL)
        If Not rs2.EOF Then
            strHQMfgCode = rs2("PCBarCode") & Format(rs2("BarCodeCounter"), "0000000")
            Set rs2 = Nothing
        Else
            intErrNo = intErrNo + 1
            Set rs2 = Nothing
            GoTo NextRec
        End If
 
        strSQL = "SELECT * FROM dbo_OP_Product_SubCategory "
        strSQL = strSQL & "WHERE Product_Category='" & rs1("PCCode") & "' "
        strSQL = strSQL & "AND Product_CategorySub='" & rs1("PSCCode") & "' "
        Set rs2 = db.OpenRecordset(strSQL)
        If rs2.EOF Then
            intErrNo = intErrNo + 1
            Set rs2 = Nothing
            GoTo NextRec
        End If
        Set rs2 = Nothing
 
        strItemDesc = ""
        For intCount = 1 To Len(rs1("ItemDesc"))
            If Mid(rs1("ItemDesc"), intCount, 1) <> "'" Then
                strItemDesc = strItemDesc & Mid(rs1("ItemDesc"), intCount, 1)
            End If
        Next
 
        strSQL = "INSERT INTO dbo_ChinaStk_Master (PrjID, "
        strSQL = strSQL & "SetNo,PCCode,PBCode,PSCCode,ItemDesc,"
        strSQL = strSQL & "UOM,StdCost,RSP,MthlyExpectSalesQty,CustomCode,"
        strSQL = strSQL & "MfgBarCode,QtyOfUnit,ItemType,MeasurementUnit,UnitCode) "
        strSQL = strSQL & "VALUES("
        strSQL = strSQL & ProjID("Project_Counter") & ",'"
        strSQL = strSQL & rs1("AccSet") & "','"
        strSQL = strSQL & rs1("PCCode") & "','"
        strSQL = strSQL & rs1("PBCode") & "','"
        strSQL = strSQL & rs1("PSCCode") & "','"
        strSQL = strSQL & strItemDesc & "','"
        strSQL = strSQL & IIf(Len(Trim(rs1("Unit"))) = 0 Or IsNull(rs1("Unit")), " ", rs1("Unit")) & "',"
        strSQL = strSQL & rs1("StdCost") & ","
        strSQL = strSQL & rs1("RSP") & ","
        strSQL = strSQL & IIf(Len(Trim(rs1("ExpectedSalesQty"))) = 0 Or IsNull(rs1("ExpectedSalesQty")), 1, rs1("ExpectedSalesQty")) & ",'"
        strSQL = strSQL & IIf(Len(Trim(rs1("CustomCode"))) = 0 Or IsNull(rs1("CustomCode")), " ", rs1("CustomCode")) & "','"
        strSQL = strSQL & strHQMfgCode & "',"
        strSQL = strSQL & rs1("QtyOfUnit") & ",'"
        strSQL = strSQL & rs1("ItemType") & "',"
        strSQL = strSQL & rs1("MeasurementUnit") & ",'"
        strSQL = strSQL & rs1("UnitCode") & "'"
        strSQL = strSQL & ")"
        db.Execute strSQL
 
        strSQL = "UPDATE NewItem SET Updated=1 WHERE NewItemIndex=" & rs1("NewItemIndex")
        db.Execute strSQL
 
        strSQL = "UPDATE dbo_ChinaStk_ProdCat SET BarCodeCounter=BarCodeCounter+1 "
        strSQL = strSQL & "WHERE PCCode='" & rs1("PCCode") & "' "
        db.Execute strSQL
 
        strSQL = "INSERT INTO [Outlet Item List] (HQMfgCode,UnitCost) "
        strSQL = strSQL & "VALUES('" & strHQMfgCode & "',"
        strSQL = strSQL & rs1("StdCost") & ")"
        db.Execute strSQL
NextRec:
        rs1.MoveNext
    Loop
 
    If updStatus Then
        strSQL = "UPDATE Project_Counter SET Project_Counter = Project_Counter+1 WHERE Desc_Project='Chinastk'"
        db.Execute strSQL
    End If
 
 
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set ProjID = Nothing
    Set db = Nothing
 
 
 
    DoCmd.OpenQuery "qrymoveProcessesNewItem"
    'DoCmd.OpenQuery "qryDeleteNewItem"
    If intErrNo > 0 Then
        MsgBox intErrNo & " record(s) cannot be process due to the invalid Product Category or Product Subcategory, please correct it!"
    End If
End Function
 
 
 

[Guido Geurs]

to replace " in string: this replaces: " in text1 with: _

Text2 = Replace(Text1, """", "_")

to check the lenght of a string:

If Len(Text1) > 40 Then MsgBox "To Long"