Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with lookup in form (Access 2002)

    I am having a problem with a subform in a form. Everything is working except if I select a record it won't change. If I backspace, sometimes it will let me change records but it sets at one or whichever is chosen first. The main form has the primary key which is relative to the rest of the information the subform contains the dropdown lookup which goes back to a table containing possible entries. Not sure why this is happening and if you don't make a mistake it is fine... in a perfect world.

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

    Re: Problem with lookup in form (Access 2002)

    We'll need to know more about the setup of the form and subform, or better still, see the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with lookup in form (Access 2002)

    Please find the file attached.

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

    Re: Problem with lookup in form (Access 2002)

    The bound column of your combo boxes is column 2. This corresponds to the QSTATE column from TBLQST, and this contains CT in all three records. You should include the QSTID field in the row source of the combo box, and make that the bound column.

  5. #5
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with lookup in form (Access 2002)

    Thank you that was it. I removed the OSTID because it was "showing" in the form when bound but by not showing (0") rather than removing and binding to that column it fixed the problem. I really appreciate your help.

  6. #6
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with lookup in form (Access 2002)

    I have fixed this and it is working great however now I run into a problem when attempting to query and I only show the ID numbers instead of the text. When I include the table holding the text (drop down in form) I don't get any results? Any ideas as how to approach this problem?

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

    Re: Problem with lookup in form (Access 2002)

    Please provide more detailed information. What and where are you trying to query?

  8. #8
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with lookup in form (Access 2002)

    Sorry, I am attempting to create a new querry using the DIAG1 in the TBLRESC along with the D1Q1 from TBLQID. D1Q1 stores the DQID which relates to the actual text (drop down) in QTEXT of TBLQST. The info in DIAG1 comes from (drop down/lookup) TBLDIAG.
    SQL shows like this:
    SELECT TBLRESC.[DIAG#1], TBLRESC.STATE, TBLDQID.D1Q1, TBLDQID.D1Q2, TBLDQID.D1Q3, TBLQST.QSUMMARY
    FROM TBLQST INNER JOIN (TBLRESC INNER JOIN TBLDQID ON TBLRESC.IDNUM = TBLDQID.RESC) ON TBLQST.QSTID = TBLDQID.DQID
    ORDER BY TBLRESC.[DIAG#1], TBLRESC.STATE;

    But when I delete the TBLQST I get results. That SQL shows as this:
    SELECT TBLRESC.[DIAG#1], TBLRESC.STATE, TBLDQID.D1Q1, TBLDQID.D1Q2, TBLDQID.D1Q3
    FROM TBLRESC INNER JOIN TBLDQID ON TBLRESC.IDNUM = TBLDQID.RESC
    ORDER BY TBLRESC.[DIAG#1], TBLRESC.STATE;

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

    Re: Problem with lookup in form (Access 2002)

    I think that you should join TBLQST on TBLDQID on QSTID vs (for example) D1Q1, not DQID. But in the database you attached earlier, D1Q1 etc. are memo fields, that won't work. D1Q1 should be a number field.

  10. #10
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with lookup in form (Access 2002)

    I had to change this to a memo field because in the form it wouldn't allow all of the text information to show. This probably goes back to the bound column problem I was having initially. If I join the QSTID and DQID I can get the results but I can get the question relating to the number to show. For instance what I am trying to show (end result) is what D1Q1 shows the most for DIAG1 and then D1Q2 and so on. To do so I need to know what QSUMMARY (D1Q1) text reads and the QNUM and QSTD fields which all relate to the D1Q2 answer which is really a lookup for QSUMMARY or TBLQID. It looked great on paper but in working with it I am running into those problems.

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

    Re: Problem with lookup in form (Access 2002)

    I think you should re-think the data design of your database. Before you do so, read up on relational database design. You'll find a short introduction in Understanding Relational Database Design (a free download from Microsoft), and more in-depth info in Access Database Design & Programming by Steven Roman.

Posting Permissions

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