Adding information to Query

[Junkie88]

Hello,

I am creating a query (the vba code is below) which extracts certain records from two different tables and shows the results in a form called frmQuestions.

Now, I want to assign values to Session ID, Patient ID and Session Date. These all will be passed as parameters to this module. How can I do it so that all the records that are extracted by this query get assigned the same values of Session ID, Patient ID and Session Date?

Also are these changes going to get written to the table automatically?


Set dbs = CurrentDb
strQueryName = "qryQuestionnaire"

For Each q In dbs.QueryDefs
If q.Name = strQueryName Then
dbs.QueryDefs.Delete strQueryName
End If
Next

strSQL = "SELECT ASK.SESSION_ID, ASK.PATIENT_ID, ASK.SESSION_DATE, QUESTION.QUES_STATEMENT, ASK.ANSWER FROM QUESTION LEFT JOIN ASK ON QUESTION.QUES_ID=ASK.QUES_ID WHERE (((QUESTION.QUESN_ID)= " & num & "));"

'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

DoCmd.OpenForm "frmQuestions", acNormal

 

[fneily]

Use the output of your query as input to an update query:
Example, Ask_question_query is the original query:

UPDATE Ask_Question_query SET Ask_Question_query.Session_ID = "3", Ask_Question_query.Patient_ID = "hhh", Ask_Question_query.Session_Date = #12/1/2008#;

 

[TheAceMan1]

How are ya Junkie88 . . .

It appears your going around the world for what you can do at home! All you really need ia an [blue]Update[/blue] Query/SQL on table ASK, using the criteria you've provided.

Example Routine (leaving it up to you to provide routine arguements):

[blue]Public Sub UpdateAsk(QuesID As Long, SesID As Long, PatID As Long, SesDate As Date)
   Dim Db As DAO.Database, SQL As String
   
   Set Db = CurrentDb
   SQL = "UPDATE ASK " & _
         "SET [Session_ID] = " & SesID & ", " & _
             "[PatientID] = " & PatID & ", " & _
             "[Session_Date] = " & SesDate & " " & _
         "WHERE ([Ques_ID] = " & QuesID & ";"
   Db.Execute SQL, dbFailOnError
   
End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Junkie88]

I guess, I am a little confused. Both answers are telling me to write and update query. But one is updating another query and the second one is updating the table.

So my question is, is it possible to update an existing query?

Secondly, will the update query automatically change things in the table on which the first or updated query is based?

I do not want to overwrite the existing information in the table. So the question, answer and other information are specific to specific to session id, patient id and session date.

But right now I only have question statement and question answer inserted once in the table.

 

[fneily]

If your query is updatable, then it will update the underlying table. That is what my code does.
TheAceMan1 suggestion updates the Ask table directly.
An Update query only updates the fields you want, in this case Session_ID, Patient_ID, and Session_Date.
Concept, the output of a query is a subset of the table(s) the query is using. That's why tables and the output of queries are both known as "recordsets". Now, because of joins, a query maybe non-updatable. See:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

And it wouldn't hurt to try things first to see if that's what you want. You can always make copies of your tables and test the answers given to you.

 

[Junkie88]

The link about updatable queries is no longer available. However, I wrote the following code, but I get the following error on running it. The error says syntax error in Update Statement. And the error occurs at
dbs.Execute UpdateSQL

Can someone help me out?

Public Function RunQuery(num As Integer, str1 As Integer, str2 As Date, str3 As Long)

Dim dbs As dao.Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
Dim q As dao.QueryDef
Dim UpdateSQL As String

Set dbs = CurrentDb
strQueryName = "qryQuestionnaire"

For Each q In dbs.QueryDefs
If q.Name = strQueryName Then
dbs.QueryDefs.Delete strQueryName
End If
Next

strSQL = "SELECT ASK.SESSION_ID, ASK.PATIENT_ID, ASK.SESSION_DATE, QUESTION.QUES_STATEMENT, ASK.ANSWER FROM QUESTION LEFT JOIN ASK ON QUESTION.QUES_ID=ASK.QUES_ID WHERE (((QUESTION.QUESN_ID)= " & num & "));"

'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
UpdateSQL = "UPDATE qryQuestionnaire SET WHERE (((qryQuestionnaire.SESSION_ID) = " & str3 & ") AND ((qryQuestionnaire.PATIENT_ID) = " & str1 & ") AND ((qryQuestionnaire.SESSION_DATE) = " & str2 & "));"
dbs.Execute UpdateSQL
DoCmd.OpenForm "frmQuestions", acNormal
End Function

 

[Golom]

I do not want to overwrite the existing information in the table.

A query contains NO data ... only an SQL statement (and possibly some parameters.) Any attempt to update a query (assuming that it is updatable) will cause the underlying tables to be updated. The only thing you can change that affects the query is the SQL.

