Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: ListBox - Newbie help

    Hi,

    The scenario is like this, I have two list box

    Mth (List1) Multiselect-none, data from lookup table
    01
    02
    03
    04
    12


    Year(List2) Multiselect-none, data from lookup table
    1999
    2000
    2001
    2002

    A command2_Button, - Add to list

    A list box (list5) which will extract data from "tblChosendate" with fields Mth and Year.
    So once command2_button press, the data selected in list 1 and list 3 will be added to the correspnding fields of "tblChosendate".

    But I have some problems, it works some times but do fail most of the time. For example, when I try to select Mth 01 and Year 1999, List 5 will show Mth 01 and Year 2002 instead.

    ---------------------
    Questions
    ---------------------
    1. Why is this happening? Below is my code.
    2. Any one has a much neater code which do not need to have lookuptables or adding data to a table?
    3. How to write code to delete data individually from list 5?

    Thanks!

    -------------------------------------------------------------------------------

    Private Sub Command2_Click()
    Dim chosenmth, chosenyear As String
    Dim ChosenDate As Recordset
    Dim MyDB

    Set chosenmth = Me![List1]
    Set chosenyear = Me![List3]

    'defaut value set to "xx"
    If chosenmth = "xx" Then
    MsgBox "You must choose a month to import"
    Exit Sub
    End If

    'defaut value set to "xxxx"
    If chosenyear = "xxxx" Then
    MsgBox "You must choose a year to import"
    Exit Sub
    End If

    Set MyDB = CurrentDb()
    Set ChosenDate = MyDB.OpenRecordset("tblChosendate")

    ChosenDate.AddNew
    ChosenDate!mth = chosenmth
    ChosenDate!Year = chosenyear
    ChosenDate.Update
    DoCmd.Requery "List5"
    ChosenDate.Close

    End Sub
    ------------------------------------------------------------------------------
    Private Sub Form_Load()
    Dim MyDB, Q
    Set MyDB = CurrentDb()
    Set Q = MyDB.CreateQueryDef("")

    Q.SQL = "Delete * FROM Tblchosendate"
    Q.Execute
    DoCmd.Requery "List5"
    End Sub
    -------------------------------------------------------------------------------

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    First off, I would not use Set unless I was assigning something to an object. Yours uses it to assign a value to a string variable.

    I would also declare the variable types instead of using so many variants:
    Dim MyDB as DAO.Database
    Dim chosenmth as String
    etc.

    You should also tell Access what kind of recordset you are trying to open:
    Set ChosenDate = MyDB.OpenRecordset("tblChosendate", dbOpenTable)

    To Requery a list, use Me.List5.Requery instead of DoCmd.Requery

    I actually couldn't get the code to (1) Compile (until I removed Set from the string variable) or (2) Run because of DoCmd.Requery.

    Second,
    Without really knowing what your form is trying to accomplish, I cannot determine a 'better' way to write the code. Writing data and using lookup tables might be the right solution. Can't see the big picture.

    Third,
    Really depends on your question 2 requirements. To address data within your single-select listbox, you can use Me.List1.ItemData(Me.List1.ListIndex), but it really depends on how the data got into the listbox first (table/Query, Value List, Field List)
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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