Table Cascade Causes Reset in Yes/No

[timothyd]

I have these tables and fields:

Status Table
Status ID (auto number, primary key)
Status Name (text)

Student Table
Student ID (auto number, primary key)
...

Student Statuses Table
Student ID (value from Student Table)
Status ID (value from Status Table)
Student Status (Yes/No)

Ok, here's my problem. I have the values from the Status table cascade to the Student Statuses table, but when I change the name of one of the statuses, any record that has a 'yes' in the Student Status field is changed to 'no'. Is it supposed to do this or should the Status Name update without affecting the Student Status?

If it should reset all records, is there a way to go around this besides manually going through each record and updating it?

 

[vbaInet]

Copy and paste the sql statement of your update query here. Go to the SQL VIEW in the query design.

 

[timothyd]

I'm not doing the cascade in a query but in a VBA script. Here is what I am doing.

Private Function Save_Status()
  
  On Error GoTo errhandler
  
  Dim rs As Recordset
  Set rs = Me.Recordset
   
  If Pri_Status_Updated = True Then
    If rs.RecordCount = 0 Then
      With rs
        .AddNew
        ![Status Name] = Me.Status_Name.Value
        .Update
      End With
      rs.MoveFirst
      Update_Tables      'method that adds new records for each student ID
                         'and the new status to the Student Status Table
      Pri_Status_Updated = False
      Pri_Status_Name = Me.Status_Name.Value
    ElseIf Check_Status_Name Then      'Here is where the name of the status
      With rs                          'might change and will overwrite the
        .Edit                          'current records in the  Student Status Table
        ![Status Name] = Me.Status_Name.Value
        .Update
      End With
      Pri_Status_Updated = False
      Pri_Status_Name = Me.Status_Name.Value
    End If
  End If
  
ExitHere:
  
  Set rs = Nothing
  
  Exit Function
  
errhandler:
    
  With Err
      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "Save Error"
  End With
      
  Resume ExitHere
  
End Function

 

[vbaInet]

What you're trying to do is more efficiently done using INSERT/UPDATE queries. A recordset is an overkill for this task. Here are links:

Insert query

Update query

Also, your IF recordcount and ELSEIF check_status_name block aren't related so I was wondering why you were nesting it that way?

 

[timothyd]

Also, your IF recordcount and ELSEIF check_status_name block aren't related so I was wondering why you were nesting it that way?

The reason they are seperate is because I have a "New" button that will change some visual settings on the form, such as hide the button being pushed in this code segment. But on the occasion that there are no records in the table I am working with, I can push the "Save" button and it will create a new record. Otherwise if I push the "Save" button when there are records, it should update the Status name to whatever is in the Status_Name text box.

Sorry if this sounds dumb, but I am relatively new to VBA, how do I go about using the update and insert SQL? Do I just create a String statement and use a DoCmd command of some sort? Thanks for the help. If I can get it to work, I have several places that I can update my code.

 

[vbaInet]

Use an ELSE instead of an ELSEIF then inside the ELSE you use an IF check ...

That block of code would look like this:

  If Pri_Status_Updated = True Then
    If rs.RecordCount = 0 Then

        Docmd.SetWarnings False
[COLOR=Red][B]           Docmd.runsql "INSERT INTO ..."[/B][/COLOR]
        Docmd.SetWarnings True

        Update_Tables      
        Pri_Status_Updated = False
        Pri_Status_Name = Me.Status_Name.Value
    Else
        If [COLOR=Blue][B]Len(Check_Status_Name & "") > 0[/B][/COLOR] Then      
             Docmd.SetWarnings False
[COLOR=Red][B]                 Docmd.runsql "UPDATE ..."[/B][/COLOR]
             Docmd.SetWarnings True         
        End If
        
        Pri_Status_Updated = False
        Pri_Status_Name = Me.Status_Name.Value
    End If
  End If

I've highlighted that line in blue because I'm unsure what value you're supposed to be checking against? It's a text field from what I can see but are you checking if that value is not "empty"? If that's the case then what I've written is correct.

 

[timothyd]

The Check_Status_Name does a couple things like check for a blank line or if there is a status with the same name in use, so I do need it to run that method.

I tried changing the code to use and Insert SQL but it still causes the Yes/No values in the Student Statuses Table to all become 'No'. It has something to do with the cascade and I can't quite seem to figure out why the values would be reset when all that is changing is the Status Name.

 

[vbaInet]

You need to set criteria in your UPDATE statement so it knows which records to update. If you don't set the WHERE criteria, it will update the whole table.

UPDATE [Student Statuses Table] SET [Status Name] = something WHERE [Something] = Something

Something of this nature was stated in that link.

 

[timothyd]

I did set the where criteria and it updated it just like it should. But then in the cascade when it updates the other table (the part of the update statement that I am not doing because it should do it on its own) updates the name but then also resets all the values in another field. I could take the cascade off and have an update statement that updates the other table instead of letting the cascade do it.

 

[timothyd]

Well I solved it, but I had to take off the Enforce Referential Integrity, Cascade, and Delete options from the relationship and do it myself in sql statements. Not my optimal way to do it, but I guess it works now. Thanks for the help.

 

[vbaInet]

I've not really had problems with Cascade Updates/Deletes so it could be the way things are set. But glad you found a solution.

 

[vbaInet]

archibald753 - spam reported

 

[timothyd]

Now this is interesting. I had my professor take a look at what was going on and it suddenly was "fixed". Ahh, the power of some people's presence.

 

[vbaInet]

Ensure you have more professors around you next time to save you the hassle