This usually means you have not spelled something correctly. You do know that a SubForm can have a different RecordSource so you could eliminate the SQL, right?
I know that a subform can have a different recordsource, but I was under the impression from this thread that I can't do all 3 things listed below without SQL.
1. Cascade the cbos
2. Write the value selected in each cbo into the appropriate table
3. Have the cbos update based on what Incident I am looking at
If it's possible to do it without SQL, that'd be great, but thought that doing all of this required it.
Incidentally, this code compiles after I updated the table name but doesn't insert.
Code:Private Sub Combo573_AfterUpdate() Dim x As Integer x = [Forms]![frmNewMain]![Combo573] If x = True Then CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');" Else CurrentDb.Execute "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND AgencyID = " & x, dbFailOnError End If End Sub
I suspect you may have a design issue but can't be sure with the sample you supplied so far. It bothers me that you are needing to deal with so many tables at the same time. RDBMS are supposed to take care of that for you.
Agreed, but the design is more complex (specifically referring to the tables the cbos are involved in) because an incident can be an agency, a department, or a program or all three, so I have to relate them back to the Incident table as well as relate them to each other in the hierarchy. Admitted, I find that to be more of a pain from the design standpoint, but there's not much else that can be done that I'm aware of.
Cobbling together kluges to compensate for a questionable design will only get worse. Better to address the design issue early in the process. You might find later on that some issues are just to complex to code around. Just my $0.02.
I see what you're saying (and agree), but unfortunately the way they collect data and the way the data is interrelated makes the current design a necessity. Incidentally I was able to get the code below to compile, but it doesn't insert into the table. Didn't know if you had any thoughts along that line. Thanks!Code:Private Sub Combo573_AfterUpdate() Dim x As Integer x = [Forms]![frmNewMain]![Combo573] If x = True Then CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');" Else CurrentDb.Execute "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND AgencyID = " & x, dbFailOnError End If End Sub
How about:
so you can see what is happening.Code:Private Sub Combo573_AfterUpdate() Dim x As Integer x = [Forms]![frmNewMain]![Combo573] If x = True Then CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');", dbFailOnError Else CurrentDb.Execute "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND AgencyID = 'x'", dbFailOnError End If End Sub
I added the dbFailOnError, but nothing shows up since it doesn't fail, it just doesn't write to the table. Odd, no error but no Insert either.
How about adding error handling to that procedure? I'm sure it is failing. If you need help just ask.
Here's some error handling:
Code:Private Sub Combo573_AfterUpdate() On Error GoTo Combo573Error Dim x As Integer x = [Forms]![frmNewMain]![Combo573] If x = True Then CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');", dbFailOnError Else CurrentDb.Execute "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND AgencyID = 'x'", dbFailOnError End If ExitCombo573: Exit Sub Combo573Error: MsgBox Err & ": " & vbCrLf & Err.Description Resume ExitCombo573 End Sub
Thanks for the error handling code! For some reason, no errors show still, it just doesn't insert. Not really sure where to go from here
Have you single stepped the code to see what is happening?
If no error is generated then it is simply not meeting the selection criteria.
Might not be worth much help but - have you tried printing to immediate window the values that you are trying to insert? Maybe the process isn't collecting the correct values and might not have anything to insert. Just another thought.