Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Button to add record (2007)

    On my form I have a combo box. Currently I have the combo box filled with data from tblClient. The control source of the combo box is tblPOEntry. I want the user to be able to add a new client if it does not exist in the tblClinet. I have the combobox set to where it does not limit to the current list, but I don't know how to make it add the new client to the tblClient while also adding it to tblPOEntry.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Button to add record (2007)

    To do this, you must set the Limit to List property of the combo box to Yes, and write code for the On Not In List event of the combo box.

    If you search this forum for NotInList, you will find several examples, such as the thread starting at <post#=146,627>post 146,627</post#>.

    (Setting Limit to List to No only allows the user to enter a value that is not in the row source, but this value won't be added to the row source)

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button to add record (2007)

    Can you please tell me what I am missing? It says I have a syntax error.

    Private Sub cmbOrderedFrom_NotInList(NewData As String, Response As Integer)
    If MsgBox(NewData & " does not occur in the vendor list." & vbCrLf & _
    "Do you want to add it?", vbYesNo + vbQuestion) = vbYes Then
    Dim strSQL As String
    strSQL = "INSERT INTO tblVendor (Vendor Name) VALUES (" & _
    Chr$(34) & NewData & Chr$(34) & ")"
    CurrentDb.Execute strSQL
    Response = acDataErrAdded
    Else
    cboPhoneType.Undo
    Response = acDataErrContinue
    End If
    End Sub

    thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Button to add record (2007)

    The code needs to go into the On Not In List event of the combo box. Is cmbOrderedFrom an combo box or a command button?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button to add record (2007)

    It is a combo box and it is in the Not in List Event. I get the syntax error when I enter a vendor that isn't in the list.

    The error message I get says "Syntax error in INSERT INTO statement."

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Button to add record (2007)

    Since the field name contains a space, there should be square brackets around it:

    strSQL = "INSERT INTO tblVendor ([Vendor Name]) VALUES (" & _
    Chr$(34) & NewData & Chr$(34) & ")"

    PS Why do you undo cboPhoneType in code for cmbOrderedFrom?

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button to add record (2007)

    That was a mistake. I copied to code from the other post and didn't change that part yet.

    Adding the square brackets worked. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •