none
cell value from a table will be shown in a forms text box RRS feed

  • Question

  • Hi,

    I have a table that has pre filled out questions with multiple choice answers and I need these fields to be shown in a forms text boxes using a randomize function. The numbers have to be taken as unique numbers and cannot be repeated.

    Any suggestions?

    Br.Ross


    • Edited by Rostislav S Monday, August 24, 2015 8:13 AM
    Monday, August 24, 2015 8:11 AM

Answers

  • I was trying to make response short.   Use first query below to create tblEmpResponse and then delete the one record it adds.

    SELECT TOP 1 Tblotazky.ID AS QuestID, [Enter Employee ID] AS EmpID, Null AS AnswrID INTO tblEmpResponse
    FROM Tblotazky;

    Use this query to append 20 random selected question for an employee to answer --

    INSERT INTO tblEmpResponse ( QuestID, EmpID, AnswrID )
    SELECT TOP 20 qryQuestAnswr.ID AS QuestID, [Enter Employee ID] AS EmpID, Null AS AnswrID
    FROM qryQuestAnswr
    ORDER BY Rnd([ID]);


    Build a little, test a little

    Thursday, September 3, 2015 5:05 PM

All replies

  • You need to explain further.   Post examples of the data in your table  and what you want the form to show.

    Build a little, test a little

    Monday, August 24, 2015 8:33 PM
  • Hi Karl,

    To explain what I want:

    I have a table called tblotazky where I have fields (otazky, odpoved1, odpoved2, odpoved3, odpoved4 , vaseodpoved). This table serves as a database that is prefilled by a user.

    Now I would like the fields to be shown in a forms text boxes, but having them randomly generated meaning.

    On open randomly generate one of the lines. Then on add randomly generate a different line, but do not repeat if already asked.

    So it would be something like . In the Form_tblstart txt1 = otazky, txt2=odpoved and so on.

    Hope this helps you :)

    Ross

    Tuesday, August 25, 2015 3:40 AM
  • Anyone any ideas?
    Tuesday, August 25, 2015 10:39 AM
  • I still do not understand what you are trying to do.  Post example data that would be in the table and then displayed on the form.  

    What do you mean by "having them randomly generated meaning?"

    Having fields like odpoved1, odpoved2, odpoved3, and odpoved4 are like having a spreadsheet.   What happens when you need odpoved5 and odpoved6?


    Build a little, test a little

    Tuesday, August 25, 2015 4:31 PM
  • Hi sorry, :(

    I will give you the direct examples of what I have.

    I have a table called Tblotazky where I have columns (otazky, odpoved1, odpoved2, odpoved3, odpoved4 , vaseodpoved). This table has prefilled rows.

     ID        Otazka                odpoved1             odpoved2             odpoved3          odpoved4               vaseodpoved

      1         How are you        Fine                     Ok                       Bad                   So so                      Fine

      2         Do you like fish    Maybe                  Yes                      No                     I hate them             Yes

    For example we could use this.

    And these rows i need to have placed together in the forms(tblstart) textboxes randomly

    So the first has to be on form_load

    Then I have made a function where the answers from this form get written in a different table called tblvýsledky

    so its an on_click event.

    So lets say on load I would like the form to be filled out with the a row completely, but it has to be generatred randomly.

    These cell values have to be placed into textboxes.

    So It should be if random is 1 then :

    (all Txt are textboxes)

    Txt1= How are you

    Txt2 = Fine

    Txt3 = Ok

    Txt4 = Bad

    txt5 = So So

    Txt6 = is a unbound field where the user has to write something.

    Then on_click I have setup the program to clear the text box values and I want to generate a new row with a different question meaning in this case the only possibilty left is the second.

    And the same thing will happen only for the second question

    Ross

    Wednesday, August 26, 2015 4:36 AM
  • I see what you are trying to do. Will work on it later.

    Build a little, test a little

    Wednesday, August 26, 2015 4:47 AM
  • Hi Karl,

    Great this is really appreciated . :)

    Ross

    Wednesday, August 26, 2015 5:34 AM
  • I figured out a plan but I need answers to a couple of questions.

    1- How many questions do you plan to have?   Will it grow?

    2- Do you have a people table with primary key?

    3- Are you wanting to present one question at a time or a screen full?

    4- How do you plan to extract (display) the data after it is collected?


    Build a little, test a little

    Thursday, August 27, 2015 1:27 AM
  • Well for you to understand the whole plan in my head.

    I have a standard login screen(form_loginID) / username + password this login is connected to a table of users. Meaning if the user is not created you do not have access to further info. If you want to get access you have to create a user by clicking a command button that will navigate you to a form called form_tbluser. Once that has been filled out it will be written in a table called tbluser. Then you are looped back to form_loginid

    Once you are logged in it asks you what categories you will want to do (meaning what type of test do you want to do). This is a form called form_typtestu where it has 2 list boxes that depending on what you choose should generate a specific category of test questions from Tblotazky.

    These questions should then be asked in the form_tblstart

    Example:

    (all Txt are textboxes)

    Txt1= How are you

    Txt2 = Fine

    Txt3 = Ok

    Txt4 = Bad

    txt5 = So So

    Txt6 = is a unbound field where the user has to write something.

    So to your questions:

    1.The amount of questions will of course grow depending on howmany rows are placed into tblotazky by the "master" user.

    2. Yes there is a people table called tbluser, which works closely with the login screen form_loginID. The primary key is placed on the user ID.

    3. Yes I need to always have one question presented at a time. So on form_load () I need the txt1-txt5 to be filled out randomly from the categorie chosen in form_typtestu. Once that had been answered you click next (command button) or the timer ends (response = ok or close fill fields out "not answered" go next)

    Meaning you could have a category called : time

    And you will have 50 questions filled out in tblotazky on this topic, but I will want 20 questions to be randomly chosen without asking the same twice.

    4. When the user is in form_tblstart there is a subform called tblvýsledky subform1 where the input will be stacked under each other . Then I will create a report out of this subform that will be printed on completion.

    The data from the user will be stored for future analysis and some graphs will be made.

    Br.Ross 

    Thursday, August 27, 2015 3:34 AM
  • Hi Rostislav,

    For this requirement, you could refer to these steps below:

    1. Have a table that store the user ID with asked question ID
    2. On the form load event, obtain the questions records without asked question
    3. Based on total records count to generate a random number, then base on the random number to get corresponding records.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 31, 2015 6:29 AM
    Moderator
  • Have not forgotten you.

    Once you have created your list of questions that includes the possible answers create this query –

      qryQuestAnswr  --

    SELECT Tblotazky.ID, 1 AS ODPOVED_ID, Tblotazky.odpoved1 AS ODPOVED

    FROM Tblotazky

    UNION ALL

    SELECT Tblotazky.ID, 2 AS ODPOVED_ID, Tblotazky.odpoved2 AS ODPOVED

    FROM Tblotazky

    UNION ALL

    SELECT Tblotazky.ID, 3 AS ODPOVED_ID, Tblotazky.odpoved3 AS ODPOVED

    FROM Tblotazky

    UNION ALL

    SELECT Tblotazky.ID, 4 AS ODPOVED_ID, Tblotazky.odpoved4 AS ODPOVED

    FROM Tblotazky

    UNION ALL

    SELECT Tblotazky.ID, 5 AS ODPOVED_ID, Tblotazky.odpoved5 AS ODPOVED

    FROM Tblotazky;

    First use the query below to create tblEmpResponse (Make Table Query) and then to generate the random questions (Append Query) for an employee  --

    INSERT INTO tblEmpResponse ( QuestID, EmpID, AnswrID )

    SELECT TOP 20 Tblotazky.ID AS QuestID, [Enter Employee ID] AS EmpID, Null AS AnswrID

    FROM Tblotazky

    ORDER BY Rnd([ID]);

    More later on individual's selection of answers.


    Build a little, test a little

    Monday, August 31, 2015 8:15 PM
  • Hi,

    Sorry I was away. I am looking at what you have written. I have created the query for both anmswer and results however I lost you on the other parts.

    Br.Ross

    Thursday, September 3, 2015 10:21 AM
  • I was trying to make response short.   Use first query below to create tblEmpResponse and then delete the one record it adds.

    SELECT TOP 1 Tblotazky.ID AS QuestID, [Enter Employee ID] AS EmpID, Null AS AnswrID INTO tblEmpResponse
    FROM Tblotazky;

    Use this query to append 20 random selected question for an employee to answer --

    INSERT INTO tblEmpResponse ( QuestID, EmpID, AnswrID )
    SELECT TOP 20 qryQuestAnswr.ID AS QuestID, [Enter Employee ID] AS EmpID, Null AS AnswrID
    FROM qryQuestAnswr
    ORDER BY Rnd([ID]);


    Build a little, test a little

    Thursday, September 3, 2015 5:05 PM
  • Hi Karl,

    So I have the data working with a few fixs, but what I do not understand is that when I have input the data in the form and "post it" then the data gets deleted from the table where I recieve it from and it gets written in the table that should be used for evaluation.

    Is there a way to stop this ? since I am only retrieving data from a table , showing it in a form and then duplicating it in a evaluation table.

    Ross

    Friday, September 4, 2015 4:33 AM
  • Nevermind I have fixed that now. :)...

    Karl thanks for your help you have solved the issue :)

    Br.Ross

    Friday, September 4, 2015 5:53 AM