Insert Null column(0) on combo box via Code 1

[Fattire]

I'm trying to give the user the ability to update a combo box to null if they no longer want a value from the combo box on the main table.

varSalespersonID = IIf(IsNull(Me.cboSalesPerson.Column(0)) = True, "", Me.cboSalesPerson.Column(0))

It's the INSERT statement that doesn't like the two single quotes next to themselves ('') as a value for the combo box id on the main table, the text boxes except the two single quotes just fine.

Any way around this?

 

[dhookom]

Why not simply use:

  varSalespersonID = Me.cboSalesPerson.Column(0)

I don't have a clue what you mean by "INSERT statement ..." There is no insert statement in your code.


Duane
Hook'D on Access
MS Access MVP

 

[Fattire]

Because that was giving a runtime 94, "Invalid use of Null" error before it even got to the insert sql:

Here's basically what it looks like, minus 15 other fields:

"INSERT INTO TBL_CUST (CUSTOMER_ID, SALESREP_ID) VALUES ('" & varCustID & "'," & varSalespersonID & ")

When I make the value "[]" on the IIF function and runs it through an UPDATE statement it works just fine, it takes the null - but not in an INSERT sql statement for a new record with no cbo value assigned.

So this works on an UPDATE

varSalespersonID = IIf(IsNull(Me.cboSalesPerson.Column(0)) = True, "[]", Me.cboSalesPerson.Column(0))

But the "[]" doesn't work in the INSERT.

 

[dhookom]

I think you need to use the word Null with code like:

If IsNull(Me.cboSalesPerson.Column(0)) Then
   varSalesPersonID = " Null "
  Else
   'assume SALESREP_ID is text
   varSalesPersonID = """" & Me.cboSalesPerson.Column(0) & """"
End If
' ... "INSERT INTO TBL_CUST (CUSTOMER_ID, SALESREP_ID) VALUES (" & varCustID & "," & varSalespersonID & ")

Duane
Hook'D on Access
MS Access MVP

 

[Fattire]

Nice job Duane...

Here's what it ended up being - works for both updates and inserts.

    If IsNull(Me.cboSalesPerson.Column(0)) Then
        varSalespersonID = " Null "
        Else
        varSalespersonID = """" & Me.cboSalesPerson.Column(0) & """"
    End If

    If IsNull(Me.cboCollector.Column(0)) Then
        varCollectorID = " Null "
        Else
        varCollectorID = """" & Me.cboCollector.Column(0) & """"
    End If

    If IsNull(Me.cboTerms.Column(0)) Then
        varTermsID = " Null "
        Else
        varTermsID = """" & Me.cboTerms.Column(0) & """"
    End If

    If IsNull(Me.txtCreditLimit) Then
        varCreditLimit = " Null "
        Else
        varCreditLimit = 0
    End If

Values ended up looking like this:

VALUES (454454,454,454,'HOOKA BAR 3','','','','', Null , Null , Null ,'','','','','','',0,False,False)