Results 1 to 21 of 21

Thread: Some querries ...

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Some querries ...

    Dear Friends,

    Why some of the option buttons are not working properly?

    Attached is my project.

    Seema.
    Attached Files Attached Files

  2. #2

  3. #3
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: Some querries ...

    could you post the code here...because i cannt open the attachement here, in my pc...
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by ganeshmoorthy
    could you post the code here...because i cannt open the attachement here, in my pc...
    The following is the code in Module:

    VB Code:
    1. Public db As New ADODB.Connection
    2. Public rs As New ADODB.Recordset
    3. Public Sub data_connect()
    4. Dim path As String
    5. path = App.path & "\Database2006.mdb"
    6. db.CursorLocation = adUseClient
    7. db.Open "Provider=microsoft.jet.oledb.4.0;persist security info=false;data source=" & path & ";"
    8. End Sub
    9. Sub Main()
    10. data_connect
    11. frmStatement.Show
    12. End Sub

    VB Code:
    1. Option Explicit
    2. Public strSQL As String
    3. Private Sub Form_Load()
    4.     Call data_connect
    5.     LoadListViews
    6. End Sub
    7. Private Sub LoadListViews(Optional strSortHow As String)
    8. Dim lvwItem As ListItem
    9.     Set rs = New ADODB.Recordset
    10.     strSQL = "Select CustomerName, InvoiceNumber, InvoiceDate, InvoiceAmount, FileNumber from Sales "
    11.            Select Case strSortHow
    12.                 Case "CustomerName"
    13.                      strSQL = strSQL & " ORDER BY CustomerName"
    14.                 Case "InvoiceNumber"
    15.                      strSQL = strSQL & " ORDER BY InvoiceNumber"
    16.                 Case " InvoiceDate"
    17.                      strSQL = strSQL & " ORDER BY InvoiceDate"
    18.                 Case " InvoiceAmount"
    19.                      strSQL = strSQL & " ORDER BY InvoiceAmount"
    20.                 Case " FileNumber"
    21.                      strSQL = strSQL & " ORDER BY FileNumber"
    22.           End Select
    23.  
    24.     rs.Open strSQL, db, adOpenDynamic, adLockOptimistic
    25.     lvwSales.ListItems.Clear
    26.    
    27. Do While Not rs.EOF
    28.         Set lvwItem = lvwSales.ListItems.Add(, , rs.Fields(0).Value, , 0)
    29.         lvwItem.SubItems(1) = rs.Fields(1).Value
    30.         lvwItem.SubItems(2) = rs.Fields(2).Value
    31.         lvwItem.SubItems(3) = rs.Fields(3).Value
    32.         lvwItem.SubItems(4) = rs.Fields(4).Value
    33.         rs.MoveNext
    34.    
    35. Loop
    36.    
    37.  rs.Close
    38.     Set rs = Nothing
    39.    
    40. strSQL = "Select CustomerName, DepositDate, DepositedIn, AmountReceived, FileNumber  from Collections "
    41.            Select Case strSortHow
    42.                 Case "CustomerName"
    43.                      strSQL = strSQL & " ORDER BY CustomerName"
    44.                 Case "DepositDate"
    45.                      strSQL = strSQL & " ORDER BY DepositDate"
    46.                 Case "DepositedIn"
    47.                      strSQL = strSQL & " ORDER BY DepositedIn"
    48.                 Case "AmountReceived"
    49.                      strSQL = strSQL & " ORDER BY AmountReceived"
    50.                 Case "FileNumber"
    51.                      strSQL = strSQL & " ORDER BY FileNumber"
    52.  
    53.           End Select
    54.  
    55.     Set rs = New ADODB.Recordset
    56.     rs.Open strSQL, db, adOpenDynamic, adLockOptimistic
    57.    
    58.  lvwCollections.ListItems.Clear
    59.    
    60. Do While Not rs.EOF
    61.      
    62. Set lvwItem = lvwCollections.ListItems.Add(, , rs.Fields.Item("CustomerName"))
    63.         lvwItem.SubItems(1) = rs.Fields("DepositDate").Value
    64.         lvwItem.SubItems(2) = rs.Fields("DepositedIn").Value
    65.         lvwItem.SubItems(3) = rs.Fields("AmountReceived").Value
    66.         lvwItem.SubItems(4) = rs.Fields("FileNumber").Value
    67.  
    68.     rs.MoveNext
    69.     Loop
    70.     rs.Close
    71.     Set rs = Nothing
    72.  
    73. End Sub
    74.  
    75. Private Sub cmdGetTotalSales_Click()
    76. strSQL = "select sum(InvoiceAmount) as TotalSales from Sales"
    77. Set rs = New ADODB.Recordset
    78. rs.Open strSQL, db, adOpenDynamic, adLockOptimistic
    79. txtGetTotalSales.Text = rs(0)
    80. txtGetTotalSales.Text = Format(txtGetTotalSales.Text, "##,##.00")
    81. rs.Close
    82. Set rs = Nothing
    83. End Sub
    84.  
    85. Private Sub cmdGetTotalCollections_Click()
    86. strSQL = "select sum(AmountReceived) as TotalMarks from Collections"
    87. Set rs = New ADODB.Recordset
    88. rs.Open strSQL, db, adOpenDynamic, adLockOptimistic
    89. txtGetTotalCollections.Text = rs(0)
    90. txtGetTotalCollections.Text = Format(txtGetTotalCollections.Text, "##,##.00")
    91. rs.Close
    92. Set rs = Nothing
    93. End Sub
    94.  
    95. Private Sub optCustomerName_Click()
    96. LoadListViews "CustomerName"
    97. End Sub
    98.  
    99. Private Sub optInvoiceNumber_Click()
    100. LoadListViews "InvoiceNumber"
    101. End Sub
    102.  
    103. Private Sub optInvoiceDate_Click()
    104. LoadListViews "InvoiceDate"
    105. End Sub
    106.  
    107. Private Sub optInvoiceAmount_Click()
    108. LoadListViews "InvoiceAmount"
    109. End Sub
    110.  
    111. Private Sub optDepositDate_Click()
    112. LoadListViews "DepositDate"
    113. End Sub
    114.  
    115. Private Sub optDepositedIn_Click()
    116. LoadListViews "DepositedIn"
    117. End Sub
    118.  
    119. Private Sub optAmountReceived_Click()
    120. LoadListViews "AmountReceived"
    121. End Sub
    122.  
    123. Private Sub optFileNumber_Click()
    124. LoadListViews "FileNumber"
    125. End Sub
    126. Private Sub cmdLoadList_Click()
    127. Dim lvwItem As ListItem
    128.     Set rs = New ADODB.Recordset
    129.     strSQL = "Select CustomerName, InvoiceNumber, InvoiceDate, InvoiceAmount, FileNumber from Sales Order "
    130.            Select Case strSortHow
    131.                 Case "CustomerName"
    132.                      strSQL = strSQL & " ORDER BY CustomerName"
    133.                 Case "InvoiceNumber"
    134.                      strSQL = strSQL & " ORDER BY InvoiceNumber"
    135.                 Case "InvoiceDate"
    136.                      strSQL = strSQL & " ORDER BY InvoiceDate"
    137.                 Case "InvoiceAmount"
    138.                      strSQL = strSQL & " ORDER BY InvoiceAmount"
    139.                 Case "FileNumber"
    140.                      strSQL = strSQL & " ORDER BY FileNumber"
    141.  
    142.           End Select
    143.     rs.Open strSQL, db, adOpenDynamic, adLockOptimistic
    144.     Do While Not rs.EOF
    145.         Set lvwItem = lvwSales.ListItems.Add(, , rs.Fields(0).Value, , 0)
    146.         lvwItem.SubItems(1) = rs.Fields(1).Value
    147.         lvwItem.SubItems(2) = rs.Fields(2).Value
    148.         lvwItem.SubItems(3) = rs.Fields(3).Value
    149.         lvwItem.SubItems(4) = rs.Fields(4).Value
    150.  
    151.         rs.MoveNext
    152.     Loop
    153.     rs.Close
    154.     Set rs = Nothing
    155.    
    156.  strSQL = "Select CustomerName, DepositDate, DepositedIn, AmountReceived, FileNumber  from Collections Order "
    157.            Select Case strSortHow
    158.                 Case "CustomerName"
    159.                      strSQL = strSQL & " ORDER BY Customername"
    160.                 Case "Deposit Date"
    161.                      strSQL = strSQL & " ORDER BY DepositDate"
    162.                 Case "DepositedIn"
    163.                      strSQL = strSQL & " ORDER BY DepositedIn"
    164.                 Case "AmountReceived"
    165.                      strSQL = strSQL & " ORDER BY AmountReceived"
    166.                 Case "FileNumber"
    167.                      strSQL = strSQL & " ORDER BY FileNumber"
    168.  
    169.           End Select
    170.  
    171.     Set rs = New ADODB.Recordset
    172.     rs.Open strSQL, db, adOpenDynamic, adLockOptimistic
    173.     Do While Not rs.EOF
    174.      Set lvwItem = lvwCollections.ListItems.Add(, , rs.Fields.Item("CutomerName"))
    175.         lvwItem.SubItems(1) = rs.Fields("DepositDate").Value
    176.         lvwItem.SubItems(2) = rs.Fields("DepositedIn").Value
    177.         lvwItem.SubItems(3) = rs.Fields("AmountReceived").Value
    178.         lvwItem.SubItems(4) = rs.Fields("FileNumber").Value
    179.  
    180.         rs.MoveNext
    181.    
    182.  Loop
    183.  
    184.     rs.Close
    185.     Set rs = Nothing
    186. End Sub
    187. Private Sub cmdExit_Click()
    188. End
    189. End Sub

    The following option buttons are not working:

    1. optInvoiceNumber
    2. optInvoiceDate
    3. optInvoiceAmount

    and the following two buttons are working wtih Collections table but not with Sales table.

    1. optCustomerName
    2. optFileNumber
    Attached Images Attached Images  

  5. #5
    Addicted Member
    Join Date
    Feb 2006
    Location
    Hyderabad, India
    Posts
    233

    Re: Some querries ...

    In your loadlistview function, in the select case statement, some of the options are preceeded by blanks.Example " filenumber"," invoicenumber" etc.
    I cannot positively say that is the reason for problem. But I think you are better off declaring integer constants for each of the options and using them. Something like private const INVOICENUMBER as integer = 1. etc. In this case if there are any typos, they will be caught.

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,692

    Re: Some querries ...

    In cmdLoadList_Click, you haven't declared strSortHow (you declared it in LoadListViews, but that doesn't mean anytning in cmdLoadList). Since the Select statements in cmdLoadList_Click never return a string, you have no Order By clause.

    Put a breakpoint at your rs.Open strSQL statements in cmdLoadList_Click and Debug.Print strSQL.

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by Al42
    Put a breakpoint at your rs.Open strSQL statements in cmdLoadList_Click and Debug.Print strSQL
    How can I do this could you please explain me clearly?

    Seema.

  8. #8
    Addicted Member
    Join Date
    Feb 2006
    Location
    Hyderabad, India
    Posts
    233

    Re: Some querries ...

    Bring the cursor to the line containing the statement specified and press F9. That particular line will be highlighted. When you run the program, execution will stop at this particular line. Here press F8 , next line will be highlighted in yellow. NOw, open the immediate window (Ctrl + G) and say debug.print strSQL. Once you have viewed the result, in the code window bring the cursor to the line with breakpoint and press F9, breakpoint will be removed. Then you can resume execution by clicking the run button or F5.

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by srisa
    Bring the cursor to the line containing the statement specified and press F9. That particular line will be highlighted. When you run the program, execution will stop at this particular line. Here press F8 , next line will be highlighted in yellow. NOw, open the immediate window (Ctrl + G) and say debug.print strSQL. Once you have viewed the result, in the code window bring the cursor to the line with breakpoint and press F9, breakpoint will be removed. Then you can resume execution by clicking the run button or F5.
    Hi,

    I tried but could not understand it. The problem still exists. How can I change the code so that option buttons work properly?

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by seema_s
    Hi,

    I tried but could not understand it. The problem still exists. How can I change the code so that option buttons work properly?
    Please let me know how to modify the code?

  11. #11
    Addicted Member
    Join Date
    Feb 2006
    Location
    Hyderabad, India
    Posts
    233

    Re: Some querries ...

    Quote Originally Posted by seema_s
    The following is the code in Module:


    VB Code:
    1. Private Sub LoadListViews(Optional strSortHow As String)
    2. Dim lvwItem As ListItem
    3.     Set rs = New ADODB.Recordset
    4.     strSQL = "Select CustomerName, InvoiceNumber, InvoiceDate, InvoiceAmount, FileNumber from Sales "
    5.            Select Case strSortHow
    6.                 Case "CustomerName"
    7.                      strSQL = strSQL & " ORDER BY CustomerName"
    8.                 Case "InvoiceNumber"
    9.                      strSQL = strSQL & " ORDER BY InvoiceNumber"
    10.                [B] Case " InvoiceDate[/B]"
    11.                      strSQL = strSQL & " ORDER BY InvoiceDate"
    12.                [B] Case " InvoiceAmount[/B]"
    13.                      strSQL = strSQL & " ORDER BY InvoiceAmount"
    14.                 [B]Case " FileNumber[/B]"
    15.                      strSQL = strSQL & " ORDER BY FileNumber"
    16.           End Select
    17.  
    18.  
    19.  
    20. Private Sub optCustomerName_Click()
    21. LoadListViews "CustomerName"
    22. End Sub
    23.  
    24. Private Sub optInvoiceNumber_Click()
    25. LoadListViews "InvoiceNumber"
    26. End Sub
    27.  
    28. Private Sub optInvoiceDate_Click()
    29. [B]LoadListViews "InvoiceDate[/B]"
    30. End Sub
    31.  
    32. Private Sub optInvoiceAmount_Click()
    33. [B]LoadListViews "InvoiceAmount[/B]"
    34. End Sub
    35.  
    36. Private Sub optDepositDate_Click()
    37. LoadListViews "DepositDate"
    38. End Sub
    39.  
    40. Private Sub optDepositedIn_Click()
    41. LoadListViews "DepositedIn"
    42. End Sub
    43.  
    44. Private Sub optAmountReceived_Click()
    45. LoadListViews "AmountReceived"
    46. End Sub
    47.  
    48. Private Sub optFileNumber_Click()
    49. [B]LoadListViews "FileNumber"[/B]End Sub
    50.  
    51. Private Sub cmdLoadList_Click()
    52. Dim lvwItem As ListItem
    53.     Set rs = New ADODB.Recordset
    54.     strSQL = "Select CustomerName, InvoiceNumber, InvoiceDate, InvoiceAmount, FileNumber from Sales Order "
    55.            Select Case [B]strSortHow[/B]
    In the option button click event , you are calling loadlistviews like "Filenumber" but in the subroutine select case statement option is like this " Filenumber". Take note of the extra blank before F in Case. Remove those blanks for this and other case options and see.
    And in the cmdloadlistclick event strSortHow is not declared as a variable nor is it being passed as a parameter.Of course you cannot pass a parameter to the event generated by the program.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by srisa
    In the option button click event , you are calling loadlistviews like "Filenumber" but in the subroutine select case statement option is like this " Filenumber". Take note of the extra blank before F in Case. Remove those blanks for this and other case options and see.
    And in the cmdloadlistclick event strSortHow is not declared as a variable nor is it being passed as a parameter.Of course you cannot pass a parameter to the event generated by the program.
    Hi,

    Wow! your are so keen.

    Though I have made the changes (removed the extra gap/space), it is not working. Actually I have copied this code from another program and modified the fields according to my database fields.

    Please help.

    Seema.

  13. #13
    Addicted Member
    Join Date
    Feb 2006
    Location
    Hyderabad, India
    Posts
    233

    Re: Some querries ...

    For the lstview sales you have set the sorted property to true and the field as 0 . Uncheck the sorted check box. In the property sheet of listview control you have a field custom. Click on it and select the sorting tab, in that uncheck the sorted box.
    One more thing: I think you are better off having separate subroutines for loading the sales and collections listviews. For the fields which are common for both , you will have to call two subroutines and for fields which are unique one subroutine will do.

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by srisa
    One more thing: I think you are better off having separate subroutines for loading the sales and collections listviews. For the fields which are common for both , you will have to call two subroutines and for fields which are unique one subroutine will do.
    Thanks for your kind help. It works pretty well now. But I did not get your second point regarding separate subroutines. I have done this coding by getting help and I am a learner.

    As heading shows "some querries" I have got help for my first querry. The second querry is regarding search.

    How can I write code to search a name using txtSearch.Text? Means, if I write "S" in the txtSearch.Text the lvwSales and lvwCollections should show the result i.e. all the names starting with 'S'.

    Once again thanks a lot for your help and hope to get more help from you.

    Regards.

    Seema_s

  15. #15
    Addicted Member
    Join Date
    Feb 2006
    Location
    Hyderabad, India
    Posts
    233

    Re: Some querries ...

    This is the sql statement that you need.
    VB Code:
    1. strSQL = "Select CustomerName, DepositDate, DepositedIn, AmountReceived, FileNumber  from Collections "
    2. strSQL = strSQL & " WHERE CustomerName like '" & TxtSearch.Text & "%'"
    % is a wildcard character for sql which denotes 0 or more characters. If you know DOS, dir s*.* will list all files starting with s and with any extensions. "%" performs similar function with sql.

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by srisa
    This is the sql statement that you need.
    VB Code:
    1. strSQL = "Select CustomerName, DepositDate, DepositedIn, AmountReceived, FileNumber  from Collections "
    2. strSQL = strSQL & " WHERE CustomerName like '" & TxtSearch.Text & "%'"
    % is a wildcard character for sql which denotes 0 or more characters. If you know DOS, dir s*.* will list all files starting with s and with any extensions. "%" performs similar function with sql.
    Hi,

    Thanks. I have written the code as below. But I cant get the result (and no error also).

    VB Code:
    1. Private Sub txtSearch_Change()
    2. Set rs = New ADODB.Recordset
    3.     rs.Open strSQL, db, adOpenDynamic, adLockOptimistic
    4. strSQL = "Select CustomerName, DepositDate, DepositedIn, AmountReceived, FileNumber  from Collections "
    5. strSQL = strSQL & " WHERE CustomerName like '" & txtSearch.Text & "%'"
    6. End Sub

    What is the next step which I have to do now?

    Seema_s

  17. #17
    Addicted Member
    Join Date
    Feb 2006
    Location
    Hyderabad, India
    Posts
    233

    Re: Some querries ...

    You don't seem to populating the listview the recordset that you have obtained. I think you are better off, putting this code in click event of the label "Search" that you have or add a command button and put this code in the click event of that button. When you put code in the change event , it will be executed every time you type in a character. One more thing ,in the code in your attachment, cmdloadlist click event is useless because there is no command button with that name on your form.

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by srisa
    You don't seem to populating the listview the recordset that you have obtained.
    Hi!

    The following code is not working:

    VB Code:
    1. Set lvwCollections.DataSource = rs

    How to change it?

    Seema_S

  19. #19
    Addicted Member
    Join Date
    Feb 2006
    Location
    Hyderabad, India
    Posts
    233

    Re: Some querries ...

    First thing, listview doesn't have datasource property. You have populated the listview in your loadlistviews subroutine. You have to do something similar here.

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Some querries ...

    Quote Originally Posted by srisa
    You have populated the listview in your loadlistviews subroutine. You have to do something similar here.
    Hi,

    I would like to see the result of searched name in the listview. So what actually I have to do now?

    VB Code:
    1. LoadListViews "CustomerName"
    It is also not showing the result in listview.

    How can I do this?

    Seema_S

  21. #21
    Addicted Member
    Join Date
    Feb 2006
    Location
    Hyderabad, India
    Posts
    233

    Re: Some querries ...

    This is the piece of code in the loadlistviews subroutine that I was talking about
    VB Code:
    1. lvwSales.ListItems.Clear
    2.    
    3. Do While Not rs.EOF
    4.         Set lvwItem = lvwSales.ListItems.Add(, , rs.Fields(0).Value, , 0)
    5.         lvwItem.SubItems(1) = rs.Fields(1).Value
    6.         lvwItem.SubItems(2) = rs.Fields(2).Value
    7.         lvwItem.SubItems(3) = rs.Fields(3).Value
    8.         lvwItem.SubItems(4) = rs.Fields(4).Value
    9.         rs.MoveNext
    10.    
    11. Loop
    This is how list view is filled with values. If I remember correctly , you said this code is written by someone else which you are using. That being the case, take some time to go through the code, understand it and analyze what is being done and how. Then you will be able to write your own code without depending too much on others.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width