Steve is correct about this line:
If x = True Then
I changed it to:
If x <> 0 Then
... and it does execute the Insert to the table.
Steve is correct about this line:
If x = True Then
I changed it to:
If x <> 0 Then
... and it does execute the Insert to the table.
It works! Thanks! The only problem now is getting the delete to work. I need to figure out something other than having it delete if x is null because I need it to delete if someone changes the agency to a different one...
I apologize for miss leading you on that expression. If instead you had written: "If x Then" it would have executed with any non zero value. Steve can verify this for us. Thanks Steve. Really!
No problem! Anyone have any ideas as to how I could handle the deletes when a different agency is selected in the cbo?
In your current code for the Combo573_AfterUpdate event, the delete SQL deletes the record for the NEW "AgencyID" (x) instead of deleting the record for the OLD "AgencyID". I'm thinking there wouldn't already be a record with the InternalIncidentID/AgencyID (because it is the NEW Agency)??Anyone have any ideas as to how I could handle the deletes when a different agency is selected in the cbo?
So, for a given "InternalIncidentID", can there be multiple agencies?
For example, if "InternalIncidentID" = 100, in tblAgencyIncident, can you have records:
InternalIncidentID
AgencyID
100 4 100 5
??
I thought about using the OldValue property of the combo box, and compare the old "AgencyID" to the new "AgencyID", but the OldValue property is only available with bound combo boxes.
The problem is that on the form/tab, you don't know which AgencyID is currently connected to the incident number because of the unbound combo box.
But I don't think you need the "IF x<>0" test - if you are changing the "AgencyID" for an incident, wouldn't you delete any record with the old "AgencyID" for the incident number, THEN insert a record with the NEW "AgencyID" and incident number???
So a delete, then an append (insert) should happen. (and have a message "Are you sure you want to do this" type of question before the delete/insert)
Otherwise, you will have to use recordset or DLookup to get the current "AgencyID" for an incident and decide what to do.
Just trying to understand the process.......
The way the process works is that an InternalIncidentID will only ever be associated with one AgencyID. My reasoning behind needing the delete functionality was if a user selected the wrong Agency from the combo box and needed to change it and select the correct one from the dropdown. There should never be two AgencyIDs for a single InternalIncidentID. Hopefully that clears it up a little! I should've clarified before, sorry about that
Since there will only be 1 record in "tblAgencyIncident" for a specific "InternalIncidentID",
consider :
Code:Private Sub Combo573_AfterUpdate() On Error GoTo Combo573Error ' Dim x As Integer Dim sSQL As String ' x = Me.Combo573 'Delete all records for "InternalIncidentID" (should only be 1) sSQL = "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "';" ' Debug.Print sSQL CurrentDb.Execute sSQL, dbFailOnError 'insert new record for "InternalIncidentID" and Agency sSQL = "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values ('" & Me.InternalIncidentID & "', " & Me.Combo573 & ");" ' Debug.Print sSQL CurrentDb.Execute sSQL, dbFailOnError ' MsgBox "The value of x is " & x 'requery the subform Me.fsubAgencyIncident.Requery ExitCombo573: Exit Sub Combo573Error: MsgBox Err & ": " & vbCrLf & Err.Description Resume ExitCombo573 ' Me.Combo579 = 0 ' Me.Combo579.Requery ' ' Me.Combo587 = 0 ' Me.Combo587.Requery ' ' Me.Requery End Sub
Thanks Steve! The code is definitely writing to the tables now. Just a couple of things:
The code writes but it won't update as I page through the different InternalIncidentIDs. Is it possible to get the code to do that so it will update accordingly? Also when I open the form, it displays the first record in the InternalIncidentID table, but no values for the Agency, Department, or Program even if they're in the tables already.
Also, when I clear out a combo box (say I needed to delete the record and leave the box blank, it deletes it in the underlying table after it errors out and says "syntax error in the INSERT INTO statement". Is there any way for the DELETE to happen when I clear the box out without having the error pop up?
Thanks again!
I don't understandThe code writes but it won't update as I page through the different InternalIncidentIDs. Is it possible to get the code to do that so it will update accordingly?
The form is bound to "tblIncidents". There are no Agency, Department and Program fields in that table. So those controls are unbound. You might be able to write code to display those values in the unbound combo boxes.Also when I open the form, it displays the first record in the InternalIncidentID table, but no values for the Agency, Department, or Program even if they're in the tables already.
Something like this?Also, when I clear out a combo box (say I needed to delete the record and leave the box blank, it deletes it in the underlying table after it errors out and says "syntax error in the INSERT INTO statement". Is there any way for the DELETE to happen when I clear the box out without having the error pop up?
Code:Private Sub Combo573_AfterUpdate() On Error GoTo Combo573Error ' Dim x As Integer Dim sSQL As String ' x = Me.Combo573 'Delete current record for "InternalIncidentID" sSQL = "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "';" ' Debug.Print sSQL CurrentDb.Execute sSQL, dbFailOnError 'check if an Agency has been selected If Not IsNull(Me.Combo573) Then 'insert new record for "InternalIncidentID" and Agency sSQL = "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values ('" & Me.InternalIncidentID & "', " & Me.Combo573 & ");" ' Debug.Print sSQL CurrentDb.Execute sSQL, dbFailOnError End If 'requery the subform Me.fsubAgencyIncident.Requery ExitCombo573: Exit Sub Combo573Error: MsgBox Err & ": " & vbCrLf & Err.Description Resume ExitCombo573 ' Me.Combo579 = 0 ' Me.Combo579.Requery ' ' Me.Combo587 = 0 ' Me.Combo587.Requery ' ' Me.Requery End Sub
The code writes but it won't update as I page through the different InternalIncidentIDs. Is it possible to get the code to do that so it will update accordingly?
By that I mean that on my form, which displays the InternalIncidentID info, I can use arrows to access the Next Record and look at a different InternalIncidentID. As I do that, the values in Agency, Department, and Program should change accordingly to show the accurate info for each particular InternalIncidentID. Is there any way to do that? Basically it's related to my question about:
Also when I open the form, it displays the first record in the InternalIncidentID table, but no values for the Agency, Department, or Program even if they're in the tables already.
Gak!! I thought I was down to my last brain cell.
I was looking at the tab "Classifications" and couldn't understand how the check boxes were being changed because the check box controls are unbound.
Found it finally!! It is being done in code in the form current event.
So that is how you will have to change the combo boxes on the tab "568". Write code to set the combo boxes, just like the check boxes (in the form current event).
------------------------------------
Couple of other things:
EVERY code module should have
as the top two lines.Code:Option Compare Database Option Explicit
A rule I learned was:
If you create it, destroy it.
If you open it, close it.
So in the sub "UpdateClassificationCheckBoxes" (and others), you create a reference to a database and a recordset. You open a recordset - but NOT the database.
Instead of this
you should useCode:rs.Close db.Close
Code:rs.Close Set rs = Nothing Set db = Nothing
Below is the code I was using to update the check boxes when the InternalIncidentID changed. I know enough to change the table names, but being relatively new to Access how do I update the combo boxes when the code below was based on the check boxes being numbered 1 through 31 (the "For x = 1 to 31 part). I'm sorry to be helpless, but I'm in over me head for sure
Code:Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim x As Integer For x = 1 To 31 Me.Controls("Chk" & x) = False Next x Set db = CurrentDb strSQL = "SELECT ClassificationID FROM tblIncidentClassifications WHERE tblIncidentClassifications.InternalIncidentID = '" & Me.InternalIncidentID & "'" Set rs = db.OpenRecordset(strSQL) Do While Not rs.EOF Me.Controls("Chk" & rs!ClassificationID) = True rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing
Add this code:
Code:Private Sub UpdateCombo573() Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Me.Combo573.Value = Empty strSQL = "SELECT [tblAgencyIncident].[AgencyID]" strSQL = strSQL & " FROM tblAgencyIncident" strSQL = strSQL & " WHERE tblAgencyIncident.InternalIncidentID = '" & Me.InternalIncidentID & "';" ' Debug.Print strSQL Set rs = CurrentDb.OpenRecordset(strSQL) If Not rs.BOF And Not rs.EOF Then Me.Combo573 = rs("AgencyID") End If rs.Close Set rs = Nothing Set db = Nothing End Sub
Add the line in BLUE to this sub:
Code:Private Sub Form_Current() ' This code runs the check box updates as the InternalIncidentID changes ' UpdateClassificationCheckBoxes UpdateActionCheckBoxes UpdateReferralCheckBoxes UpdateRecommendationCheckBoxes UpdateCombo573 End Sub
Great! Thank you so much! The code works great except in those circumstances where I select a value in each cbo (Agency, Department, Program) and the decide to change it to a different Agency (the top of the hierarchy). When that happens the cbos reset but the values for the department and program are still sitting in the tables. However, I need them to delete if that happens because changing the agency means that the other two values that depend on it are no longer valid and should be deleted. Am I trying to do too much in the code by trying to work that in too, or is that possible at all?