Let's take them one at a time. On the ProgramTest tab the cbo's that won't write. To where did you want them to write? As best I can tell, there is no Agency Name field in the tblIncidents table which is the RecordSource of the Form.
Let's take them one at a time. On the ProgramTest tab the cbo's that won't write. To where did you want them to write? As best I can tell, there is no Agency Name field in the tblIncidents table which is the RecordSource of the Form.
As far as the three cbo's on three separate SubForms cascading, Their RowSource should be based on a static query that references the limiting cbo.
Did I loose you? Are you still there?
Oops my apologies, I didn't get any notifications via email that there were any replies on the thread, so I assumed there was nothing new.
The programtest tab cbo's were supposed to write to the associative tables. For each level of the organization (agency, department, program) there is an associative table tied back to the Incidents table. There is one for each level because an incident can apply at any (or only one or two) of the three levels. Then agency name field, for example, is connected to the Incidents table via the AgencyIncidents table. The working tab (that doesn't sort subsequent cbo's) writes to each associative table when a value is selected. Hopefully that provides a little more background!
This makes sense, except then when I scroll through different incidents on the main form, the cbo's don't update. Right now they do because they are tied to the incidentID. They've already got the queries in place and cascade just fine, but they don't write to the tables.
I've got some stuff to do this morning. I'll try and get back to you this afternoon.
They will not scroll unless you requery the SubForms, but you may have other issues.This makes sense, except then when I scroll through different incidents on the main form, the cbo's don't update. Right now they do because they are tied to the incidentID. They've already got the queries in place and cascade just fine, but they don't write to the tables.
The only tables/queries you can write to without an Update Query is the RecordSource of the Form.The programtest tab cbo's were supposed to write to the associative tables. For each level of the organization (agency, department, program) there is an associative table tied back to the Incidents table. There is one for each level because an incident can apply at any (or only one or two) of the three levels. Then agency name field, for example, is connected to the Incidents table via the AgencyIncidents table. The working tab (that doesn't sort subsequent cbo's) writes to each associative table when a value is selected. Hopefully that provides a little more background!
Okay, it sounds like I'll either need to do an update query or requery the Subforms. Thanks!
Below is my attempt to Insert the cbo value into the composite table. It currently errors out. Again, I am relatively new to VBA, so I'm sure this has some holes in it! I would gladly welcome any advice. Thanks
Private Sub Combo573_AfterUpdate()
Dim x As String
x = [Forms]![frmNewMain]![Combo573]
If x = True Then
CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values ("Me.InternalIncidentID", "x")"
Else
CurrentDb.Execute "DELETE FROM AgencyIncident (InternalIncidentID, AgencyID)"
WHERE InternalIncidentID = "Me.InternalIncidentID" And AgencyID = "x"
End If
End Sub
-The error seems to be (for now) centered around the Me.InternalIncidentID value. I'm also getting an ambiguous name detected error for Combo573 since I am using it elsewhere in other code.
I'll see if I can fix it.
See if this works:
Notice how using the # (code tags) helps the readability?Code:Private Sub Combo573_AfterUpdate() Dim x As String x = [Forms]![frmNewMain]![Combo573] If x = True Then CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');" Else CurrentDb.Execute "DELETE FROM AgencyIncident (InternalIncidentID, AgencyID)" & _ " WHERE InternalIncidentID = " & Me.InternalIncidentID & " And AgencyID = 'x';" End If End Sub
It's closer to working, but I'm getting the 'Too few parameters. Expected 1." error. It's highlighting this line of code:
Code:CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');"
And actually, I'm displaying a text value in the cbo, but the inserted value is an integer, so I've now changed to Dim x as Integer. Is it possible that the fact that I'm displaying the text value but inserting the numeric value (pk) into the table is what is causing the issue?
edit: I also changed the delete from table reference to tblAgencyIncident because I had it in as AgencyIncident. However, it still errors out.