Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    Question Unanswered: Adding an item using limited list in VBA

    Hi all,

    I am trying to add a new item (City) using a Combo Box in a Customers form. The Form is in Access 2002 and tables are MS SQL Server 2000.
    I would like to use a code like one I used when working with Access 2002 tables but without success. If a new City is typed the code asks if
    new city will be added. Can someone suggest a version which would work on a MS SQL Server 2000?

    Thanks

    Dani

    PS

    Here is the VBA code I use when working against Access 2002 tables.

    Dim Response As Integer

    Private Sub City_NotInList(NewData As String, Response As Integer)
    ' Add new City
    On Error GoTo City_NotInList_Err

    Dim conConnection As ADODB.Connection
    Dim StrSQL As String
    Dim iAffected As Integer

    Set conConnection = CurrentProject.Connection
    StrSQL = "INSERT INTO Cities (City) Values " _
    & "(" & "'" & NewData & "'" & ");"

    If LogMsgBox(Msg("Add new city") & " " & NewData _
    & vbCrLf & Msg("Are you sure?"), vbQuestion + vbYesNo, _
    Msg("Add new city"), Form.Name, "City_NotOnList", True) = vbYes Then
    ' Add new city
    conConnection.Execute StrSQL, iAffected, adExecuteNoRecords
    Response = acDataErrAdded
    Else
    ' No
    Response = acDataErrDisplay

    End If

    ' Close ADODB connection
    conConnection.Close

    Exit_City_NotInList:
    Set conConnection = Nothing ' Deassign ADO object
    Exit Sub
    City_NotInList_Err:
    MsgBox Err.Description
    Resume Exit_City_NotInList

  2. #2
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    Talking Found the problem.

    I found where is the proble.

Posting Permissions

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