Kindly find the attachment 17dpo.zip
thatsort issue is resolved now i want when user should clickon grid1only item_code, product_name, unit should come and cursor should
be at quantity column so that user put quantity on that cell for material requisition.

kindly find 17dpo.zip

FlexGrid is not editable Grid. you cannot edit MSFlexgrid/MSHFlexGrid directly. u have to use another TextBox control to display covering that cell and after data is input and validated in that text box, it can be updated to the Grid.

Instead of using 2 grids, u can use a DataGrid which can Edit, Delete, Update rows dynamically without the need of any extra control.

U actually want to add a list of items with their quantities specified for the MR. Why do you want to take 1 grid, populate it with all items, then select one by one and populate them to another grid. a very tedious task.

Instead if the Grid is initially empty with only headings and an empty row say, when you enter the first column all Product Codes are dropped down and you select the required product. As soon as ur product is selected, remaining 2 columns are populated with the relevant data of the product and the cursor is moved to the quantity column. once some qty is entered you can add a new row now and move the cursor to the first column of the 2nd row... and so on...

The cursor won't move to the next row if a duplicate Item is selected/ if the qty is = zero.

Once all the items are entered, save them using the save button. For the purpose of which u may use the DataGrid. Making Editable MSFlexGrid/MSHFlexGrid requires much extra programming.

If u could give me a day's time, i can upload the modified form for u. I need some to modify the form suiting ur requirement.

FlexGrid is not editable Grid. you cannot edit MSFlexgrid/MSHFlexGrid directly. u have to use another TextBox control to display covering that cell and after data is input and validated in that text box, it can be updated to the Grid.

Instead of using 2 grids, u can use a DataGrid which can Edit, Delete, Update rows dynamically without the need of any extra control.

U actually want to add a list of items with their quantities specified for the MR. Why do you want to take 1 grid, populate it with all items, then select one by one and populate them to another grid. a very tedious task.

Instead if the Grid is initially empty with only headings and an empty row say, when you enter the first column all Product Codes are dropped down and you select the required product. As soon as ur product is selected, remaining 2 columns are populated with the relevant data of the product and the cursor is moved to the quantity column. once some qty is entered you can add a new row now and move the cursor to the first column of the 2nd row... and so on...

The cursor won't move to the next row if a duplicate Item is selected/ if the qty is = zero.

Once all the items are entered, save them using the save button. For the purpose of which u may use the DataGrid. Making Editable MSFlexGrid/MSHFlexGrid requires much extra programming.

If u could give me a day's time, i can upload the modified form for u. I need some to modify the form suiting ur requirement.

Akhtar sir ,
i am working in saudi arabia since it is a manufactering company
here financial year starts from january not from april ,in this month
means december 17 to december 31 i will have to complete at least
Material requistion form as well as purchase form . since it is having
seven department manufactering, engineering,purchase dept,sales dept ... all dept require Mr form in this month i will have to complete
at least MR Form and Purchase form as well as all concern report using Crystal report .

if you help me i will remain grateful to you.

can you give updated form for material requisiton upto tomorrow evening.

Here it is 6:00 PM. I can work it out and give it 2 u in another 3-4 hrs.
By the way what is the time there now?

Hi! I worked through the solution for nearly 5 hours. At last arrived at some sort of solution. i hope for the best that it will help u. I am uploading the ZIP File here.

NOTES ON USING THE SENT FILES.
-------------------------------------------
Unzip this file some where else other than your project. and Before overwriting any of your work with the given logic, make a backup of ur project.

I am sending a total of 8 files (3 Modules, 1 class module, 2 user controls of 3files and the MR form u have given)

once u have unzipped them to a location and having ur current project backup.

1. Open your CURRENT project with which u r working. (don't open "FINALMR.VBP" project which is included in the zip file i have sent you.)

2. From the Project->Components menu make sure u have these 2 controls are selected. a) Microsoft Hierarchical Flex grid Control (MSHFLXGD.OCX) and b) Microsoft Windows Common Controls-2 6.0 (MSCOMCT2.OCX)

3. Project -> Add Module and add the three modules i have sent one by one. (ADOSUPPORT.BAS, MODERROR.BAS & MODGLOBALVARS.BAS)

4. Project -> Add Class Module and add the PRODUCTS.CLS

5. Project -> Add User Control and add the "uscEntityCode.ctl" first. (IMPORTANT)

6. Then Project -> Add User Control and add the "uscMSFlexGridEditor.ctl"

7. Lastly Replace your MR form with the one which i have modified.

8. Run Check ur program and get back if any errors coming up or if u r unable to add these files to your project.

