Complex table update question

[mcelligott]

I am attempting to do a tricky update, so here is my best explanation of what I am attempting to do.

In one table I have a bunch of questions broken down into categories (a field indicates the category number), in another table I have the responses (one-to-many relationship). If person A responds with "Y" to the first question in any of the categories, I would like to get all the question ID #'s of the same category from the questions table (the one side) then go back to the response table (the many side) and automatically update all those same questions in the response table to "Y" as well for only person A's responses.

I hope I have been able to explain it well enough to be understood. My head is spinning just trying to think how to explain it.

Thanks in advance for any help that can be offered.

Bob

 

[dhookom]

I think you need to provide some table and field names as well as sample records and desired results.

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

Duane,

Thank god. I am actually using your AYS2000 database as the base to what I am working on. It is a database to keep track of who gets notified for what event and when they were actually notified it happened.

Each survey is broken down into categories (each survey has multiple categories because each event that needs to be tracked may have multiple categories for it that need notifies done). I am using QstnLvl1 field as the category and then QstnLvl2 and QstnLvl3 as the questions and sub-questions. I have added the field visible (yes/no) to the tblResponses.

Here is what I want to do. If person A responds "Y" (in the Rspns field) to the first question in any category (QstnLvl1). I would like to go to tblQuestions, get all other questions (QstnID) in the same category (QstnLvl1) and survey (SrvID) and update the Visible field to True of the records in the tblResponses for person A.

I found out that if I added the visible field, when the person is going through the categories (answering Y or N), if the visible field is changed to true, the questions for that category appear, otherwise the user does not even see them.

I hope that explains things. I think the database you created is an absolute work of genius.

Bob

 

[dhookom]

Bob,
Thanks for the remarks about At Your Survey. I can't not attempt to answer your question. I expect you could add code to the after update of the Rspns control in the subform for entering responses.

Private Sub Rspns_AfterUpdate()
    Dim db As DAO.Database
    Dim strSQL As String
    Dim lngQstnLvl1 As Long
    Dim lngRspnsID As Long
    If Me.Rspns = "Y" Then
        lngQstnLvl1 = Me.QstnLvl1
        lngRspnsID = Me.RspnsID
        Set db = CurrentDb
        strSQL = "UPDATE  tblQuestions INNER JOIN tblResponses ON " & _
            "tblQuestions.QstnID = tblResponses.QstnID " & _
            "SET Visible = -1 " & _
            "WHERE RspnsID =" & lngRspnsID & " AND QstnLvl1 = " & lngQstnLvl1
        db.Execute strSQL, dbFailOnError
        Set db = Nothing
    End If
End Sub

You might need to modify the code to set the lower level questions back to visible=0 if the level 1 question is changed back to N.

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

Hi again Duane,

I finally had a chance to add the code you sent me. I keep getting the following error:

Run-time error '3464':

Data type mismatch in criteria expression

When I go to debug, it points to the statement:

db.Execute strSQL, dbFailOnError

Any thoughts?

Bob

 

[dhookom]

These are the types of errors that I make that you should have the ability to troubleshoot and fix.

QstnLvl1 is text rather than numeric so you need to change the update SQL:

        strSQL = "UPDATE  tblQuestions INNER JOIN tblResponses ON " & _
            "tblQuestions.QstnID = tblResponses.QstnID " & _
            "SET Visible = -1 " & _
            "WHERE RspnsID =" & lngRspnsID & " AND QstnLvl1 = """ & lngQstnLvl1 & """ "

Duane
Hook'D on Access
MS Access MVP