Results 1 to 13 of 13

Thread: Listbox Issue

  1. #1
    Join Date
    Nov 2006
    Posts
    40

    Unanswered: Listbox Issue

    I am encountering an error while displaying the corresponding values of the listbox to the textbox. By right the selected values of the populated listbox should be displayed correctly in the textbox. For instance, these are the values stored in my listbox (pencil, eraser, sharpener). When i selected pencil from the listbox, pencil shld be displayed in the textbox. I am using the AfterUpdate event property to trigger the action. This is done using MS Acess with visual basic. . The encontered error : AddNew or Edit displayed when the field is bound to the controlsource. This resulted the prog to be terminated immaturely.

    Any advice, assistance or sample snippets of code is kindly appreciated.

    Thanks in advance!
    Last edited by technocraze; 12-07-06 at 09:43.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    AddNew or Edit displayed when the field is bound to the controlsource
    Is the text box bound to anything? Specifically, is it bound to a field of the form's record source? If you gave us a glimpse of your VBA code that should be performing the action, it would also be helpful.

    Sam

  3. #3
    Join Date
    Nov 2006
    Posts
    40
    Hi SAM,

    AfterUpdate event for Listbox0

    Dim rs as RecordSet
    set rs = Me.RecordSetClone
    rs.FindFirst[StudentId] & (Str(Nz!Me(List0,0)
    If not EOF then Me.Bookmark = rs.Bookmark

    However, when i bound the textbox to the controlSource. I will always get this error Update or CancelUpdate without AddNew or Edit

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Assuming that the listbox name is ListBox0 (not List0), the bound column is 0 (StudentId) and that [StuentId] is a field in the form record source then I would use this

    Code:
        if ListBox0 & "" = "" then exit sub
    
        With Me.RecordsetClone
            .FindFirst "[StudentId] = " & ListBox0
            If Not .EOF then 
                  Me.Bookmark = .Bookmark
            else
                  Msgbox "Student not found  etc."
            end if
        End With
    Or similar

    If you do use rs = me.RecordsetClone then it should be a DAO recordset and you need qualify EOF (rs.EOF)

    BTW if StudentId is a string then it should be like this

    Code:
        if ListBox0 & "" = "" then exit sub
    
        With Me.RecordsetClone
            .FindFirst "[StudentId] = '" & ListBox0 & "'"
            If Not .EOF then 
                  Me.Bookmark = .Bookmark
            else
                  Msgbox "Student not found  etc."
            end if
        End With

    HTH

    Rereading, what textbox are you binding ?

    MTB

  5. #5
    Join Date
    Nov 2006
    Posts
    40
    Hi MikeTheBike,

    First of all thanks for your enlightenment. Well, actually i have a listbox with some values (eg:engineering,business,medical science). What i want is to iterate through the listbox and display the corresponding values (selected values) from the listbox and display in the textbox as it iterate through the list via using foreach loop.

    btw, do u have any idea how can i query a listbox using sqlquery in a vb environment? I an encountering a problem assigning the selected values of the listbox when using the where clause. I tried this: where studentid = & Listbox1.selected / Listbox.value/ id but nt working. Because i am assigning the variable as variant to the listbox and use it in the where clause for that matching condition. Thanks in advance! Cheers

  6. #6
    Join Date
    Nov 2006
    Posts
    40
    Hi MikeTheBike,

    First of all thanks for your enlightenment. Well, actually i have a listbox with some values (eg:engineering,business,medical science). What i want is to iterate through the listbox and display the corresponding values (selected values) from the listbox and display in the textbox as it iterate through the list via using foreach loop.

    btw, do u have any idea how can i query a listbox using sqlquery in a vb environment? I an encountering a problem assigning the selected values of the listbox when using the where clause. I tried this: where studentid = & Listbox1.selected / Listbox.value/ id but nt working. Because i am assigning the variable as variant to the listbox and use it in the where clause for that matching condition. Thanks in advance! Cheers

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I must confess to being totally confused to what you are trying to do.

    You can certainly iterate through the selected items of a listbox, but if you just display each one in turn in a (single) textbox it will not be visible (for very long).

    Also you say it contains engineering, business, medical, science etc., but you use StudentId as the bound column ??

    I think a little more info is required before a reasonable stab at what you are trying to do can be made.

    Perhaps posting your code would help !?



    MTB

  8. #8
    Join Date
    Nov 2006
    Posts
    40

    Sql Insert Issue

    Hi All,

    I have an issue with Sql query in VB environment using MS Acess. Can I know how can I query an insert stat in vb environment and insert the form fields in MS Acess table? I tried something like this but nt working. I am nt too sure how to use a recordSet in vb environment, set focus for the control (TextBox), set the recordSet, open recordSet, connection for ODBC. I am using a combo dropdown list for my selection in the where clause of the sql query.

    Any guidance or sample code will be highly appreciated.
    Thanks in advance!

    Dim strSql as string
    Dim rs as DAO.RecordSet
    Dim db as Database

    cmdInsert

    set rs = db.OpenRecordSet("TableName")
    strSql = insert into [tableName] (field1.field2,field3) values(TextBox1, TextBox2, TextBox3) where studentid= Combo3.value)

    field 1 = Subject
    field 2 = SubjectCode
    field 3 = ClassCode

  9. #9
    Join Date
    Nov 2006
    Posts
    40

    ListBox Iterate and Sql Update Issue

    I am nt too sure as hw to iterate through the listbox. I am doing something like this. What I need is to iterate though the listbox, select mutilple values in a multi listbox and update the selected data in database / MS Aceess [TableName]. But I am too sure hw to use the recordSet properties or DAO / AODB

    For instance
    Dim studentName as String
    Dim subjectCode as String
    Dim classCode as String
    Dim item as Variant
    Dim StudentId as String
    Dim sql as String

    Assign the variable to the control(TextBox)
    Assign the variable to the control(ListBox) - MultiListBox

    Foreach item in ListBox1.Selected
    sql = Update [tableName] set [control] where [ListBox selection].

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    amusing!

    where would you like to start?
    strSql = "something
    will be a major step forward but your query will still fail since you are using the syntax for inserting a number but the value you attempt to insert is the literal text TextBox1 which is not numeric.
    so next step is to concatenate in the form field value rather than the literal text:
    values(" & me.TextBox1 & ".....and continue (you still need the literal comma)
    if any of these guys are text rather than numeric then they need quoting:
    values('" & me.TextBox1 & "'.....and continue
    similarly the WHERE clause - it is unlikely that you have a studentid with the literal value Combo3.value so have a go with
    WHERE studentid = " & me.combo3 & "...and continue
    (same rules as before if id is text: = '" & me.combo3 & "'...)

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ?? are you planning to mess around with a recordset or use an INSERT query
    (similar question applies to your other post)

    if you are running SQL against your table, you don't need rs
    if you are planning to mess with a recordset, at best you need a SELECT to fill the recordset but you dont need the INSERT

    my preference:
    forget the recordset
    tweak your SQL so it is viable
    currentdb.execute strSql

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Look in the Help files for the Selected property. Microsoft gives you the code on a silver platter.

    Sam

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Please open only one thread per issue.

    I closed one of your threads and merged the other two.

    The reason we like to try to keep all of the threads for a certain issue together is so everyone can see the whole issue as well as what suggestions have already been posed,and perhaps were there may have been some clarity issues with the suggestions that we can assist with.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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