Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't get lookup to work right (Access 2003)

    It's very late at night and I'm at my wits end. I'm trying something very simple I'm sure, but I can't get it to work quite right.

    I have a database that I enter websites into, categorize them, then select topics that are covered on the website, describe the topic, and rate the topic. A website is limited to one category. You can select as many topics as you want, but you can only use a topic once per website.

    Anyway, I'm starting to design a form (frmSite Data Entry) that has the SiteName, SiteURL, and CategoryNumber (this is all from tblSite). On this form I want to be able to type in my site name and URL, then have a combo box that lists the categories I have to choose from. The combo box looks up its information from the tblCategory which has fields: CategoryNumber, CategoryName, and CategoryDefinition. Now this part works. I can see the list of my Category Names. But what I also want is for the form (frmSite Data Entry) to show the CategoryDefinition on it when I select the CategoryName. I just can't get it to do that.

    I feel like such a dunce. It's probably something really simple.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't get lookup to work right (Access 2003)

    many ways to do this. Is this a main form subform? IF not make an unbound text box, put the description as a colum in the combo box, you can set it swidth to Zero and the after update f the combo to put that colum in the textbox

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

    Re: Can't get lookup to work right (Access 2003)

    Zave gave you the basic idea, but you don't even need code for this. Set the following properties for the combo box:
    Column Count: 3
    Column Widths: 0";1";0"
    Row Source: either tblCategory or a query based on tblCategory returning all three fields.
    Say that you name the combo box cboCategory.
    Put a text box on the form with Control Source

    =[cboCategory].[Column](2)

    (The columns are numbered starting at 0, so Column(2) is the 3rd column)

  4. #4
    Lounger
    Join Date
    Oct 2003
    Location
    Toronto, Ontario, Canada
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't get lookup to work right (Access 2003)

    I think I am having a similar problem as JenniferS. I have a form based on a query and its working fine. I have lots of rows that I am stepping through with the form to score answers to a test. I would like to put a textbox on the form that displays the results from a different query showing percentage complete. I have this query written and it return 1 row with 1 column. The query works fine on its own. I would like to run the query every time the screen repaints (such as when I go to the next record). I tried your suggestion with a combo box and text box, but so far, no joy. Neither the combo box or the text box are getting any values.
    The combo box properties are:
    Name: cboPctComplete
    Column Count: 2
    Column Widths: 2.54cm;2.54cm
    List Rows: 1
    Control Source: =[PolAdminScoringPctComplete]![PctComplete] (=[QueryName]![QueryColumnName])
    Row/Source Type: Table/Query
    Row Source: blank
    Bound Column: 1
    No Event values

    The text box properties are:
    Name: TxtPctComplete
    Control Source: =[cboPctComplete].[Column](0)

    Any thoughts as to what I am doing wrong?

    Stu <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Can't get lookup to work right (Access 2003)

    I don't think you need a combo box here. You can use a text box TxtPctComplete with the following control source:

    =DLookup("PctComplete","PolAdminScoringPctComplete ")

    To make this update each time you move to a new record, you can create an event procedure for the On Current event of the form:

    Private Sub Form_Current()
    Me.Recalc
    End Sub

  6. #6
    Lounger
    Join Date
    Oct 2003
    Location
    Toronto, Ontario, Canada
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't get lookup to work right (Access 2003)

    Thanks Hans, that worked great. Should have sent this days ago.

    Stu

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't get lookup to work right (Access 2003)

    Thanks, Hans and Zave. Works great! I was just missing the reference to the column.

    Jen

Posting Permissions

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