Page 1 of 3 123 LastLast
Results 1 to 15 of 36

Thread: List Box help

  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: List Box help

    I droped the idea of Datagrid since it is not so helpful in Access db. Instead I placed list box and started calling data in it from a table. But the problem is I can not fix the column width of the Listbox.

    The below code is on a Save command button's on click event.

    'Othe code lines are here

    cla.ProRec2
    Dim X As Long
    Dim V As String
    cla.Rec2.Open "SELECT ProductCode,Productname,UOM,ROL,SERIALNO, SalesPrice, SalesQty, Amount FROM T_SalesInvFoot WHERE InvNum='" & Trim(InvNum) & "'"
    If cla.Rec2.RecordCount >= 1 Then
    For X = 1 To cla.Rec2.RecordCount

    V = (cla.Rec2!ProductCode) & " " & Trim(cla.Rec2!Productname) & " " & Trim(cla.Rec2!UOM) & " " & Trim(cla.Rec2!ROL) & " " & Trim(cla.Rec2!SERIALNO) & " " & Trim(cla.Rec2!SalesPrice) & " " & Trim(cla.Rec2!SalesQty) & " " & Trim(cla.Rec2!Amount)
    List2007.AddItem (V)
    cla.Rec2.MoveNext
    X = 1
    Next
    End If
    cla.Rec2.Close
    MsgBox "saved"

    The above code is used to display records from table T_SalesInvFoot once it is saved thru previous code lines (which I did not mentioned here). The above code lines will show how many records you created for a particular invoice. Everything is ok but the main problem is when I add records and saves then the above code calls that data to display in List but in following manner.

    77 Mouse - Optical $30 2 $ 60.00

    77 Mouse - Optical $30 2 $ 60.00
    104 Keyboard - 101 keys $ 42 4 $ 168


    77 Mouse - Optical $30 2 $ 60.00
    104 Keyboard - 101 keys $ 42 4 $ 168
    90 Filter $ 5 1 $ 5

    So each time I enter records in invoice footer table (T_SalesInvFoot) it displayes previous records again with a gap of 1 blank line in the ListBox.

    Please note that it saves properly in T_SalesInvFoot but problem with displaying in List box only.

    I would appreciate your help.

    With kind regards,
    Ashfaque
    Last edited by Ashfaque; 03-27-07 at 06:23.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Ashfaque
    ...fix the column width of the Listbox.
    Right click > Properties > Format > Column Widths
    Quote Originally Posted by Example from help file
    Input: 1.5 in;0;2.5 in
    Result: The first column is 1.5 inches, the second column is hidden, and the third column is 2.5 inches.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks George.

    But this will be fine when list box itself holds record source. In my case I m using ADODB and hence I used ;

    V = (cla.Rec2!ProductCode) & " " & Trim(cla.Rec2!Productname) & " " & Trim(cla.Rec2!UOM) & " " & Trim(cla.Rec2!ROL) & " " & Trim(cla.Rec2!SERIALNO) & " " & Trim(cla.Rec2!SalesPrice) & " " & Trim(cla.Rec2!SalesQty) & " " & Trim(cla.Rec2!Amount)
    List2007.AddItem (V)

    And that is why it is not sure that name of the product will have the same width. In my example of records appearing, First numbers are Product Code and then Product Name and Price ....so on..

    When I sets according to your direction, all the data (code, name, price etc) displays in first column only and rest col are null

    Regards,
    Ashfaque

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is there a set number of columns in your list?
    If so, you need to declare that number in the listbox properties.

    Remember, properties can also be assigned via VBA code - this may be useful to you.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    When set column count 8 from the property of Listbox2007 bcz the fields will apprear in the list are 8. But no use so I again set column count to 1

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, I was looking at this problem incorrectly, I didn't realise that you had used a string to populate your listbox...

    why are you doing it this way?
    The simplest way would be using SQL...
    Code:
    Dim SQL As String
    
    SQL = ""
    SQL = SQL & "SELECT * FROM MyTable"
    
    Me.MyListBox.RowSource = SQL
    Me.MyListBox.Requery
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Again thanks George.

    Please note that I don't have any data tables locally. All data I am pulling from Server.

    In this situation I must first make the connection on form load / open hence I used: cla.ProRec2 which is a connection string defined in a class module. Then I started displaying data cla.Rec2.Open "SELECT ProductCode....WHERE InvNum='" & Trim(InvNum) & "'"
    Because I want only those relational data of Invoice that is currently on the screen. Your string might work well when there is sub form and used local tables I believe. If you would like, I would upload my FE db along with minimal records of Server data backup.

    Thanks and regards,
    Ashfaque

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you using linked tables or connection strings?
    give the example code I posted a quick try and let me know the result.

    Another quick thought
    Code:
    Dim Test As String
    
    Test = cla.Rec2.Open "SELECT ProductCode,Productname,UOM,ROL,SERIALNO, SalesPrice, SalesQty, Amount FROM T_SalesInvFoot WHERE InvNum='" & Trim(InvNum) & "'"
    
    Me.MyListBox.RowSource = Test
    Me.MyListBox.Requery
    Completely untested and theoretical
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I am using connecting string



    Shows error in

    Test = cla.Rec2.Open "SELECT ProductCode....."

    Regards

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, the data type for our Test variable should not be string...
    I'm thinking Array or similar.
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Anybody else ?

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    You can set the listbox to have it's Row Source Type as Value List. Then, rather than concatenating the way you are, just put a semi-colon ( between each entry as you add the whole string at one time. By having the list box defined as having 8 columns, the first 8 entries you add to the Row Source property will be the first row in the list box. This way you can have your columns in a list box.

  13. #13
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Urgent help required..

    Thanks Golfer,

    I do not have any local tables. They are all on SQL Server Desktop Edition. I am using ADODB connection string and so it won't work. I tried already. Hence I again dropped this idea also of using List box. Instead I placed subform and tried. As I told b4 that I don't have any local tables neither for Header form table nor sub form.

    Now my question is ; I need to ONLY display subform records based on InvNum text box on header form. I wrote the connection string code but it produces error.
    This code is on OnCurrent event of sub form:

    cla.Connect
    cla.ProRec1 ' This is my string declared in a class module and works fine.
    cla.Rec.Open "select * from T_SalesInvFoot where InvNum = '" & Trim(InvNum) & "'"
    If cla.Rec.RecordCount >= 1 Then
    'Other code lines...

    The error occuring in Red line says;

    Run-Time error '-2147217913 (80040e07);'
    [Micorsoft][ODBC SQL Server Driver][SQL Server]errror converting
    datatype varchar to numeric.

    Please note that datatype for InvNum in both Header and Footer is kept numeric.

    Please advice if sub-form idea will work with ADODB connection string or this one also I should drop?

    Eagerly waiting for reply.

    With kind regards,
    Ashfaque

  14. #14
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    At one point you posted this code as how you were putting the data so it could be seen.
    Code:
    V = (cla.Rec2!ProductCode) & " " & Trim(cla.Rec2!Productname) & " " & Trim(cla.Rec2!UOM) & " " & Trim(cla.Rec2!ROL) & " " & Trim(cla.Rec2!SERIALNO) & " " & Trim(cla.Rec2!SalesPrice) & " " & Trim(cla.Rec2!SalesQty) & " " & Trim(cla.Rec2!Amount)
    But this produced only one column, so I suggested putting a semicolon between these fields in your string and that would give you columns. To do that you would need to change the Row Source Type of the list box to "Value List" and then put the "V" string into the Row Source property. Here is how to change your string builder I referenced above to have columns in your list box.
    Code:
    V = (cla.Rec2!ProductCode) & ";" & Trim(cla.Rec2!Productname) & ";" & Trim(cla.Rec2!UOM) & ";" & Trim(cla.Rec2!ROL) & ";" & Trim(cla.Rec2!SERIALNO) & ";" & Trim(cla.Rec2!SalesPrice) & ";" & Trim(cla.Rec2!SalesQty) & ";" & Trim(cla.Rec2!Amount)
    MyListBox.RowSource = MyListBox.RowSource + V
    Be sure to change the column count within the listbox properties to 8.

    If you get any errors when testing this, please show the line of code that produces the error and what the error is. Having all the information really helps other to help you.

  15. #15
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Golfer,

    50 % is ok now...

    Here is the code of listbox including yours.

    cla.Rec2.Open "SELECT ProductCode,Productname,UOM,ROL,SERIALNO, SalesPrice, SalesQty, Amount FROM T_SalesInvFoot WHERE InvNum='" & Trim(InvNum) & "'"

    If cla.Rec2.RecordCount >= 1 Then

    For X = 1 To cla.Rec2.RecordCount
    V = (cla.Rec2!ProductCode) & ";" & Trim(cla.Rec2!Productname) & ";" & Trim(cla.Rec2!UOM) & ";" & Trim(cla.Rec2!ROL) & ";" & Trim(cla.Rec2!SERIALNO) & ";" & Trim(cla.Rec2!SalesPrice) & ";" & Trim(cla.Rec2!SalesQty) & ";" & Trim(cla.Rec2!Amount)
    List2007.RowSource = List2007.RowSource + V
    cla.Rec2.MoveNext
    V = ""
    Next

    End If
    cla.Rec2.Close

    Please have a look at the attached pic generated thru above code after addttion you semicolon idea. It created 8 columns but in this fashion. Items entered previously are repeated with newly entered items.

    Thanks in advance,
    With kind regards,
    Ashfaque
    Attached Thumbnails Attached Thumbnails Listbox.JPG  

Posting Permissions

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