Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    60

    Unanswered: Help With A Recordset in my database. No Idea What To Do lol

    I have made a quick database to try and test a few features I will be adding to my own database. 1 feature was filter jobs if the person has been assigned, which if you open the database you will see what I mean.

    The other was to use recordset somehow (I have no idea) to get the email of a person based on knowing there name/ID. The name is chosen from a combo box (but it is bound to that persons ID). So using the ID how can I get there email to appear in a msgbox when I push the button. Again see the database and u will know what I mean.

    Now I know I can add the email to the combo box and get it that way but I don’t want to. I want to know how to do it this way. If someone can re-write the code I attempted (I made it up by looking at this happening on a different working database) that would be great. If they can get the email then all my problems will be solved.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    A combobox is a query, and although the Value property equals the bound column, you can get the value in any column using the Column property. In addition to the column property, you'll need to use the ListIndex property to know which row is selected.

    Both properties are explained nicely in the help file.

    good luck,
    tc

  3. #3
    Join Date
    Feb 2006
    Posts
    60
    yeah i know how to use the column property, its just that i dont want to and i wanted to find out how to do it this way.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sure you can mess with a recordset.
    DAOhow (ADO equivalent exists)

    dim dabs as dao.database
    dim recs as dao.recordset
    dim strSQL as string
    strSQL = "SELECT eMailField FROM yourTableName WHERE userID = " & IDfromCombo & ";"
    set dabs = currentdb
    set recs = dabs.openrecordset(strSQL)
    with recs
    if .eof and .bof then
    msgbox "cannot find userID: " & IDfromCombo
    else
    'assuming userID is PK and thus unique, so max one record
    msgbox "mail address is " & !eMailField
    endif
    end with
    set recs = nothing
    set dabs = nothing


    or how about the very much simpler:

    hisEmail = dlookup("eMailField", "yourTableName", "ID = " & IDfromCombo)

    yes, folk will tell you domain aggregate functions are slow, but you are only doing this once per usersession so a millisecond or two wont hurt you.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    My point about a combobox being a query is that the combobox already performed the query for you. Using a recordset, domain lookup or any other method is extra work since the combox already perfomed the query.

    To izy's point about tossing milliseconds around, ditch the combo box - when the form opens, Access performs the query in the combo box 3 times as it goes through the initial events.

    It's a great idea to learn how to use recordset, though - mastering recordsets will take your app development abilities to new heights.

    tc

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    just for fun... a workaround to tc's combo requeries at form open:

    form designtime:
    combo gets setup exactly as you have it now (rowsource, boundcolumn, columnwidth/count etc) ...when done: delete the rowsource.

    form designtime:
    timerinterval = 1
    i.e. timer ticks one millisecond after the form completes loading (another millisecond wasted )

    formwide declaration
    dim amLoading as boolean

    in Form_Load()
    amLoading = true

    in Form_Timer()
    if amLoading then
    me.timerinterval = 'your choice: 0 for off; some other interval if using the timer for something else
    amloading = false
    'now set the rowsource to whatever you had before you deleted it:
    me.myCombo.rowsource = "SELECT blah FROM here WHERE something;"
    else 'if using the timer for something else
    'code for your something else goes here
    endif

    izy
    currently using SS 2008R2

Posting Permissions

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