sorry i could not upload the files yesterday. you might have waited hoping for my reply. sorry for the inconvenience. Actual problem was that my internet connection was down from yesterday night till today morning. that was why i was unable to upload the solution. I had the code written yesterday night only by 10PM. (may be 7pm at ur side)

when i click on product_id got error method or datamember not found when i ok on message box it go on Grd_LeaveCell() and
cursor is highlighted upon Grd = FlexGridEditor.Value

Firstly, did u get any error msgs while adding those files to the project.

Then open Class Module Products.cls. Check out the Tablename and FieldNames which i have given are correct or not.

If possible plz post database if u can. it will be very easy to solve if i can see the database.

The error u got is related to fieldname of PARTDETAIL i think. since i exactly don't know the fieldname spellings, maybe i might have spelled them wrongly.

Also please note down the entire error message and post it here along with the Module Name, Procedure Name, Error Number and Description.

Sir ,
I Did not get any error message while adding all mentioned file Secodly I did upload productaable_test along with .doc attach.

Kindly find the uploaded file.

>>most urgent

Maybe for you, but not for us. Describe the problem you have because we are not interested in just looking at your code and try to read your mind.

And next time try to be a little more creative in the title of your threads.

[edit]Merged your two threads. Don't start new threads for the same topic.

Sir ,

I already send Product_tabletest.zip and a doc
kindly find the uploaded file

Sir,
I Did not get any error message while adding all mentioned file Secodly I did upload productaable_test along with .doc attach.

Edited 3 Years Ago by happygeek: fixed formatting

'In MR Form Change the FieldName from UNIT  to  MaxOFUnit
'I already adviced u to have a look at the fieldnames which i have given
'In ur PARTDETAIL Query you have given 
'MaxOfUnit as the Output Field Name

Private Sub SetProductValues()

    Dim rs As New ADODB.Recordset

    On Error GoTo SetProductValues_Error

    rs.Open " SELECT ITEM_CODE, PRODUCTNAME, MAXOFUNIT FROM PARTDETAIL WHERE ITEM_CODE = '" & Grd.TextMatrix(Grd.Row, cItemCode) & "' ", con, adOpenKeyset, adLockReadOnly
    If rs.BOF = False Then
        Grd.TextMatrix(Grd.Row, cItemName) = rs!ProductName
        Grd.TextMatrix(Grd.Row, cUnit) = rs!MAXOFUNIT
    End If

SetProductValues_Done:
    If rs.State Then rs.Close
    Set rs = Nothing
    Exit Sub

SetProductValues_Error:
    Call Process_Error(MODULE_NAME, "SetProductValues")
    Resume SetProductValues_Done

End Sub

also the same issue in Products Class Module.

Public Function GetAll(Optional ByVal vstrProductID As Variant, Optional ByVal vstrProductName As Variant, Optional ByVal vstrProductUnit As Variant) As ADODB.Recordset

    On Error GoTo GetAll_Error

    If DataPath = "" Then Exit Function

    Dim rs As ADODB.Recordset
    Dim strSql As String
    Dim cn As ADODB.Connection

    Set cn = New ADODB.Connection
    cn.CursorLocation = adUseClient
    cn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & DataPath
    Set rs = New ADODB.Recordset
    strSql = " SELECT ITEM_CODE, PRODUCTNAME, MAXOFUNIT FROM PARTDETAIL WHERE "

    If Not IsMissing(vstrProductID) Then strSql = strSql & " ITEM_CODE = " & "'" & Replace(vstrProductID, "'", "''") & "'" & " AND "
    If Not IsMissing(vstrProductName) Then strSql = strSql & " PRODUCTNAME = " & "'" & Replace(vstrProductName, "'", "''") & "'" & " AND "
    If Not IsMissing(vstrProductUnit) Then strSql = strSql & " MAXOFUNIT = " & "'" & Replace(vstrProductUnit, "'", "''") & "'" & " AND "

    strSql = strSql & " TRIM(ITEM_CODE) <> '' AND "
    If FilterStr <> "" Then
        strSql = strSql & " LEFT(TRIM(ITEM_CODE),1) = '" & FilterStr & "' AND "
    End If

    strSql = Trim(strSql)
    If Right(strSql, 5) = "WHERE" Then strSql = Left(strSql, Len(strSql) - 5)
    If Right(strSql, 3) = "AND" Then strSql = Left(strSql, Len(strSql) - 3)

    rs.CursorLocation = adUseClient
    cn.Open
    rs.Open strSql & " ORDER BY UCASE(LEFT(ITEM_CODE,1)) & FORMAT(VAL(MID(ITEM_CODE,2)),'000000000')", cn, adOpenForwardOnly, adLockReadOnly
    Set rs.ActiveConnection = Nothing

    Set GetAll = rs
    Set rs = Nothing
    If cn.State Then cn.Close
    Set cn = Nothing