The error you are getting us an invalid update statement

UpdateSQL = "UPDATE qryQuestionnaire SET WHERE (((qryQuestionnaire.SESSION_ID) = " & str3 & ") AND ((qryQuestionnaire.PATIENT_ID) = " & str1 & ") AND ((qryQuestionnaire.SESSION_DATE) = " & str2 & "));"

Specifically, you're not SETTING anything. An Update statement has the form

UpdateSQL = "UPDATE qryQuestionnaire 

SET [red]Field1 = Value1, Field2 = Value2, etc.[/red]

WHERE (((qryQuestionnaire.SESSION_ID) = " & str3 & ") 
  AND ((qryQuestionnaire.PATIENT_ID) = " & str1 & ") 
  AND ((qryQuestionnaire.SESSION_DATE) = " & str2 & "));"

The part in [red]red[/red] is missing.

 

[TheAceMan1]

How are ya Golom . . .

I've gone over the thread several times I don't see the quote you posted.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

To All . . .

From the thread origination:

[blue]I am creating a query . . . which extracts certain records from two different tables and shows the results in a form called frmQuestions.

I want to assign values to Session ID, Patient ID and Session Date.

How can I do it so that [purple]all the records that are extracted[/purple] by this query [purple]get assigned the same values of Session ID, Patient ID and Session Date?[/purple][/blue]

This is indicitive of an update query/sql to me.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Golom]

TheAceMan1

I agree ... but coupled with the quote I mentioned, I infer that the OP is under the impression that the query can be updated without updating the underlying tables. Since queries do not contain data, an update applied to a query DOES update the underlying tables.

 

[fneily]

I do not want to overwrite the existing information in the table. So the question, answer and other information are specific to specific to session id, patient id and session date."
Now, I first thought that too. But after reading between the lines, I translated it to mean they do not want to update the OTHER fields beside Session ID, Patient ID and Session Date.

 

[TheAceMan1]

To All:

So between the 1st and 4th post (thanks for pointing it out [blue]Golom[/blue]) we have an [purple]unresolved ambiguity![/purple]

[blue]Junkie88 . . . your thoughts?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Junkie88]

Okay so here is the story.

The ASK table is supposed to store answers to questions along with session ID, session date and patient ID. So I guess what i wanted to say was that I do not want to overwrite the old values of session id, patient id, answers and session date.

I want to fill in the three fields (patient id, session id and session date) before the frmQuestions opens up. So when frmQuestions opens, the only thing that is added by the user is answers to the questions in the answer field. And in the end everything gets saved in the table.

I hope this makes sense.

 

[PHV]

So, you don't want an update query but an append query ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Junkie88]

Okay, now I have written an append query, but now I am getting error at
DoCmd.OpenForm "frmQuestions", acNormal

The error says:
the query cannot be used as a row source.

frmQuestions is based on qryQuestionnaire.

Public Function RunQuery(num As Integer, str1 As Integer, str2 As Date, str3 As Long)
Dim dbs As dao.Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
Dim q As dao.QueryDef

Set dbs = CurrentDb
strQueryName = "qryQuestionnaire"

For Each q In dbs.QueryDefs
If q.Name = strQueryName Then
dbs.QueryDefs.Delete strQueryName
End If
Next

strSQL = "INSERT INTO ASK (SESSION_ID, PATIENT_ID, SESSION_DATE, ANSWER) SELECT ASK.SESSION_ID, ASK.PATIENT_ID, ASK.SESSION_DATE, ASK.ANSWER, ASK.* FROM QUESTION LEFT JOIN ASK ON QUESTION.QUES_ID = ASK.QUES_ID WHERE (((QUESTION.QUESN_ID)=" & 8 & "));"

Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

DoCmd.OpenForm "frmQuestions", acNormal
End Function

 

[Golom]

Your INSERT statement is incorrect

strSQL = "INSERT INTO ASK (SESSION_ID, PATIENT_ID, SESSION_DATE, ANSWER) " & _
"SELECT ASK.SESSION_ID, ASK.PATIENT_ID, ASK.SESSION_DATE, ASK.ANSWER[b][red], ASK.*[/red][/b] FROM QUESTION LEFT JOIN ASK ON QUESTION.QUES_ID = ASK.QUES_ID WHERE (((QUESTION.QUESN_ID)=" & 8 & "));"

The thing in [red]red[/red] should not be there. There must be the same number of fields being SELECTed as are being INSERTed.

I don't understand the logic of inserting values from a table (ASK) back into the same table.

 

[Junkie88]

I don't understand the logic of inserting values from a table (ASK) back into the same table."

I thought that is what PHV asked me to do.

 

[Junkie88]

okay, i have figured something out without using queries, i just update the fields of the forms by hand.