Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    16

    Unanswered: Rowsource Problem

    Hi

    I ahve entered this into the rowsource which basically brings up in a combobox with the values which are relevent to QUESTIONSID and CLIENTID on the form.

    SELECT Stockqa.AnswerValue
    FROM Stockqa
    WHERE (((Stockqa.QuestionsID)=" & me.questionsid.value &") AND ((Stockqa.ClientID)=" & me.clientid.value &"));

    I keep getting

    Data type mismatch in experession and no results..

    If i create a MOUSE DOWN event on the combo box with the following code

    Private Sub AnswerLookup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

    Dim tempclientid As Long
    Dim tempquestionsid As Long
    Dim sqllookup As String

    tempclientid = ClientID
    tempquestionsid = questionsID

    sqllookup = "SELECT Stockqa.AnswerValue FROM Stockqa WHERE ((Stockqa.QuestionsID)= " & tempquestionsid & ") AND ((Stockqa.ClientID)= " & tempclientid & " );"

    AnswerLookup.RowSource = sqllookup


    End Sub


    i get the correct values in the COMBOBOX but i cannot select a value it jsut keeps going blank. I do not get any errors either..

    I dont understand what I a mdoing wrong?
    Can anyone point me in the right direction?

    thanks in advance..
    mat

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    consider that A uses ampersand ( & ) as the concatenation operator AND also as the escape character (AND in certain circumstances the hotkey indicator).
    as in
    &underline displays as underline
    &&underline displays as &underline

    try changing &" to & " (i.e. always keep a space both sides of & unless you are trying to use it as escape).

    if that don't fix it, you have a genuine data type missmatch so it would be helpful to know the datatypes of everything in all expressions.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2004
    Posts
    16
    Tried the spaces but still no joy..

    The datatypes are as follows

    questionsid is AUTONUMBER
    clientid is NUMBER
    answer is TEXT
    answerlookup is TEXT

    stockqa table is as follows

    QuestionsID is NUMBER
    ClientID is NUMBER
    AnswerValue is TEXT

    i hope this helps
    thanks
    mat

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    .rowsource =
    SELECT Stockqa.AnswerValue
    FROM Stockqa
    WHERE (((Stockqa.QuestionsID)=" & me.questionsid.value & ")
    AND ((Stockqa.ClientID)=" & me.clientid.value & "));

    ...is NOT going to allow you to select from the combo: QuestionsID is a (unique) autonumber so you get max 1 row in your combo.

    also: since the QuestionsID is unique, the only thing the ClientID= criteria can do is return zero records if you don't have a record with questionsID=me.questionsID AND ClientID=me.clientid

    can you give a hint what you are trying to achieve?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and access wont like it at all if you try "editing" the QuestionsID autonumber by selecting from (or typing in) the combo.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2004
    Posts
    16
    hi again

    The user can make up a number of questions with carying answers..
    ie..
    q1 What colour is ur hair?
    a1 Red,blue,green
    q2 What size is ur head?
    a2 big,small,HUGe

    etc

    Each client has a unique set of asnwers for each question.
    Also each question can have a subsection ie.1b,1c with the same stock answers..

    When the clients questions are brought up and they then go to select an appropriate answer it should onyl bring up the relevent answers for that question..

    They only have to select the asnwer and that value stored..

    Does this help?
    Feeling better a bit as its not what I am doing that is causing the problem..well it is but...
    mat

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about a rethink of the tables

    tblClient:
    IDclient, auto PK
    ....and all the other stuff that belongs to clients

    tblQuestion:
    IDquestion, auto PK
    txtQuestion (what size is your head, what is your shoe size)

    tblAnswer:
    IDanswer, auto PK
    IDquestion FK
    txtAnswer (small, medium, big, huge)
    and probably numAnswer (1, 2, 3, 4) will be useful

    ...if you have a fixed number of answers to each question (like a multiple-choice test) you can also have txtAns1, txtAns2, txtAns3 in tblQuestion but it makes it more difficult to feed the answers into a combo.

    finally:

    tblResults:
    IDresult, auto PK
    IDclient FK
    IDquestion FK
    IDanswer FK

    for a given client & question, feed a combo from tblAnswer and you can select from a combo.

    add a date to tblResults, and you can track your client's hair-loss over the years ;D

    ?? 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
  •