Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Another Dlookup problem

    Hi,
    I have a form that has a on it a combo box and a text box (well there are more than this but this issue revolves around only these 2 fields)

    When the user selects the combo box (Interest_sel) the text box (tech_int) should be populated with a value using DLookup

    In the text box Control source I have put the following value

    DLookup("[RiPolicyRef]", "Policies", "[RiPolicyRef]='" & Me.[Forms]![Select_records]![Interest_sel] & "'")

    But all I get in he text box is #Name?

    The combo box is text and so is the text box. This is a single form only that is unbound. The combo box is also unbound.

    DLookup seems to be a minefield as there seems to be lots of other people that have had problems. I've searched through the forum but none of the answers to other peoples problems seems to answer mine.

    Regards
    John

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Another Dlookup problem

    Originally posted by Sticker
    Hi,
    I have a form that has a on it a combo box and a text box (well there are more than this but this issue revolves around only these 2 fields)

    When the user selects the combo box (Interest_sel) the text box (tech_int) should be populated with a value using DLookup

    In the text box Control source I have put the following value

    DLookup("[RiPolicyRef]", "Policies", "[RiPolicyRef]='" & Me.[Forms]![Select_records]![Interest_sel] & "'")

    But all I get in he text box is #Name?

    The combo box is text and so is the text box. This is a single form only that is unbound. The combo box is also unbound.

    DLookup seems to be a minefield as there seems to be lots of other people that have had problems. I've searched through the forum but none of the answers to other peoples problems seems to answer mine.

    Regards
    John
    How many columns in the combobox John? DLookup returns only 1 column ... Also, when you get the #name? that usually means that access cannot find/determine the table/column of the query ...

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Mike,
    I'm going to have to start paying you soon as my personal tutor!!!

    There are 3 columns in the combo box

    Regards
    John

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Sticker
    Hi Mike,
    I'm going to have to start paying you soon as my personal tutor!!!

    There are 3 columns in the combo box

    Regards
    John
    Since that's the case you'd be better served to make your control source a SELECT query ... The problem is that when Access fills a combobox it populates EACH ROW FIRST ... So by having 1 column in your dlookup, you're losing 2 rows (as they are absorbed by the other 2 columns of the combobox) for each row of the combobox.

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Mike,
    Thanks for the reply. I've used your suggestion to use a query but unfortunately it didn't work, HOWEVER, what I did was put a AfterUpdate event on the combo box and used that to force the value of the text box using DLookup on a query and that worked. Strangely if I used the same thing but instead of a query used the combo box, it wouldn't work. So your suggestion has got me to the desired end.

    Thanks for your help

    Cheers
    John

  6. #6
    Join Date
    Dec 2003
    Posts
    268

    Store the value in the ComboBox

    Is there anyway you can store the value for the text box in the combobox in your select criteria without duplicating? If this is the case on the click event of the combo box you can pass the value of a hidden column to the text box.

    tech_int = interest_sel.column(3)

    This is assuming that you would be adding a 4th column to the combo box. The integer in the column function is the index of the column in the combo box. So in other words the first column is index 0, the second column is index 1 and so on.

    You can set the number of columns to 4 in the combo box and the column widths to 1";2";3";0" (or what ever you need, just set the last column to a width of 0.

    Now you dont have to mess with dlookup at all and reduces the number of times you are hitting the DB for info.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Sticker
    Hi Mike,
    Thanks for the reply. I've used your suggestion to use a query but unfortunately it didn't work, HOWEVER, what I did was put a AfterUpdate event on the combo box and used that to force the value of the text box using DLookup on a query and that worked. Strangely if I used the same thing but instead of a query used the combo box, it wouldn't work. So your suggestion has got me to the desired end.

    Thanks for your help

    Cheers
    John
    I guess I helped you out in spite of myself ... I totally blew your question. Talk about barking up the wrong tree ...

  8. #8
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Mjweyland,
    That works perfectly, thanks for the suggestion

    Regards
    John

Posting Permissions

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