How insert a 0 if desired

I have the following code in a not in list event of a combobox on a form.

Private Sub cboProofType_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboProofType_NotInList

    Dim intAnswer As Integer
    Dim strSQL As String
    
    intAnswer = MsgBox(NewData & " is not in the list. Would you like to add it?", vbQuestion + vbYesNo, "Description")

    If intAnswer = vbYes Then
              
    strSQL = "INSERT INTO tblProofTypesLU([Description],[CustomerID]) " & "VALUES ('" & NewData & "'," & Forms!frmEstimates.cboCustomerID & ");"
        CurrentDb.Execute strSQL, dbSeeChanges
        Response = acDataErrAdded
    End If

Exit_cboProofType_NotInList:
    Exit Sub

Err_cboProofType_NotInList:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_cboProofType_NotInList

End Sub

Open in new window

This code inserts the cboCustomerID value into the CustomerID field in the table named tblProofTypesLU.  But I want to give the user the ability to add the current Customer ID as it does now BUT allow them to not use the current Customer ID but rather, insert a 0 (zero) if they indicate they don't want to insert the current Customer ID.

How would I do this?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
If intAnswer = vbYes Then
if msgbox("Do you want to use the ID you have") = vbYes then
              
    strSQL = "INSERT INTO tblProofTypesLU([Description],[CustomerID]) " & "VALUES ('" & NewData & "'," & Forms!frmEstimates.cboCustomerID & ");"
else
    strSQL = "INSERT INTO tblProofTypesLU([Description],[CustomerID]) " & "VALUES ('" & NewData & "','0');"
End If
        CurrentDb.Execute strSQL, dbSeeChanges
        Response = acDataErrAdded
    End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vfinatoCommented:
I'm just asking.  Is the Customer ID unique?  If so, you wouldn't want 0's for multiple Customer ID values.
0
John TsioumprisSoftware & Systems EngineerCommented:
TO add...i forgot ....i would prefer instead of 0 to Insert the negative value of the id and filter that your form shows only positive values
Something like this
 strSQL = "INSERT INTO tblProofTypesLU([Description],[CustomerID]) " & "VALUES ('" & NewData & "'," &  -1* Forms!frmEstimates.cboCustomerID & ");"

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SteveL13Author Commented:
Here is what I have.  But If I answer yes to the 2nd msgbox I don't get the CustomerID inserted.  I get a 0 (zero).

    Dim intAnswer As Integer
    Dim strSQL As String
    
    intAnswer = MsgBox(NewData & " is not in the list. Would you like to add it?", vbQuestion + vbYesNo, "Description")

    If intAnswer = vbYes Then
        If MsgBox("Do you want your selection to be limited to this customer?") = vbYes Then
        strSQL = "INSERT INTO tblProofTypesLU([Description],[CustomerID]) " & "VALUES ('" & NewData & "'," & Me.txtCustomerID & ");"
        Else
        strSQL = "INSERT INTO tblProofTypesLU([Description],[CustomerID]) " & "VALUES ('" & NewData & "','0');"
        End If
    CurrentDb.Execute strSQL, dbSeeChanges
    Response = acDataErrAdded
    End If

Open in new window


Also, I'd prefer to have the 2nd msgbox show a Yes / No choice.
0
mbizupCommented:
Compare your code for the second messagebox to the first.  You forgot to add the buttons.  The value returned by the messagebox statement depends on the buttons clicked by the user, so it is understandable that you are getting unexpected results from the second statement which only has the OK button.  vbYes is associated with the YES button, so it needs to be there for your code to function properly:


        If MsgBox("Do you want your selection to be limited to this customer?", vbQuestion + vbYesNo) = vbYes Then
                ' etc 

Open in new window

0
PatHartmanCommented:
insert a 0 (zero) if they indicate they don't want to insert the current Customer ID.
Not a good idea.  The CustomerID should be a foreign Key in the ProofTypes table.  That means you will have defined a relationship and inserting a 0 rather than a valid valid value will cause a referential integrity error.

I question adding CustomerID to this lookup table at all.  It is almost certainly a design flaw.  It looks like you are making a pathological relationship.  tblA has a FK to tblB and tblB has a FK to tblA.
0
hnasrCommented:
May you upload a demo database, comment on what to do, and what to expect?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.