Combo Box filter value for a 2nd Combo Box

[tbbrown32]

But still no insert.

[tbbrown32]

Yes it displays the correct value in the code, but it never inserts.

[RuralGuy]

IIRC, the field types *must* match the incoming value types.
InternalIncidentID, AgencyID - Me.InternalIncidentID, x
...do they?

[tbbrown32]

InternalIncidentID is a text field, and AgencyID is a number field. the variable x is declared an integer and is used for the AgencyID value.

I'm wondering if I need to stop using Me.InternalIncidentID and maybe declare a variable for it. When I changed the code to read "If x = 4" which is one of the two values x can have, I got a "Too few parameters expected error." That makes me think maybe it's something with the InternalIncidentID, but I did step through the code. This is odd.

[RuralGuy]

What *kind* of number "AgencyID is a number field"? Integer, Long, single, double...

[tbbrown32]

It's an integer

[RuralGuy]

If InternalIncidentID is a text field then I'm pretty sure you will need delimiters:

('" & Me.InternalIncidentID & "',

[tbbrown32]

that's true, I missed that. I added them in but no change to the insert.

[RuralGuy]

I'll have to go get another thinking cap. Mine seems to be dry right now.

[tbbrown32]

No problem! I'm a bit baffled myself

[RuralGuy]

Since your UPDATE query has no qualification (WHERE or HAVING clause) wouldn't it just update every record?

[tbbrown32]

Possibly, but I think since x only has one value, either 4 or 5, it would only update the one record... although I'm not positive...

[tbbrown32]

RuralGuy, I've attached another copy of the db per your request, just to make sure it is the most recent copy of this. The workable combo boxes that don't sort are on the tab title "Program" and the ones that do sort but don't write are on the tab "Page568" Thanks!

[ssanfu]

PMFJI,

I've been looking at the code and have a few comments.

    Dim x As Integer
    x = [Forms]![frmNewMain]![Combo573]
    MsgBox "The value of x is [" & x & "]"
    If x = True Then
        sSQL = "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values ('" & Me.InternalIncidentID & "', " & x & ")"
    Else
        sSQL = "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND  AgencyID = 'x'"
    End If

Referring to the code snippet above:
1) The RED "X" (in the "DELETE" sql statement) should be concatenated to the SQL, just like the append string.

2) The test (in BLUE) "x = True" will ALWAYS return FALSE.
Consider: "X" is declared Integer. But MS has defined a constant TRUE as -1. So, if x = 4 and TRUE = -1, then the test, 4 = -1, is forever FALSE. (AFAIK )


Not that it matters , but I don't understand the reasoning behind the test " If x = True Then". The only time the code will execute is when the combo box "Combo573" (poor name, BTW) is changed. Do you really want to delete a record if you change agencies?
I would add a check to ensure an Incident # was entered before trying to execute the SQL statements.


My $0.02.........


-----------------------------------------------
In table "tblIncidents", "Description" is a reserved word and shouldn't be used as object names.
In same table, there are spaces in field names. Shouldn't use spaces, punctuation or special characters (exception is the underscore) in object names.

[tbbrown32]

Thanks for the info! I will change the DELETE x to be concatenated to the SQL, I fixed the table names and desc field. As far as the code goes, I'm basically trying to get the table to update if x changes. I guess I don't need to delete the record, but will it update when only one part of the composite pk changes like that? If that's possible, then that'd be great!