Populating a temporary table 1

[Mgoo]

Hi

So I am trying to populate a temporary table in the same form I created it. I create a temporary table based on a survey the user summited. I want to populate that table with questions. It would look like:

Municipality Question3 Question5 Question 19
Population? Date Created Personnel
Toronto 2 mil December 3 000

I am confused about how to access a table when the table is not where the form's data comes from. Should I create this in a macro? Is there a way to access a record from any table?


Here is my code to create my temporary table for the record.

*********************
Option Compare Database

Private Sub Command12_Click()
Dim sqlStr
Dim strWhere As String
Dim SurveySelect As Integer
Dim result
Dim count As Integer
SurveySelect = 3
Dim trackQuestionID(50) As Integer
count = 0
sqlStr = "CREATE TABLE tempTable ( "
sqlStr = sqlStr & "Municipality longtext, "
result = DLookup("QuestionID", "Question", "SurveyID = " & SurveySelect & strWhere)
Do While (Not IsNull(result))
trackQuestionID(count) = result

sqlStr = sqlStr & "Question" & result & " LONGTEXT, "

strWhere = strWhere & " and QuestionID <> " & result

count = count + 1
result = DLookup("QuestionID", "Question", "SurveyID = " & SurveySelect & strWhere)

Loop
sqlStr = Mid(sqlStr, 1, Len(sqlStr) - 2)
sqlStr = sqlStr & " );"
DoCmd.RunSQL sqlStr
End Sub

 

[gwinn7]

From the looks of it so far, I think you should nix your approach.

If you must create a table temporarily, you could use DAO or ADO create your table like this structure...

CREATE TABLE usertest
(
id autonumber primary key,
userid long NOT NULL,
questionid long NOT NULL,
useranswer integer NOT NULL DEFAULT 0,
answeredtimedate datetime NOT NULL DEFAULT now()
);

Then...

Use an INSERT statement in your code to populate your questions. Example...

...
currentdb.execute "DELETE * FROM usertest"
Do
Currentdb.Execute "INSERT INTO usertest SELECT * FROM survey WHERE surveyid = " & myid
rs.movenext
Loop until rs.eof

Then, your form is already created and recordsource is set to the "usertest" table.

Obviously, you can customize what you want depending on your needs.

Hope that helps,

Gary
gwinn7

 

[Mgoo]

Money in the bank. Thank you so much. I used
CurrentDb.Execute "INSERT INTO tempTable(Field1) SELECT questionID FROM question WHERE surveyID = 3"

It immediatly populates the field, so works really well

 

[Mgoo]

Do you happen to know how to skip a field?

 

[gwinn7]

What do you mean by skip a field?

You can specify which fields you want to insert, update, etc.

Gary
gwinn7

 

[Mgoo]

if i use the currentdb.execute command it will populate the field from the last recently filled table
example, populating city with 1 2 3 4 5 6
City Q1 Q2 Q3
1
2
3
4
5
6

I want to leave a field blank sot hat it would be
City Q1 Q2 Q3
1
2
__ <-- blank
3
4
5
6

 

[gwinn7]

Well, I am a bit confused about why you are still sticking with the table structure of...

CREATE TABLE tempTable
(
City,
Q1,
Q2
);

In the words of Capt Scott (Star Trek II), "No Spock No!".

But hey, its your project. Anyhow, back to your question...

If I am interpreting this properly, then you would need to modify the source recordset of your inserts...

Set rs = currentdb.openrecordset("SELECT * FROM survey WHERE not [city] is null", dbopenforwardonly)

Do

Currentdb.Execute "INSERT INTO usertest SELECT * FROM survey WHERE surveyid = " & rs("surveyid")

rs.movenext

Loop until rs.eof

This would eliminate where your City value would be null.

Gary
gwinn7

 

[gwinn7]

Eek!, circular reference in my prior example, but hey I think you can get the idea.

Gary
gwinn7

 

[Mgoo]

Im trying to get what your saying but the code doesnt lik eme..

grr On another note, I am trying to populate the table with some information from another coloumn. This information is the actual text to the corresponding Questions. Using the following code:

For i = 0 To count - 1 Step 1
CurrentDb.Execute "INSERT INTO tempTable(Question" & trackQuestionID(i) & ") SELECT Question FROM Question WHERE QuestionID =" & trackQuestionID(i)
Next i

'tempTable is the table name
'trackQuestionID(i) is an array of question numbers corresponding to the column I created (e.g. Question1 trackQuestionID is 1, Question 4 trackQuestionID is 4)
'I am searching for the data from table Question field Question that stores the actual question
'I am searching by QuestionID


The result is that the data comes in diagonal
Q1 Q2 Q3
Pop.?
Staff #?
Tier?


Also is there a way to populate the table with a variable instead of another table?

Finally my end result of this is to use this table a report that would look like the following

Municipality Question1 Question3 Question4
SurveyName Q1txt Q3txt Q4txt
Toronto A1txt A3txt A4txt
Quebec A1txt A3txt A4txt

I cannot have this displayed normally since Surveys, Municipalities, answers and questions are all in seperate tables and not all surveys include all questions so forth and so forth.... its kind of a mess

 

[gwinn7]

I am still uncertain as to what your problem is exactly. I see the code and your explanation, but I don't know where things are breaking down. Could you explain where things are going wrong?

Are you saying that instead of ...

Q1 Q2 Q3
Pop.?
Staff #?
Tier?

It should appear...


Q1 Q2 Q3
Pop.? Staff #? Tier?

???

Gary
gwinn7

 

[Mgoo]

Sorry about being unclear

Currently my table appears like:
Table: Actual
___________________________
Q1 Q2 Q3
Pop.?
Staff #?
Tier?
--------------------------

I want it to appear like:

Table: Desired
__________________________
Q1 Q2 Q3
Pop.? Staff #? Tier?
-------------------------

And I am currently using the code i mentioned above

btw thank you very much for your responses and time. Its greatly appreciated.

 

[gwinn7]

Got it.

In that case...

Dim SQLInsert as string
Dim SQLSelect as String
Dim rs as dao.recordset


For i = 0 To count - 1
set rs = currentdb.openrecordset( _
"SELECT question FROM question WHERE questionid = " & trackquestionid(i), dbopenforwardonly)

if len(sqlinsert) > 0 then sqlinsert = sqlinsert & ","
sqlinsert = "Question" & i
if len(sqlselect) > 0 then sqlselect = sqlselect & ","
sqlselect = "'" & trackQuestionID(i) & "'"
Next i

currentdb.execute "INSERT INTO tempTable (" & sqlinsert & ") VALUES (" & sqlselect & ")"


There, that should do it.

Gary
gwinn7

 

[Mgoo]

The code above works perfect but how would I input 'VALUES' with spaces in them. if i want sqlSelect to be = "How many employees" it gives me a runtime error '3075' - Syntax error (missing operator) in query expression 'How many employees'. I know that refers to the lack of spacing so is there a way I can input that.

 

[Mgoo]

Never mind, i got it.. just have to put 'How many employees'

 

[gwinn7]

Good Job Mgoo!

Gary
gwinn7