Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bind unbound form list box after update (A2k)

    Okay...this is getting on my nerves. I have an unbound form I am using to add information to one of my tables (good for me, right?) - well anyways, the info being entered into this table *could* (partially) come from another table. Therefore, I have a list box on my form, where the user can select a person's name if they are already in my database, or can type in their information to add them to the table. In the list box's after update event, I have:

    Private Sub lstNames_AfterUpdate()
    Dim db As Database
    Dim rst As Recordset
    Dim searchfor As String

    Set db = CurrentDB
    Set rst = db.OpenRecordset("tblPersonnel", dbOpenDynaset)

    searchfor = "[ssn] = " & [lstnames]

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">rst.FindFirst searchfor</span hi> 'Compile error: Method or data member not found. I have the appropriate references checked.... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Me!RANK = rst("rank")
    Me!LNAME = rst("lname")
    Me!FNAME = rst("fname")

    rst.Close
    End Sub

    If I change .FindFirst to .Find, I then get the error:

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Set rst = db.OpenRecordset("tblPersonnel", dbOpenDynaset)</span hi> 'Run time error '13'. Type mismatch.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bind unbound form list box after update (A2k)

    Recordset can be both a DAO and an ADO object. In this case, it's DAO (Database is only a DAO object, not an ADO object), so make sure that you have a reference set to the Microsoft DAO 3.6 Object Library. And be explicit in the declarations, to avoid ambiguity:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Chances are that Access thought rst was an ADO recordset, you can't open an ADO recordset from a DAO Database object.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bind unbound form list box after update (A2k)

    FindFirst is a DAO method.
    Set a reference to Microsoft DAO 3.6 Object Library and change your dim statements as follow :
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Francois

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bind unbound form list box after update (A2k)

    Hans, that works beautifully, Francois, thank you as well. I've now run into another problem on the same form.

    When trying to add records, my date fields give me the error : Data type conversion error.

    I need some troubleshooting tips, because I've checked and rechecked the formats....they're exactly the same.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Bind unbound form list box after update (A2k)

    When you insert the value from the unbound into the table, try explicitly converting it to a date with CDate function.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bind unbound form list box after update (A2k)

    Have you set the Format property of the text boxes that will become date fields to a date format?

    You can set a breakpoint at the beginning of the code that adds a record (double click in the left margin, or click in a line and press F9 to toggle a breakpoint on/off)
    When running the code, it will pause at the breakpoint. You can then single-step through it using F8.
    Hovering the mouse pointer over a variable or control name will display its value in a tooltip.
    You can also inspect variables in the Immediate window:

    ? VariableName

    followed by Enter.

Posting Permissions

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