Results 1 to 8 of 8

Thread: list box

  1. #1
    Join Date
    Jan 2007
    Posts
    47

    Unanswered: list box

    I have a list box where I can select multiple items. I am storing the selected items in a seperate table as individual records. The issue is when I revisit the form I want to see the selected items. So, I have below code in the form load. But, I am getting an error message "The Microsoft Jet database engine does not recognize 'Realquest' as a valid field name or expression" .
    Could anyone please let me know where I am going wrong.


    ************************************************** ****

    Private Sub lstAudits()

    Dim rsAudits As Recordset, dbslog As Database, i As Integer, Criteria As String

    sSql = "SELECT ItemName FROM tbl_audits_items where SSN = '" & SSN & "' and AppSeq = '" & AppSeq & "' and audit_type = '" & scorecard & "'"
    Set dbslog = CurrentDb
    Set rsAudits = dbslog.OpenRecordset(sSql, dbOpenSnapshot)

    For i = 0 To Me.lstItems.ListCount - 1
    Criteria = "[ItemName] = " & Me.lstItems.Column(0, i) & ""
    If Not rsAudits.BOF Then
    rsAudits.FindFirst Criteria
    If rsAudits.NoMatch = False Then
    Me.lstItems.Selected(i) = True
    Else
    '...Do nothing
    End If
    End If
    Next i

    End Sub

    ************************************************** *****************

    Tbl - List Items

    SSN AppSeq Audit_Type ItemName
    111-11-1111 03 Appraiser Other
    111-11-1111 03 Appraiser Assessor records online
    111-11-2222 02 Appraiser Realtor.com
    111-11-2222 02 Appraiser Realquest

    ************************************************** *****************

    I appreciate all your help.

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by ujjwala
    "The Microsoft Jet database engine does not recognize 'Realquest' as a valid field name or expression" .
    that mesage infers that you are supplying REALQUEST as a column name in the SQL. so Id check that the SQL you think you are sending to the parser is what you actually are.. try a mesage box or debug.print

    if that is as you expect then check the filter is correctly formed

    my guess would be that you are missing some quote marks. I find it easier to debug if you escape string /text columns using chr$(34) in place of quite marks.. leastaways I think its chr$(34).. could be 64 for all i can remember
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    So, I have below code in the form load.
    Are you sure that code's in the form's load event? It looks to me more like it's in the listbox's events. And I can't tell which one, either, because your snippet is missing the "_EventName" after the "Sub lstAudits" in the first line.

    What line is your code breaking on?

    Also, healdem has a point that a line is missing quotes. Fix the

    Code:
    Criteria = "[ItemName] = " & Me.lstItems.Column(0, i) & ""
    line to read

    Code:
    Criteria = "[ItemName] = '" & Me.lstItems.Column(0, i) & "'"
    because Criteria is simply a SQL WHERE clause without the word WHERE, and SQL requires those single quotes. Also, I would change the variable name Criteria to txtCriteria; Criteria is probably a reserved word that will get you in trouble when you least expect it.

    HTH,
    Sam

  4. #4
    Join Date
    Jan 2007
    Posts
    47
    You guys are awesomeeeeeeeee! Yes, Qoutes were missing. I tried as you suggested and it worked perfectly. I have been trying to resolve this for long time.

    Thank you so much.

  5. #5
    Join Date
    Jan 2007
    Posts
    47
    Hey,

    I have one more question. Suppose say I have selected Item 1, Item3, Item5 in a lsit box that gets stored in the tbl_audit_items as individual records. I want to edit the list box and deselect Item5 . How do I delete the record with Item5 that has already been stored in the tbl_audit_Items? I mean looking for SQL query that does this action.

    Thanks.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You want to run a DELETE FROM SQL statement;
    Code:
    DoCmd.RunSQL "DELETE FROM tbl_audit_Items WHERE ... <insert where criteria here>
    Me.Refresh
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Posts
    47
    My concern is how do I know when an item is deselected in the list box.
    After knowing the deselected item, that particular record should be deleted from the tbl_audit_items.

    Thanks.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Create your code to loop through the listbox (ie..For i = 0 To Me.lstItems.ListCount - 1), have a statement like: If Me.lstItems.Selected(i) = False then call CODE_TO_DELETE(me.lstItems.ItemData(I)).... You then have a function called: CODE_TO_DELETE(DataValue as integer) which has your code to delete the selected record where the table field matches the fist (or Bound) column of your lstItems (assuming this is an integer value, otherwise change to: DataValue as String or DataValue as Variant.)

    But you may also want to put in a...

    Dim QI as integer
    QI = msgbox("Are you sure you want to delete this item: <item deselected name>",vbyesno)
    if QI = vbNo then Exit Function

    .... first before you actually delete it.
    Last edited by pkstormy; 07-15-07 at 20:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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