Results 1 to 2 of 2
  1. #1
    hawkins is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    82

    Combo Box Question

    I have a combo box in a subform that requeries a list of capacitor banks for a given feeder name. The feeder name is in a main form and the combo box relating the name is namescmb. In a subform called CapacitorBanksubform with a control name of ctrCapBank the combo box Capcmb requeries and I am able to add a new capacitor name using this code but it does not include the feeder ID in a Name3ID column in the table containing the Capacitor bank list. I was wondering how to include the feedername in the change of the name. The capacitor bank list is in CapacitorBank table and the Feeder name is in the static table and is related by NameID in Static and Name3ID in CapacitorBank. My database file in attached to make it easier to answer possibly.

    Private Sub Capcmb_NotInList(NewData As String, Response As Integer)
    On Error GoTo Capcmb_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The Capcitor Number " & Chr(34) & NewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _


    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "Avista Utilities")
    If intAnswer = vbYes Then
    strSQL = "INSERT INTO CapacitorBank([CapacitorBank]) " & _
    "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "The new Capacitor number has been added to the list." _
    , vbInformation, "Avista Utilities"
    Response = acDataErrAdded
    Else
    MsgBox "Please choose a Capacitor number from the list." _
    , vbInformation, "Avista Utilities"
    Response = acDataErrContinue
    End If
    Capcmb_NotInList_Exit:
    Exit Sub
    Capcmb_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume Capcmb_NotInList_Exit
    End Sub

  2. #2
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I was wondering how to include the feedername in the change of the name.
    I hope this is just a slip; INSERT INTO appends a new row into the table it does not change an existing row. To effect a change use UPDATE.

    strSQL = "INSERT INTO CapacitorBank([CapacitorBank]) " & _
    "VALUES ('" & NewData & "');"
    Before proceeding I think there is an error above. CapacitorBank!CapacitorBank is defined on the table as numeric yet you are inserting a string! I leave you to sort that one out.

    strSQL = "INSERT INTO CapacitorBank ([Name3ID], [CapacitorBank]) " & _
    "VALUES (" & [Forms]![Main]![Namescmb] & ", " & ??? & ");"

    The above should work for you after you have resolved the ??? string or number concern.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combo/List box question
    By wacky1 in forum Forms
    Replies: 2
    Last Post: 05-22-2011, 09:07 PM
  2. Question-Combo-Box
    By rockrider in forum Forms
    Replies: 1
    Last Post: 02-16-2011, 06:24 PM
  3. Simple combo box question
    By laqa in forum Queries
    Replies: 9
    Last Post: 08-03-2009, 08:32 AM
  4. Combo box question
    By nkenney in forum Forms
    Replies: 1
    Last Post: 07-09-2009, 08:08 PM
  5. A combo box question
    By GeorgeD in forum Forms
    Replies: 3
    Last Post: 05-05-2008, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums