Problem with lookup in form (Access 2002)

[cdem]

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.

[HansV]

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.

[cdem]

Please find the file attached.

[HansV]

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.

[cdem]

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.

[cdem]

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?

[HansV]

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

[cdem]

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;

[HansV]

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.

[cdem]

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.

[HansV]

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.