GetAll_Done:
    Exit Function

GetAll_Error:
    Call Process_Error(MODULE_NAME, "GetAll")
    Resume GetAll_Done

End Function


Public Function GetByKey(ByVal vstrProductID As Variant, Optional ByRef vstrProductName As Variant, Optional ByRef vstrProductUnit As Variant) As Boolean

    On Error GoTo GetByKey_Error

    If DataPath = "" Then Exit Function
    Dim rs As ADODB.Recordset
    Dim strSql As String
    Dim cn As ADODB.Connection

    Set cn = New ADODB.Connection
    cn.CursorLocation = adUseClient
    cn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & DataPath
    Set rs = New ADODB.Recordset
    strSql = " SELECT ITEM_CODE, PRODUCTNAME, MAXOFUNIT FROM PARTDETAIL WHERE "
    strSql = strSql & " ITEM_CODE = '" & vstrProductID & "' AND "

    strSql = Trim(strSql)
    If Right(strSql, 3) = "AND" Then strSql = Left(strSql, Len(strSql) - 3)

    rs.CursorLocation = adUseClient
    cn.Open
    rs.Open strSql, cn, adOpenForwardOnly, adLockReadOnly

    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
        If Not IsMissing(vstrProductName) Then vstrProductName = IIf(IsNull(rs.Fields.Item(fldProductName).Value), NULL_STRING, rs.Fields.Item(fldProductName).Value)
        If Not IsMissing(vstrProductUnit) Then vstrProductUnit = IIf(IsNull(rs.Fields.Item(fldProductUnit).Value), NULL_STRING, rs.Fields.Item(fldProductUnit).Value)
        GetByKey = True
    Else
        GetByKey = False
    End If

    rs.Close
    Set rs = Nothing
    If cn.State Then cn.Close
    Set cn = Nothing

GetByKey_Done:
    Exit Function

GetByKey_Error:
    Call Process_Error(MODULE_NAME, "GetByKey")
    Resume GetByKey_Done

End Function

I have marked all the changes in Red.

Grd_LeaveCell() in this procedure
If FlexGridEditor.Visible Then
strOldValue = Grd
Grd = FlexGridEditor.Value FlexGridEditor.Visible = False If Grd.Col = cItemCode Then
Call SetProductValues
End If
how you are putting Grd = FlexGridEditor.Value

Referring Grd if same as referring to the Current Cell which is active.
u can even write

Grd.TextMatrix(Grd.Row, Grd.Col) = FlexGridEditor.Value

When you press enter in the Product Id Column the FlexGridEditor control becomes visible populating all ur Products. When FlexGridEditor needs to be made Visible = False then the Value in the cell is temporarily stored in strOldValue and the cell is updated. Lest any problem, the old value can be replaced by

Grd = strOldValue

Hope this solves ur doubt.
Is it working anyway?

Can you not remotely access my pc through Goto My pc if i send setup of Goto my pc Since it is urgent that is why i am so hurry ?

Sir ,
Now Moderror.bas has thrown a error while run the form:
An error has occurred in the application in module
‘MR’ at procedure ‘grd_leavecell’.

Error Number :13
Type Mismatch
Please Contact Your System’s Administrator.

When this error shows up, without clicking the OK button,

Press Ctrl + (Break/Pause) button and debug each statement execution by pressing F8 key (executing one line at a time for each press of F8 key) till the line of error in Grd_LeaveCell event is highlighted

and post the highlighted line in Grd_LeaveCell() event


Also change the following statement in RED your MR form.

Private Sub SetProductValues()

    Dim rs As New ADODB.Recordset

    On Error GoTo SetProductValues_Error

    rs.Open " SELECT ITEM_CODE, PRODUCTNAME, MAXOFUNIT FROM PARTDETAIL WHERE ITEM_CODE = '" & Grd.TextMatrix(Grd.Row, cItemCode) & "' ", con, adOpenKeyset, adLockReadOnly
    If rs.BOF = False Then
        Grd.TextMatrix(Grd.Row, cItemName) = rs!ProductName
        Grd.TextMatrix(Grd.Row, cUnit) = [B]rs!MAXOFUNIT & ""[/B]
    End If

SetProductValues_Done:
    If rs.State Then rs.Close
    Set rs = Nothing
    Exit Sub

SetProductValues_Error:
    Call Process_Error(MODULE_NAME, "SetProductValues")
    Resume SetProductValues_Done

End Sub
This article has been dead for over six months. Start a new discussion instead.